/**
* Removes related records from the database
*
*/
public function delete()
{
$result = true;
foreach ($this->tables as $table => $options) {
$query = "DELETE FROM " . $table . " WHERE {$options['primary_field']} = " . $this->{$options}['primary_field'];
$this->db->executeNoresSQL($query, $res);
$result &= $res > 0;
}
return $result;
}
/**
* Updates vehicle_details table with images (only records with vendor+model+color)
*
*/
public function addImagesVehicles()
{
$this->mainDB->executeSQL("SELECT l_make, l_model, l_image FROM vehicles WHERE l_image != '' and l_www_id = 16 GROUP BY l_make, l_model", $resultArry, $rowsAffected);
$ignore = 'ignore';
echo "Total images found: " . count($resultArry);
foreach ($resultArry as $result) {
$i++;
$this->keywordsDB->executeNoresSQL("UPDATE vehicle_details SET l_image = '{$result['l_image']}' WHERE l_make = '" . addslashes($result['l_make']) . "' and l_model = '" . addslashes($result['l_model']) . "'", $rowsAffected, $ignore);
}
}
function testCleanup()
{
if ($this->item->gag_id) {
$this->db->executeNoresSQL("DELETE FROM google_ad_groups WHERE gag_id=" . $this->item->gag_id, $rowsAffected);
$this->db->executeNoresSQL("DELETE FROM m2m_keywords_to_google_ad_groups WHERE gag_id=" . $this->item->gag_id, $rowsAffected);
$this->db->executeSQL("SELECT gag_id FROM google_ad_groups WHERE gag_id=" . $this->item->gag_id, $resultArry, $rowsAffected);
$this->assertEqual($rowsAffected, 0);
$this->db->executeSQL("SELECT gag_id FROM m2m_keywords_to_google_ad_groups WHERE gag_id=" . $this->item->gag_id, $resultArry, $rowsAffected);
$this->assertEqual($rowsAffected, 0);
}
}
function newAntiDuplicate($l_countries, $max = 100)
{
$dbConf = new Config("/home/eyezeek/db.conf", "ini");
$optionsTemplate = array('connectTimeout' => 5, 'arrayResult' => true, 'matchMode' => SPH_MATCH_EXTENDED2, 'limits' => $max, 'page' => 1, 'index' => "i_ref_jobs i_delta_jobs", 'groupBy' => array('duplicates', SPH_GROUPBY_ATTR, '@count desc'));
foreach ($l_countries as $target) {
dumpMsg('Starting anti-dup ' . $target['reg']);
$goOnWithNextPage = TRUE;
$page = 1;
$conf = new Conftmp($target['port']);
$sph = new CSphinx($conf, $target['server']);
//var_dump($target);
// ids to keep
$keepIDs = array();
// crc to delete
$crcIDs = array(1);
// ids to set inactive = 0
$duplicateIDs = array(0);
dumpMsg('Grouping ... ');
while ($goOnWithNextPage) {
sleep(1);
$options = $optionsTemplate;
$options['page'] = $page;
$page++;
$sph->resetSphinx();
$sph->setAttribute('what', 'jobs');
$sortParam = 'alps_promoted desc, l_postdate desc';
$options['sortMode'] = array(SPH_SORT_EXTENDED, $sortParam);
$sph->setOptions($options);
$res = $sph->search();
$spx_ok = TRUE;
if (!$res) {
dumpError('Grouping: ' . $sph->getError());
die;
}
$relevantGroups = 0;
if (isset($res['matches'])) {
foreach ($res['matches'] as $oneMatch) {
// if this is > 1 then there are duplicates
$matchCounter = $oneMatch['attrs']['@count'];
if ($matchCounter > 1) {
$keepIDs[] = $oneMatch['id'];
$crcIDs[] = $oneMatch['attrs']['duplicates'];
$relevantGroups++;
} else {
$goOnWithNextPage = FALSE;
}
}
// extra condition to check if there is no more need to traverse next page
if ($relevantGroups < $max) {
$goOnWithNextPage = FALSE;
}
} else {
// no more matches i.e. nothing to do
$goOnWithNextPage = FALSE;
}
// for now simple put manual cut-off to avoid offset overflows
$goOnWithNextPage = FALSE;
}
dumpMsg('Grouped ' . ($page - 1) . ' pages and ' . count($keepIDs) . ' listings have at least 1 duplicate... ');
dumpMsg('Processing IDs ... ');
$goOnWithNextPage = TRUE;
$page = 1;
if (count($keepIDs) == 0) {
dumpMsg('Nothing to do. No duplicated were detected ');
dumpMsg('Finished anti-dup ' . $target['reg']);
return TRUE;
}
while ($goOnWithNextPage) {
// find actual duplicate ids
$options = $optionsTemplate;
$options['page'] = $page;
$page++;
$options['limits'] = 2000;
unset($options['groupBy']);
//var_dump($options);die;
$sph->resetSphinx();
$sph->setAttribute('what', 'jobs');
$sph->setOptions($options);
$sph->setFilter($sph->VALUE_FILTER, array('theid', $keepIDs, TRUE));
$sph->setFilter($sph->VALUE_FILTER, array('duplicates', $crcIDs, FALSE));
$res = $sph->search();
if (!$res) {
dumpError('Error in fetching duplicates: ' . $sph->getError());
die;
}
$resultArray = $sph->getIDs();
if (sizeof($resultArray) < $options['limits']) {
$goOnWithNextPage = FALSE;
}
$duplicateIDs = array_merge($duplicateIDs, $sph->getIDs());
// for now simple put manual cut-off to avoid offset overflows
$goOnWithNextPage = FALSE;
}
dumpMsg('Doing SQL updates and deactivating ' . sizeof($duplicateIDs) . ' duplicates ... ');
$lastDump = 0;
$maxDump = 200;
$tmpIDs = array();
$liveDB = new DBAdapter2(array('host' => $dbConf->live->host, 'username' => $dbConf->live->username, 'password' => $dbConf->live->password, 'schema' => $target['schemali']));
//var_dump($liveDB);
for ($i = 0; $i < sizeof($duplicateIDs); $i++) {
$tmpIDs[] = $duplicateIDs[$i];
$lastDump++;
if ($lastDump == $maxDump || $i + 1 == sizeof($duplicateIDs)) {
sleep(1);
$lastDump = 0;
$sql = "UPDATE jobs SET l_isActive=0 WHERE l_id IN (" . implode(",", $tmpIDs) . ")";
$tmpIDs = array();
try {
$affectedRows = 0;
$lid = 0;
$liveDB->executeNoresSQL($sql, $affectedRows, $lid);
} catch (DBAdapter2Exception $e) {
dumpError(" MYSQL Error: " . $e->getMessage() . " *** QUERY: {$sql}");
die;
}
}
}
unset($sph);
dumpMsg('Finished anti-dup ' . $target['reg']);
}
}
<?php
$lines = file($argv[1]);
require_once '../classes/DBAdapter2.class.php';
require_once '../config/database.php';
$db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA));
preg_match("!(\\d{4})(\\d{2})(\\d{2})!", $argv[1], $matches);
$date = $matches[1] . "-" . $matches[2] . "-" . $matches[3];
foreach ($lines as $key => $line) {
$tmp = explode("\t", trim($line));
$ignore = 'ignore';
$tmp[5] = str_replace("%", "", $tmp[5]);
if ($key && strlen($tmp[0]) && strlen($tmp[1])) {
$db->executeNoresSQL("UPDATE keyword_performance SET\n\t\t\tunique_events = {$tmp['2']},\n\t\t\tevents_value = {$tmp['3']},\n\t\t\ttotal_events = {$tmp['4']},\n\t\t\tbounce_rate = {$tmp['5']},\n\t\t\tvisitor_cost = avg_cpc*(1+{$tmp['5']}),\n\t\t\tvisitor_payout = 0.1*{$tmp['3']}/{$tmp['2']}\n\t\t\tWHERE\n\t\t\t\tcampaign = '{$tmp['0']}' and\n\t\t\t\tkeyword = '" . addslashes($tmp[1]) . "' and \n\t\t\t\tstat_date = '{$date}'\n\t\t\t", $rowsAffected, $ignore);
}
}
$db->executeNoresSQL("UPDATE keyword_performance SET profit = visitor_payout - visitor_cost, profit_total = clicks * visitor_payout - cost WHERE 1", $rowsAffected, $ignore);
private function modCrawlerDB($activate, $vals)
{
$val = $activate ? 'YES' : 'NO';
if (sizeof($vals) == 0) {
return TRUE;
}
$l_oDBCrawler = new DBAdapter2(array('host' => $this->dbConf->crawler->host, 'username' => $this->dbConf->crawler->username, 'password' => $this->dbConf->crawler->password, 'schema' => $this->dbConf->crawler->schema));
$sql = 'UPDATE ' . $this->dbConf->crawler->schema . '.crawler SET exit_time=NOW() WHERE cr_id in (' . implode(',', $vals) . ') ';
$affectedRows = 0;
try {
$l_oDBCrawler->executeNoresSQL($sql, $affectedRows);
} catch (DBAdapter2Exception $e) {
print "\n" . date("y/m/d : H:i:s", time()) . " File:CRunner.php. Line:" . __LINE__ . ". AUTO : SQL ERROR: " . $sql . " *** " . $e->getMessage();
unset($l_oDBCrawler);
return FALSE;
}
unset($l_oDBCrawler);
return TRUE;
}
$tmpresult = NULL;
$tmpresult = array();
$fetchedRows = 0;
$l_aIDs = array();
$o_dbPR->executeSQL($allSQL, $tmpresult, $fetchedRows);
$bExecuted = TRUE;
if ($fetchedRows > 0) {
foreach ($tmpresult as $oneRow) {
$l_aIDs[] = $oneRow['l_id'];
}
}
unset($tmpresult);
if (sizeof($l_aIDs) > 0) {
$fetchedRows = 0;
$allSQL = "UPDATE vehicles SET l_isActive = 0 WHERE l_id IN (" . implode(',', $l_aIDs) . ") ";
$o_dbLI->executeNoresSQL($allSQL, $fetchedRows);
$allSQL = "UPDATE vehicles SET l_isActive = 0, l_i_pending = 15 WHERE l_id IN (" . implode(',', $l_aIDs) . ") ";
$fetchedRows = 0;
$o_dbPR->executeNoresSQL($allSQL, $fetchedRows);
print "\n SYNCH DONE (" . sizeof($l_aIDs) . ")";
} else {
print "\n SYNCH NOT NEEDED (0)";
}
} catch (DBAdapter2Exception $e) {
print "\nFETCH #### " . $e->getMessage() . " #### QUERY: {$allSQL}";
unset($o_dbLI);
unset($o_dbPR);
exit(0);
}
unset($o_dbLI);
unset($o_dbPR);
<?php
$path = dirname(dirname(dirname(__FILE__)));
require_once $path . "/crawler/engine/classes/DBAdapter2.class.php";
require_once $path . "/crawler/engine/classes/Config.php";
$dbConf = new Config("/home/eyezeek/db.conf", "ini");
$liveDB = new DBAdapter2(array('host' => $dbConf->live->host, 'username' => $dbConf->live->username, 'password' => $dbConf->live->password, 'schema' => "ru_eyezeek"));
$update_sql = "\n insert into cache_normalized_realestate (\n l_geo_city, l_assetType, l_deal_type,\n norm_city, norm_asset)\n SELECT sq1.l_geo_city, sq1.l_assetType, sq1.l_deal_type,\n sq1.l_geo_city AS norm_city,\n norm2.normalized_value AS norm_asset\n FROM (\n SELECT r.l_geo_city, r.l_assetType, r.l_deal_type\n FROM realestate AS r\n WHERE r.l_geo_city <> '' AND r.l_assetType<>'' AND r.l_isActive = 1 AND l_rooms > 0\n GROUP BY r.l_geo_city, r.l_assetType, r.l_deal_type\n ) AS sq1\n JOIN np_view_re_normalized AS norm2 ON norm2.keyword = sq1.l_assetType\n AND norm2.property = 'l_assetType' and norm2.normalized_value<>'' ";
// truncate cache
echo "Realestate cache:: truncating...\n";
$cnt = 0;
$lastId = 0;
$liveDB->executeNoresSQL("TRUNCATE TABLE `cache_normalized_realestate` ", $cnt, $lastId);
echo "Realestate cache:: updating...\n";
$cnt = 0;
$lastId = 0;
$liveDB->executeNoresSQL($update_sql, $cnt, $lastId);
foreach ($l_countries as $oneC) {
echo "\n*** Normalizing realestate in " . $oneC['reg'] . " \n ";
/*
$preliveDB = new DBAdapter2(array(
'host'=>$dbConf->prelive->host,
'username'=>$dbConf->prelive->username,
'password'=>$dbConf->prelive->password,
'schema'=>$oneC['schemapl']
));
*/
$liveDB = new DBAdapter2(array('host' => $dbConf->live->host, 'username' => $dbConf->live->username, 'password' => $dbConf->live->password, 'schema' => $oneC['schemali']));
foreach ($oneC['normalize_vals'] as $oneProp) {
echo "\n Normalizing for {$oneProp} : \n";
$sql = "\n update realestate as veh join ( \n\n select v.l_id as thevID, v.{$oneProp}, pv.rpv_value as normValue from realestate as v\n join np_realestate_keywords as kw on v.{$oneProp}=kw.rk_value\n join np_realestate_pv as pv on kw.rk_id = pv.rpv_id and pv.rpv_prop_name = '{$oneProp}'\n where (pv.rpv_value <> '') AND (pv.rpv_value <> v.{$oneProp}) and v.l_isactive=1\n\n\n ) as bigSub\n ON bigSub.thevID = veh.l_id\n set veh.{$oneProp} = bigSub.normValue\n where veh.l_isActive = 1\n ";
try {
/*
$affectedRows=0;
$lid = 0;
$preliveDB->executeNoresSQL($sql,$affectedRows,$lid);
*/
$affectedRows = 0;
$lid = 0;
$liveDB->executeNoresSQL($sql, $affectedRows, $lid);
sleep(2);
} catch (DBAdapter2Exception $e) {
print "\nAUTO MYSQL: Error: " . $e->getMessage() . " *** QUERY: {$sql}";
}
}
unset($liveDB);
//unset($preliveDB);
}
private function pushMessage($msg)
{
if ($msg == '') {
return 0;
}
$l_oConfig = new Config(dirname(dirname(dirname(dirname(__FILE__)))) . "/db.conf", "ini");
$l_oDBCrawler = new DBAdapter2(array('host' => $l_oConfig->crawler->host, 'username' => $l_oConfig->crawler->username, 'password' => $l_oConfig->crawler->password, 'schema' => $l_oConfig->crawler->schema));
$l_oDBCrawler->esc($msg);
$sql = 'INSERT INTO ' . $l_oConfig->crawler->schema . '.processes_messages (pid,message,host_ip) VALUES ';
$sql .= "( " . $this->m_pids . ",'{$msg}' , '" . $this->get_my_IP() . "')";
$affectedRows = 0;
try {
$l_oDBCrawler->executeNoresSQL($sql, $affectedRows);
} catch (DBAdapter2Exception $e) {
unset($l_oDBCrawler);
return 0;
}
unset($l_oDBCrawler);
return 1;
}
<?php
$path = dirname(dirname(dirname(__FILE__)));
require_once $path . "/crawler/engine/classes/DBAdapter2.class.php";
require_once $path . "/crawler/engine/classes/Config.php";
$dbConf = new Config("/home/eyezeek/db.conf", "ini");
$liveDB = new DBAdapter2(array('host' => $dbConf->live->host, 'username' => $dbConf->live->username, 'password' => $dbConf->live->password, 'schema' => "ru_eyezeek"));
$select_ids_sql = "\nselect p.p_id as id_delete, sq.p_id\nfrom np_vehicles_pending as p\nleft join (\n select p2.p_id\n from np_vehicles_pending as p2\n join vehicles as v on p2.p_value= v.l_model and v.l_isActive=1 and p2.p_prop_name = 'l_model'\n group by p2.p_id\n) as sq on p.p_id = sq.p_id\nwhere p.p_prop_name='l_model' and sq.p_id is null\n";
// fetch the ids that need to be deleted
echo "Fetching pending model-keywords for deletion...";
$liveDB->executeSQL($select_ids_sql, $res, $cnt);
$ids = array();
foreach ($res as $row) {
$ids[] = (int) $row['id_delete'];
}
echo $cnt . " found.\nDeleting...";
$limit = 200;
$pages = (int) ceil($cnt / $limit);
for ($page = 0; $page < $pages; $page++) {
$ids_on_page = array_slice($ids, $page * $limit, $limit);
if (count($ids_on_page) == 0) {
continue;
}
echo ".";
$ids_on_page = implode(", ", $ids_on_page);
$sql = "delete from np_vehicles_pending where p_id in ({$ids_on_page}) ";
$liveDB->executeNoresSQL($sql, $cnt, $lastId);
}
echo "Finished!";
$l_aToProcess[$id] = $l_sGeneralSQL . $l_sTmp;
}
unset($result);
unset($recordSet);
try {
$i = 0;
$l_sSQL = 'INSERT INTO vehicles_promoted (l_id,www_id,l_rank,www_name,i_salt,www_redirect_out,www_rank) ';
foreach ($l_aToProcess as $key => &$value) {
$l_sSQL .= "\n\n" . $value . " UNION ";
}
$l_sSQL = rtrim($l_sSQL, "UNION ");
$l_sSQL .= " ON DUPLICATE KEY UPDATE i_salt = " . $salt . ", l_isActive=1 ";
// die();
// eyezeek.vehicles_promoted
//var_dump($l_sSQL);
$o_db->executeNoresSQL($l_sSQL, $rowsAffected, $last_id);
$l_sSQL = "UPDATE vehicles_promoted SET l_isActive = 0 WHERE i_salt <> " . $salt;
$o_db->executeNoresSQL($l_sSQL, $rowsAffected, $last_id);
} catch (DBAdapter2Exception $e) {
print "\n get specific listings from each wwwid";
die($e->getMessage());
}
unset($o_db);
} else {
}
unset($o_db);
return true;
} catch (DBAdapter2Exception $e) {
print "\n Die on first";
die($e->getMessage());
unset($o_db);
*/
// 60 days kill listings
$l_sKill_date = date('Y-m-d', strtotime('180 days ago'));
$l_sKill_date_re = date('Y-m-d', strtotime('180 days ago'));
$l_sKill_date_jo = date('Y-m-d', strtotime('180 days ago'));
try {
//FROM LIVE
print "\n" . date("d/m/y : H:i:s", time()) . " pid: " . getmypid() . ". Killing listings older than {$l_sKill_date}";
print "\n" . date("d/m/y : H:i:s", time()) . " pid: " . getmypid() . ". Vehicles";
$affectedRows = 0;
if ($dbConf->domain->prefix == 'il') {
$l_sSQLl = "UPDATE vehicles SET l_isActive = 0 WHERE l_isActive = 1 AND \n\t\t\t\t\t\t(l_postdate <= '{$l_sKill_date}' OR (l_www_id=7 AND l_postdate < DATE_ADD(NOW(),INTERVAL -55 DAY)))";
} else {
$l_sSQLl = "UPDATE vehicles SET l_isActive = 0 WHERE l_isActive = 1 AND l_postdate <= '{$l_sKill_date}'";
}
$m_oDB->executeNoresSQL($l_sSQLl, $affectedRows);
print "\n" . date("d/m/y : H:i:s", time()) . " pid: " . getmypid() . ". Realestate";
$affectedRows = 0;
$l_sSQLl = "UPDATE realestate SET l_isActive = 0 WHERE l_isActive = 1 AND l_postdate <= '{$l_sKill_date_re}'";
$m_oDB->executeNoresSQL($l_sSQLl, $affectedRows);
print "\n" . date("d/m/y : H:i:s", time()) . " pid: " . getmypid() . ". Jobs";
$affectedRows = 0;
$l_sSQLl = "UPDATE jobs SET l_isActive = 0 WHERE l_isActive = 1 AND l_postdate <= '{$l_sKill_date_jo}'";
$m_oDB->executeNoresSQL($l_sSQLl, $affectedRows);
print "\n" . date("d/m/y : H:i:s", time()) . " pid: " . getmypid() . ". Killing listings by postdate ok";
print "\n\n" . date("d/m/y : H:i:s", time()) . " pid: " . getmypid() . ". Updating postdate for sources with www_update_postdate flag on";
$l_aUpdatePostdate = array('0');
$sql = "SELECT www_id FROM np_wwwsite WHERE www_update_postdate=1";
$tmpresult = NULL;
$fetchedRows = 0;
$m_oDB->executeSQL($sql, $tmpresult, $fetchedRows);
$m_oDBLV_il->executeSQL($l_sSQLl, $tmpresult, $fetchedRows);
if ($fetchedRows > 0) {
$l_aUniques = array();
foreach ($tmpresult as $oneRow) {
$l_aUniques[] = $oneRow['id'];
}
$affectedRows = 0;
$l_sSQLl = "delete from atlas_transactions where id not in (" . implode(',', $l_aUniques) . ")";
$m_oDBLV_il->executeNoresSQL($l_sSQLl, $affectedRows);
print "\natlas trsnsaction table cleaned. Rows affected: " . $affectedRows;
}
$fetchedRows = 0;
$tmpresult = array();
$l_sSQLl = "select id from google_transactions group by request";
$m_oDBLV_ru->executeSQL($l_sSQLl, $tmpresult, $fetchedRows);
if ($fetchedRows > 0) {
$l_aUniques = array();
foreach ($tmpresult as $oneRow) {
$l_aUniques[] = $oneRow['id'];
}
$affectedRows = 0;
$l_sSQLl = "delete from google_transactions where id not in (" . implode(',', $l_aUniques) . ")";
$m_oDBLV_ru->executeNoresSQL($l_sSQLl, $affectedRows);
print "\ngoogle trsnsaction table cleaned. Rows affected: " . $affectedRows;
}
unset($m_oDBLV_il);
unset($m_oDBLV_ru);
} catch (DBAdapter2Exception $e) {
print "\n Error: " . $e->getMessage();
}
echo "\n Finished \n";
print "\n" . date("d/m/y : H:i:s", time()) . " Response status name: {$l_sResponseStatusName}";
print "\n" . date("d/m/y : H:i:s", time()) . " COORDINATES : Setting dummy coordinates. 32.07,34.81";
//impossible to fetch coordinates for this address
$l_sLongitude = "32.070000";
$l_sLatitude = "34.810000";
}
}
}
// now check to see if coords are good and if so save
if ($l_sLongitude != 0 && $l_sLatitude != 0) {
//do save
print "\n" . date("d/m/y : H:i:s", time()) . " Updating database...";
$l_sSQL = "UPDATE toProcessGeoRealEstate SET l_ischecked=1, l_longitude='" . $l_sLongitude . "' , l_latitude='" . $l_sLatitude . "' WHERE l_id = " . $l_aOneRow['l_id'];
$affectedRows = 0;
$lastListID = 0;
$m_oDB->executeNoresSQL($l_sSQL, $affectedRows, $lastListID);
print "\n" . date("d/m/y : H:i:s", time()) . " Database updated.";
}
$l_sHtml->clear();
unset($l_sHtml);
} else {
//page load unsuccessful write a message which can be copy pasted
print "\n** ERROR START";
print "\nListing ID: " . $l_aOneRow['l_id'];
print "\nType: CURL";
print "\nURL: {$l_sURLTemp}";
print "\nReturned Statud: {$l_iStatus}";
print "\n** ERROR END";
}
}
}
} else {
$l_sSQLSequence .= "'" . $l_aAreaData['gui_display'] . "',";
}
if (strpos($l_aAreaData['db_area'], "'") != FALSE) {
$l_sSQLSequence .= '"' . $l_aAreaData['db_area'] . '",';
} else {
$l_sSQLSequence .= "'" . $l_aAreaData['db_area'] . "',";
}
$l_sSQLSequence .= $l_aAreaData['count'] . ',' . $l_aAreaData['deal_type'] . ')';
$l_aSequence[] = $l_sSQLSequence;
}
}
//".$dbConf->live->schema."
$sql1 = "TRUNCATE TABLE popular_realestate_areas ";
$sql2 = "INSERT INTO popular_realestate_areas (`pra_display`, `pra_area`, `pra_count`, `pra_deal_type`) VALUES " . implode(',', $l_aSequence);
$tmpresult = NULL;
$tmpresult = array();
$fetchedRows = 0;
$lastListID = 0;
try {
echo "\n Do insertions into popular_realestate_areas";
$o_db->executeNoresSQL($sql1, $affectedRows, $lastListID);
sleep(2);
$lastListID = 0;
$o_db->executeNoresSQL($sql2, $affectedRows, $lastListID);
} catch (DBAdapter2Exception $e) {
print "\n" . date("d/m/y : H:i:s", time()) . " popular_realestate_areas ERROR: " . $e->getMessage();
unset($m_oDB);
die('SQL ERROR');
}
echo "\n Script finished OK \n";
<?php
$lines = file($argv[1]);
require_once '../classes/DBAdapter2.class.php';
require_once '../config/database.php';
$db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA));
preg_match("!(\\d{4}-\\d{2}-\\d{2})!", $argv[1], $matches);
$date = $matches[1];
foreach ($lines as $key => $line) {
$tmp = explode("\t", trim($line));
$ignore = 'ignore';
if ($key) {
$db->executeNoresSQL("REPLACE INTO keyword_performance (campaign, ad_group, keyword, clicks, impressions, ctr, avg_cpc, cost, avg_pos, stat_date) VALUES ('{$tmp['0']}', '" . addslashes($tmp[1]) . "', '" . addslashes($tmp[2]) . "', '{$tmp['14']}', '{$tmp['13']}', '{$tmp['15']}', '{$tmp['16']}', '{$tmp['18']}', '{$tmp['19']}', '{$date}')", $rowsAffected, $ignore);
}
}
<?php
$path = dirname(dirname(dirname(__FILE__)));
require_once $path . "/crawler/engine/classes/DBAdapter2.class.php";
require_once $path . "/crawler/engine/classes/Config.php";
$dbConf = new Config("/home/eyezeek/db.conf", "ini");
$liveDB = new DBAdapter2(array('host' => $dbConf->live->host, 'username' => $dbConf->live->username, 'password' => $dbConf->live->password, 'schema' => "ru_eyezeek"));
$update_sql = "\ninsert into `popular_makes` (pm_make, pm_model, pm_make_count)\nselect l_make as pm_make, l_model as pm_model,\n count(l_id) as pm_make_count\nfrom vehicles \nwhere l_isActive = 1 group by l_make, l_model\norder by count(l_id) desc limit 14";
echo "Popular vehicles:: truncating...\n";
$cnt = 0;
$lastId = 0;
$liveDB->executeNoresSQL("TRUNCATE TABLE `popular_makes` ", $cnt, $lastId);
echo "Popular vehicles:: updating...\n";
$cnt = 0;
$lastId = 0;
$liveDB->executeNoresSQL($update_sql, $res, $lastId);
$update_sql = '
insert into `popular_realestate_areas`
select concat("%s в г.", l_geo_city) as pra_display,
l_geo_city as pra_area, count(l_id) as pra_count, l_deal_type as pra_deal_type
from realestate
where l_deal_type = %d and l_isActive=1
and l_assettype="Квартира"
group by l_geo_city
order by count(l_id) desc
limit 7
';
echo "Popular realestate:: truncating...\n";
$cnt = 0;
$lastId = 0;
$liveDB->executeNoresSQL("TRUNCATE TABLE `popular_realestate_areas` ", $cnt, $lastId);
include_once '/home/eyezeek/crawlers/www/crawler/engine/classes/CCURL.class.php';
include_once '/home/eyezeek/crawlers/www/crawler/engine/classes/CExchangeRate.class.php';
// which currencies to update for
$l_aCurrencies = array('USD', 'EUR');
$l_countries = array(array('reg' => 'RUSSIA', 'schema' => 'il_prelive', 'currency' => 'ILS'), array('reg' => 'ISRAEL', 'schema' => 'ru_prelive', 'currency' => 'RUB'));
$dbConf = new Config(dirname(dirname(__FILE__)) . "/db.conf", "ini");
foreach ($l_countries as $oneRegion) {
$l_currence = $oneRegion['currency'];
print "\n" . date("y/m/d : H:i:s", time()) . " INITIALIZING EXCHANGE RATE FOR: " . $l_currence;
$l_exrate = new CExchangeRate($l_currence);
$m_oDBpre = new DBAdapter2(array('host' => $dbConf->prelive->host, 'username' => $dbConf->prelive->username, 'password' => $dbConf->prelive->password, 'schema' => $oneRegion['schema']));
foreach ($l_aCurrencies as $oneCurrency) {
print "\n" . date("y/m/d : H:i:s", time()) . " GETTING RATE FOR: " . $oneCurrency . " to 1 {$l_currence}";
$l_dMultiplier = $l_exrate->get_rate($oneCurrency);
if (is_numeric($l_dMultiplier) && $l_dMultiplier > 0) {
$exbankupdateSQL = "INSERT INTO exbank (curr_id,multiplier) VALUES ('" . $oneCurrency . "','" . $l_dMultiplier . "') ON DUPLICATE KEY UPDATE multiplier=" . $l_dMultiplier;
try {
$affectedRows = 0;
$lastListID = 0;
$m_oDBpre->executeNoresSQL($exbankupdateSQL, $affectedRows, $lastListID);
} catch (DBAdapter2Exception $e) {
print "\n" . date("y/m/d : H:i:s", time()) . " SQL ERROR UPDATING CURRENCY FOR: {$oneCurrency} *** " . $sql . " *** " . $e->getMessage();
}
} else {
print "\n" . date("y/m/d : H:i:s", time()) . " MULTIPLIER ERROR UPDATING CURRENCY FOR: " . $oneCurrency . " *** " . $l_dMultiplier;
}
}
unset($l_exrate);
unset($m_oDBpre);
echo "\n UPDATING " . $oneRegion['reg'] . " CURRENCIES FINISHED \n";
}
function disableExpired($p_sTerritory, $p_sSection, $p_aExpiredIDs)
{
$liveDB = new DBAdapter2(array('host' => '192.168.240.15', 'username' => 'eyezeek', 'password' => 'p!n6access', 'schema' => $p_sTerritory . '_eyezeek'));
$sql = 'UPDATE ' . $p_sSection . ' SET l_isactive=0 WHERE l_id in (' . implode(',', $p_aExpiredIDs) . ')';
unset($p_aExpiredIDs);
$p_aExpiredIDs = NULL;
try {
$affectedRows = 0;
$lid = 0;
$liveDB->executeNoresSQL($sql, $affectedRows, $lid);
printer("Affected " . $affectedRows);
} catch (DBAdapter2Exception $e) {
die(" MYSQL Error: " . $e->getMessage() . " *** QUERY: {$sql}");
}
unset($sql);
$sql = NULL;
}
<?php
$path = dirname(dirname(dirname(__FILE__)));
require_once $path . "/crawler/engine/classes/DBAdapter2.class.php";
require_once $path . "/crawler/engine/classes/Config.php";
$dbConf = new Config("/home/eyezeek/db.conf", "ini");
$liveDB = new DBAdapter2(array('host' => $dbConf->live->host, 'username' => $dbConf->live->username, 'password' => $dbConf->live->password, 'schema' => "il_eyezeek"));
echo "Updating counts...\n";
$cnt = 0;
$lastId = 0;
// -------------vehicles
$update_sql = "\n\tupdate np_counts\n\tset prop_value=(\n\t select count(l_id)\n\t from vehicles\n\t where l_isActive=1\n\t)\n\twhere prop_name='total_vehicles'\n";
//-------------- realestate - all
$liveDB->executeNoresSQL($update_sql, $cnt, $lastId);
$update_sql = "\n\tupdate np_counts\n\tset prop_value=(\n\t select count(l_id)\n\t from realestate\n\t where l_isActive=1\n\t)\n\twhere prop_name='total_realestate'\n";
$liveDB->executeNoresSQL($update_sql, $cnt, $lastId);
// -------------realestate - rent
$update_sql = "\n\tupdate np_counts\n\tset prop_value=(\n\t select count(l_id)\n\t from realestate\n\t where l_isActive=1 and l_deal_type = 0\n\t)\n\twhere prop_name='total_realestate_rent'\n";
$liveDB->executeNoresSQL($update_sql, $cnt, $lastId);
// -------------realestate - sale
$update_sql = "\n\tupdate np_counts\n\tset prop_value=(\n\t select count(l_id)\n\t from realestate\n\t where l_isActive=1 and l_deal_type = 1\n\t)\n\twhere prop_name='total_realestate_sale'\n";
$liveDB->executeNoresSQL($update_sql, $cnt, $lastId);
// JOBS
$update_sql = "\n\tupdate np_counts\n\tset prop_value=(\n\t select count(l_id)\n\t from jobs\n\t where l_isActive=1\n\t)\n\twhere prop_name='total_jobs'\n";
$liveDB->executeNoresSQL($update_sql, $cnt, $lastId);
