/** * 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);