public function getActiveGoal($idSite, $idGoal)
{
$idSite = (int) $idSite;
$idGoal = (int) $idGoal;
$goals = Db::fetchRow("SELECT * FROM " . $this->table . "\n WHERE idsite = {$idSite} AND idgoal = {$idGoal}\n AND deleted = 0 LIMIT 1");
return $goals;
}
public static function get($idvisitor)
{
$row = Db::fetchRow("SELECT * FROM " . Common::prefixTable('chat_personnal_informations') . " WHERE idvisitor = ?", array(@Common::hex2bin($idvisitor)));
if (!$row) {
$row = array('name' => NULL, 'email' => NULL, 'phone' => NULL, 'comments' => NULL);
} else {
$row = ChatCommon::formatRow($row);
}
return $row;
}
/**
* Gets the MySQL table status of the requested Piwik table.
*
* @param string $table The name of the table. Should not be prefixed (ie, 'log_visit' is
* correct, 'piwik_log_visit' is not).
* @return array See http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html .
*/
public function getTableStatus($table)
{
$prefixed = Common::prefixTable($table);
// if we've already gotten every table status, don't issue an uneeded query
if (!is_null($this->tableStatuses) && isset($this->tableStatuses[$prefixed])) {
return $this->tableStatuses[$prefixed];
} else {
return Db::fetchRow("SHOW TABLE STATUS LIKE ?", array($prefixed));
}
}
/**
* Create access table record for the user
* THis will create a record for the user to access at least one site when he logs in first time
*
* @param String $userLogin The user login string
* @param Integer $idSite The ID of the site user is given access to
* @return type Description
*/
public function createAccess($userLogin, $idSite)
{
//TODO: get the list of user access to the sites and update the records accordingly
//check if the record already exists
$sql = "SELECT *\r\n FROM " . Common::prefixTable($this->__PIWIK_ACCESS_TABLE) . " pa\r\n WHERE pa.login = '******' AND pa.idsite = " . $idSite;
if (!($access = Db::fetchRow($sql))) {
$sql = "INSERT INTO " . Common::prefixTable($this->__PIWIK_ACCESS_TABLE) . " (login, idsite, access) \r\n VALUES('" . $userLogin . "', " . (int) $idSite . ", 'view')";
Db::exec($sql);
}
}
public function test_PiwikUserIsNotCreated_IfPiwikUserAlreadyExists()
{
Access::getInstance()->setSuperUserAccess(true);
UsersManagerAPI::getInstance()->addUser(self::TEST_LOGIN, self::TEST_PASS, '*****@*****.**', $alias = false);
Access::getInstance()->setSuperUserAccess(false);
$this->authenticateViaLdap();
$user = Db::fetchRow("SELECT login, password, alias, email, token_auth FROM " . Common::prefixTable('user') . " WHERE login = ?", array(self::TEST_LOGIN));
$this->assertNotEmpty($user);
$this->assertEquals(array('login' => self::TEST_LOGIN, 'password' => md5(self::TEST_PASS), 'alias' => self::TEST_LOGIN, 'email' => '*****@*****.**', 'token_auth' => UsersManagerAPI::getInstance()->getTokenAuth(self::TEST_LOGIN, md5(self::TEST_PASS))), $user);
$this->assertNoAccessInDb();
}
/**
* Computes the total number of unique visitors who visited at least one site in,
* a set of sites and the number of unique visitors that visited all of the sites
* in the set.
*
* Comparison is done in dates for the UTC time, not for the site specific time.
*
* Performance: The SQL query this method executes was tested on a Piwik instance
* with 13 million visits total. Computing data for 4 sites with no
* date limit took 13s to complete.
*
* @param int[] $idSites The IDs of the sites for whom unique visitor counts should be
* computed.
* @param Date $startDate The lower bound of the date range of the visits to check.
* @param Date $endDate The upper bound of the date range of the visits to check.
* @param Segment $segment An optional segment to apply to the visits set before aggregation.
* To supply no segment, use `new Segment()`.
* @return int[] Returns two metrics: **nb_total_visitors** and **nb_shared_visitors**.
*
* **nb_total_visitors** is the total number of unique visitors who visited
* at least one site in the list.
*
* **nb_shared_visitors** is the total number of unique visitors who visited
* every site in the list.
* @throws Exception if less than 2 site IDs are supplied,
*/
public function getCommonVisitorCount($idSites, Date $startDate, Date $endDate, Segment $segment)
{
Log::debug("%s::%s('%s', '%s', '%s', '%s') called", "Model\\DistinctMetricsAggregator", __FUNCTION__, $idSites, $startDate, $endDate, $segment);
if (count($idSites) == 1) {
throw new Exception(Piwik::translate('InterSites_PleasSupplyAtLeastTwoDifferentSites'));
}
$select = "config_id, COUNT(DISTINCT idsite) AS sitecount";
$from = array('log_visit');
$where = 'visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite IN (' . Common::getSqlStringFieldsArray($idSites) . ')';
$orderBy = false;
$groupBy = 'config_id';
$startDateTime = new \DateTime($startDate->toString());
$endDateTime = new \DateTime($endDate->toString());
$bind = array_merge(array($startDateTime->format("Y-m-d 00:00:00"), $endDateTime->format("Y-m-d 23:59:59")), $idSites);
$innerQuery = $segment->getSelectQuery($select, $from, $where, $bind, $orderBy, $groupBy);
$wholeQuery = "SELECT COUNT(sitecount_by_config.config_id) AS nb_total_visitors,\n SUM(IF(sitecount_by_config.sitecount >= " . count($idSites) . ", 1, 0)) AS nb_shared_visitors\n FROM ( {$innerQuery['sql']} ) AS sitecount_by_config";
$result = Db::fetchRow($wholeQuery, $innerQuery['bind']);
// nb_shared_visitors can be NULL if there are no visits
if ($result['nb_shared_visitors'] === null) {
$result['nb_shared_visitors'] = 0;
}
Log::debug("%s::%s() returned '%s'", "Model\\DistinctMetricsAggregator", __FUNCTION__, $result);
return $result;
}
public function getVisitorLastMessage()
{
$arguments = array($this->idsite, @Common::hex2bin($this->idvisitor));
$row = Db::fetchRow("SELECT idvisitor, answerfrom, content, microtime,\n\t\t(SELECT name FROM " . Common::prefixTable('chat_personnal_informations') . " WHERE idvisitor = chat.idvisitor) AS name\n\t\tFROM " . Common::prefixTable('chat') . " AS chat WHERE idsite = ? AND idvisitor = ? ORDER BY microtime DESC LIMIT 1", $arguments);
$row = ChatCommon::formatRow($row);
return $row;
}
public static function get($idSegment)
{
return Db::fetchRow("SELECT * FROM " . Common::prefixTable('segment') . " WHERE idsegment = ?", array($idSegment));
}
/**
* 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());
}
public function heatStatus($idvisitor)
{
$status = Db::fetchRow("SELECT status FROM " . Common::prefixTable("snoopy_visitors_statuses") . " WHERE idvisitor = ? ", array($idvisitor));
if (!empty($status)) {
return $status['status'];
}
}
private function getIdVisit($idVisit)
{
return Db::fetchRow("SELECT * FROM " . Common::prefixTable('log_visit') . " WHERE idvisit = ?", array($idVisit));
}
public static function get($idAutoMsg)
{
return Db::fetchRow("SELECT * FROM " . Common::prefixTable('chat_automatic_message') . " WHERE id = ?", array($idAutoMsg));
}
public function getVisit($idVisit, $allColumns = false)
{
$columns = $allColumns ? "*" : "location_country, location_region, location_city, location_latitude, location_longitude";
$visit = Db::fetchRow("SELECT {$columns} FROM " . Common::prefixTable('log_visit') . " WHERE idvisit = ?", array($idVisit));
return $visit;
}
protected function getUser($login)
{
return Db::fetchRow("SELECT login, password, alias, email, token_auth FROM " . Common::prefixTable('user') . " WHERE login = ?", array($login));
}
public function getTableStatus($tableName)
{
return Db::fetchRow("SHOW TABLE STATUS LIKE ?", array($tableName));
}
