/**
* Creates keyword set for given niche
* @param string $name Niche name as it appears in the database
* @param string $table Table name
*/
private function forgeKeywordsForNiche($name = '', $table = '')
{
$class = ucfirst($table);
require_once "../classes/{$class}.php";
$this->keywordsDB->executeSQL("SELECT niche_id FROM niches WHERE niche='{$name}'", $resultArry, $rowsAffected);
$processed = 0;
if ($rowsAffected) {
$niche_id = $resultArry[0]['niche_id'];
$ignore = 'ignore';
$this->keywordsDB->executeNoresSQL("UPDATE m2m_keywords_to_niches SET occurs = 0 WHERE niche_id = {$niche_id}", $resultArry, $ignore);
foreach ($this->fields[$table] as $key => $val) {
$word = $val['sql'] ? $val['sql'] . ($val['alias'] ? " as " . $val['alias'] : "") . ", {$key}" : $key;
$idx = $val['alias'] ? $val['alias'] : $key;
if (isset($this->fields[$table][$key]['prefix']) || isset($this->fields[$table][$key]['suffix'])) {
$idx = "CONCAT('" . $this->fields[$table][$key]['prefix'] . "', " . $word . ", '" . $this->fields[$table][$key]['suffix'] . "')";
$fields .= ($fields ? ", " : "") . $idx . ($val['alias'] ? " as " . $val['alias'] : "") . ", " . $word;
$replacements[$val['alias']] = $key;
} else {
$fields .= ($fields ? ", " : "") . $word;
}
if (isset($this->fields[$table][$key]['sql'])) {
$replacements[$val['alias']] = $key;
}
if ($key != 'l_latitude' && $key != 'l_longitude' && $key != 'l_image') {
$group .= ($group ? ", " : "") . $key;
}
if ($val['mandatory']) {
$condition .= " and {$key}!=''";
}
}
$start = 0;
$stage = 1;
$source_keyword = new SourceKeyword($this->keywordsDB);
$keyword = new $class($this->keywordsDB);
do {
$processed = 0;
echo "Stage " . $stage . "\r\n";
$this->mainDB->executeSQL("SELECT {$fields}, count(*) as occurs FROM {$table} WHERE l_isActive=1 {$condition} GROUP BY {$group} ORDER BY {$group} LIMIT {$start}, " . STEP, $resultArry, $rows);
$time_start = date("U");
$total = $rows;
foreach ($resultArry as $kk => $val) {
$arr = array();
foreach ($val as $k => $v) {
if ($this->fields[$table][$k]['skip'] == true || $k == 'occurs' || is_array($replacements) && in_array($k, $replacements)) {
continue;
}
if ($v == 'NA' || $v == 'N/A') {
$arr[$k] = '';
} else {
$arr[$k] = trim(preg_replace("![ ]{2,}!", " ", str_replace("\t", " ", $v)));
}
}
$positions = count($arr);
$source_keyword->reset();
$source_keyword->source_keyword = implode(' ', array_values($arr));
$source_keyword->origin = 'kwforger';
$source_keyword->save();
for ($i = 1; $i < $this->powers[$positions]; $i++) {
$word = '';
$j = 0;
foreach ($arr as $key => $term) {
$is_mandatory = $this->fields[$table][$key]['mandatory'] == true || isset($replacements[$key]) && $this->fields[$table][$replacements[$key]]['mandatory'] == true;
if ($i & $this->powers[$j] && strlen($term) || $is_mandatory) {
$word .= ($word ? ' ' : '') . $term;
if (isset($replacements[$key])) {
$kw[$replacements[$key]] = $val[$replacements[$key]];
} else {
$kw[$key] = $term;
}
}
$j++;
$prev = $term;
}
if (!isset($keywords[$word])) {
$keywords[$word] = $kw;
}
$keywords[$word]['l_latitude'] = $val['l_latitude'];
$keywords[$word]['l_longitude'] = $val['l_longitude'];
$keywords[$word]['occurs'] = $val['occurs'];
$keywords[$word]['source_keyword_id'] = $source_keyword->source_keyword_id;
unset($kw);
}
$processed++;
unset($resultArry[$kk]);
if (!rand(0, 200)) {
$time_past = date("U") - $time_start;
$time_to_keyword = $time_past / $processed;
$time_left = ($total - $processed) * $time_to_keyword;
echo $processed . "/" . $total . " keywords processed\r\n";
echo "Time left: " . round($time_left) . " seconds\r\n";
}
}
$total = count($keywords);
$processed = 0;
if ($keywords) {
foreach ($keywords as $key => $val) {
foreach ($val as $k => $v) {
$keyword->{$k} = $v;
}
$keyword->keyword = $key;
$keyword->word_count = substr_count($keyword->keyword, ' ') + 1;
$keyword->save();
$keyword->addToNiches(array(array('niche' => $niche_id, 'occurs' => $val['occurs'])));
$keyword->reset();
$processed++;
if (!rand(0, 200)) {
echo $processed . "/" . $total . " keywords processed\r\n";
}
}
unset($keywords);
}
$stage++;
$start += STEP;
} while ($rows > 0);
} else {
return false;
}
}
/**
* @return \yii\db\ActiveQuery
*/
public function getSourceKeywords()
{
return $this->hasMany(SourceKeyword::className(), ['keyword_id' => 'id']);
}