function testLoad() { $id = $this->vehicle->keyword_id; $vehicle = $this->vehicle; $vehicle->reset(); $vehicle->load($id); $this->db->executeSQL("SELECT * FROM keywords LEFT JOIN vehicle_details USING(keyword_id) WHERE keywords.keyword_id={$id}", $a, $b); foreach ($a[0] as $key => $val) { $this->assertEqual($vehicle->{$key}, $val, "{$key}: {$vehicle->{$key}} != {$val} "); } }
function testCreate() { $this->item->giag_name = 'Test Ad Group'; $this->item->giag_campaign_id = 1; $this->item->giag_destination_url = "http://www.sdfsdaf.com/asfg/csvbdfb"; $this->item->giag_display_url = DISPLAY_URL; $this->assertTrue($this->item->save()); $this->db->executeSQL("SELECT * FROM google_image_ad_groups WHERE giag_id = " . $this->item->giag_id, $resultArry, $rowsAffected); foreach ($resultArry[0] as $key => $val) { $this->assertEqual($val, $this->item->{$key}); } }
function testBuildVehiclesAdGroup() { require_once "../config/keyword_forger.php"; $this->maindb->executeSQL("SELECT * FROM vehicles WHERE l_isActive = 1 ORDER BY RAND() LIMIT 1", $res, $bulk); $record = $res[0]; $item = new GoogleAdGroup($this->db); $item->buildVehiclesAdGroup($record); $this->assertEqual($item->gag_name, $record['l_make'] . " " . $record['l_model'] . " " . $item->gag_id . ":" . $item->gag_l_id); $this->db->executeSQL("SELECT count(*) as cnt FROM m2m_keywords_to_google_ad_groups WHERE gag_id = " . $item->gag_id, $res, $bulk); $this->assertTrue($res[0]['cnt'] > 0); $item->delete(); }
/** * Creates database record according to current instance properties. * */ private function insert() { $result = TRUE; foreach ($this->tables as $table => $options) { if (count($options['unique'])) { $query = ''; foreach ($options['unique'] as $val) { $query .= ($query ? " and " : "") . $val . "='" . addslashes($this->{$val}) . "'"; } $query = "SELECT {$options['primary_field']} FROM {$table} WHERE {$query}"; $this->db->executeSQL($query, $res, $cnt); if ($cnt) { $this->{$options}['primary_field'] = $res[0][$options['primary_field']]; continue; } } $names = ''; $values = ''; $query = "INSERT INTO " . $table . " "; foreach ($options['fields'] as $val) { if (!($val == $options['primary_field'] && $options['parent_table'] == true)) { $names .= ($names ? ", " : '') . $val; $values .= ($values ? ", " : '') . "'" . addslashes($this->{$val}) . "'"; $this->tables[$table]['data'][$val] = $this->{$val}; } } $query .= "({$names}) VALUES ({$values})"; $this->db->executeNoresSQL($query, $res, $cnt); $this->{$options}['primary_field'] = $cnt; $result &= $cnt > 0; } return $result; }
public static function get_site_rank($a_iWID) { $dbConf = new Config(dirname(dirname(dirname(dirname(__FILE__)))) . "/db.conf", "ini"); $db = new DBAdapter2(array('host' => $dbConf->live->host, 'username' => $dbConf->live->username, 'password' => $dbConf->live->password, 'schema' => $dbConf->live->schema)); $sql = "SELECT www_rank FROM np_wwwsite WHERE www_id = {$a_iWID} "; try { $tmpresult = array(); $fetchedRows = 0; $db->executeSQL($sql, $tmpresult, $fetchedRows); if ($fetchedRows == 1) { $l_iResult = $tmpresult[0]['www_rank']; } else { $l_iResult = 1; } } catch (DBAdapter2Exception $e) { print "\n SQL screw up in CSite.Class.php " . $sql; $l_iResult = 1; } /* foreach($l_aResult as $key => $value){ if($key != "wr_wwwid"){ $l_iSum += $value; } } */ //return $l_iSum / (sizeof($l_aResult) - 1); return (int) $l_iResult / 2500 * 100; }
/** * 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); } }
// take only 500 listings at a time $l_iLimit = 500; $l_iOffset = 0; //make the main url $l_sURL = "http://gis.il.emea.atlasct.com/gis/geocoder?q={address}&lang=heb&countrycode=isr&key=Gu5rdksS86zC39Sa&output=KML&api=3.0&projection=latlong&ie=utf-8"; //make the main sql $l_sMainSQL = " SELECT l_id, l_address, l_city FROM toProcessGeoRealEstate WHERE l_ischecked=0 LIMIT "; $l_iCounter = 0; //do until results found = limit (i.e. all listings traversed) do { try { unset($l_aResultset); $l_aResultset = array(); $l_iFetchedRows = 0; $l_sSQL = $l_sMainSQL . $l_iLimit; $m_oDB->executeSQL($l_sSQL, $l_aResultset, $fetchedRows); foreach ($l_aResultset as $l_aOneRow) { $l_iCounter++; print "\n===================\n"; print "\n" . date("d/m/y : H:i:s", time()) . " #{$l_iCounter} [L_ID:" . $l_aOneRow['l_id'] . "]"; //first time calling full address $l_sAddress = trim($l_aOneRow['l_address']); //$l_sAddress = str_replace(',',' ',$l_sAddress); //$l_sAddress = str_replace(' ','+',$l_sAddress); $l_sLongitude = 0; $l_sLatitude = 0; if ($l_sAddress) { //make url $l_sURLTemp = str_replace('{address}', $l_sAddress, $l_sURL); //call curl to get xml $a_sResult = '';
private function readFromDB() { $o_db = new DBAdapter2(array('host' => $this->dbConf->crawler->host, 'username' => $this->dbConf->crawler->username, 'password' => $this->dbConf->crawler->password, 'schema' => $this->dbConf->crawler->schema)); $sql = "SELECT cr_id,cr_name,cr_path,cr_section,cr_rule,cr_rule_param,cr_expiredays FROM " . $this->dbConf->crawler->schema . ".crawler WHERE cr_status = 1 and cr_type = " . $this->crType . " AND cr_section = '" . $this->sSection . "' AND cr_host_ip = '" . $this->theIP . "' order by cr_counter ASC"; $recordSet = ''; $affectedRows = 0; try { $o_db->executeSQL($sql, $recordSet, $affectedRows); if ($affectedRows > 0) { foreach ($recordSet as $result) { $tmpOneCrawler['id'] = $result['cr_id']; $tmpOneCrawler['name'] = $result['cr_name']; $tmpOneCrawler['path'] = $result['cr_path']; $tmpOneCrawler['section'] = $result['cr_section']; $tmpOneCrawler['rule'] = $result['cr_rule']; $tmpOneCrawler['rparam'] = $result['cr_rule_param']; $tmpOneCrawler['expire'] = $result['cr_expiredays']; $tmpOneCrawler['ppid'] = $this->ppid; $tmpOneCrawler['dbid'] = -1; $tmpOneCrawler['pid'] = -1; $tmpOneCrawler['active'] = FALSE; $tmpOneCrawler['inc'] = 0; $this->m_aCrawlers[] = $tmpOneCrawler; } unset($o_db); } unset($o_db); return TRUE; } 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($o_db); return FALSE; } }
define(INC_PATH, '/home/eyezeek/www/synchronizer/include/'); include_once INC_PATH . 'include.php'; print "\n\n STARTED SYNCHRONIZATION"; $dbConf = new Config("/home/eyezeek/db.conf", "ini"); //create a db object to prelive $o_dbPR = new DBAdapter2(array('host' => $dbConf->prelive->host, 'username' => $dbConf->prelive->username, 'password' => $dbConf->prelive->password, 'schema' => $dbConf->prelive->schema)); $o_dbLI = new DBAdapter2(array('host' => $dbConf->live->host, 'username' => $dbConf->live->username, 'password' => $dbConf->live->password, 'schema' => $dbConf->live->schema)); //do all insertions l_i_pending =3 $getId = 0; $allSQL = "SELECT l_id FROM vehicles WHERE l_isActive = 0 AND l_i_pending = 10 "; try { $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) . ")";
<?php require_once '../config/database.php'; require_once '../config/keyword_forger.php'; require_once '../classes/DBAdapter2.class.php'; foreach ($ad_types as $class) { require_once '../classes/' . $class . '.php'; } require_once '../classes/GoogleCampaign.php'; require_once '../classes/Keyword.php'; $db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA)); $campaign = new GoogleCampaign($db); $campaign->ga_campaign_name = CAMPAIGN_NAME_BASE . " " . date("Y-m-d H i"); $campaign->save(); $db->executeSQL("SELECT keyword_id, keyword, occurs, word_count, source_keyword_id FROM m2m_keywords_to_niches a left join keywords b using(keyword_id)\nWHERE niche_id = " . NICHE . " and occurs > " . GROUP_SIZE . " and word_count > 2 order by word_count desc, occurs desc", $resultArry, $rowsAffected); $total = $rowsAffected; $left = $total; echo $total . " keywords\r\n"; $start_time = date('U'); $kws = array(); $keyword = new Keyword($db); $fp = fopen("../output/" . str_replace(" ", "_", $campaign->ga_campaign_name) . ".csv", "a+"); fputs($fp, "Campaign,Daily Budget,Language,Location,Proximity Targets,Ad Schedule,Ad Group,Max CPC,Keyword Type,Keyword,Headline,Description Line 1,Description Line 2,Display URL,Destination URL\r\n"); foreach ($resultArry as $val) { $adgroup = $val['keyword']; fputs($fp, "{$campaign->ga_campaign_name},20,wi,IL,,,{$adgroup}," . MAX_CPC . "\r\n"); foreach ($ad_types as $class) { $ad[$class] = new $class($db); $ad[$class]->ga_campaign_id = $campaign->ga_campaign_id; $ad[$class]->build($val['keyword_id'], $table, $main_table); fputs($fp, "{$campaign->ga_campaign_name},,,,,,{$adgroup},,,," . $ad[$class]->ga_headline . "," . $ad[$class]->ga_description1 . "," . $ad[$class]->ga_description2 . "," . $ad[$class]->ga_display_url . "," . $ad[$class]->ga_destination_url . "\r\n");
$l_sSQLl = "select id from atlas_transactions group by request"; $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(); }
$salt = crc32(strtotime('now')); /* 1. take all wwwid where insudtry = automotive + rules 2. From 1 take random X listings that satisfy rules in 1 (X is defined for all www) 3. Place resultset from 2. in new table (defined) 4. increment all flags */ $l_sSection = "vehicle"; $l_sGeneralSQL = 'SELECT v.l_id, v.l_rank FROM vehicles v WHERE l_isActive =1 AND (1=1) '; $l_sSQL = "SELECT www_id as id, www_name as wname, www_promoted_picture as picture, www_promoted_price as price,\n www_promoted_date as pdate, www_promoted_daysfrom as dfrom, www_promoted_daysto as dto, www_redirect_out as wredirect, www_promoted_listings as lnumber, www_rank as wrank\n FROM wwwsite \n WHERE LOWER(www_sections) LIKE '%{$l_sSection}%' AND www_allow_promoted = 1"; $o_db = new DBAdapter2(array('host' => $conf->live->host, 'username' => $conf->live->username, 'password' => $conf->live->password, 'schema' => $conf->live->schema)); $affectedRows = 0; $recordSet = ''; //var_dump($l_sSQL); try { $o_db->executeSQL($l_sSQL, $recordSet, $affectedRows); if ($affectedRows > 0) { $l_aToProcess = array(); foreach ($recordSet as &$result) { $id = $result['id']; $l_sGeneralSQL = "SELECT v.l_id, " . $id . ", v.l_rank,'" . $result['wname'] . "'," . $salt . "," . $result['wredirect'] . "," . $result['wrank'] . " FROM vehicles v WHERE l_isActive =1 AND v.l_www_id=" . $id . " "; $l_aTmpWNames[] = $result['wname']; $l_sTmp = ""; if ($result['picture'] == 1) { $l_sTmp .= " AND (v.l_isDealer = 1) "; } else { if ($result['picture'] == 0) { $l_sTmp .= " AND (v.l_isDealer = 0) "; } } if ($result['price'] == 1) {
$main_table = 'realestate'; $main_db = new DBAdapter2(array('host' => '192.168.240.15', 'username' => 'eyezeek', 'password' => 'p!n6access', 'schema' => 'ru_eyezeek')); $filename = "../output/realestate_" . date("U") . ".csv"; file_put_contents($filename, file_get_contents("yandex_header.txt")); $fp = fopen($filename, "a+"); $cur = 0; $step = 10000; $i = 0; $c = 0; $regions = file('regions.txt'); foreach ($regions as $region) { $reg = trim($region); if (!$region) { continue; } $main_db->executeSQL("select distinct l_assettype, l_deal_type, l_geo_city, l_rooms, count(*) as cnt, l_longitude, l_latitude\nfrom realestate\nwhere l_isActive = 1 and l_deal_type = 0 and l_d_price > 1000 and l_www_id = 1369 and !(l_assettype LIKE '%/%')\nGROUP BY l_assettype, l_deal_type, l_geo_city, l_rooms\nHAVING cnt > 10\nORDER BY cnt DESC", $resultArry, $rowsAffected); // --$cur, $step" $total = $rowsAffected; echo "region `{$reg}`\r\n"; if (!$total) { continue; } foreach ($resultArry as $val) { echo $c . "/" . ++$i . "/" . $total . "\r\n"; $ad = new YandexAdRealEstate(); $ad->l_id = $val['l_id']; $ad->build($val); $ad->save($fp); } $cur += $step; }
<?php /** * READ THIS * to use this file for reporting, due to the fact APC is shared amongst apache processes and is not shared with CLI php * code, we need to ping this page via apache. * The cron job should do this * wget http://192.168.240.70/crawlerstrunk/cronjobs/googleproxyreporter.php * for every once in a while you want the data to be collected * */ $_home = '/home/eyezeek/crawlers/www/crawlerstrunk'; include_once "{$_home}/crawler/engine/classes/Config.php"; include_once "{$_home}/crawler/engine/classes/DBAdapter2.class.php"; include_once "{$_home}/googleproxy/MemoryData.php"; $conf = new Config("/home/eyezeek/db.conf", "ini"); $o_db = new DBAdapter2(array('host' => $conf->crawler->host, 'username' => 'proxyuser', 'password' => 'E.Z.2011', 'schema' => 'proxy_reports')); $_numRequests = MemoryData::getNumRequestsResetCounter(true); $_sql = "INSERT INTO reports (`hourofreport`, `requests`) VALUES (now(), {$_numRequests}) "; try { $tmpArr = array(); $o_db->executeSQL($_sql, $tmpresult, $tmpArr); // if successully updated the DB, reset the counter $_numRequests = MemoryData::getNumRequestsResetCounter(); } catch (DBAdapter2Exception $e) { echo $e->getMessage(); }
private function anti_duplicate() { $this->m_aListing['l_url_md5'] = md5($this->m_aListing['l_url']); $this->m_aListing['l_uniq_md5'] = md5($this->m_aListing['l_d_price'] . $this->m_aListing['l_deal_type'] . $this->m_aListing['l_rooms'] . $this->m_aListing['l_prop_size_from'] . $this->m_aListing['l_prop_size_to'] . $this->m_aListing['l_floor'] . $this->m_aListing['l_i_lot_size'] . trim($this->m_aListing['l_assetType']) . trim($this->m_aListing['l_geo_city'])); $l_oLiveDB = new DBAdapter2(array('host' => $this->dbConf->live->host, 'username' => $this->dbConf->live->username, 'password' => $this->dbConf->live->password, 'schema' => $this->dbConf->live->schema)); //search by url $byUrl = "SELECT l_id,l_www_id,l_d_price,l_prop_size_from,l_prop_size_to,l_i_lot_size,l_floor,l_total_floors,l_geo_city,l_image,l_rooms,l_deal_type FROM realestate WHERE l_url_md5 = '" . $this->m_aListing['l_url_md5'] . "'"; $tmpresult = NULL; $tmpresult = array(); $fetchedRows = 0; try { $l_oLiveDB->executeSQL($byUrl, $tmpresult, $fetchedRows); } catch (DBAdapter2Exception $e) { unset($l_oLiveDB); print "\nERROR : SQL ERROR SEARCHING BY URL: " . $byUrl . " *** " . $e->getMessage(); return -3; } if ($fetchedRows > 0) { print "\nFound " . $fetchedRows . " matches by url: " . $this->m_aListing['l_url']; //if the price changed more than 0.4% then update $oldListing = $tmpresult[0]; $this->escape_bad_strings($oldListing); $l_dPriceDelta = (double) $oldListing['l_price'] / 100 * 0.4; $l_dAbsDiff = (double) $oldListing['l_price'] - (double) $this->m_aListing['l_price']; $l_dAbsDiff = abs($l_dAbsDiff); if ((int) $oldListing['l_prop_size_from'] != (int) $this->m_aListing['l_prop_size_from'] || (int) $oldListing['l_prop_size_to'] != (int) $this->m_aListing['l_prop_size_to'] || (int) $oldListing['l_i_lot_size'] != (int) $this->m_aListing['l_i_lot_size'] || (int) $oldListing['l_floor'] != (int) $this->m_aListing['l_floor'] || (int) $oldListing['l_total_floors'] != (int) $this->m_aListing['l_total_floors'] || $l_dAbsDiff > $l_dPriceDelta || strcmp($oldListing['l_geo_city'], $this->m_aListing['l_geo_city']) != 0 || strcmp($oldListing['l_image'], $this->m_aListing['l_image']) != 0 || (double) $oldListing['l_rooms'] != (double) $this->m_aListing['l_rooms'] || (int) $oldListing['l_deal_type'] != (int) $this->m_aListing['l_deal_type']) { $this->update_with_same_url($oldListing['l_id'], $oldListing, $l_oLiveDB); } return 1; } //search by unique set of fields $byUnuqSet = "SELECT l_id, l_www_id, l_url FROM realestate WHERE l_uniq_md5 = '" . $this->m_aListing['l_uniq_md5'] . "'"; $tmpresult = NULL; $tmpresult = array(); $fetchedRows = 0; try { $l_oLiveDB->executeSQL($byUnuqSet, $tmpresult, $fetchedRows); } catch (DBAdapter2Exception $e) { unset($l_oLiveDB); print "\nERROR : SQL ERROR SEARCHING BY UNIQUE SET: " . $byUrl . " *** " . $e->getMessage(); return -3; } if ($fetchedRows > 0) { print "\nFound " . $fetchedRows . " matches by unique set of fields: " . $this->m_aListing['l_url'] . " with " . $tmpresult[0]['l_url']; //if it's not winwin - just skip it print "\n\$this->m_aListing['l_www_id']: " . $this->m_aListing['l_www_id']; if ($this->m_aListing['l_www_id'] != 16) { return 2; } $oldListing = $tmpresult[0]; print "\n\$oldListing['l_www_id']" . $oldListing['l_www_id']; //if existing is also winwin - just skip it if ($oldListing['l_www_id'] == 16) { return 2; } //remove the old one and save the new one $sql = "DELETE FROM realestate WHERE l_id = " . $oldListing['l_id']; $rowsAffected = 0; try { $l_oLiveDB->executeNoresSQL($sql, $rowsAffected); } catch (DBAdapter2Exception $e) { unset($l_oLiveDB); print "\nERROR : SQL ERROR DELETING BY UNIQUE SET: " . $byUrl . " *** " . $e->getMessage(); return -3; } print "\nDeleted listing " . $oldListing['l_id'] . " that matches by unique set of fields. affected " . $rowsAffected; } return 0; }
$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); foreach ($tmpresult as $oneresult) { $l_aUpdatePostdate[] = $oneresult['www_id']; } foreach ($m_aSections as $l_aSection) { $l_sSQLl = "UPDATE " . $l_aSection['SectionName']; $l_sSQLl .= " SET l_postdate = NOW() "; $l_sSQLl .= " WHERE (l_www_id in (" . implode(',', $l_aUpdatePostdate) . ") and l_isActive=1) "; if ($l_aSection['SectionName'] == 'vehicles') { $l_sSQLl .= " or (l_isDealer = 1 and l_isActive=1)"; } $affectedRows = 0; $lastListID = 0; $m_oDB->executeNoresSQL($l_sSQLl, $affectedRows, $lastListID); print "\n" . date("d/m/y : H:i:s", time()) . " pid: " . getmypid() . ". " . $l_aSection['SectionName'] . ": " . $affectedRows . " updated"; }
<?php include_once '/home/eyezeek/crawlers/www/crawler/engine/classes/Config.php'; include_once '/home/eyezeek/crawlers/www/crawler/engine/classes/DBAdapter2.class.php'; $conf = new Config("/home/eyezeek/db.conf", "ini"); $l_aRentAreas = array('תל אביב וגוש דן' => 'דירות להשכרה בתל אביב וגוש דן', 'איזור יהודה ושומרון' => 'דירות להשכרה באיזור יהודה ושומרון', 'איזור הצפון, הגולן והגליל' => 'דירות להשכרה באיזור הצפון, הגולן והגליל', 'איזור המרכז' => 'דירות להשכרה באיזור המרכז', 'איזור הדרום, אילת והערבה' => 'דירות להשכרה באיזור הדרום, אילת והערבה', 'חיפה והסביבה' => 'דירות להשכרה בחיפה והסביבה', 'ירושליים והסביבה' => 'דירות להשכרה בירושליים והסביבה'); $l_aSaleAreas = array('תל אביב וגוש דן' => 'דירות למכירה בתל אביב וגוש דן', 'איזור יהודה ושומרון' => 'דירות למכירה באיזור יהודה ושומרון', 'איזור הצפון, הגולן והגליל' => 'דירות למכירה באיזור הצפון, הגולן והגליל', 'איזור המרכז' => 'דירות למכירה באיזור המרכז', 'איזור הדרום, אילת והערבה' => 'דירות למכירה באיזור הדרום, אילת והערבה', 'חיפה והסביבה' => 'דירות למכירה בחיפה והסביבה', 'ירושליים והסביבה' => 'דירות למכירה בירושליים והסביבה'); $o_db = new DBAdapter2(array('host' => $conf->live->host, 'username' => $conf->live->username, 'password' => $conf->live->password, 'schema' => $conf->live->schema)); echo "\n Get cities for each area Rent ..."; foreach ($l_aRentAreas as $l_sArea => $l_sDisplay) { $l_sSQL = 'SELECT cl_name FROM cities_geo WHERE cl_area = "' . $l_sArea . '"'; try { $o_db->executeSQL($l_sSQL, $tmpresult, $fetchedRows); $l_aCities = array(); if ($fetchedRows > 0) { foreach ($tmpresult as $l_aRow) { if (strpos($l_aRow['cl_name'], "'") != FALSE) { $l_aCities[] = '"' . $l_aRow['cl_name'] . '"'; } else { $l_aCities[] = "'" . $l_aRow['cl_name'] . "'"; } } } $l_iCount = 0; if (sizeof($l_aCities) > 0) { $l_sSQL2 = 'SELECT count(l_id) as l_city_count FROM realestate WHERE l_geo_city IN (' . implode(',', $l_aCities) . ') AND l_isActive = 1 AND l_deal_type = 0'; unset($l_sSQL); unset($tmpresult); unset($fetchedRows); try { $o_db->executeSQL($l_sSQL2, $l_oResult, $fetchedRows);
$table = 'vehicles_details'; $main_table = 'vehicles'; $db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA)); $main_db = new DBAdapter2(array('host' => '192.168.240.15', 'username' => 'eyezeek', 'password' => 'p!n6access', 'schema' => 'ru_eyezeek')); $campaign = new GoogleCampaign($db); $campaign->ga_campaign_name = CAMPAIGN_NAME_BASE . " " . date("Y-m-d H i"); $campaign->save(); $fp = fopen("../output/" . str_replace(" ", "_", $campaign->ga_campaign_name) . ".csv", "w+"); fputs($fp, "Campaign,Daily Budget,Language,Location,Proximity Targets,Ad Schedule,Ad Group,Max CPC,Keyword Type,Keyword,Headline,Description Line 1,Description Line 2,Display URL,Destination URL\r\n"); $cur = 0; $step = 10000; $i = 0; $c = 0; $kk = 0; do { $main_db->executeSQL("select distinct l_make, l_model, count(*) as cnt\nfrom vehicles\nwhere l_isActive = 1 and \nl_make != '' and\nl_model != ''\nGROUP BY l_make, l_model\nHAVING cnt > 2\nORDER BY l_make LIMIT {$cur}, {$step}", $resultArry, $rowsAffected); $total = $rowsAffected; if (!$rowsAffected || $kk > 60000) { break; } foreach ($resultArry as $val) { if ($val['l_make'] == $prev_make && isset($ag)) { $ag->m_aKeywords[] = array('keyword' => $val['l_make'] . " " . $val['l_model'], 'url' => "http://vehicles.pingola.ru.msn.com/search/" . str_replace(" ", "+", $val['l_make']) . "?make=" . str_replace(" ", "+", $val['l_make']) . "&model=" . $val['l_model'] . "&share&refGoogle=Network=_{network}_&Keyword=_{keyword}_&MatchType=_{matchtype}_&Ad=_{creative}_&AdPos=_{adposition}_&shared"); continue; } else { if (isset($ag)) { $ag->saveAds($fp, $campaign->ga_campaign_name); //$ag->refineKeywords(); foreach ($ag->m_aKeywords as $v) { echo $v['keyword'] . "\n\r"; $k++;
define('DISPLAY_URL', 'kabel.co.il'); $table = 'vehicles_details'; $main_table = 'vehicles'; $db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA)); $main_db = new DBAdapter2(array('host' => '192.168.240.15', 'username' => 'eyezeek', 'password' => 'p!n6access', 'schema' => 'il_eyezeek')); $campaign = new GoogleCampaign($db); $campaign->ga_campaign_name = CAMPAIGN_NAME_BASE . " " . date("Y-m-d H i"); $campaign->save(); $fp = fopen("../output/" . str_replace(" ", "_", $campaign->ga_campaign_name) . ".csv", "w+"); fputs($fp, "Campaign,Daily Budget,Language,Location,Proximity Targets,Ad Schedule,Ad Group,Max CPC,Keyword Type,Keyword,Headline,Description Line 1,Description Line 2,Display URL,Destination URL\r\n"); $cur = 0; $step = 10000; $i = 0; $c = 0; do { $main_db->executeSQL("SELECT l_make, l_model, l_year, l_owners, min(l_price) as l_d_price, count(*) as cnt FROM vehicles WHERE l_isActive = 1 and l_price > 3000 and l_owners = 'שניה' GROUP BY l_make, l_model, l_year HAVING cnt > 5 ORDER BY l_id DESC LIMIT {$cur}, {$step}", $resultArry, $rowsAffected); if (!$rowsAffected) { break; } $total = $rowsAffected; foreach ($resultArry as $val) { echo $c . "/" . ++$i . "/" . $total . "\r\n"; $ag = new GoogleAdGroup($db); $ag->m_sMainTable = 'vehicles'; $ag->gag_l_id = $val['l_id']; $ag->gag_name = $val['l_make'] . " " . $val['l_model'] . " " . $val['l_year'] . " " . $i; $ag->save(); $ag->buildAds(array('GoogleAdVehicles17072011_1IL', 'GoogleAdVehicles17072011_2IL', 'GoogleAdVehicles17072011_3IL', 'GoogleAdVehicles17072011_4IL', 'GoogleAdVehicles17072011_5IL', 'GoogleAdVehicles17072011_6IL', 'GoogleAdVehicles17072011_7IL'), $val); $ag->saveAds($fp, $campaign->ga_campaign_name); if (!$ag->is_has_ads) { echo "no good ads\r\n";
public function loadActiveProcessesByName($section, $name) { $o_db = new DBAdapter2(array('host' => $this->dbConf->crawler->host, 'username' => $this->dbConf->crawler->username, 'password' => $this->dbConf->crawler->password, 'schema' => $this->dbConf->crawler->schema)); $region = $this->dbConf->domain->prefix; $theIP = $this->dbConf->domain->ip; $fname = '/home/eyezeek/crawlers/www/crawler/engine/parsers/' . $region . '/' . $section . '/' . $name; $sql = "SELECT pid,ppid,file_name FROM " . $this->dbConf->crawler->schema . ".processes WHERE file_name = '{$fname}' AND host_ip = '{$theIP}' AND exit_time IS NULL "; $recordSet = ''; $affectedRows = 0; try { $o_db->executeSQL($sql, $recordSet, $affectedRows); if ($affectedRows > 0) { $this->m_aCrawlers = array(); print "\n Found {$affectedRows} processes :"; foreach ($recordSet as $result) { $tmpOneCrawler['pid'] = $result['pid']; $tmpOneCrawler['ppid'] = $result['ppid']; $tmpOneCrawler['name'] = $result['file_name']; print "\n PID=" . $result['pid'] . " | " . "PPID=" . $result['ppid'] . " | " . "Name=" . $result['file_name']; $this->m_aCrawlers[] = $tmpOneCrawler; } print "\n"; unset($o_db); } unset($o_db); //return TRUE; return $this->m_aCrawlers; } catch (DBAdapter2Exception $e) { unset($o_db); return FALSE; } }
define('DEFAULT_MATCH_TYPE', 'Broad'); define('DISPLAY_URL', 'kabel.co.il'); $table = 'realestate_details'; $main_table = 'realestate'; $db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA)); $main_db = new DBAdapter2(array('host' => '192.168.240.15', 'username' => 'eyezeek', 'password' => 'p!n6access', 'schema' => 'il_eyezeek')); $campaign = new GoogleCampaign($db); $campaign->ga_campaign_name = CAMPAIGN_NAME_BASE . " " . date("Y-m-d H i"); $campaign->save(); $fp = fopen("../output/" . str_replace(" ", "_", $campaign->ga_campaign_name) . ".csv", "w+"); fputs($fp, "Campaign,Daily Budget,Language,Location,Proximity Targets,Ad Schedule,Ad Group,Max CPC,Keyword Type,Keyword,Headline,Description Line 1,Description Line 2,Display URL,Destination URL\r\n"); $cur = 0; $step = 10000; $i = 0; $c = 0; $main_db->executeSQL("SELECT l_geo_city, l_rooms, l_assetType, min(l_d_price) as l_d_price, count(*) as cnt FROM realestate WHERE l_www_id = 16 and l_isActive = 1 and l_d_price > 1500 and l_rooms < 4 and l_deal_type = 0 GROUP BY l_geo_city, l_rooms, l_assetType HAVING cnt > 10 ORDER BY cnt DESC", $resultArry, $rowsAffected); $total = $rowsAffected; foreach ($resultArry as $val) { echo $c . "/" . ++$i . "\r\n"; $ag = new GoogleAdGroup($db); $ag->m_sMainTable = 'realestate'; $ag->gag_l_id = $val['l_id']; $ag->gag_name = "Group " . $c; $ag->save(); $ag->buildAds(array("GoogleAdRealEstateIL"), $val); $ag->buildRealEstateKeywordsIL($val); $ag->saveAds($fp, $campaign->ga_campaign_name); if (!$ag->is_has_ads) { echo "no good ads\r\n"; continue; }
<?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!";
define('GROUP_SIZE', 10000); define('CAMPAIGN_NAME_BASE', "Realestate RU"); define('MAX_CPC', 0.2); define('DEFAULT_MATCH_TYPE', 'Broad'); define('DISPLAY_URL', 'realestate.pingola.ru.msn.com'); $table = 'realestate_details'; $main_table = 'realestate'; $db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA)); $main_db = new DBAdapter2(array('host' => '192.168.240.15', 'username' => 'eyezeek', 'password' => 'p!n6access', 'schema' => 'ru_eyezeek')); $fp = fopen("../output/geo_" . date("U") . ".csv", "w+"); fputs($fp, "Campaign,Daily Budget,Language,Location,Proximity Targets,Ad Schedule,Ad Group,Max CPC,Keyword Type,Keyword,Headline,Description Line 1,Description Line 2,Display URL,Destination URL\r\n"); $cur = 0; $step = 10000; $i = 0; $c = 0; $main_db->executeSQL("SELECT *, count(*) as cnt FROM realestate\n\tWHERE \tl_deal_type = 0 and \n\t\t\tl_www_id = 1369 and \n\t\t\tl_isActive = 1 and \n\t\t\tl_d_price > 10000 and\n\t\t\tl_assetType = 'Квартира'\n\tGROUP BY \n\t\tl_geo_city \n\tORDER BY \n\t\tcnt DESC \n\tLIMIT 20", $resultArry, $rowsAffected); if (!$rowsAffected) { break; } $total = $rowsAffected; foreach ($resultArry as $val) { $campaign = new GoogleCampaign($db); $campaign->ga_campaign_name = "Real Estate Sale " . $val['l_geo_city']; $campaign->save(); echo $c . "/" . ++$i . "/" . $total . "\r\n"; $ag = new GoogleAdGroup($db); $ag->m_sMainTable = 'realestate'; $ag->gag_l_id = $val['l_id']; $ag->createName(); $ag->save(); $ag->buildAds(array('GoogleAdRealEstateRU5', 'GoogleAdRealEstateRU6', 'GoogleAdRealEstateRU7', 'GoogleAdRealEstateRU12', 'GoogleAdRealEstateRU13'), $val);
define('DISPLAY_URL', 'realestate.pingola.ru.msn.com'); $table = 'realestate_details'; $main_table = 'realestate'; $db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA)); $main_db = new DBAdapter2(array('host' => '192.168.240.15', 'username' => 'eyezeek', 'password' => 'p!n6access', 'schema' => 'ru_eyezeek')); $campaign = new GoogleCampaign($db); $campaign->ga_campaign_name = CAMPAIGN_NAME_BASE . " " . date("Y-m-d H i"); $campaign->save(); $fp = fopen("../output/" . str_replace(" ", "_", $campaign->ga_campaign_name) . ".csv", "w+"); fputs($fp, "Campaign,Daily Budget,Language,Location,Proximity Targets,Ad Schedule,Ad Group,Max CPC,Keyword Type,Keyword,Headline,Description Line 1,Description Line 2,Display URL,Destination URL\r\n"); $cur = 0; $step = 10000; $i = 0; $c = 0; do { $main_db->executeSQL("select distinct l_id, l_assettype, l_deal_type, l_geo_city, count(*) as cnt, l_longitude, l_latitude\nfrom realestate\nwhere l_isActive = 1 and l_d_price > 1000 \nGROUP BY l_assettype, l_deal_type, l_geo_city\nHAVING cnt > 10\nORDER BY cnt DESC LIMIT {$cur}, {$step}", $resultArry, $rowsAffected); if (!$rowsAffected) { break; } $total = $rowsAffected; foreach ($resultArry as $val) { $asset_types = explode("/", $val['l_assettype']); foreach ($asset_types as $asset_type) { $val['l_assettype_en'] = assetTypeEn($asset_type); $val['l_assettype'] = $asset_type; echo $c . "/" . ++$i . "/" . $total . "\r\n"; $ag = new GoogleAdGroup($db); $ag->m_sMainTable = 'realestate'; $ag->gag_l_id = $val['l_id']; $ag->createName($val); $ag->save();
<?php require_once '../config/database.php'; require_once '../config/keyword_forger.php'; require_once '../classes/DBAdapter2.class.php'; foreach ($ad_types as $class) { require_once '../classes/' . $class . '.php'; } require_once '../classes/GoogleCampaign.php'; require_once '../classes/Keyword.php'; $db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA)); $campaign = new GoogleCampaign($db); $campaign->ga_campaign_name = CAMPAIGN_NAME_BASE . " " . date("Y-m-d H i"); $campaign->save(); $db->executeSQL("SELECT keyword_id, keyword, occurs, word_count, source_keyword_id , c.l_image, c.l_make, c.l_color, c.l_model\n\t\t\t\t\tFROM m2m_keywords_to_niches a \n\t\t\t\t\tleft join keywords b using(keyword_id)\n\t\t\t\t\tLEFT JOIN {$table} c USING(keyword_id)\nWHERE c.l_image!= '' and niche_id = " . NICHE . " and occurs > " . GROUP_SIZE . " and word_count > 2 order by word_count desc, occurs desc", $resultArry, $rowsAffected); $total = $rowsAffected; $left = $total; echo $total . " keywords\r\n"; $start_time = date('U'); $kws = array(); $keyword = new Keyword($db); $fp = fopen("../output/" . str_replace(" ", "_", $campaign->ga_campaign_name) . ".csv", "w+"); fputs($fp, "Campaign,Daily Budget,Language,Location,Proximity Targets,Ad Schedule,Ad Group,Max CPC,Keyword Type,Keyword,Image,Ad Name,Image Size,Display URL,Destination URL\r\n"); foreach ($resultArry as $val) { $adgroup = new GoogleImageAdGroup($db); $adgroup->giag_campaign_id = $campaign->ga_campaign_id; $adgroup->giag_display_url = DISPLAY_URL; $adgroup->buildDestinationUrl($val); $adgroup->save(); $adgroup->giag_name = "Ad " . $adgroup->giag_id . " " . $val['keyword']; $adgroup->save();
define('DISPLAY_URL', 'kabel.co.il'); $table = 'vehicles_details'; $main_table = 'vehicles'; $db = new DBAdapter2(array('host' => KWDB_HOST, 'username' => KWDB_USERNAME, 'password' => KWDB_PASSWORD, 'schema' => KWDB_SCHEMA)); $main_db = new DBAdapter2(array('host' => '192.168.240.15', 'username' => 'eyezeek', 'password' => 'p!n6access', 'schema' => 'il_eyezeek')); $campaign = new GoogleCampaign($db); $campaign->ga_campaign_name = CAMPAIGN_NAME_BASE . " " . date("Y-m-d H i"); $campaign->save(); $fp = fopen("../output/" . str_replace(" ", "_", $campaign->ga_campaign_name) . ".csv", "w+"); fputs($fp, "Campaign,Daily Budget,Language,Location,Proximity Targets,Ad Schedule,Ad Group,Max CPC,Keyword Type,Keyword,Headline,Description Line 1,Description Line 2,Display URL,Destination URL\r\n"); $cur = 0; $step = 10000; $i = 0; $c = 0; do { $main_db->executeSQL("SELECT * FROM vehicles WHERE l_www_id = 16 and l_isActive = 1 and l_price > 1000 ORDER BY l_id DESC LIMIT {$cur}, {$step}", $resultArry, $rowsAffected); $total = $rowsAffected; foreach ($resultArry as $val) { echo $c . "/" . ++$i . "/" . $total . "\r\n"; $ag = new GoogleAdGroup($db); $ag->m_sMainTable = 'vehicles'; $ag->gag_l_id = $val['l_id']; $ag->createName(); $ag->save(); $ag->buildAds(array('GoogleAdVehiclesIL'), $val); $ag->buildVehiclesKeywordsIL($val); $ag->saveAds($fp, $campaign->ga_campaign_name); if (!$ag->is_has_ads) { echo "no good ads\r\n"; continue; }