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