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