Online PHP editor | rfc for 72KOL

<?php /* * class.MarsImportToSsi.php * * CreatedBy: dbuchanan@cotterweb.net * CreatedOn: 4/3/15 * * Requires: _classes/class.MarsLogger.php * * Handles: * 1. sending members to SSI via import web service * 2. maintains/updates db tables ssi_member_list and ssi_member_list_prev * 3. Retrieve SSI demographics (called features) * 4. Pulls ssi and cw question/answer matches from id.survey_partner_xref * */ require_once '_classes/class.MarsLogger.php'; require_once '_classes/class.DbConnectionETL.php'; /* ------------------------------------------------------------- */ /* * Child class with SSI specific id */ /* ------------------------------------------------------------- */ class SSI extends MarketResearch_logger { private $partner_id = 10; //Dev might be different then prod, could be different between SE and IBD as well function __construct($name,$debug,$env,$site,$type) { $this->setDebug($debug); $this->initLog($name); $this->env = $env; $this->site = $site; $this->type = $type; } public function getType() { return $this->type; } public function getPartnerID() { return $this->partner_id; } } /* ------------------------------------------------------------- */ /* * For talking to SSI's import api, requires instance of SSI passed in */ /* ------------------------------------------------------------- */ ///This function is used one time to pull out unique IDS, required to be this way because of quirky 5.2 bug with array_map ///PHP 5.2 doesn't like anonymous functions function tmp_get_ids($element){ return $element['respondentID']; } class SSI_api { private $ibd_source_id = '694_1264'; private $se_source_id = '694_1266'; private $batch = 100; //number of members to send in a bulk validate/submit batch private $timeout = 1500; private $qa_url = 'https://qaproxy.surveysampling.com/partner/saas/importsvc/'; // qa private $prod_url = 'https://dkr1.ssisurveys.com/partner/saas/importsvc/'; private $base_url; private $url_context; private $ob; private $country = 'US'; private $language = 'en'; private $username = 'vnd_inboxdollars'; private $password = 'pRv\'cX2*'; private $context; function __construct(SSI $ssi) { $this->ssi = $ssi; $this->base_url = ($ssi->getEnv()=='live') ? $this->prod_url : $this->qa_url; } private function createContext($ob,$type) { $credentials = "$this->username:$this->password"; $content_type = "Content-Type: application/json"; $creds = "Authorization: Basic ". base64_encode("$credentials"); $opts = array( 'http'=> array( 'method'=>$type, 'header'=>array($content_type,$creds), 'timeout'=> $this->timeout ) ); if(isset($ob)) { $opts['http']['content']=$ob; $this->ob=$ob; } else { $this->ob=''; } ////$this->ssi->logComment("Options to send: ".$ob); $this->context = stream_context_create($opts); } private function makeRequest($url) { $this->ssi->logComment("Post request ".$url); try { $result = file_get_contents($url,false,$this->context); } catch (Exception $e) { $this->ssi->logComment($e); $this->ssi->sendEmailAlert($e); } if($result===false) { $this->ssi->logComment("Request failed for $url"); $this->ssi->sendEmailAlert("Request failed for $url"); $this->ssi->addFinishComment("Failed...$url..".$this->ob); } return $result; } private function getKey() { if($this->ssi->getSite()=='inboxdollars') return $this->ibd_source_id; else if($this->ssi->getSite()=='sendearnings') return $this->se_source_id; else { $this->ssi->logComment("No api key for this, ".$this->getEnv()); $this->ssi->sendEmailAlert("No api key for this, ".$this->getEnv()); die; } } public function getBatchCount() { return $this->batch; } public function getLanguage() { return $this->language; } public function getCountry() { return $this->country; } public function getConfig() { $this->createContext(null,'GET'); return json_decode($this->makeRequest($this->base_url.'config?sourceID='.$this->getKey().'&country='.$this->getCountry().'&language='.$this->getLanguage())); } public function validateBulk($json) { $this->createContext($json,'POST'); return json_decode($this->makeRequest($this->base_url.'validate/bulk'),true); } public function submitBulk($json) { $this->createContext($json,'POST'); return json_decode($this->makeRequest($this->base_url.'submit')); } public function sendBatch($members,$features) { $this->ssi->logComment("Batch...".count($members)); $final=array('sourceId'=>$this->getKey(),'language'=>$this->getLanguage(),'country'=>$this->getCountry(),'features'=>$features,'respondents'=>$members); $json_final = json_encode($final); $validations=$this->validateBulk($json_final); $this->ssi->logComment("Validations has ".count($validations)." errors."); ///IF there are records in validations returned, that means there are problems ///Simply remove those members and log it if(count($validations) !== 0) { $this->ssi->logComment("Validation errors are..."); $this->ssi->logComment(json_encode($validations)); $this->ssi->addFinishComment("Validation errors=".count($validations).".data>>>.".json_encode($validations)); ///Need to remove those who aren't validated and log them $valid_members = array(); $bad_members = array(); ///Pull out just the IDs $invalid_member_ids = array_map("tmp_get_ids", $validations); for($x=0; $x<count($members); $x++) { if(!in_array($members[$x]['respondentID'],$invalid_member_ids)) { array_push($valid_members,$members[$x]); } else { array_push($bad_members,$members[$x]); } } $final['respondents']=$valid_members; $json_final = json_encode($final); } ///Submit members if there are some if(count($final['respondents'])>0) { $this->ssi->logComment("Submitting ".count($final['respondents'])." members to SSI."); $submissions=$this->submitBulk($json_final); $this->ssi->logComment("Submitted has ".count($submissions)." errors."); $this->ssi->logComment(json_encode($submissions)); $this->ssi->addFinishComment("Submission errors for.batch=".count($members).".data>>>.".json_encode($submissions)); //return (count($submissions)===0) ? true : false; return count($submissions); } else { $this->ssi->logComment("No members to submit! Could be bad?"); return false; } } } /* ------------------------------------------------------------- */ /* * SSI specific db calls for ssi_member_list and ssi_member_list_prev * @param instance of SSI * @extends CW_db (class.DBConnectionETL.php) */ /* ------------------------------------------------------------- */ class SSI_db extends CW_db { function __construct(SSI $ssi) { $this->ssi=$ssi; ///Init database connections to Slave and ETL $this->init($ssi->getSite(),$ssi->getEnv()); $this->connectToSlave(); $this->ssi->logComment("Connecting to ".$this->ssi->getEnv()." SLAVE >>> ".$this->db_host); $this->connectToEtl(); $this->ssi->logComment("Connecting to ".$this->ssi->getEnv()." ETL >>> ".$this->db_host); } public function checkMemberPartnerIntegration() { $sql = "SELECT COUNT(*) FROM `{$this->etl_db}`.`member_partner_integration`"; $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); if ( $row = mysql_fetch_array($result, MYSQL_NUM) ) { if ( (int)$row[0] == 0 ) { $this->ssi->logComment( "member_partner_integration table is empty ... exiting!"); $this->ssi->endLog('Finished: '); $this->ssi->sendEmailAlert(" Problem Alert:\n The member_partner_integration table was found empty during the daily SSI partner update.\n\n Update operation was aborted.\n\n EOD;"); return false; } } return true; } public function insertEmailPartner($name,$address_domain,$type) { $sql = "INSERT INTO `{$this->slv_db}`.`email_partner` (partner_name,email_address_domain,partner_type) VALUES ('$name','$address_domain','$type')"; $result = mysql_query($sql, $this->slv_con) or die($this->ssi->logComment(mysql_error())); } public function dropMemberListPrev() { $sql = "DROP TABLE IF EXISTS `{$this->etl_db}`.`ssi_member_list_prev`"; $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); $this->ssi->logComment("Successfully dropped {$this->etl_db}.ssi_member_list_prev"); return true; } /* BELOW USED FOR TESTING PUPOSES public function copyFromCintPrevToSSIPrev() { $sql = "CREATE TABLE IF NOT EXISTS `{$this->etl_db}`.`ssi_member_list_prev` ( `member_id` INT(11) NOT NULL UNIQUE DEFAULT '0', `member_code` INT(11) NOT NULL DEFAULT '0', PRIMARY KEY (`member_id`)) ENGINE=MyISAM SELECT * FROM `{$this->etl_db}`.`cint_member_list_prev`"; $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); $this->ssi->logComment("Successfully copied new {$this->etl_db}.ssi_member_list_prev from {$this->etl_db}.cint_member_list_prev"); return true; } */ public function createMemberListPrev() { $sql = "RENAME TABLE `{$this->etl_db}`.`ssi_member_list` TO `{$this->etl_db}`.`ssi_member_list_prev`"; $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); $this->ssi->logComment("Successfully renamed {$this->etl_db}.ssi_member_list to {$this->etl_db}.ssi_member_list_prev"); return true; } public function populateMemberList() { $sql = "CREATE TABLE IF NOT EXISTS `{$this->etl_db}`.`ssi_member_list` ( `member_id` INT(11) NOT NULL UNIQUE DEFAULT '0', `member_code` INT(11) NOT NULL DEFAULT '0', PRIMARY KEY (`member_id`)) ENGINE=MyISAM SELECT * FROM `{$this->etl_db}`.`member_partner_integration`"; $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); $this->ssi->logComment("Successfully created new {$this->etl_db}.ssi_member_list from {$this->etl_db}.member_partner_integration"); } private function getMemberListPrevCount() { $sql_prev = "SELECT COUNT(*) FROM `{$this->etl_db}`.`ssi_member_list_prev`"; $result_prev = mysql_query($sql_prev, $this->etl_con) or die($this->ssi->logComment(mysql_error())); $row = mysql_fetch_array($result_prev, MYSQL_NUM); return (int)$row[0]; } private function getMemberListCount() { $sql = "SELECT COUNT(*) FROM `{$this->etl_db}`.`ssi_member_list`"; $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); $row = mysql_fetch_array($result, MYSQL_NUM); return (int)$row[0]; } public function compareMemberListCounts() { $prev_count = $this->getMemberListPrevCount(); $new_count = $this->getMemberListCount(); if ( $prev_count == $new_count ) { $this->ssi->logComment("ssi_member_list_prev and ssi_member_list appear identical ... exiting!"); $this->ssi->endLog('Finished: '); $this->ssi->sendEmailAlert("Problem Alert:\n\n ssi_member_list_prev and ssi_member_list appear identical during the daily SSI partner update.\n\n Update operation was aborted."); return false; } return true; } // Run this DELETE query for SE only. // This removes any SE member having a duplicate email address with a member in IBD. public function deleteDuplicateSEMembers() { $sql = "DELETE FROM `{$this->etl_db}`.`ssi_member_list` WHERE member_id IN ( SELECT se_ms.member_id FROM `sendearnings`.`member_status` se_ms, `id`.`member_status` id_ms WHERE se_ms.email = id_ms.email )"; $this->ssi->logComment($sql); $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); $this->ssi->logComment("Successfully removed duplicate emails from SSI_MEMBER_LIST for SE."); } public function getMembersToRemove() { // Which members need to be deleted in SSI's DB? $sql = "SELECT member_id, member_code FROM `{$this->etl_db}`.`ssi_member_list_prev` WHERE member_id NOT IN (SELECT member_id FROM `{$this->etl_db}`.`ssi_member_list`)"; $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); return $result; } public function getQuestionFromSurveyPartnerXref() { $sql = "SELECT DISTINCT survey_partner_question_desc FROM `{$this->slv_db}`.`survey_partner_xref` WHERE survey_partner_id = {$this->ssi->getPartnerID()} ORDER BY survey_partner_question_desc"; $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); return $result; } public function getSurveyMembers($features, $only_new=true) { $sql = "SELECT DISTINCT member_code as respondentID, left(trim(ms.zip),5) AS postalcode, (SELECT month(dd.date_key) FROM $this->slv_db.survey_results_dob srdob, $this->slv_db.date_dim dd WHERE dd.julian = srdob.plain_value AND srdob.member_id = sml.member_id) AS monthdob ,(SELECT day(dd.date_key) FROM $this->slv_db.survey_results_dob srdob, $this->slv_db.date_dim dd WHERE dd.julian = srdob.plain_value AND srdob.member_id = sml.member_id) AS daydob ,(SELECT year(dd.date_key) FROM $this->slv_db.survey_results_dob srdob, $this->slv_db.date_dim dd WHERE dd.julian = srdob.plain_value AND srdob.member_id = sml.member_id) AS yeardob "; ///Loop and append select for each feature passed in foreach($features as $feature) { $sql .= ",(SELECT GROUP_CONCAT(DISTINCT survey_partner_answer_id) FROM $this->slv_db.survey_results sr, $this->slv_db.survey_partner_xref spx, $this->slv_db.survey_answers sa WHERE sr.member_id=ms.member_id AND spx.survey_partner_question_desc = '$feature' AND survey_partner_id = {$this->ssi->getPartnerID()} AND sr.survey_question_id=spx.survey_question_id AND sr.survey_answer_id=spx.survey_answer_id) as $feature"; } $sql .=" FROM $this->etl_db.ssi_member_list sml JOIN $this->slv_db.member_status ms ON sml.member_id = ms.member_id "; if($only_new===true) { $sql .=" WHERE sml.member_id NOT IN (SELECT member_id FROM `{$this->etl_db}`.`ssi_member_list_prev`)"; } ///For debugging //$sql.=" limit 20"; ////$this->ssi->logComment($sql); $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); return $result; } public function getMemberFeatures($id) { $sql = "SELECT DISTINCT sa.answer,survey_partner_question_desc, survey_partner_answer_id,survey_partner_answer_desc FROM $this->slv_db.survey_results sr, $this->slv_db.survey_partner_xref spx, $this->slv_db.survey_answers sa WHERE sr.member_id=$id AND survey_partner_id = {$this->ssi->getPartnerID()} AND sr.survey_question_id=spx.survey_question_id AND sr.survey_answer_id=spx.survey_answer_id AND sr.survey_answer_id=sa.id ORDER BY survey_partner_question_desc"; $result = mysql_query($sql, $this->etl_con) or die($this->ssi->logComment(mysql_error())); return $result; } }

3v4l.org

Active branches

Archived branches