API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 29th, 2017
767
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 40.28 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.  
  472. $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
  473.  
  474. $opt = [
  475. PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  476. PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  477. PDO::ATTR_EMULATE_PREPARES => false,
  478. ];
  479.  
  480.  
  481.  
  482. $this->pdo = new PDO($dsn, $user, $pass, $opt);
  483.  
  484. }
  485.  
  486. public function connect_psql(){
  487. $host = "host = 127.0.0.1";
  488. $port = "port = 5433";
  489. $dbname = "dbname = maverick";
  490. $credentials = "user = postgres password=cfvceyu";
  491.  
  492.  
  493.  
  494. $this->psql = pg_connect( "$host $port $dbname $credentials" );
  495. if(!$this->psql) {
  496. echo "Error : Unable to open database\n";
  497. } else {
  498. }
  499. }
  500.  
  501. public function getOffersRevenues($offers){
  502. echo PHP_EOL . "GETTING OFFER REVENUES...." . PHP_EOL ;
  503.  
  504. $i = 0;
  505. foreach($offers as $k => $v ) {
  506. $i++;
  507. $offers[$k]['quantity'] = $v['revenue'] ;
  508. $offers[$k]['precentage'] = 0 ;
  509. unset($offers[$k]['revenue']);
  510.  
  511. foreach($v['campaigns'] as $kc => $vc){
  512.  
  513. $sql = "SELECT CCP_STARTD `date`,CCP_ADPUBID pubid ,CCP_CAMPAIGNID cgid, CCP_AMOUNT/100 as quantity, CCP_PERCENT as percentage
  514. FROM CAMPAIGNS_CUSTOM_PAYOUT
  515. WHERE CCP_TYPE = 3 AND CCP_CAMPAIGNID = " . $vc . " AND CCP_ADPUBID = ". $v['advertiser'] . "
  516. ORDER BY CCP_STARTD DESC LIMIT 1";
  517.  
  518. // echo PHP_EOL . $sql . PHP_EOL;
  519. echo "\r(".$i." of ".count($offers).") - Offer in campaignid: ". $vc . " ";
  520.  
  521. try{
  522. $stmt = $this->pdo->query($sql);
  523. $revenues = $stmt->fetchAll();
  524. } catch (Exception $e) {
  525. echo $sql;
  526. die("Oh noes! There's an error in the query!");
  527. }
  528.  
  529. foreach($revenues as $m => $n)
  530. $offers[$k]['revenues'][] = ['quantity'=>$n['quantity'],'default'=>'no','startDate'=>$n['date']];
  531.  
  532. }
  533.  
  534. foreach($v['campaigns'] as $mk=>$mv){
  535. $msql = "SELECT * FROM CUSTOM_PAYOUT WHERE CP_CAMPAIGNID = " . $mv . " AND CP_ADVERTISERID = " . $v['advertiser'];
  536. $stmt = $this->pdo->query($msql);
  537. $c_revenues = $stmt->fetchAll();
  538.  
  539. if($c_revenues && !empty($c_revenues)){
  540. foreach($c_revenues as $z => $y)
  541. $offers[$k]['global_campaigns'][] = $y;
  542. }
  543. }
  544. }
  545.  
  546. return $offers;
  547.  
  548. }
  549.  
  550. public function insertOffersRevenues($offers){
  551.  
  552.  
  553.  
  554.  
  555. foreach($offers as $k => $offer){
  556. $this->insertOfferRevenues($offer);
  557. }
  558. return $offers;
  559. }
  560.  
  561.  
  562. public function insertOfferRevenues($offer){
  563. $priority = 1;
  564. if(isset($offer['revenues']) && !empty($offer['revenues'])){
  565. $payoutModel = $offer['revenueModel'] == 1 ? 'RPA' : $offer['revenueModel'] == 3 ? 'RPC' : false ;
  566. $i = 1;
  567. foreach($offer['revenues'] as $k => $revenues){
  568. echo "\r INSERTING CUSTOM REVENUES OFFER ID: ".$offer['id']." (".$i." of ".count($offer['revenues']).") ";
  569. $i++;
  570. $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)];
  571. $payout = json_encode($payout);
  572. $sql = 'INSERT INTO "public"."payouts" ("payout", "priority" ,"payoutModel", "createdAt", "offerId","startDate", "updatedAt", "createdById","updatedById", "publisherId")';
  573. $sql .= " VALUES " ;
  574. $sql .= " ('$payout' , $priority ,'$payoutModel', current_timestamp, ".$offer['id'].", ('".$revenues['startDate']." 00:00:00')::timestamp, current_timestamp ,1,1,1)" ;
  575. pg_query($sql);
  576. $priority++;
  577. }
  578. }
  579.  
  580. $i = 1;
  581. if(isset($offer['global_campaigns']) && !empty($offer['global_campaigns'])){
  582. foreach($offer['global_campaigns'] as $gk => $gc){
  583. $payout = '{"EUR": '.round($gc['CP_REVENUE_EUR'],4).',"USD":'.round($gc['CP_REVENUE_USD'],4).'}';
  584. $payoutModel = $gc['CP_REVENUE_MODEL'] == 1 ? 'RPA' : $gc['CP_REVENUE_MODEL'] == 3 ? 'RPC' : false ;
  585. echo "\r Inserting offer id: ".$offer['id']." (" . $i++ . " of ".count($offer['global_campaigns']).") ";
  586. $sql = 'INSERT INTO "public"."payouts" ("payout", "priority" ,"payoutModel", "createdAt", "offerId","startDate", "updatedAt", "createdById","updatedById", "publisherId")';
  587. $sql .= " VALUES " ;
  588. $sql .= " ('$payout' , $priority ,'$payoutModel', current_timestamp, ".$offer['id'].", current_timestamp, current_timestamp ,1,1,1)" ;
  589. pg_query($sql);
  590. $priority++;
  591. }
  592. }
  593. }
  594.  
  595. public function insertOffersTargeting($offers){
  596. foreach($offers as $offer){
  597. $sql = "INSERT INTO \"targetingDetails\" (\"countries\", \"countriesInclude\", \"ispsInclude\", \"offerId\") ";
  598. $sql .= "VALUES ";
  599.  
  600. if(!isset($offer['targeting'])) continue;
  601.  
  602.  
  603. $sql .= "('{".implode(',',$offer['targeting']['countries'])."}', true, true, " . $offer['id'] . ") RETURNING \"id\";";
  604.  
  605. $result = pg_query($sql);
  606.  
  607. $id = false;
  608. while ($row = pg_fetch_array($result)) $id = $row['id'];
  609.  
  610. if($id && !empty($offer['targeting']['carriers'])){
  611. foreach($offer['targeting']['carriers'] as $isps){
  612. if(is_int($isps)){
  613. $sql2 = "INSERT INTO \"targetingDetailIsps\" (\"createdAt\", \"updatedAt\", \"targetingDetailId\", \"ispId\") VALUES (current_timestamp, current_timestamp, $id, $isps);";
  614. $result = pg_query($sql2);
  615. }else{
  616. echo PHP_EOL . 'unknown carrier: '.$isps ;
  617. }
  618. }
  619. }
  620. }
  621.  
  622. }
  623.  
  624. public function getOffersTargetingDetails($offers){
  625.  
  626. echo PHP_EOL ;
  627. $campaigns = '';
  628. foreach($offers as $v => $o){
  629. $campaigns = "AND CL_CAMPAIGNID IN (" . implode(',', $o['campaigns']) . ")";
  630.  
  631. foreach($this->days as $k => $d ){
  632.  
  633. //check Countries
  634. echo "\r Checking for Countries on Offer id: " .$o['offid'] . ". Day($d) ";
  635. $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";
  636. $stmt = $this->pdo->query($sqlCountries);
  637. $res = $stmt->fetchAll();
  638. if($res && count($res)> 0){
  639. foreach($res as $m=> $n){
  640. $offers[$v]['targeting']['countries'][] = $n['CL_COUNTRY'];
  641. }
  642. $offers[$v]['targeting']['countries'] = array_unique( $offers[$v]['targeting']['countries']);
  643. }
  644.  
  645. //check OS
  646. echo "\r Checking for OS types on Offer id: " .$o['offid'] . ". Day($d) ";
  647. $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";
  648.  
  649. $stmt = $this->pdo->query($sqlOS);
  650. $res = $stmt->fetchAll();
  651.  
  652. if($res && count($res)> 0){
  653. foreach($res as $m=> $n){
  654. $offers[$v]['targeting']['os'][] = $n['CLE_WAP_OS'];
  655. }
  656. $offers[$v]['targeting']['os'] = array_unique( $offers[$v]['targeting']['os']);
  657. }
  658.  
  659. //check ISP
  660. echo "\r Checking for Carriers types on Offer id: " .$o['offid'] . ". Day($d) ";
  661. $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";
  662. $stmt = $this->pdo->query($sqlISP);
  663. $res = $stmt->fetchAll();
  664.  
  665. if($res && count($res)> 0){
  666. foreach($res as $m=> $n){
  667. $checkCarriersSQL = "SELECT id FROM isps WHERE isp LIKE '%" . $n['CLE_WAP_CARRIER'] . "%'";
  668. $response = pg_query($checkCarriersSQL);
  669. $response = pg_fetch_assoc($response);
  670. if(isset($response['id'])){
  671. $offers[$v]['targeting']['carriers'][] = (int)$response['id'];
  672. }else{
  673. $offers[$v]['targeting']['carriers'][] = $n['CLE_WAP_CARRIER'];
  674. }
  675. }
  676.  
  677. $offers[$v]['targeting']['carriers'] = array_unique($offers[$v]['targeting']['carriers']);
  678.  
  679. }
  680. }
  681. }
  682.  
  683.  
  684. return $offers;
  685. }
  686.  
  687. public function insertOffers($offers){
  688.  
  689. $i = 0;
  690. echo PHP_EOL;
  691.  
  692.  
  693. foreach($offers as $k=> $offer){
  694. $i++;
  695. echo "\r(".$i." of ".count($offers).") - Inserting Offer... : ";
  696. $offers[$k]['id'] = $this->insertOffer($offer);
  697. echo "\r(".$i." of ".count($offers).") - Offer " . $offers[$k]['id'] . " INSERTED ";
  698. }
  699.  
  700.  
  701.  
  702. return $offers;
  703. }
  704.  
  705. public function insertOfferCampaigns($offer = []){
  706.  
  707.  
  708. foreach($offer['campaigns'] as $c){
  709. $cmd = './../Serhii/scripts/app -c ' . $c . ' -o ' . $offer['id'];
  710. shell_exec($cmd);
  711. echo "\r". ' campaign: ' . $c . ' offerid: ' . $offer['id'] ;
  712.  
  713. }
  714.  
  715.  
  716. }
  717.  
  718. public function insertOffer($v){
  719. extract($v);
  720.  
  721. $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)];
  722. $payoutModel = $revenueModel == 1 ? 'RPA' : $revenueModel == 3 ? 'RPC' : false ;
  723.  
  724. $abs = !isset($percentage) || !$precentage ? json_encode($payout,true) : $percentage ;
  725. $abs = isset($percentage) ? $percentage : json_encode($payout,true) ;
  726.  
  727. $sql = "INSERT INTO \"offers\" (\"name\",\"description\",\"payout\", \"payoutModel\",\"url\", \"updatedAt\",\"createdAt\", \"createdById\", \"accountId\", \"managerId\", \"advertiserId\",\"updatedById\") ";
  728. $sql .= " VALUES ( '" . str_replace("'","''",$name) . "', '', '". $abs ."','$payoutModel' ,'".$url."', current_timestamp , current_timestamp , 1,1,1, $advertiser,1) RETURNING \"id\" ";
  729.  
  730. $result = pg_query($sql);
  731.  
  732. $id = false;
  733. while ($row = pg_fetch_array($result)) $id = $row['id'];
  734.  
  735. return $id;
  736.  
  737. }
  738.  
  739. public function getOffers($ids){
  740.  
  741. $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 (
  742. 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
  743. FROM CAMPAIGNS_SPLASH_OFFER, CAMPAIGNS
  744. WHERE CSO_CAMPAIGNID = CA_ID AND CSO_SPOFTYPE = 2
  745. AND CSO_CAMPAIGNID IN (".implode(',',$ids).")
  746. GROUP BY CSO_URL
  747. UNION ALL
  748. 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
  749. FROM CAMPAIGNS_SPLASH_OFFER, CAMPAIGNS
  750. WHERE CSO_CAMPAIGNID = CA_ID AND CA_OFFER_RULES NOT LIKE '{}' AND CA_OFFER_RULES NOT LIKE '[]' AND CA_OFFER_RULES IS NOT NULL
  751. AND CSO_SPOFTYPE = 2 AND CSO_ACTIVE = 1 AND CSO_SHARE > 0
  752. AND CSO_CAMPAIGNID IN (".implode(',',$ids).")
  753. GROUP BY CSO_URL
  754. ) t GROUP BY t.url
  755. ";
  756.  
  757. $stmt = $this->pdo->query($sql);
  758. $res = $stmt->fetchAll();
  759.  
  760. foreach($res as $k=> $v){
  761. $res[$k]['campaigns'] = explode(',',$v['campaigns']);
  762. }
  763.  
  764. return $res;
  765.  
  766. }
  767.  
  768.  
  769. public function hotfixes(){
  770. echo PHP_EOL . 'ADDITIONAL PATCHES:';
  771. echo PHP_EOL . 'REMOVING NON EXISTING COUNTRY CODES (AN):';
  772. $sql1 ="DELETE FROM public.payouts WHERE country like 'AN';";
  773.  
  774.  
  775. }
  776.  
  777.  
  778. public function insertCustomCost($costs, $globalCampaigns, $payoutModel, $cgid, $currency){
  779. //we insert campaign custom costs.
  780. $priority = 1;
  781. $l = 0;
  782. foreach($costs as $k=> $v){
  783. $l++;
  784. echo "\r Campaign $cgid. Custom Costs( $l of ".count($costs)." ) ";
  785.  
  786.  
  787. $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)];
  788. $payout = json_encode($payout);
  789.  
  790. if($payoutModel == 'REVENUE_SHARE'){
  791. $payout = json_encode(['share'=>$v['percentage']]);
  792. }
  793.  
  794.  
  795. if($v['managerId'] == 74)
  796. $v['managerId'] = 1;
  797.  
  798. $sql = 'INSERT INTO "public"."payouts" ("payout", "priority" ,"payoutModel", "createdAt", "updatedAt", "campaignId", "createdById","updatedById", "publisherId")';
  799. $sql .= " VALUES " ;
  800. $sql .= " ('$payout' , $priority ,'$payoutModel', current_timestamp, current_timestamp, $cgid, ".$v['managerId'].",".$v['managerId'].",".$v['publisher']." )" ;
  801.  
  802. $result = pg_query($sql);
  803. $priority++;
  804. }
  805.  
  806. //we insert global campaigns
  807. $e = 0;
  808. foreach($globalCampaigns as $gk => $gv){
  809. $e++;
  810. echo "\r Campaign $cgid. Global campaigns( $e of ".count($globalCampaigns)." ) ";
  811. $priority++;
  812. $payout = json_encode(['EUR'=>round((float)$gv['CP_COST_EUR'],4), 'USD'=>round((float)$gv['CP_COST_USD'],4)]);
  813. $payoutModel = $gv['CP_COST_MODEL'] == '1' ? 'CPA' : 'CPC';
  814.  
  815. if($gv['CP_MANAGERID'])
  816. $gv['CP_MANAGERID'] = 1 ;
  817.  
  818.  
  819. $sqla = 'INSERT INTO "public"."payouts" ("payout", "priority" ,"country","payoutModel", "createdAt", "updatedAt", "campaignId", "createdById","updatedById", "publisherId")';
  820. $sqla .= " VALUES " ;
  821. $sqla .= " ('$payout' , $priority ,'".$gv['CP_COUNTRY']."','$payoutModel', current_timestamp, current_timestamp, ".$gv['CP_CAMPAIGNID'].", ".$gv['CP_MANAGERID'].",".$gv['CP_MANAGERID'].",".$gv['CP_PUBLISHERID']." )" ;
  822.  
  823. $result = pg_query($sqla);
  824. }
  825.  
  826.  
  827. /*
  828. 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');
  829. */
  830. }
  831.  
  832.  
  833. public function insertCampaigns($campaigns){
  834. echo PHP_EOL . 'INSERTING CAMPAIGNS' . PHP_EOL;
  835. $count = 1;
  836. foreach($campaigns as $k=> $c){
  837. echo "\r Campaign ".$c['id'].". INSERTING ";
  838. $this->insertCampaign($c);
  839. echo "\r Campaign ".$c['id'].". DONE ";
  840.  
  841. $count ++;
  842. }
  843.  
  844. }
  845.  
  846.  
  847.  
  848.  
  849. public function insertCampaign($campaign){
  850. extract($campaign);
  851.  
  852. $hideReferer = $hideReferer == 1 ? 'true' : 'false' ;
  853. $payout = $currency == 'USD' ? ['EUR'=>($cost*(1/$this->rates)), 'USD'=>$cost] : ['EUR'=>$cost, 'USD'=>$cost*$this->rates];
  854.  
  855. $payout = json_encode($payout,true);
  856.  
  857. $description = str_replace("'","''",$description);
  858. $name = str_replace("'","''",$name);
  859.  
  860.  
  861. $payoutModel = $costModel == 1 ? 'CPA' : $costModel == 3 ? 'CPC' : 'REVENUE_SHARE';
  862.  
  863. if($payoutModel == 'REVENUE_SHARE'){
  864. $payout = json_encode(['share'=>(int)$percent]);
  865. }
  866.  
  867. if($managerId == 74){
  868. $managerId = 1;
  869. }
  870.  
  871.  
  872. $sql = 'INSERT INTO "public"."campaigns" ("id","payoutModel", "status","description", "hideReferrer","name", "payout", "createdAt", "updatedAt", "managerId", "createdById", "updatedById")';
  873. $sql .= " VALUES " ;
  874. $sql .= " ( $id, '$payoutModel', 1, '$description', $hideReferer, '$name', '$payout', current_timestamp,current_timestamp, $managerId, $managerId, $managerId)" ;
  875.  
  876. $result = pg_query($sql);
  877.  
  878. $this->insertCustomCost($costs, $globalCampaigns, $payoutModel, $id , $currency );
  879.  
  880.  
  881. /*
  882. 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');
  883. */
  884. }
  885.  
  886. public function getCampaigns($cgids){
  887.  
  888. echo PHP_EOL . 'GETTING ('.count($cgids).')CAMPAIGNS INFO:... ';
  889.  
  890. $cgids = array_unique($cgids);
  891.  
  892. $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 ";
  893. /*
  894. if($this->envOrig == 'test')
  895. $cgids= [18786,18785];
  896. */
  897.  
  898. $sql2 .= "WHERE CA_ID IN (" . implode(',',$cgids) . ")";
  899.  
  900.  
  901. $this->connect();
  902.  
  903.  
  904. $stmt2 = $this->pdo->query($sql2);
  905. $campaigns = $stmt2->fetchAll();
  906.  
  907. foreach( $campaigns as $k => $v ){
  908. $this->campaigns[$v['id']] = $v;
  909. }
  910.  
  911. echo PHP_EOL . 'GETTING COST DATA CUSTOM_PAYOUT + GLOBAL_CAMPAIGNS'. PHP_EOL ;
  912.  
  913. $i = 1;
  914. foreach( $this->campaigns as $k => $v ){
  915. $sql = "SELECT latest.amount/100 quantity , latest.percent percentage , CCP_ADPUBID as publisher, CCP_ACCOUNTID managerId FROM CAMPAIGNS_CUSTOM_PAYOUT
  916. LEFT JOIN
  917. (
  918. SELECT CCP_ADPUBID pubid ,CCP_CAMPAIGNID cgid, CCP_AMOUNT as amount, CCP_PERCENT as percent
  919. FROM CAMPAIGNS_CUSTOM_PAYOUT
  920. WHERE CCP_TYPE = 2
  921. ORDER BY CCP_STARTD DESC
  922. ) latest
  923. ON latest.pubid = CCP_ADPUBID AND latest.cgid = CCP_CAMPAIGNID
  924. WHERE CCP_CAMPAIGNID = " . $v['id'] . " AND CCP_TYPE = 2 GROUP BY CCP_ADPUBID ORDER BY CCP_ID DESC;";
  925.  
  926. $stmt = $this->pdo->query($sql);
  927. $cost = $stmt->fetchAll();
  928.  
  929. $this->campaigns[$k]['costs'] = $cost;
  930. // echo 'Campaign Cost (' . $i++ . ' of '. count($this->campaigns) .')'. '\r';
  931. $sqlSub = "SELECT * FROM CUSTOM_PAYOUT WHERE CP_CAMPAIGNID = " . (int) $v['id'];
  932. $stmt = $this->pdo->query($sqlSub);
  933. $globalCampaigns = $stmt->fetchAll();
  934.  
  935. $this->campaigns[$k]['globalCampaigns'] = $globalCampaigns ;
  936.  
  937. $this->displayProgress($i++, count($this->campaigns));
  938. }
  939.  
  940. return $this->campaigns;
  941.  
  942. }
  943.  
  944. public function displayProgress($current, $total) {
  945. echo "\r ($current of $total) Completed";
  946. }
  947.  
  948.  
  949. public function getOfferIdsLastDays(){
  950. $offerIds = [];
  951.  
  952. foreach($this->days as $k => $d ){
  953. $sql = "SELECT DISTINCT CL_CAMPAIGNID as cgid, CL_OFFERID AS offerid, count(1) as `counter` FROM `CLICKS_$d` GROUP BY CL_CAMPAIGNID, CL_OFFERID";
  954.  
  955.  
  956.  
  957. $stmt = $this->pdo->query($sql);
  958. $res = $stmt->fetchAll();
  959.  
  960.  
  961.  
  962.  
  963. foreach($res as $k => $v){
  964. if($v['counter'] > $this->minTraff)
  965. $offerIds[$v['cgid'] . '.' . $v['offerid']] = [
  966. 'offerid'=>$v['offerid'],
  967. 'campaignid'=>$v['cgid']
  968. ];
  969. }
  970. }
  971.  
  972. return $offerIds;
  973. }
  974.  
  975.  
  976.  
  977.  
  978.  
  979. public function getCompanies($users){
  980. echo PHP_EOL . 'GETTING COMPANIES...' ;
  981. $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";
  982.  
  983.  
  984. $stmt = $this->pdo->query($sql);
  985. $res = $stmt->fetchAll();
  986.  
  987. $companies = [];
  988.  
  989. $i = 0;
  990. foreach($res as $v){
  991.  
  992. if(!isset($companies[trim($v['company'])]))
  993. $companies[trim($v['company'])] = $i++ ;
  994.  
  995. if(!isset($companies[trim($v['managing_company'])]))
  996. $companies[trim($v['managing_company'])] = $i++;
  997. }
  998.  
  999. $t_companies = [];
  1000.  
  1001. $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);";
  1002.  
  1003. $stmt2 = $this->pdo->query($sql2);
  1004. $res2 = $stmt2->fetchAll();
  1005.  
  1006. foreach ($companies as $k => $v){
  1007. $email = null;
  1008. $direct_partner = 0;
  1009.  
  1010. foreach($res as $t => $z){
  1011. if(trim($z['company']) == trim($k)){
  1012. $email = $z['email'];
  1013. }
  1014. }
  1015.  
  1016. foreach ($res2 as $l => $m)
  1017. if(trim($m['company']) == trim($k))
  1018. $direct_partner = 1;
  1019.  
  1020.  
  1021. $currency = 'USD';
  1022. $createdBy = 1;
  1023. foreach ($users as $u){
  1024. if(trim($u['email']) == $email){
  1025. $currency = $u['currency'];
  1026. $createdBy = $u['id'];
  1027. }
  1028. }
  1029.  
  1030. $t_companies[] = [
  1031. 'email'=>$email,
  1032. 'name'=>$k,
  1033. 'direct'=>$direct_partner,
  1034. 'currency'=>$currency,
  1035. 'createdBy'=>$createdBy
  1036. ];
  1037. }
  1038.  
  1039.  
  1040.  
  1041. echo count($t_companies) . ' COMPANIES FOUND';
  1042.  
  1043. return $t_companies;
  1044.  
  1045. }
  1046.  
  1047.  
  1048. public function insertCompanies($companies){
  1049. echo PHP_EOL . 'STARTING COMPANY INSERTS...' .PHP_EOL;
  1050.  
  1051. $count = 1;
  1052. foreach($companies as $k => $v){
  1053. echo "\r (".$count." of ".count($companies).") - Company: ". $v['name'] . "[XX] INSERTING ";
  1054.  
  1055. $d = $v['direct'] == 1 ? 'true' : 'false';
  1056. $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\"";
  1057.  
  1058. $result = pg_query($sql);
  1059. while ($row = pg_fetch_array($result)) $companies[$k]['id'] = $row['id'];
  1060.  
  1061. echo "\r (".$count." of ".count($companies).") - Company: ". $v['name'] . "[".$companies[$k]['id']."] INSERTED ";
  1062.  
  1063. $count++;
  1064. }
  1065.  
  1066. echo PHP_EOL . $count . ' INSERTED COMPANIES';
  1067.  
  1068. return $companies;
  1069. }
  1070.  
  1071.  
  1072. public function insertUsers($users, $companies){
  1073. echo PHP_EOL . 'USER INSERTS' . PHP_EOL;
  1074.  
  1075. $i = 1;
  1076. foreach($users as $user){
  1077.  
  1078. echo "\r (".$i." of ".count($users).") - User ". $user['email'] ." [". $user['id'] ."] INSERTING ";
  1079.  
  1080.  
  1081. //calculate the company ID from the company array.
  1082. $companyId = 1;
  1083. foreach($companies as $kk => $kv){
  1084.  
  1085. if(trim(strtolower($kv['name'])) == trim(strtolower($user['company']))){
  1086. $companyId = (int) $kv['id'];
  1087. break;
  1088. }
  1089. }
  1090.  
  1091. //who is the main manager of a MANAGER? (By default MAVERICK'S SUPER USER which is userid = 1)
  1092. //David is Userid 2.
  1093. $managerId = 1;
  1094. if($user['role'] == 'MANAGER'){
  1095. $managerId = 1;
  1096. }else{
  1097. foreach($users as $tk => $tv){
  1098. if($tv['role'] == 'MANAGER'){
  1099. if($tv['firstName'] == $user['manager']){
  1100. $managerId = $tv['id'];
  1101. }
  1102. }else{
  1103. break;
  1104. }
  1105. }
  1106. }
  1107.  
  1108. $user['is_advertiser'] = $user['is_advertiser'] == 1 ? 'true' : 'false';
  1109. $user['is_publisher'] = $user['is_publisher'] == 1 ? 'true' : 'false';
  1110.  
  1111.  
  1112. $sql = "INSERT INTO \"accounts\" (\"id\",\"email\", \"secondaryEmails\",\"firstName\", \"lastName\",\"languages\", \"phoneNumbers\", \"role\", \"advertiser\", \"publisher\", \"currency\", \"companyId\", \"managerId\", \"createdAt\", \"updatedAt\" )";
  1113.  
  1114. $sql .= "VALUES(" . $user['id'] . ",'" . $user['email'] . "', '" . $this->toPgArray([$user['email_2']]) . "', '".str_replace("'","''",$user['firstname'])."', '".str_replace("'","''",$user['surname'])."', '" . $this->toPgArray([$user['language']]) . "', " ;
  1115.  
  1116. $sql .= "'" . $this->toPgArray([$user['phone']]) . "', '".$user['role']."', " . $user['is_advertiser'] . ", ".$user['is_publisher'].", '".strtoupper($user['currency'])."', " . $companyId . " , 1 , CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)";
  1117.  
  1118.  
  1119. $result = pg_query($sql);
  1120.  
  1121.  
  1122. echo "\r (".$i." of ".count($users).") - User ". $user['email'] ." [". $user['id'] ."] INSERTED ";
  1123. $i++;
  1124. }
  1125.  
  1126. return $result;
  1127.  
  1128. }
  1129.  
  1130. public function getUserRelations($managerid = false , $allUsers = [] ){
  1131. $sqlP = "SELECT P2M_PUBLISHERID as userid FROM PUBLISHERS2MANAGER WHERE P2M_MANAGERID = $managerid";
  1132. $sqlA = "SELECT A2M_ADVERTISERID as userid FROM ADVERTISERS2MANAGER WHERE A2M_MANAGERID = $managerid";
  1133.  
  1134. $this->pdo = null;
  1135. $this->connect();
  1136.  
  1137. $stmt1 = $this->pdo->query($sqlP);
  1138. $stmt2 = $this->pdo->query($sqlA);
  1139.  
  1140. $relationsA = $stmt1->fetchAll();
  1141. $relationsB = $stmt2->fetchAll();
  1142.  
  1143. $relations = array_merge($relationsA,$relationsB) ;
  1144.  
  1145. //some users (not managers have two ids because they were two different kind of users.)
  1146. foreach($relations as $r=> $u){
  1147. foreach($allUsers as $k => $v){
  1148. if($v['id'] !== $v['oldids']){
  1149. if($u['userid'] == $v['oldids']){
  1150. //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.
  1151. $relations[] = ['userid'=>$v['oldids']];
  1152. }
  1153. }
  1154. }
  1155. }
  1156.  
  1157. $rel = [];
  1158. foreach($relations as $k=> $v) $rel[] = $v['userid'];
  1159. $rel = array_unique(array_filter($rel));
  1160.  
  1161.  
  1162. return $rel;
  1163. }
  1164.  
  1165. public function insertUserRelations($managerId = false, $managedUserIds = []){
  1166. if(!$managerId) return ;
  1167. if(empty($managedUserIds)) return ;
  1168.  
  1169. foreach($managedUserIds as $accountId){
  1170. $sql = "INSERT INTO \"additionalManagers\" (\"accountId\",\"managerId\",\"createdAt\",\"updatedAt\",\"type\") ";
  1171. $sql .= "VALUES (".$accountId.",$managerId, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'view')";
  1172.  
  1173. $result = @pg_query($sql);
  1174.  
  1175.  
  1176. }
  1177.  
  1178.  
  1179. }
  1180.  
  1181.  
  1182.  
  1183. public function getUsers(){
  1184. echo PHP_EOL . 'GETTING USERS...' ;
  1185.  
  1186. $sql = "SELECT
  1187. u.id,
  1188. u.oldids oldids,
  1189. SUBSTRING_INDEX(SUBSTRING_INDEX(u.name, '_', -1), '@', 1) AS company,
  1190. u.currency AS currency,
  1191. u.email AS email,
  1192. u.email_2 email_2,
  1193. if(u.firstname = '1', '',if(u.firstname = '?', '',if(u.firstname = '.', '',u.firstname))) AS firstname,
  1194. if(u.surname = '1', '',if(u.surname = '?', '', if(u.surname = '.', '',u.surname ) )) AS surname,
  1195. 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`
  1196. FROM (
  1197. (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
  1198. FROM USERS
  1199. GROUP BY UR_COMPANY_NAME
  1200. HAVING COUNT(1) > 1
  1201. )
  1202. UNION ALL
  1203. (
  1204. 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
  1205. FROM USERS
  1206. WHERE UR_ID NOT IN (
  1207. SELECT UR_ID
  1208. FROM USERS
  1209. GROUP BY UR_COMPANY_NAME
  1210. HAVING COUNT(1) > 1) AND UR_TYPE = 2
  1211. )
  1212. UNION ALL
  1213. (
  1214. 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
  1215. FROM USERS
  1216. WHERE UR_ID NOT IN (
  1217. SELECT UR_ID
  1218. FROM USERS
  1219. GROUP BY UR_COMPANY_NAME
  1220. HAVING COUNT(1) > 1) AND UR_TYPE = 3
  1221. )
  1222. UNION ALL
  1223. (
  1224. 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
  1225. FROM USERS
  1226. WHERE UR_ID NOT IN (
  1227. SELECT UR_ID
  1228. FROM USERS
  1229. GROUP BY UR_COMPANY_NAME
  1230. HAVING COUNT(1) > 1) AND UR_TYPE = 4)
  1231. ) u ";
  1232. if($this->limit) $sql .= 'LIMIT 100';
  1233.  
  1234. $stmt = $this->pdo->query($sql);
  1235. $res = $stmt->fetchAll();
  1236.  
  1237. echo count($res) . ' USERS FOUND';
  1238.  
  1239. return $res;
  1240.  
  1241. }
  1242.  
  1243.  
  1244.  
  1245.  
  1246. public function domains(){
  1247. echo 'GETTING DOMAINS' . PHP_EOL;
  1248. }
  1249.  
  1250. }
  1251.  
  1252.  
  1253.  
  1254. $export = new exports(10);
  1255.  
  1256.  
  1257. ?>
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!