public function safeUp()
{
$badProxies = $this->getBadProxies();
$goodProxies = $this->getGoodProxies();
foreach ($badProxies as $key => $badHost) {
$proxy = Proxies::find()->where(['host' => $badHost])->limit(1)->one();
if ($proxy) {
echo PHP_EOL . $key . ' - ' . $badHost . ' - ' . $proxy->host . ' -> ' . $goodProxies[$key];
$proxy->host = $goodProxies[$key];
$proxy->port = '7893';
$proxy->login = '******';
$proxy->password = '******';
if ($proxy->save()) {
echo ' - UPDATED';
} else {
echo ' - FAIL';
}
}
}
$proxy = Proxies::find()->where(['host' => '95.163.126.116'])->limit(1)->one();
if (is_null($proxy)) {
$proxy = new Proxies();
$proxy->host = '95.163.126.116';
$proxy->port = '7893';
$proxy->login = '******';
$proxy->password = '******';
$proxy->scope_id = 22;
$proxy->country = 'ru';
$proxy->save();
}
}
private function _saveProxy($ip, $scopeId)
{
$proxy = new Proxies();
$proxy->host = $ip;
$proxy->port = '5394';
$proxy->login = '******';
$proxy->password = '******';
$proxy->country = 'usa';
$proxy->scope_id = $scopeId;
return $proxy->save();
}
private function _saveProxy($ip, $scopeId)
{
$proxy = new Proxies();
$proxy->host = $ip;
$proxy->port = '5394';
$proxy->login = '******';
$proxy->password = '******';
$proxy->country = 'usa';
$proxy->scope_id = $scopeId;
return $proxy->save();
}
public function safeUp()
{
// 127 usa
for ($i = 34; $i < 47; $i++) {
Proxies::getDb()->createCommand("DELETE FROM " . Proxies::tableName() . " WHERE host = '173.214.164.{$i}'")->execute();
}
$sampleProxy = Proxies::getDb()->createCommand('SELECT * FROM ' . Proxies::tableName() . ' WHERE id = 66')->queryOne();
$proxies = [];
// 114 usa
//add to scope 2
for ($i = 226; $i < 239; $i++) {
$proxies[] = ['209.159.156.' . $i, $sampleProxy['port'], $sampleProxy['login'], $sampleProxy['password'], 2, $sampleProxy['country']];
}
// 126 usa
// add toscope 3
for ($i = 178; $i < 191; $i++) {
$proxies[] = ['209.159.157.' . $i, $sampleProxy['port'], $sampleProxy['login'], $sampleProxy['password'], 3, $sampleProxy['country']];
}
// 139 usa
// add toscope 3
for ($i = 34; $i < 47; $i++) {
$proxies[] = ['104.218.55.' . $i, $sampleProxy['port'], $sampleProxy['login'], $sampleProxy['password'], 3, $sampleProxy['country']];
}
// 152 usa
if (!empty($proxies)) {
Proxies::getDb()->createCommand()->batchInsert(Proxies::tableName(), ['host', 'port', 'login', 'password', 'scope_id', 'country'], $proxies)->execute();
}
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Proxies::find();
$dataProvider = new ActiveDataProvider(['query' => $query]);
if (!($this->load($params) && $this->validate())) {
return $dataProvider;
}
$query->andFilterWhere(['id' => $this->id, 'scope_id' => $this->scope_id, 'active' => $this->active]);
$query->andFilterWhere(['like', 'host', $this->host])->andFilterWhere(['like', 'port', $this->port])->andFilterWhere(['like', 'login', $this->login])->andFilterWhere(['like', 'password', $this->password])->andFilterWhere(['like', 'country', $this->country])->andFilterWhere(['scope_id' => $this->scope_id]);
return $dataProvider;
}
public function safeUp()
{
$newProxies = $this->getNewProxies();
$i = 0;
foreach ($this->getOldProxies() as $oldProxy) {
Proxies::getDb()->createCommand("\n UPDATE " . Proxies::tableName(true) . " \n SET host = '{$newProxies[$i]}', port = '7893', login = '******', password = '******'\n WHERE host = '{$oldProxy}'")->execute();
echo PHP_EOL . $oldProxy . ' -> ' . $newProxies[$i];
$i++;
}
echo PHP_EOL . 'Total proxies updated: ' . $i . PHP_EOL;
}
public function safeUp()
{
$scopesId = Proxies::getDb()->createCommand("\n SELECT scope_id, COUNT(*) as cnt\n FROM " . Proxies::tableName() . "\n WHERE `country` != 'usa'\n GROUP BY scope_id HAVING cnt > 50")->queryAll();
foreach ($scopesId as $scope) {
$proxy = Proxies::find()->where(['scope_id' => $scope['scope_id']])->limit(1)->one();
echo PHP_EOL . "proxy: {$proxy->host} (scopeid {$proxy->scope_id} -> 4)";
$proxy->scope_id = 4;
echo $proxy->save() ? " - OK" : " - FAIL";
}
$proxies = Proxies::find()->where(['scope_id' => 2])->limit(6)->all();
foreach ($proxies as $proxy) {
echo PHP_EOL . "proxy: {$proxy->host} (scopeid {$proxy->scope_id} -> 3)";
$proxy->scope_id = 3;
echo $proxy->save() ? " - OK" : " - FAIL";
}
}
public function safeUp()
{
$badProxies = $this->getBadProxies();
$goodProxies = $this->getGoodProxies();
foreach ($badProxies as $key => $badHost) {
$proxy = Proxies::find()->where(['host' => $badHost])->limit(1)->one();
if ($proxy) {
echo PHP_EOL . $key . ' - ' . $badHost . ' - ' . $proxy->host . ' -> ' . $goodProxies[$key];
$proxy->host = $goodProxies[$key];
$proxy->port = '6311';
$proxy->login = '******';
$proxy->password = '******';
if ($proxy->save()) {
echo ' - UPDATED';
} else {
echo ' - FAIL';
}
}
}
}
/**
*
*/
public function actionProxyLogReport()
{
self::$engines = [Proxy::GOOGLE, Proxy::YAHOO, Proxy::BING, Proxy::ASK, Proxy::ECOSIA];
$results = \maxlen\proxy\models\ProxyLog::getDb()->createCommand("\n SELECT pl.*, p.scope_id FROM proxy_log AS pl\n LEFT JOIN " . Proxies::tableName(true) . " AS p ON p.host = pl.ip \n WHERE pl.dt >= '" . date("Y-m-d", time() - 86400) . "' AND pl.dt <= '" . date("Y-m-d") . "'\n AND search_engine IN ('" . implode("','", self::$engines) . "')\n AND code = 302 ORDER BY pl.ip\n ")->queryAll();
$data = [];
foreach ($results as $res) {
if (!isset($data[$res['ip']])) {
$tmpSe = [];
foreach (self::$engines as $se) {
$tmpSe[$se] = 0;
}
$data[$res['ip']] = array_merge(['ip' => $res['ip'], 'scope' => $res['scope_id'], 'total' => 0], $tmpSe);
}
$data[$res['ip']][$res['search_engine']]++;
$data[$res['ip']]['total']++;
}
if (!empty($data)) {
\Yii::$app->mailer->compose("/proxyLogReport", ['count_total' => count($data), 'data' => $data])->setFrom('*****@*****.**')->setTo(['*****@*****.**', '*****@*****.**', '*****@*****.**'])->setSubject('Spider Log')->send();
}
}
public function startTest($scopeId = 0)
{
$geoDbFile = \Yii::getAlias("@common/data/geo-ip/" . \console\controllers\GeoipController::FILE_NAME);
if (!is_file($geoDbFile)) {
throw new Exception('IDB file not found: ' . $geoDbFile);
}
$reader = new Reader(\Yii::getAlias("@common/data/geo-ip/" . \console\controllers\GeoipController::FILE_NAME));
$this->result = ['badProxy' => [], 'badScopes' => [], 'resume' => 'All proxies are correct'];
$proxies = Proxies::find();
if ($scopeId != 0) {
$proxies = $proxies->where(['scope_id' => $scopeId]);
}
$proxies = $proxies->all();
foreach ($proxies as $proxy) {
$record = $reader->city($proxy->host);
$this->_putRowToResult($proxy, $record->country->isoCode);
}
if (!empty($this->result['badProxy'])) {
$this->result['resume'] = 'Total Diff Proxies: ' . count($this->result['badProxy']);
}
return;
}
private function restructuring()
{
$countAllExist = $countToDelete = 0;
$working = $this->getWorkingProxies();
// var_dump($working);
// die();
$ourProxies = [];
$tmpProxies = ProxyAdwords::find()->all();
$oldProxies = [];
$logins = [];
if (!empty($tmpProxies)) {
foreach ($tmpProxies as $proxy) {
$ourProxies[$proxy->host] = $proxy;
if (!isset($logins[$proxy->login])) {
$logins[$proxy->login] = ArrayHelper::toArray($proxy);
}
}
}
$tmpProxies = ProxyBuy::find()->all();
if (!empty($tmpProxies)) {
foreach ($tmpProxies as $proxy) {
$ourProxies[$proxy->host] = $proxy;
if (!isset($logins[$proxy->login])) {
$logins[$proxy->login] = ArrayHelper::toArray($proxy);
}
}
}
$tmpProxies = ProxySpider::find()->all();
if (!empty($tmpProxies)) {
foreach ($tmpProxies as $proxy) {
$ourProxies[$proxy->host] = $proxy;
if (!isset($logins[$proxy->login])) {
$logins[$proxy->login] = ArrayHelper::toArray($proxy);
}
}
}
$tmpProxies = ProxyUkraine::find()->all();
if (!empty($tmpProxies)) {
foreach ($tmpProxies as $proxy) {
$ourProxies[$proxy->host] = $proxy;
if (!isset($logins[$proxy->login])) {
$logins[$proxy->login] = ArrayHelper::toArray($proxy);
}
}
}
$tmpProxies = ProxyUsa::find()->all();
if (!empty($tmpProxies)) {
foreach ($tmpProxies as $proxy) {
$ourProxies[$proxy->host] = $proxy;
if (!isset($logins[$proxy->login])) {
$logins[$proxy->login] = ArrayHelper::toArray($proxy);
}
}
}
$resProxies = [];
$workingIPs = [];
$scopeId = 1;
$scopes = [];
$counter = 1;
if (!empty($ourProxies)) {
foreach ($working as $country => $proxies) {
$scopes[$scopeId] = ['id' => $scopeId, 'country' => $country];
foreach ($proxies as $proxy => $login) {
$workingIPs[] = $proxy;
$p = [];
$p['country'] = $country;
$p['scope_id'] = $scopeId;
if (array_key_exists($proxy, $ourProxies)) {
$tmpP = ArrayHelper::toArray($ourProxies[$proxy]);
$p['host'] = $tmpP['host'];
$p['login'] = $tmpP['login'];
$p['password'] = $tmpP['password'];
$p['port'] = $tmpP['port'];
} else {
$p['host'] = $proxy;
$p['login'] = $login;
if (array_key_exists($login, $logins)) {
$p['password'] = $logins[$login]['password'];
$p['port'] = $logins[$login]['port'];
} else {
$p['password'] = '';
$p['port'] = '0';
echo PHP_EOL . $login;
}
}
$resProxies[] = $p;
$countAllExist++;
$counter++;
if ($counter > 50) {
$scopeId++;
$scopes[$scopeId] = ['id' => $scopeId, 'country' => $country];
$counter = 0;
}
}
$scopeId++;
}
}
foreach ($ourProxies as $ip => $proxy) {
if (!in_array($ip, $workingIPs)) {
$countToDelete++;
}
}
if (!empty($scopes)) {
ProxiesScopes::getDb()->createCommand()->batchInsert(ProxiesScopes::tableName(), ['id', 'country'], $scopes)->execute();
}
if (!empty($resProxies)) {
Proxies::getDb()->createCommand()->batchInsert(Proxies::tableName(), ['country', 'scope_id', 'host', 'login', 'password', 'port'], $resProxies)->execute();
}
echo "exists proxies: " . count($ourProxies) . "; All email: " . $countAllExist . "; Diff: " . (count($ourProxies) - $countAllExist) . "; Delete: {$countToDelete}" . PHP_EOL;
$toFix = Proxies::find()->where(['password' => ''])->all();
foreach ($toFix as $proxy) {
echo PHP_EOL . $login;
$pr = Proxies::find()->where(['login' => $proxy->login])->limit(1)->one();
if (!is_null($pr)) {
$proxy->port = $pr->port;
$proxy->password = $pr->password;
$proxy->save();
echo ' fixed';
}
}
}
public function safeDown()
{
Proxies::getDb()->createCommand("\n UPDATE " . Proxies::tableName(true) . " \n SET scope_id = 11 \n WHERE host = '95.163.115.31'")->execute();
return true;
}
/**
* Get random proxy
*
* @param integer $scopes
* @param string $country
* @return array
*/
public static function getRandomProxy($scope = null, $country = null)
{
static::$proxy = [Proxies::getRandomProxy($scope, $country)];
static::$proxyIndex = key(static::$proxy);
$data = ['return_proxies' => count(static::$proxy)];
if (!is_null($scope)) {
$data['scope_id'] = $scope;
}
ProxyGetLog::addToLog($data);
return static::$proxy;
}
