API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 29th, 2017
669
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 40.67 KB | None | 0 0
raw download clone embed print report
  1. #!/usr/bin/php
  2. <?
  3. class exports
  4. {
  5. public $campaigns = [];
  6. public $users = [];
  7. public $offers = [];
  8. public $prelanders = [];
  9. public $results = [];
  10. public $days = [];
  11. public $daysData = 5;
  12. public $minTraff = 1;
  13. public $defPassword = '';
  14. public $env = 'tequila';
  15. public $envOrig = 'test';
  16. public $limit = false;
  17.  
  18. public $oss = [
  19. 0 => 'Linux',
  20. 1 => 'Windows',
  21. 2 => 'Mac OS',
  22. 3 => 'Android',
  23. 4 => 'iOS',
  24. 5 => 'AIX',
  25. 6 => 'Amiga OS',
  26. 7 => 'Arch',
  27. 8 => 'Bada',
  28. 9 => 'BeOS',
  29. 10 => 'BlackBerry',
  30. 11 => 'CentOS',
  31. 12 => 'Chromium OS',
  32. 13 => 'Contiki',
  33. 14 => 'Fedora',
  34. 15 => 'Firefox OS',
  35. 16 => 'FreeBSD',
  36. 17 => 'Debian',
  37. 18 => 'DragonFly',
  38. 19 => 'Gentoo',
  39. 20 => 'GNU',
  40. 21 => 'Haiku',
  41. 22 => 'Hurd',
  42. 23 => 'Joli',
  43. 24 => 'Linpus',
  44. 25 => 'Mageia',
  45. 26 => 'Mandriva',
  46. 27 => 'MeeGo',
  47. 28 => 'Minix',
  48. 29 => 'Mint',
  49. 30 => 'Morph OS',
  50. 31 => 'NetBSD',
  51. 32 => 'Nintendo',
  52. 33 => 'OpenBSD',
  53. 34 => 'OpenVMS',
  54. 35 => 'OS/2',
  55. 36 => 'Palm',
  56. 37 => 'PCLinuxOS',
  57. 38 => 'Plan9',
  58. 39 => 'Playstation',
  59. 40 => 'QNX',
  60. 41 => 'RedHat',
  61. 42 => 'RIM Tablet OS',
  62. 43 => 'RISC OS',
  63. 44 => 'Sailfish',
  64. 45 => 'Series40',
  65. 46 => 'Slackware',
  66. 47 => 'Solaris',
  67. 49 => 'SUSE',
  68. 50 => 'Symbian',
  69. 51 => 'Tizen',
  70. 52 => 'Ubuntu',
  71. 53 => 'UNIX',
  72. 54 => 'VectorLinux',
  73. 55 => 'WebOS',
  74. 56 => 'Windows Phone',
  75. 57 => 'Windows Mobile',
  76. 58 => 'Zenwalk'
  77. ];
  78.  
  79.  
  80. /**
  81. * Device Types / Traffic Type
  82. */
  83. public $types = [
  84. 0 => 'mobile',
  85. 1 => 'desktop',
  86. 2 => 'tablet',
  87. 3 => 'smarttv',
  88. 4 => 'console',
  89. 5 => 'werable',
  90. 6 => 'embedded'
  91. ];
  92.  
  93. public $rates = [];
  94.  
  95. /**
  96. * Device Vendors
  97. */
  98. public $vendors = [
  99. 0 => 'Acer',
  100. 1 => 'Alcatel',
  101. 2 => 'Amazon',
  102. 3 => 'Apple',
  103. 4 => 'Archos',
  104. 5 => 'Asus',
  105. 6 => 'BenQ',
  106. 7 => 'BlackBerry',
  107. 8 => 'Dell',
  108. 9 => 'GeeksPhone',
  109. 10 => 'Google',
  110. 11 => 'HP',
  111. 12 => 'HTC',
  112. 13 => 'Huawei',
  113. 14 => 'Jolla',
  114. 15 => 'Lenovo',
  115. 16 => 'LG',
  116. 17 => 'Meizu',
  117. 18 => 'Microsoft',
  118. 19 => 'Motorola',
  119. 20 => 'Nexian',
  120. 21 => 'Nintendo',
  121. 22 => 'Nokia',
  122. 23 => 'Nvidia',
  123. 24 => 'Ouya',
  124. 25 => 'Palm',
  125. 26 => 'Panasonic',
  126. 28 => 'Polytron',
  127. 29 => 'RIM',
  128. 30 => 'Samsung',
  129. 31 => 'Sharp',
  130. 32 => 'Siemens',
  131. 33 => 'Sony-Ericsson',
  132. 34 => 'Sprint',
  133. 35 => 'Xbox',
  134. 36 => 'ZTE',
  135. ];
  136.  
  137. /*
  138. // DEFAULT user
  139.  
  140.  
  141.  
  142. TRUNCATE accounts CASCADE;
  143. ALTER SEQUENCE accounts_id_seq RESTART WITH 1;
  144.  
  145. TRUNCATE campaigns CASCADE;
  146. ALTER SEQUENCE campaigns_id_seq RESTART WITH 1;
  147.  
  148. TRUNCATE companies CASCADE;
  149. ALTER SEQUENCE companies_id_seq RESTART WITH 1;
  150.  
  151. TRUNCATE offers CASCADE;
  152. ALTER SEQUENCE offers_id_seq RESTART WITH 1;
  153.  
  154. TRUNCATE prelanders CASCADE;
  155. ALTER SEQUENCE prelanders_id_seq RESTART WITH 1;
  156.  
  157. TRUNCATE "additionalManagers" CASCADE;
  158.  
  159. TRUNCATE prelanders CASCADE;
  160.  
  161. TRUNCATE "offerPrelanders" CASCADE;
  162. TRUNCATE prelanders CASCADE;
  163.  
  164. TRUNCATE "campaignOffers" CASCADE;
  165.  
  166. TRUNCATE "payouts" CASCADE;
  167.  
  168. TRUNCATE prelanders CASCADE;
  169.  
  170. TRUNCATE "targetingDetailIsps" CASCADE;
  171.  
  172. TRUNCATE "targetingDetails" CASCADE;
  173.  
  174. TRUNCATE "targetingRules" CASCADE;
  175.  
  176. TRUNCATE "targetingRulesItems" CASCADE;
  177.  
  178.  
  179. INSERT INTO "public"."accounts" ("id", "email", "secondaryEmails", "password", "languages", "phoneNumbers", "addresses", "role", "lastAccess", "activationEmailToken", "path", "createdAt", "updatedAt", "deletedAt") VALUES (E'1', E'admin@proxima.si', NULL, E'$2a$10$vdUSp7WkeWEXIzJch.fTRe589I8LnTVLHezlnV1thoZNupejWJRsy', E'{}', E'{}', NULL, E'ADMIN', E'2017-06-01 07:48:23.26+00', E'a4f24b28-73e4-4d2e-b4e3-95884c6b1c11', NULL, E'2017-05-31 15:25:42.025+00', E'2017-06-01 07:48:23.26+00', NULL);
  180. INSERT INTO "public"."companies" ("id", "name", "alias", "description", "domain", "billingInformation", "createdAt", "updatedAt", "deletedAt", "createdById") VALUES (E'1', E'Default Company', E'Default Company', E'Default Company', E'default.com', NULL, E'2017-05-31 15:25:42.066+00', E'2017-05-31 15:25:42.066+00', NULL, E'1');
  181.  
  182.  
  183.  
  184.  
  185. */
  186.  
  187. /**
  188. * Operating Systems
  189. * @see https://github.com/faisalman/ua-parser-js#methods
  190. */
  191.  
  192. public function __construct($days = false){
  193.  
  194. set_time_limit(0);
  195. ini_set('mysql.connect_timeout','0');
  196. ini_set('max_execution_time', '0');
  197.  
  198.  
  199. for($i = 0; $i < $this->daysData; $i++)
  200. $this->days[] = date("Y-m-d", strtotime('-'. $i .' days'));
  201.  
  202.  
  203. $this->connect();
  204. echo PHP_EOL . 'CONNECTED TO MYSQL DATABASE';
  205. $this->connect_psql();
  206. echo PHP_EOL . 'CONNECTED TO postgres DATABASE';
  207.  
  208.  
  209. echo PHP_EOL . 'GETTIG CONVERSION RATE...';
  210. $this->rates = json_decode(file_get_contents('http://api.fixer.io/latest'))->rates->USD ;
  211. echo PHP_EOL . ' 1 EUR = USD ' . $this->rates ;
  212.  
  213. //USERS AND USER RELATIONS!
  214.  
  215. $users = $this->getUsers();
  216.  
  217. if($this->limit)
  218. $users = array_slice($users, 0, 20,true);
  219.  
  220. $companies = $this->getCompanies($users);
  221.  
  222. if($this->limit)
  223. $companies = array_slice($companies, 0, 20,true);
  224.  
  225. //we need users to know companies, but we need to insert companies and generate unique ids first.
  226. $companies = $this->insertCompanies($companies);
  227. $this->insertUsers($users, $companies);
  228.  
  229. echo PHP_EOL . 'INSERTING USER RELATIONS...';
  230. foreach($users as $k => $v){
  231. if($v['role'] == 'MANAGER'){
  232. $relations = $this->getUserRelations($v['id'], $users);
  233. echo "\r Relations for : " . $v['email'] . ' has access to ' . count($relations) . ' users. ';
  234. $this->insertUserRelations($v['id'], $relations);
  235. }
  236. }
  237.  
  238. echo PHP_EOL . 'GETTING USED OFFERS AND CAMPAIGNS LAST MONTH ...';
  239. $res = $this->getOfferIdsLastDays();
  240.  
  241. //companies needs users so we can know each company default currency
  242. $offids = $cgids = [];
  243.  
  244. foreach($res as $k => $v){
  245. $cgids[] = $v['campaignid'];
  246. $offids[]= $v['offerid'];
  247. }
  248.  
  249. echo PHP_EOL . 'GETTING CAMPAIGNS ...';
  250. $campaigns = $this->getCampaigns($cgids);
  251.  
  252. $this->insertCampaigns($campaigns);
  253.  
  254. echo PHP_EOL . 'GETTING OFFERS ...';
  255. $offers = $this->getOffers($cgids);
  256.  
  257. echo PHP_EOL . 'GETTING OFFER DETAILS ...';
  258. $offers = $this->getOffersTargetingDetails($offers);
  259.  
  260. /*
  261. Give the advertiserid get the right currency
  262. */
  263. foreach($offers as $k => $offer){
  264. foreach($users as $u){
  265. if($u['id'] == $offer['advertiser']){
  266. $offers[$k]['currency']= $u['currency'];
  267. break;
  268. }
  269. }
  270. }
  271.  
  272. $offers = $this->getOffersRevenues($offers);
  273.  
  274. echo PHP_EOL . 'INSERTING OFFERS ';
  275. $offers = $this->insertOffers($offers);
  276.  
  277. echo PHP_EOL . 'INSERT OFFERS TARGETING';
  278. $this->insertOffersTargeting($offers);
  279.  
  280. echo PHP_EOL . 'INSERT OFFERS REVENUES';
  281. $this->insertOffersRevenues($offers);
  282.  
  283. echo PHP_EOL . 'INSERTING OFFERS TO CAMPAIGNS....' . PHP_EOL;
  284. foreach($offers as $offer)
  285. $this->insertOfferCampaigns($offer);
  286.  
  287. //getPrelnaders based, on prelanders used per offer.
  288. echo PHP_EOL . 'GETTING PRELANDERS';
  289. $prelanders = array();
  290. foreach($offers as $ok => $offer)
  291. $prelanders[] = $offers[$ok]['prelanders'] = $this->getPrelanders($offer);
  292.  
  293. $prelanders = array_values($prelanders);
  294.  
  295. //prelanders should have unique urls... but that's too complicated for now, they only have if the coincide.
  296. //returns the offer , includes all prelanders and the new ids for those prelanders.
  297.  
  298. echo PHP_EOL . 'INSERTING PRELANDERS' . PHP_EOL;
  299. foreach($offers as $ko => $offer)
  300. $offers[$ko] = $this->insertPrelanders($offer);
  301.  
  302. echo PHP_EOL . 'INSERTING PRELANDER TARGETING' . PHP_EOL;
  303. foreach($offers as $ko => $offer)
  304. $this->insertPrelanderTargeting($offer);
  305.  
  306. echo PHP_EOL . 'INSERT PRELANDER/OFFERS RELATIONS' . PHP_EOL;
  307. foreach($offers as $ko => $offer)
  308. $this->insertPrelanderOfferRelations($offer);
  309.  
  310.  
  311.  
  312.  
  313. }
  314.  
  315. /*for each offfer we insert the prelander*/
  316. public function insertPrelanderTargeting($offer){
  317.  
  318. if(isset($offer['prelanders']) && !empty($offer['prelanders'])){
  319. foreach($offer['prelanders'] as $kl => $pl){
  320. $sql = "INSERT INTO \"targetingDetails\" (\"countries\", \"countriesInclude\", \"ispsInclude\", \"prelanderId\") ";
  321. $sql .= "VALUES ";
  322. $sql .= "('{".implode(',',$pl['targeting']['countries'])."}', true, true, " . $pl['id'] . ") RETURNING \"id\";";
  323.  
  324. $result = pg_query($sql);
  325.  
  326. $id = false;
  327. while ($row = pg_fetch_array($result)) $id = $row['id'];
  328.  
  329. if($id && !empty($pl['targeting']['carriers'])){
  330. foreach($pl['targeting']['carriers'] as $isps){
  331. if(is_int($isps)){
  332. $sql2 = "INSERT INTO \"targetingDetailIsps\" (\"createdAt\", \"updatedAt\", \"targetingDetailId\", \"ispId\") VALUES (current_timestamp, current_timestamp, $id, $isps);";
  333. $result = pg_query($sql2);
  334. }else{
  335. echo PHP_EOL . 'unknown carrier: '.$isp ;
  336. }
  337. }
  338. }
  339. }
  340. }
  341.  
  342.  
  343. }
  344.  
  345. public function insertPrelanderOfferRelations($offer){
  346. foreach($offer['prelanders'] as $k => $v){
  347. $cmd = './../Serhii/scripts/app -p ' . $v['id'] . ' -o ' . $offer['id'];
  348. shell_exec($cmd);
  349. echo "\r". ' campaign: ' . $offer['id'] . ' prelander: ' . $v['id'] ;
  350. }
  351. }
  352.  
  353. public function insertPrelanders($offer){
  354. if($offer && !empty($offer['prelanders'])){
  355. $i = 1;
  356. foreach ($offer['prelanders'] as $k=> $pl){
  357. $sql ="INSERT INTO prelanders (\"name\", \"url\",\"createdAt\", \"updatedAt\", \"managerId\", \"createdById\") VALUES ";
  358. $sql .=" ('" . $pl['name'] . "', '" . $pl['url'] . "', current_timestamp, current_timestamp,1,1 ) RETURNING \"id\" ";
  359.  
  360. $result = pg_query($sql);
  361. while ($row = pg_fetch_array($result)) $id = $offer['prelanders'][$k]['id'] = $row['id'];
  362.  
  363. echo "\r Inserted prelander($id) from offer: ".$offer['id']." ( $i of " . count($offer['prelanders']) . " ).";
  364. $i++;
  365. }
  366. }
  367.  
  368. return $offer;
  369. }
  370.  
  371. public function __destruct(){
  372. echo PHP_EOL ;
  373. }
  374.  
  375.  
  376. public function toPgArray($set) {
  377. settype($set, 'array'); // can be called with a scalar or array
  378. $result = array();
  379. foreach ($set as $t) {
  380. if (is_array($t)) {
  381. $result[] = to_pg_array($t);
  382. } else {
  383. $t = str_replace('"', '\\"', $t); // escape double quote
  384. if (! is_numeric($t)) // quote only non-numeric values
  385. $t = '"' . $t . '"';
  386. $result[] = $t;
  387. }
  388. }
  389. return '{' . implode(",", $result) . '}'; // format
  390. }
  391.  
  392.  
  393. public function getPrelanders($offer, $prelanders = array() ){
  394. $campaigns = " AND CL_CAMPAIGNID IN (".implode(',', $offer['campaigns']).") ";
  395.  
  396. foreach($this->days as $k => $d ){
  397. echo "\r Getting prelanders and targetting for offer: " . $offer['id'] . ". On date ($d)";
  398. $sql ="SELECT count(1) c, CSO_URL url, CL_OFFERID offerid, CSO_NAME name, CL_COUNTRY country, CLE_WAP_CARRIER carrier FROM `CLICKS_$d`, `CLICKS_EXTRA_$d`, CAMPAIGNS_SPLASH_OFFER WHERE CL_ID = CLE_CLICKID AND CSO_CAMPAIGNID = CL_CAMPAIGNID AND CSO_SPOFID = CL_OFFERID AND CSO_SPOFTYPE = 1 AND CL_OFFERID = " . $offer['offid'] . " $campaigns AND (CL_CLICKDATE IS NULL OR CL_CLICKDATE <> CL_VIEWDATE) GROUP BY CL_COUNTRY, CLE_WAP_CARRIER ORDER BY c desc LIMIT 10";
  399. try{
  400. $stmt = $this->pdo->query($sql);
  401. $t_prelanders = $stmt->fetchAll();
  402. }catch(Exception $e){
  403. echo PHP_EOL . 'Connection broke.... Reconnecting ';
  404. $this->connect();
  405. $stmt = $this->pdo->query($sql);
  406. $t_prelanders = $stmt->fetchAll();
  407. }
  408.  
  409. if($t_prelanders && !empty($t_prelanders)){
  410. foreach( $t_prelanders as $k => $v ){
  411. $carrier = false;
  412.  
  413. $checkCarriersSQL = "SELECT id FROM isps WHERE isp LIKE '%" . $v['carrier'] . "%'";
  414. $response = pg_query($checkCarriersSQL);
  415. $response = pg_fetch_assoc($response);
  416. if(isset($response['id'])){
  417. $carrier = (int)$response['id'];
  418. }else{
  419. $carrier = $v['carrier'];
  420. }
  421.  
  422. if(isset($prelanders[$v['url']])){
  423. $prelanders[$v['url']]['targeting']['countries'][] = $v['country'];
  424. $prelanders[$v['url']]['targeting']['carriers'][] = $carrier;
  425. $prelanders[$v['url']]['oldIds'][] = $v['offerid'];
  426. }else{
  427. $prelanders[$v['url']] = [
  428. 'url'=>$v['url'],
  429. 'name'=>$v['name'],
  430. 'oldIds'=>[$v['offerid']],
  431. 'targeting'=>[
  432. 'countries'=>[$v['country']],
  433. 'carriers'=>[$carrier]
  434. ]
  435. ];
  436. }
  437. $prelanders[$v['url']]['targeting']['countries'] = array_unique($prelanders[$v['url']]['targeting']['countries']);
  438. $prelanders[$v['url']]['targeting']['carriers'] = array_unique($prelanders[$v['url']]['targeting']['carriers']);
  439. }
  440. }
  441. }
  442.  
  443. return $prelanders;
  444. }
  445.  
  446.  
  447. public function checkForSid($sid){
  448.  
  449. for($i = 0; $i < 30; $i++)
  450. $days[] = date("Y-m-d", strtotime('-'. $i .' days'));
  451.  
  452. foreach($days as $day){
  453. $sql = "SELECT * FROM `SIDS_$day` WHERE SID_TRANSACTIONID LIKE '$sid';";
  454.  
  455. $stmt = $this->pdo->query($sql);
  456. $res = $stmt->fetchAll();
  457. echo $day . PHP_EOL;
  458. print_r($res);
  459.  
  460. }
  461.  
  462. }
  463.  
  464. public function connect(){
  465. $host = '';
  466. $db = '';
  467. $user = '';
  468. $pass = '';
  469. $charset = 'utf8';
  470. /*
  471. if($this->envOrig == 'test'){
  472. $host = 'managerr.codqkrwf1rnt.us-east-1.rds.amazonaws.com';
  473. $db = 'test';
  474. $user = 'octavi';
  475. $pass = 'nhfrnjhbcn';
  476. }
  477. */
  478.  
  479. $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
  480.  
  481. $opt = [
  482. PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  483. PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  484. PDO::ATTR_EMULATE_PREPARES => false,
  485. ];
  486.  
  487.  
  488.  
  489. $this->pdo = new PDO($dsn, $user, $pass, $opt);
  490.  
  491. }
  492.  
  493. public function connect_psql(){
  494. $host = "host = 127.0.0.1";
  495. $port = "port = 5433";
  496. $dbname = "dbname = maverick";
  497. $credentials = "user = postgres password=cfvceyu";
  498.  
  499. if($this->env == 'tequila'){
  500. $host = "host = 52.21.64.218";
  501. $port = "port = 5432";
  502. $dbname = "dbname = maverick-api-db";
  503. $credentials = "user = maverick password=NhEfU45GiocNwSRp7rkh";
  504. }
  505.  
  506. $this->psql = pg_connect( "$host $port $dbname $credentials" );
  507. if(!$this->psql) {
  508. echo "Error : Unable to open database\n";
  509. } else {
  510. }
  511. }
  512.  
  513. public function getOffersRevenues($offers){
  514. echo PHP_EOL . "GETTING OFFER REVENUES...." . PHP_EOL ;
  515.  
  516. $i = 0;
  517. foreach($offers as $k => $v ) {
  518. $i++;
  519. $offers[$k]['quantity'] = $v['revenue'] ;
  520. $offers[$k]['precentage'] = 0 ;
  521. unset($offers[$k]['revenue']);
  522.  
  523. foreach($v['campaigns'] as $kc => $vc){
  524.  
  525. $sql = "SELECT CCP_STARTD `date`,CCP_ADPUBID pubid ,CCP_CAMPAIGNID cgid, CCP_AMOUNT/100 as quantity, CCP_PERCENT as percentage
  526. FROM CAMPAIGNS_CUSTOM_PAYOUT
  527. WHERE CCP_TYPE = 3 AND CCP_CAMPAIGNID = " . $vc . " AND CCP_ADPUBID = ". $v['advertiser'] . "
  528. ORDER BY CCP_STARTD DESC LIMIT 1";
  529.  
  530. // echo PHP_EOL . $sql . PHP_EOL;
  531. echo "\r(".$i." of ".count($offers).") - Offer in campaignid: ". $vc . " ";
  532.  
  533. try{
  534. $stmt = $this->pdo->query($sql);
  535. $revenues = $stmt->fetchAll();
  536. } catch (Exception $e) {
  537. echo $sql;
  538. die("Oh noes! There's an error in the query!");
  539. }
  540.  
  541. foreach($revenues as $m => $n)
  542. $offers[$k]['revenues'][] = ['quantity'=>$n['quantity'],'default'=>'no','startDate'=>$n['date']];
  543.  
  544. }
  545.  
  546. foreach($v['campaigns'] as $mk=>$mv){
  547. $msql = "SELECT * FROM CUSTOM_PAYOUT WHERE CP_CAMPAIGNID = " . $mv . " AND CP_ADVERTISERID = " . $v['advertiser'];
  548. $stmt = $this->pdo->query($msql);
  549. $c_revenues = $stmt->fetchAll();
  550.  
  551. if($c_revenues && !empty($c_revenues)){
  552. foreach($c_revenues as $z => $y)
  553. $offers[$k]['global_campaigns'][] = $y;
  554. }
  555. }
  556. }
  557.  
  558. return $offers;
  559.  
  560. }
  561.  
  562. public function insertOffersRevenues($offers){
  563.  
  564.  
  565.  
  566.  
  567. foreach($offers as $k => $offer){
  568. $this->insertOfferRevenues($offer);
  569. }
  570. return $offers;
  571. }
  572.  
  573.  
  574. public function insertOfferRevenues($offer){
  575. $priority = 1;
  576. if(isset($offer['revenues']) && !empty($offer['revenues'])){
  577. $payoutModel = $offer['revenueModel'] == 1 ? 'RPA' : $offer['revenueModel'] == 3 ? 'RPC' : false ;
  578. $i = 1;
  579. foreach($offer['revenues'] as $k => $revenues){
  580. echo "\r INSERTING CUSTOM REVENUES OFFER ID: ".$offer['id']." (".$i." of ".count($offer['revenues']).") ";
  581. $i++;
  582. $payout = $offer['currency'] == 'USD' ? ['EUR'=> round($revenues['quantity'] * (1/$this->rates),4), 'USD'=>round($revenues['quantity'],4)] : ['EUR'=>round($revenues['quantity'],4),'USD' => round($revenues['quantity']*$this->rates ,4)];
  583. $payout = json_encode($payout);
  584. $sql = 'INSERT INTO "public"."payouts" ("payout", "priority" ,"payoutModel", "createdAt", "offerId","startDate", "updatedAt", "createdById","updatedById", "publisherId")';
  585. $sql .= " VALUES " ;
  586. $sql .= " ('$payout' , $priority ,'$payoutModel', current_timestamp, ".$offer['id'].", ('".$revenues['startDate']." 00:00:00')::timestamp, current_timestamp ,1,1,1)" ;
  587. pg_query($sql);
  588. $priority++;
  589. }
  590. }
  591.  
  592. $i = 1;
  593. if(isset($offer['global_campaigns']) && !empty($offer['global_campaigns'])){
  594. foreach($offer['global_campaigns'] as $gk => $gc){
  595. $payout = '{"EUR": '.round($gc['CP_REVENUE_EUR'],4).',"USD":'.round($gc['CP_REVENUE_USD'],4).'}';
  596. $payoutModel = $gc['CP_REVENUE_MODEL'] == 1 ? 'RPA' : $gc['CP_REVENUE_MODEL'] == 3 ? 'RPC' : false ;
  597. echo "\r Inserting offer id: ".$offer['id']." (" . $i++ . " of ".count($offer['global_campaigns']).") ";
  598. $sql = 'INSERT INTO "public"."payouts" ("payout", "priority" ,"payoutModel", "createdAt", "offerId","startDate", "updatedAt", "createdById","updatedById", "publisherId")';
  599. $sql .= " VALUES " ;
  600. $sql .= " ('$payout' , $priority ,'$payoutModel', current_timestamp, ".$offer['id'].", current_timestamp, current_timestamp ,1,1,1)" ;
  601. pg_query($sql);
  602. $priority++;
  603. }
  604. }
  605. }
  606.  
  607. public function insertOffersTargeting($offers){
  608. foreach($offers as $offer){
  609. $sql = "INSERT INTO \"targetingDetails\" (\"countries\", \"countriesInclude\", \"ispsInclude\", \"offerId\") ";
  610. $sql .= "VALUES ";
  611.  
  612. if(!isset($offer['targeting'])) continue;
  613.  
  614.  
  615. $sql .= "('{".implode(',',$offer['targeting']['countries'])."}', true, true, " . $offer['id'] . ") RETURNING \"id\";";
  616.  
  617. $result = pg_query($sql);
  618.  
  619. $id = false;
  620. while ($row = pg_fetch_array($result)) $id = $row['id'];
  621.  
  622. if($id && !empty($offer['targeting']['carriers'])){
  623. foreach($offer['targeting']['carriers'] as $isps){
  624. if(is_int($isps)){
  625. $sql2 = "INSERT INTO \"targetingDetailIsps\" (\"createdAt\", \"updatedAt\", \"targetingDetailId\", \"ispId\") VALUES (current_timestamp, current_timestamp, $id, $isps);";
  626. $result = pg_query($sql2);
  627. }else{
  628. echo PHP_EOL . 'unknown carrier: '.$isps ;
  629. }
  630. }
  631. }
  632. }
  633.  
  634. }
  635.  
  636. public function getOffersTargetingDetails($offers){
  637.  
  638. echo PHP_EOL ;
  639. $campaigns = '';
  640. foreach($offers as $v => $o){
  641. $campaigns = "AND CL_CAMPAIGNID IN (" . implode(',', $o['campaigns']) . ")";
  642.  
  643. foreach($this->days as $k => $d ){
  644.  
  645. //check Countries
  646. echo "\r Checking for Countries on Offer id: " .$o['offid'] . ". Day($d) ";
  647. $sqlCountries = "SELECT count(1) max, CL_COUNTRY FROM `CLICKS_$d`, `CLICKS_EXTRA_$d` WHERE CL_ID = CLE_CLICKID AND CL_OFFERID = " . $o['offid'] . " $campaigns GROUP BY CL_COUNTRY ORDER BY max desc LIMIT 10";
  648. $stmt = $this->pdo->query($sqlCountries);
  649. $res = $stmt->fetchAll();
  650. if($res && count($res)> 0){
  651. foreach($res as $m=> $n){
  652. $offers[$v]['targeting']['countries'][] = $n['CL_COUNTRY'];
  653. }
  654. $offers[$v]['targeting']['countries'] = array_unique( $offers[$v]['targeting']['countries']);
  655. }
  656.  
  657. //check OS
  658. echo "\r Checking for OS types on Offer id: " .$o['offid'] . ". Day($d) ";
  659. $sqlOS = "SELECT count(1) max, CLE_WAP_OS FROM `CLICKS_$d`, `CLICKS_EXTRA_$d` WHERE CL_ID = CLE_CLICKID AND CL_OFFERID = " . $o['offid'] . " $campaigns GROUP BY CLE_WAP_OS ORDER BY max desc LIMIT 10";
  660.  
  661. $stmt = $this->pdo->query($sqlOS);
  662. $res = $stmt->fetchAll();
  663.  
  664. if($res && count($res)> 0){
  665. foreach($res as $m=> $n){
  666. $offers[$v]['targeting']['os'][] = $n['CLE_WAP_OS'];
  667. }
  668. $offers[$v]['targeting']['os'] = array_unique( $offers[$v]['targeting']['os']);
  669. }
  670.  
  671. //check ISP
  672. echo "\r Checking for Carriers types on Offer id: " .$o['offid'] . ". Day($d) ";
  673. $sqlISP = "SELECT count(1) max, CLE_WAP_CARRIER FROM `CLICKS_$d`, `CLICKS_EXTRA_$d` WHERE CL_ID = CLE_CLICKID AND CL_OFFERID = " . $o['offid'] . " $campaigns GROUP BY CLE_WAP_CARRIER ORDER BY max desc LIMIT 10";
  674. $stmt = $this->pdo->query($sqlISP);
  675. $res = $stmt->fetchAll();
  676.  
  677. if($res && count($res)> 0){
  678. foreach($res as $m=> $n){
  679. $checkCarriersSQL = "SELECT id FROM isps WHERE isp LIKE '%" . $n['CLE_WAP_CARRIER'] . "%'";
  680. $response = pg_query($checkCarriersSQL);
  681. $response = pg_fetch_assoc($response);
  682. if(isset($response['id'])){
  683. $offers[$v]['targeting']['carriers'][] = (int)$response['id'];
  684. }else{
  685. $offers[$v]['targeting']['carriers'][] = $n['CLE_WAP_CARRIER'];
  686. }
  687. }
  688.  
  689. $offers[$v]['targeting']['carriers'] = array_unique($offers[$v]['targeting']['carriers']);
  690.  
  691. }
  692. }
  693. }
  694.  
  695.  
  696. return $offers;
  697. }
  698.  
  699. public function insertOffers($offers){
  700.  
  701. $i = 0;
  702. echo PHP_EOL;
  703.  
  704.  
  705. foreach($offers as $k=> $offer){
  706. $i++;
  707. echo "\r(".$i." of ".count($offers).") - Inserting Offer... : ";
  708. $offers[$k]['id'] = $this->insertOffer($offer);
  709. echo "\r(".$i." of ".count($offers).") - Offer " . $offers[$k]['id'] . " INSERTED ";
  710. }
  711.  
  712.  
  713.  
  714. return $offers;
  715. }
  716.  
  717. public function insertOfferCampaigns($offer = []){
  718.  
  719.  
  720. foreach($offer['campaigns'] as $c){
  721. $cmd = './../Serhii/scripts/app -c ' . $c . ' -o ' . $offer['id'];
  722. shell_exec($cmd);
  723. echo "\r". ' campaign: ' . $c . ' offerid: ' . $offer['id'] ;
  724.  
  725. }
  726.  
  727.  
  728. }
  729.  
  730. public function insertOffer($v){
  731. extract($v);
  732.  
  733. $payout = $currency == 'USD' ? ['EUR'=> round($quantity * (1/$this->rates),4), 'USD'=>round($v['quantity'],4)] : ['EUR'=>round($quantity,4), 'USD'=>round($quantity * $this->rates,4)];
  734. $payoutModel = $revenueModel == 1 ? 'RPA' : $revenueModel == 3 ? 'RPC' : false ;
  735.  
  736. $abs = !isset($percentage) || !$precentage ? json_encode($payout,true) : $percentage ;
  737. $abs = isset($percentage) ? $percentage : json_encode($payout,true) ;
  738.  
  739. $sql = "INSERT INTO \"offers\" (\"name\",\"description\",\"payout\", \"payoutModel\",\"url\", \"updatedAt\",\"createdAt\", \"createdById\", \"accountId\", \"managerId\", \"advertiserId\",\"updatedById\") ";
  740. $sql .= " VALUES ( '" . str_replace("'","''",$name) . "', '', '". $abs ."','$payoutModel' ,'".$url."', current_timestamp , current_timestamp , 1,1,1, $advertiser,1) RETURNING \"id\" ";
  741.  
  742. $result = pg_query($sql);
  743.  
  744. $id = false;
  745. while ($row = pg_fetch_array($result)) $id = $row['id'];
  746.  
  747. return $id;
  748.  
  749. }
  750.  
  751. public function getOffers($ids){
  752.  
  753. $sql ="SELECT GROUP_CONCAT(DISTINCT t.cgid) campaigns,t.type as `revenueModel`, MAX(t.name) as `name`,t.offid offid, t.url, t.advid advertiser,t.revenue/100 `revenue` FROM (
  754. SELECT CSO_REVENUE_TYPE as type, CSO_REVENUE_AMOUNT as `revenue`, CSO_CAMPAIGNID cgid, COUNT(1) AS counter, MAX(CSO_NAME) AS name, CSO_URL url, CSO_SPOFID AS offid, CSO_ADVERTISERID advid
  755. FROM CAMPAIGNS_SPLASH_OFFER, CAMPAIGNS
  756. WHERE CSO_CAMPAIGNID = CA_ID AND CSO_SPOFTYPE = 2
  757. AND CSO_CAMPAIGNID IN (".implode(',',$ids).")
  758. GROUP BY CSO_URL
  759. UNION ALL
  760. SELECT CSO_REVENUE_TYPE as type, CSO_REVENUE_AMOUNT as `revenue`,CSO_CAMPAIGNID cgid, COUNT(1) AS counter, MAX(CSO_NAME) AS name, CSO_URL url, CSO_SPOFID AS offid, CSO_ADVERTISERID advid
  761. FROM CAMPAIGNS_SPLASH_OFFER, CAMPAIGNS
  762. WHERE CSO_CAMPAIGNID = CA_ID AND CA_OFFER_RULES NOT LIKE '{}' AND CA_OFFER_RULES NOT LIKE '[]' AND CA_OFFER_RULES IS NOT NULL
  763. AND CSO_SPOFTYPE = 2 AND CSO_ACTIVE = 1 AND CSO_SHARE > 0
  764. AND CSO_CAMPAIGNID IN (".implode(',',$ids).")
  765. GROUP BY CSO_URL
  766. ) t GROUP BY t.url
  767. ";
  768.  
  769. $stmt = $this->pdo->query($sql);
  770. $res = $stmt->fetchAll();
  771.  
  772. foreach($res as $k=> $v){
  773. $res[$k]['campaigns'] = explode(',',$v['campaigns']);
  774. }
  775.  
  776. return $res;
  777.  
  778. }
  779.  
  780.  
  781. public function hotfixes(){
  782. echo PHP_EOL . 'ADDITIONAL PATCHES:';
  783. echo PHP_EOL . 'REMOVING NON EXISTING COUNTRY CODES (AN):';
  784. $sql1 ="DELETE FROM public.payouts WHERE country like 'AN';";
  785.  
  786.  
  787. }
  788.  
  789.  
  790. public function insertCustomCost($costs, $globalCampaigns, $payoutModel, $cgid, $currency){
  791. //we insert campaign custom costs.
  792. $priority = 1;
  793. $l = 0;
  794. foreach($costs as $k=> $v){
  795. $l++;
  796. echo "\r Campaign $cgid. Custom Costs( $l of ".count($costs)." ) ";
  797.  
  798.  
  799. $payout = $currency == 'USD' ? ['EUR'=> round($v['quantity'] * (1/$this->rates),4), 'USD'=>round($v['quantity'],4)] : ['EUR'=>round($v['quantity'],4), 'USD'=>round($v['quantity'] * $this->rates,4)];
  800. $payout = json_encode($payout);
  801.  
  802. if($payoutModel == 'REVENUE_SHARE'){
  803. $payout = json_encode(['share'=>$v['percentage']]);
  804. }
  805.  
  806.  
  807. if($v['managerId'] == 74)
  808. $v['managerId'] = 1;
  809.  
  810. $sql = 'INSERT INTO "public"."payouts" ("payout", "priority" ,"payoutModel", "createdAt", "updatedAt", "campaignId", "createdById","updatedById", "publisherId")';
  811. $sql .= " VALUES " ;
  812. $sql .= " ('$payout' , $priority ,'$payoutModel', current_timestamp, current_timestamp, $cgid, ".$v['managerId'].",".$v['managerId'].",".$v['publisher']." )" ;
  813.  
  814. $result = pg_query($sql);
  815. $priority++;
  816. }
  817.  
  818. //we insert global campaigns
  819. $e = 0;
  820. foreach($globalCampaigns as $gk => $gv){
  821. $e++;
  822. echo "\r Campaign $cgid. Global campaigns( $e of ".count($globalCampaigns)." ) ";
  823. $priority++;
  824. $payout = json_encode(['EUR'=>round((float)$gv['CP_COST_EUR'],4), 'USD'=>round((float)$gv['CP_COST_USD'],4)]);
  825. $payoutModel = $gv['CP_COST_MODEL'] == '1' ? 'CPA' : 'CPC';
  826.  
  827. if($gv['CP_MANAGERID'])
  828. $gv['CP_MANAGERID'] = 1 ;
  829.  
  830.  
  831. $sqla = 'INSERT INTO "public"."payouts" ("payout", "priority" ,"country","payoutModel", "createdAt", "updatedAt", "campaignId", "createdById","updatedById", "publisherId")';
  832. $sqla .= " VALUES " ;
  833. $sqla .= " ('$payout' , $priority ,'".$gv['CP_COUNTRY']."','$payoutModel', current_timestamp, current_timestamp, ".$gv['CP_CAMPAIGNID'].", ".$gv['CP_MANAGERID'].",".$gv['CP_MANAGERID'].",".$gv['CP_PUBLISHERID']." )" ;
  834.  
  835. $result = pg_query($sqla);
  836. }
  837.  
  838.  
  839. /*
  840. INSERT INTO "public"."payouts" ("id", "payout", "payoutModel", "createdAt", "updatedAt", "deletedAt", "campaignId", "createdById", "publisherId") VALUES (E'1', E'{"EUR": 0.0001, "USD": 0.00011179}', E'CPC', E'2017-05-26 14:41:51.742+00', E'2017-05-26 14:41:51.742+00', NULL, E'1', E'1', E'138');
  841. */
  842. }
  843.  
  844.  
  845. public function insertCampaigns($campaigns){
  846. echo PHP_EOL . 'INSERTING CAMPAIGNS' . PHP_EOL;
  847. $count = 1;
  848. foreach($campaigns as $k=> $c){
  849. echo "\r Campaign ".$c['id'].". INSERTING ";
  850. $this->insertCampaign($c);
  851. echo "\r Campaign ".$c['id'].". DONE ";
  852.  
  853. $count ++;
  854. }
  855.  
  856. }
  857.  
  858.  
  859.  
  860.  
  861. public function insertCampaign($campaign){
  862. extract($campaign);
  863.  
  864. $hideReferer = $hideReferer == 1 ? 'true' : 'false' ;
  865. $payout = $currency == 'USD' ? ['EUR'=>($cost*(1/$this->rates)), 'USD'=>$cost] : ['EUR'=>$cost, 'USD'=>$cost*$this->rates];
  866.  
  867. $payout = json_encode($payout,true);
  868.  
  869. $description = str_replace("'","''",$description);
  870. $name = str_replace("'","''",$name);
  871.  
  872.  
  873. $payoutModel = $costModel == 1 ? 'CPA' : $costModel == 3 ? 'CPC' : 'REVENUE_SHARE';
  874.  
  875. if($payoutModel == 'REVENUE_SHARE'){
  876. $payout = json_encode(['share'=>(int)$percent]);
  877. }
  878.  
  879. if($managerId == 74){
  880. $managerId = 1;
  881. }
  882.  
  883.  
  884. $sql = 'INSERT INTO "public"."campaigns" ("id","payoutModel", "status","description", "hideReferrer","name", "payout", "createdAt", "updatedAt", "managerId", "createdById", "updatedById")';
  885. $sql .= " VALUES " ;
  886. $sql .= " ( $id, '$payoutModel', 1, '$description', $hideReferer, '$name', '$payout', current_timestamp,current_timestamp, $managerId, $managerId, $managerId)" ;
  887.  
  888. $result = pg_query($sql);
  889.  
  890. $this->insertCustomCost($costs, $globalCampaigns, $payoutModel, $id , $currency );
  891.  
  892.  
  893. /*
  894. INSERT INTO "public"."campaigns" ("id", "status", "name", "schedule", "payout", "createdAt", "updatedAt", "deletedAt", "managerId", "createdById", "updatedById") VALUES (E'2', E'1', E'Flashlight Smart Link', NULL, E'{"EUR": 0, "USD": 0}', E'2017-05-25 18:21:52.999+00', E'2017-05-25 19:37:41.569+00', NULL, E'1', E'1', E'1');
  895. */
  896. }
  897.  
  898. public function getCampaigns($cgids){
  899.  
  900. echo PHP_EOL . 'GETTING ('.count($cgids).')CAMPAIGNS INFO:... ';
  901.  
  902. $cgids = array_unique($cgids);
  903.  
  904. $sql2 = "SELECT CA_ID as id, CA_CUT as scrub, CA_NAME as name, CA_DESC as description,UR_EMAIL email,CA_COST_PERCENT percent, CA_PREVIEW_URL as 'preview url', CA_COST_TYPE costModel , CA_COST_AMOUNT/100 as cost, CA_COST_CURRENCY as currency, CA_CUT scrub, CA_HIDE_REF as `hideReferer`,CA_CUT as scrub, CA_OFFER_RULES rules,CA_OWNERID managerId FROM CAMPAIGNS LEFT JOIN USERS ON UR_ID = CA_OWNERID ";
  905. /*
  906. if($this->envOrig == 'test')
  907. $cgids= [18786,18785];
  908. */
  909.  
  910. $sql2 .= "WHERE CA_ID IN (" . implode(',',$cgids) . ")";
  911.  
  912.  
  913. $this->connect();
  914.  
  915.  
  916. $stmt2 = $this->pdo->query($sql2);
  917. $campaigns = $stmt2->fetchAll();
  918.  
  919. foreach( $campaigns as $k => $v ){
  920. $this->campaigns[$v['id']] = $v;
  921. }
  922.  
  923. echo PHP_EOL . 'GETTING COST DATA CUSTOM_PAYOUT + GLOBAL_CAMPAIGNS'. PHP_EOL ;
  924.  
  925. $i = 1;
  926. foreach( $this->campaigns as $k => $v ){
  927. $sql = "SELECT latest.amount/100 quantity , latest.percent percentage , CCP_ADPUBID as publisher, CCP_ACCOUNTID managerId FROM CAMPAIGNS_CUSTOM_PAYOUT
  928. LEFT JOIN
  929. (
  930. SELECT CCP_ADPUBID pubid ,CCP_CAMPAIGNID cgid, CCP_AMOUNT as amount, CCP_PERCENT as percent
  931. FROM CAMPAIGNS_CUSTOM_PAYOUT
  932. WHERE CCP_TYPE = 2
  933. ORDER BY CCP_STARTD DESC
  934. ) latest
  935. ON latest.pubid = CCP_ADPUBID AND latest.cgid = CCP_CAMPAIGNID
  936. WHERE CCP_CAMPAIGNID = " . $v['id'] . " AND CCP_TYPE = 2 GROUP BY CCP_ADPUBID ORDER BY CCP_ID DESC;";
  937.  
  938. $stmt = $this->pdo->query($sql);
  939. $cost = $stmt->fetchAll();
  940.  
  941. $this->campaigns[$k]['costs'] = $cost;
  942. // echo 'Campaign Cost (' . $i++ . ' of '. count($this->campaigns) .')'. '\r';
  943. $sqlSub = "SELECT * FROM CUSTOM_PAYOUT WHERE CP_CAMPAIGNID = " . (int) $v['id'];
  944. $stmt = $this->pdo->query($sqlSub);
  945. $globalCampaigns = $stmt->fetchAll();
  946.  
  947. $this->campaigns[$k]['globalCampaigns'] = $globalCampaigns ;
  948.  
  949. $this->displayProgress($i++, count($this->campaigns));
  950. }
  951.  
  952. return $this->campaigns;
  953.  
  954. }
  955.  
  956. public function displayProgress($current, $total) {
  957. echo "\r ($current of $total) Completed";
  958. }
  959.  
  960.  
  961. public function getOfferIdsLastDays(){
  962. $offerIds = [];
  963.  
  964. foreach($this->days as $k => $d ){
  965. $sql = "SELECT DISTINCT CL_CAMPAIGNID as cgid, CL_OFFERID AS offerid, count(1) as `counter` FROM `CLICKS_$d` GROUP BY CL_CAMPAIGNID, CL_OFFERID";
  966.  
  967.  
  968.  
  969. $stmt = $this->pdo->query($sql);
  970. $res = $stmt->fetchAll();
  971.  
  972.  
  973.  
  974.  
  975. foreach($res as $k => $v){
  976. if($v['counter'] > $this->minTraff)
  977. $offerIds[$v['cgid'] . '.' . $v['offerid']] = [
  978. 'offerid'=>$v['offerid'],
  979. 'campaignid'=>$v['cgid']
  980. ];
  981. }
  982. }
  983.  
  984. return $offerIds;
  985. }
  986.  
  987.  
  988.  
  989.  
  990.  
  991. public function getCompanies($users){
  992. echo PHP_EOL . 'GETTING COMPANIES...' ;
  993. $sql = "SELECT DISTINCT(SUBSTRING_INDEX(SUBSTRING_INDEX(UR_COMPANY_NAME, '_', -1), '@', 1)) company, SUBSTRING_INDEX(SUBSTRING_INDEX(UR_COMPANY_NAME, '@', -1), '_', -1) managing_company, UR_EMAIL as email FROM USERS";
  994.  
  995.  
  996. $stmt = $this->pdo->query($sql);
  997. $res = $stmt->fetchAll();
  998.  
  999. $companies = [];
  1000.  
  1001. $i = 0;
  1002. foreach($res as $v){
  1003.  
  1004. if(!isset($companies[trim($v['company'])]))
  1005. $companies[trim($v['company'])] = $i++ ;
  1006.  
  1007. if(!isset($companies[trim($v['managing_company'])]))
  1008. $companies[trim($v['managing_company'])] = $i++;
  1009. }
  1010.  
  1011. $t_companies = [];
  1012.  
  1013. $sql2 = "SELECT SUBSTRING_INDEX(UR_COMPANY_NAME ,'@',-1) as company FROM USERS WHERE UR_COMPANY_NAME LIKE '%@%' AND SUBSTRING_INDEX(UR_COMPANY_NAME ,'@',-1) NOT LIKE '' GROUP BY SUBSTRING_INDEX(UR_COMPANY_NAME ,'@',-1);";
  1014.  
  1015. $stmt2 = $this->pdo->query($sql2);
  1016. $res2 = $stmt2->fetchAll();
  1017.  
  1018. foreach ($companies as $k => $v){
  1019. $email = null;
  1020. $direct_partner = 0;
  1021.  
  1022. foreach($res as $t => $z){
  1023. if(trim($z['company']) == trim($k)){
  1024. $email = $z['email'];
  1025. }
  1026. }
  1027.  
  1028. foreach ($res2 as $l => $m)
  1029. if(trim($m['company']) == trim($k))
  1030. $direct_partner = 1;
  1031.  
  1032.  
  1033. $currency = 'USD';
  1034. $createdBy = 1;
  1035. foreach ($users as $u){
  1036. if(trim($u['email']) == $email){
  1037. $currency = $u['currency'];
  1038. $createdBy = $u['id'];
  1039. }
  1040. }
  1041.  
  1042. $t_companies[] = [
  1043. 'email'=>$email,
  1044. 'name'=>$k,
  1045. 'direct'=>$direct_partner,
  1046. 'currency'=>$currency,
  1047. 'createdBy'=>$createdBy
  1048. ];
  1049. }
  1050.  
  1051.  
  1052.  
  1053. echo count($t_companies) . ' COMPANIES FOUND';
  1054.  
  1055. return $t_companies;
  1056.  
  1057. }
  1058.  
  1059.  
  1060. public function insertCompanies($companies){
  1061. echo PHP_EOL . 'STARTING COMPANY INSERTS...' .PHP_EOL;
  1062.  
  1063. $count = 1;
  1064. foreach($companies as $k => $v){
  1065. echo "\r (".$count." of ".count($companies).") - Company: ". $v['name'] . "[XX] INSERTING ";
  1066.  
  1067. $d = $v['direct'] == 1 ? 'true' : 'false';
  1068. $sql = "INSERT INTO \"companies\" (\"email\",\"directPartner\",\"name\",\"currency\", \"createdAt\", \"updatedAt\", \"createdById\") VALUES ( '" . str_replace("'","''",$v['email']) . "', " . $d . ", '" . str_replace("'","''",$v['name']) . "', '".$v['currency']."' ,current_timestamp,current_timestamp, ".$v['createdBy'].") RETURNING \"id\"";
  1069.  
  1070. $result = pg_query($sql);
  1071. while ($row = pg_fetch_array($result)) $companies[$k]['id'] = $row['id'];
  1072.  
  1073. echo "\r (".$count." of ".count($companies).") - Company: ". $v['name'] . "[".$companies[$k]['id']."] INSERTED ";
  1074.  
  1075. $count++;
  1076. }
  1077.  
  1078. echo PHP_EOL . $count . ' INSERTED COMPANIES';
  1079.  
  1080. return $companies;
  1081. }
  1082.  
  1083.  
  1084. public function insertUsers($users, $companies){
  1085. echo PHP_EOL . 'USER INSERTS' . PHP_EOL;
  1086.  
  1087. $i = 1;
  1088. foreach($users as $user){
  1089.  
  1090. echo "\r (".$i." of ".count($users).") - User ". $user['email'] ." [". $user['id'] ."] INSERTING ";
  1091.  
  1092.  
  1093. //calculate the company ID from the company array.
  1094. $companyId = 1;
  1095. foreach($companies as $kk => $kv){
  1096.  
  1097. if(trim(strtolower($kv['name'])) == trim(strtolower($user['company']))){
  1098. $companyId = (int) $kv['id'];
  1099. break;
  1100. }
  1101. }
  1102.  
  1103. //who is the main manager of a MANAGER? (By default MAVERICK'S SUPER USER which is userid = 1)
  1104. //David is Userid 2.
  1105. $managerId = 1;
  1106. if($user['role'] == 'MANAGER'){
  1107. $managerId = 1;
  1108. }else{
  1109. foreach($users as $tk => $tv){
  1110. if($tv['role'] == 'MANAGER'){
  1111. if($tv['firstName'] == $user['manager']){
  1112. $managerId = $tv['id'];
  1113. }
  1114. }else{
  1115. break;
  1116. }
  1117. }
  1118. }
  1119.  
  1120. $user['is_advertiser'] = $user['is_advertiser'] == 1 ? 'true' : 'false';
  1121. $user['is_publisher'] = $user['is_publisher'] == 1 ? 'true' : 'false';
  1122.  
  1123.  
  1124. $sql = "INSERT INTO \"accounts\" (\"id\",\"email\", \"secondaryEmails\",\"firstName\", \"lastName\",\"languages\", \"phoneNumbers\", \"role\", \"advertiser\", \"publisher\", \"currency\", \"companyId\", \"managerId\", \"createdAt\", \"updatedAt\" )";
  1125.  
  1126. $sql .= "VALUES(" . $user['id'] . ",'" . $user['email'] . "', '" . $this->toPgArray([$user['email_2']]) . "', '".str_replace("'","''",$user['firstname'])."', '".str_replace("'","''",$user['surname'])."', '" . $this->toPgArray([$user['language']]) . "', " ;
  1127.  
  1128. $sql .= "'" . $this->toPgArray([$user['phone']]) . "', '".$user['role']."', " . $user['is_advertiser'] . ", ".$user['is_publisher'].", '".strtoupper($user['currency'])."', " . $companyId . " , 1 , CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)";
  1129.  
  1130.  
  1131. $result = pg_query($sql);
  1132.  
  1133.  
  1134. echo "\r (".$i." of ".count($users).") - User ". $user['email'] ." [". $user['id'] ."] INSERTED ";
  1135. $i++;
  1136. }
  1137.  
  1138. return $result;
  1139.  
  1140. }
  1141.  
  1142. public function getUserRelations($managerid = false , $allUsers = [] ){
  1143. $sqlP = "SELECT P2M_PUBLISHERID as userid FROM PUBLISHERS2MANAGER WHERE P2M_MANAGERID = $managerid";
  1144. $sqlA = "SELECT A2M_ADVERTISERID as userid FROM ADVERTISERS2MANAGER WHERE A2M_MANAGERID = $managerid";
  1145.  
  1146. $this->pdo = null;
  1147. $this->connect();
  1148.  
  1149. $stmt1 = $this->pdo->query($sqlP);
  1150. $stmt2 = $this->pdo->query($sqlA);
  1151.  
  1152. $relationsA = $stmt1->fetchAll();
  1153. $relationsB = $stmt2->fetchAll();
  1154.  
  1155. $relations = array_merge($relationsA,$relationsB) ;
  1156.  
  1157. //some users (not managers have two ids because they were two different kind of users.)
  1158. foreach($relations as $r=> $u){
  1159. foreach($allUsers as $k => $v){
  1160. if($v['id'] !== $v['oldids']){
  1161. if($u['userid'] == $v['oldids']){
  1162. //then the old id is also a user ID that needs to be inserted in the new user relations.So we add to relations the old userid.
  1163. $relations[] = ['userid'=>$v['oldids']];
  1164. }
  1165. }
  1166. }
  1167. }
  1168.  
  1169. $rel = [];
  1170. foreach($relations as $k=> $v) $rel[] = $v['userid'];
  1171. $rel = array_unique(array_filter($rel));
  1172.  
  1173.  
  1174. return $rel;
  1175. }
  1176.  
  1177. public function insertUserRelations($managerId = false, $managedUserIds = []){
  1178. if(!$managerId) return ;
  1179. if(empty($managedUserIds)) return ;
  1180.  
  1181. foreach($managedUserIds as $accountId){
  1182. $sql = "INSERT INTO \"additionalManagers\" (\"accountId\",\"managerId\",\"createdAt\",\"updatedAt\",\"type\") ";
  1183. $sql .= "VALUES (".$accountId.",$managerId, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'view')";
  1184.  
  1185. $result = @pg_query($sql);
  1186.  
  1187.  
  1188. }
  1189.  
  1190.  
  1191. }
  1192.  
  1193.  
  1194.  
  1195. public function getUsers(){
  1196. echo PHP_EOL . 'GETTING USERS...' ;
  1197.  
  1198. $sql = "SELECT
  1199. u.id,
  1200. u.oldids oldids,
  1201. SUBSTRING_INDEX(SUBSTRING_INDEX(u.name, '_', -1), '@', 1) AS company,
  1202. u.currency AS currency,
  1203. u.email AS email,
  1204. u.email_2 email_2,
  1205. if(u.firstname = '1', '',if(u.firstname = '?', '',if(u.firstname = '.', '',u.firstname))) AS firstname,
  1206. if(u.surname = '1', '',if(u.surname = '?', '', if(u.surname = '.', '',u.surname ) )) AS surname,
  1207. SUBSTRING_INDEX(u.name, '_', 1) AS manager, SUBSTRING_INDEX(SUBSTRING_INDEX(u.name, '@', -1), '_', -1) managing_company, u.is_publisher, u.is_advertiser, u.is_manager,u.role, u.`language`, IF(LENGTH(u.phone) < 7,'', IF(u.phone REGEXP '[0-9]',u.phone, '')) AS phone, IF(u.`status` = 1, 1, 0) `active`
  1208. FROM (
  1209. (SELECT UR_ID as id, MAX(UR_ID) oldids, 'USER' role, UR_PHONE AS phone, UR_LANGUAGE AS `language`, UR_STATUS AS `status`, UR_COMPANY_NAME name, UR_EMAIL email, UR_EMAIL2 email_2, UR_FIRSTNAME AS firstname, UR_LASTNAME AS surname, UR_CURRENCY currency, 1 is_publisher, 1 is_advertiser, 0 is_manager
  1210. FROM USERS
  1211. GROUP BY UR_COMPANY_NAME
  1212. HAVING COUNT(1) > 1
  1213. )
  1214. UNION ALL
  1215. (
  1216. SELECT UR_ID as id,UR_ID oldids, 'USER' role, UR_PHONE AS phone, UR_LANGUAGE AS `language`, UR_STATUS AS `status`, UR_COMPANY_NAME name, UR_EMAIL email, UR_EMAIL2 email_2, UR_FIRSTNAME AS firstname, UR_LASTNAME AS surname, UR_CURRENCY currency, 1 is_publisher, 0 is_advertiser, 0 is_manager
  1217. FROM USERS
  1218. WHERE UR_ID NOT IN (
  1219. SELECT UR_ID
  1220. FROM USERS
  1221. GROUP BY UR_COMPANY_NAME
  1222. HAVING COUNT(1) > 1) AND UR_TYPE = 2
  1223. )
  1224. UNION ALL
  1225. (
  1226. SELECT UR_ID as id,UR_ID oldids, 'USER' role, UR_PHONE AS phone, UR_LANGUAGE AS `language`, UR_STATUS AS `status`, UR_COMPANY_NAME name, UR_EMAIL email, UR_EMAIL2 email_2, UR_FIRSTNAME AS firstname, UR_LASTNAME AS surname, UR_CURRENCY currency, 0 is_publisher, 1 is_advertiser, 0 is_manager
  1227. FROM USERS
  1228. WHERE UR_ID NOT IN (
  1229. SELECT UR_ID
  1230. FROM USERS
  1231. GROUP BY UR_COMPANY_NAME
  1232. HAVING COUNT(1) > 1) AND UR_TYPE = 3
  1233. )
  1234. UNION ALL
  1235. (
  1236. SELECT UR_ID as id,UR_ID oldids, 'MANAGER' role, UR_PHONE AS phone, UR_LANGUAGE AS `language`, UR_STATUS AS `status`, UR_COMPANY_NAME name, UR_EMAIL email, UR_EMAIL2 email_2,UR_FIRSTNAME AS firstname, UR_LASTNAME AS surname, UR_CURRENCY currency, 0 is_publisher, 0 is_advertiser,1 is_manager
  1237. FROM USERS
  1238. WHERE UR_ID NOT IN (
  1239. SELECT UR_ID
  1240. FROM USERS
  1241. GROUP BY UR_COMPANY_NAME
  1242. HAVING COUNT(1) > 1) AND UR_TYPE = 4)
  1243. ) u ";
  1244. if($this->limit) $sql .= 'LIMIT 100';
  1245.  
  1246. $stmt = $this->pdo->query($sql);
  1247. $res = $stmt->fetchAll();
  1248.  
  1249. echo count($res) . ' USERS FOUND';
  1250.  
  1251. return $res;
  1252.  
  1253. }
  1254.  
  1255.  
  1256.  
  1257.  
  1258. public function domains(){
  1259. echo 'GETTING DOMAINS' . PHP_EOL;
  1260. }
  1261.  
  1262. }
  1263.  
  1264.  
  1265.  
  1266. $export = new exports(10);
  1267.  
  1268.  
  1269. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!