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;
}
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();
}
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;
}
}
/**
* 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);
}
}
$dbConf = new Config("/home/eyezeek/db.conf", "ini");
$l_countries = array(array('reg' => 'ISRAEL', 'schemapl' => 'il_prelive', 'schemali' => 'il_eyezeek', 'currency' => 'ILS', 'normalize_vals' => array('l_assetType')), array('reg' => 'RUSSIA', 'schemapl' => 'ru_prelive', 'schemali' => 'ru_eyezeek', 'currency' => 'RUB', 'normalize_vals' => array('l_assetType', 'l_bathroom_unit')));
$preliveDB = null;
$liveDB = null;
// the main execution
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}";
require_once '../classes/DBAdapter2.class.php';
require_once '../classes/YandexAdRealEstate.php';
/**
* Niche ID in the database
* @var integer
*/
define('NICHE', 4);
/**
* Minimum number if items related to the core keyword of the group.
* @var integer
*/
define('GROUP_SIZE', 10000);
define('MAX_CPC', 0.2);
define('DISPLAY_URL', 'realestate.pingola.ru.msn.com');
$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"
* @var integer
*/
define('NICHE', 4);
/**
* Minimum number if items related to the core keyword of the group.
* @var integer
*/
define('GROUP_SIZE', 1000);
define('CAMPAIGN_NAME_BASE', "Vehicles Mini");
define('MAX_CPC', 0.3);
define('DEFAULT_MATCH_TYPE', 'Broad');
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;
<?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);
* @var integer
*/
define('NICHE', 4);
/**
* Minimum number if items related to the core keyword of the group.
* @var integer
*/
define('GROUP_SIZE', 1000);
define('CAMPAIGN_NAME_BASE', "Vehicles IL");
define('MAX_CPC', 0.2);
define('DEFAULT_MATCH_TYPE', 'Broad');
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);
* @var integer
*/
define('NICHE', 4);
/**
* Minimum number if items related to the core keyword of the group.
* @var integer
*/
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();
<?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");
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;
}
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;
}
* @var integer
*/
define('NICHE', 4);
/**
* Minimum number if items related to the core keyword of the group.
* @var integer
*/
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'));
$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;
<?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);
<?php
include_once '/home/eyezeek/crawlers/www/crawler/engine/constants/Constants.php';
include_once '/home/eyezeek/crawlers/www/crawler/engine/classes/Config.php';
include_once '/home/eyezeek/crawlers/www/crawler/engine/classes/DBAdapter2.class.php';
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;
}
}
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"));
$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);
* @var integer
*/
define('NICHE', 4);
/**
* Minimum number if items related to the core keyword of the group.
* @var integer
*/
define('GROUP_SIZE', 1000);
define('CAMPAIGN_NAME_BASE', "Real Estate IL");
define('MAX_CPC', 0.2);
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';
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
$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);
/* sleep for a second before trying a page */
$l_iCurrentTry = $l_iCurrentTry - 1;
$l_hHandler->createCurl();
$l_iHTTPStatus = $l_hHandler->getHttpStatus();
}
unset($l_iHTTPStatus);
if ($l_hHandler->getHttpStatus() == 200) {
$a_sResult = (string) $l_hHandler->__tostring();
}
$l_iHTTPStatus = $l_hHandler->getHttpStatus();
unset($l_hHandler);
return (int) $l_iHTTPStatus;
}
}
$dbConf = new Config("/home/eyezeek/db.conf", "ini");
$m_oDB = new DBAdapter2(array('host' => $dbConf->live->host, 'username' => $dbConf->live->username, 'password' => $dbConf->live->password, 'schema' => $dbConf->live->schema));
// 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;
* @var integer
*/
define('NICHE', 3);
/**
* Minimum number if items related to the core keyword of the group.
* @var integer
*/
define('GROUP_SIZE', 10000);
define('CAMPAIGN_NAME_BASE', "Vehicles RU");
define('MAX_CPC', 0.2);
define('DEFAULT_MATCH_TYPE', 'Broad');
define('DISPLAY_URL', 'vehicles.pingola.ru.msn.com');
$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;
<?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
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();
<?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);
