13may05 Original File 2> To Do : *******************************************************************************/ #[\AllowDynamicProperties] class sf_query { public $table_name; // Holds the temp. table name public $seed_list; //store seed list emails. public $srm_prefix; // store srm prefix public $eml_prefix; // store email prefix public $event_prefix; // store evnt prefix public $mainSingleQuery; // holds the main single query public $table_field_str; public $query_category; public $advSingleQueryFlag; public $parent_flag; public $consolidateMergeValuedStr = ''; public $reportHeaderRowandFirstDatarow = ''; // stores the report header row and first data row public $report_query_type; public $reportError = 0; public $table_name_final; public $API_counter = 1; public $fromOwnerEmail; public $replyOwnerEmail; public $emailFiled = "Email"; public $emailBounceFiled = ""; public $altEmailFlg = ""; public $TwomergedIdFields; // holds 2 merged id fields one from email pkg and other from base pakg public $basePKG_prefix = "TargetX_Base__"; // doing this for the hot fix, just don't want to modify the database public $followupTypeBroadCast = ""; public $followup_eml_flag = "N"; //construct function public function __construct() { $this->dbconn_read_new = DbConnectNew::connect("read"); $this->dbconn_read_tx = DbConnectTxEmail::connect("read"); $this->sent_log_status = array('bounced', 'delivered', 'processed', 'dropped', 'sent', 'spam'); } public function __destruct() { $this->dbconn_read_new->disconnect(); $this->dbconn_read_tx->disconnect(); } // this function returns the relation ship str public function getRelation($relation) { switch ($relation) { case 1: $rln = "="; break; case 2: $rln = "<>"; break; case 3: $rln = "<"; break; case 4: $rln = ">"; break; case 5: $rln = "<="; break; case 6: $rln = ">="; break; case 7: $rln = "like"; break; case 8: $rln = "like"; break; case 9: $rln = "like"; break; case 10: $rln = "in"; break; case 11: $rln = "not in"; break; } return $rln; } /******************************************************** Description : This function return the Mysql eqalent of Sales force Date related functions. */ public function sfToMysqlDate($field_name, $val, $relation) { $rr = $this->getRelation($relation); if (strpos($val, ":") > 0) { $val_array = explode(":", $val); $val = $val_array[0]; $days = $val_array[1]; } switch ($val) { case "YESTERDAY": $rln = "DATE($field_name) $rr DATE_SUB(CURDATE(),INTERVAL 1 DAY)"; break; case "TODAY": $rln = "DATE($field_name) $rr CURDATE()"; break; case "TOMORROW": $rln = "DATE($field_name) $rr DATE_ADD(CURDATE(),INTERVAL 1 DAY)"; break; case "LAST_WEEK": $rln = "YEARWEEK($field_name)-1 $rr YEARWEEK(CURDATE())-1"; break; case "THIS_WEEK": $rln = "YEARWEEK($field_name) $rr YEARWEEK(CURDATE())"; break; case "NEXT_WEEK": $rln = "YEARWEEK($field_name)+1 $rr YEARWEEK(CURDATE())+1"; break; case "LAST_MONTH": $rln = "EXTRACT(YEAR_MONTH FROM $field_name) -1 $rr EXTRACT(YEAR_MONTH FROM CURDATE()) -1 "; break; case "THIS_MONTH": $rln = "EXTRACT(YEAR_MONTH FROM $field_name) $rr EXTRACT(YEAR_MONTH FROM CURDATE())"; break; case "NEXT_MONTH": $rln = "EXTRACT(YEAR_MONTH FROM $field_name) $rr EXTRACT(YEAR_MONTH FROM CURDATE())+1"; break; case "LAST_N_DAYS": $rln = "DATE($field_name) $rr DATE_SUB(CURDATE(),INTERVAL $days DAY)"; break; case "NEXT_N_DAYS": $rln = "DATE($field_name) $rr DATE_ADD(CURDATE(),INTERVAL $days DAY)"; break; case "LAST_YEAR": $rln = "YEAR($field_name)-1 $rr YEAR(CURDATE())-1"; break; case "THIS_YEAR": $rln = "YEAR($field_name) $rr YEAR(CURDATE())"; break; case "NEXT_YEAR": $rln = "YEAR($field_name)+1 $rr YEAR(CURDATE())+1"; break; default: $sql_str = "SELECT STR_TO_DATE(\"$val\",\"%m/%d/%Y\") as dd "; $row = $this->dbconn_read_tx->query($sql_str); $row->fetchinto($result); $dd = $result->dd; $rln = "DATE($field_name) $rr DATE(\"$dd\")"; break; } return $rln; } /******************************************************** Description : This function return the value string for fields from campaign and event objects. */ public function getValueStrCampaignEvent($relation, $field_type, $field_value, $field_name) { $rln = $this->getRelation($relation); $first_array = explode($field_value, '~~'); $camp_id_str1 = $first_array[0]; $have_comma = strpos($camp_id_str, ","); if ($have_comma > 0) { $val_array = explode(",", $camp_id_str); for ($k = 0; $k < legacy_count($val_array); $k++) { $val_array[$k] = "'" . trim($val_array[$k]) . "'"; } $camp_id_str2 = legacy_implode(",", $val_array); } else { $camp_id_str2 = "'" . $camp_id_str1 . "'"; } } /******************************************************** Description : This function return the value string for fields from objects other than campaign and events campaign and event objects. */ public function getValueStr($relation, $field_type, $field_value, $field_name) { $rln = $this->getRelation($relation); $field_value = trim($field_value); $field_value_orig = $field_value; $have_comma = strpos($field_value, ","); if ($have_comma > 0 and $field_type != 'double' and $field_type != 'int') { $val_array = explode(",", $field_value); for ($k = 0; $k < legacy_count($val_array); $k++) { $val_array[$k] = "'" . trim($val_array[$k]) . "'"; } $field_value = legacy_implode(",", $val_array); } if (strtolower($field_value) == null or $field_value == '') { $field_value_str = "$rln " . 'null'; return $field_value_str; } switch ($field_type) { case "picklist": case "string": case "email": case "reference": case "textarea": case "id": { if ($relation < 7 and $have_comma < 1) { $field_value_str = " $rln '" . $field_value . "'"; } else if (($have_comma > 0 and $relation < 7) or ($relation == 10 or $relation == 11)) { if ($have_comma > 0) { if ($rln == "=") {$rln = "in";} if ($rln == "<>") {$rln = "not in";} $field_value_str = "$rln (" . $field_value . ")"; } else { $field_value_str = "$rln ('" . $field_value . "')"; } } else if ($relation == 9 or $relation == 7 or $relation == 8) { $field_value_arr = @explode(",", $field_value_orig); for ($i = 0; $i < legacy_count($field_value_arr); $i++) { if ($relation == 9) { $field_value_str .= $rln . " '" . trim($field_value_arr[$i]) . "%'"; } else if ($relation == 7 or $relation == 8) { $field_value_str .= $rln . " '%" . trim($field_value_arr[$i]) . "%'"; } if ($i + 1 != legacy_count($field_value_arr)) { $field_value_str .= " or $field_name "; } else if (($i + 1 == legacy_count($field_value_arr)) and $relation == 8) { $field_value_str = $field_value_str . ")"; } } } break; } case "double": case "int": { if ($relation < 7 and $have_comma < 1) { $field_value_str = " $rln " . $field_value; } else if (($have_comma > 0 and $relation < 7)) { if ($rln == "=") {$rln = "in";} if ($rln == "<>") {$rln = "not in";} $field_value_str = "$rln (" . $field_value . ")"; } break; } case "multipicklist": { if ($relation < 7 and $have_comma < 1) { $field_value_str = " $rln '" . $field_value . "'"; } else if (($have_comma > 0 and $relation < 7) or ($relation == 10 or $relation == 11)) { if ($have_comma > 0 and $relation < 7) { if ($rln == "=") {$rln = "in";} if ($rln == "<>") {$rln = "not in";} $field_value_str = "$rln (" . $field_value . ")"; } else if ($have_comma < 1 and ($relation == 10 or $relation == 11)) { if ($relation == 10) {$rln = 'includes';} if ($relation == 11) {$rln = 'excludes';} $field_value_str = "$rln ('" . $field_value . "')"; } else if ($have_comma > 1 and ($relation == 10 or $relation == 11)) { if ($relation == 10) {$rln = 'includes';} if ($relation == 11) {$rln = 'excludes';} $field_value_str = "$rln (" . $field_value . ")"; } else { $field_value_str = "$rln ('" . $field_value . "')"; } } else if ($relation == 9 or $relation == 7 or $relation == 8) { if ($relation == 7 or $relation == 9) {$rln = "in";} if ($relation == 8) {$rln = "not in";} $field_value_str = "$rln (" . $field_value . ")"; } break; } case "date": { if (checkdate(@date("m", $field_value), @date("d", $field_value), @date("Y", $field_value))) { $field_val = strtotime($field_value); $field_val = date("Y-m-d", $field_val); } else { $field_val = $field_value; } $field_value_str = "$rln " . $field_val; break; } case "datetime": { if (checkdate(@date("m", $field_value), @date("d", $field_value), @date("Y", $field_value))) { $sf_common = new sf_common(); $field_val = $sf_common->get_next_date($field_value, 0, 'Y-m-d\TH:i:s\Z'); } else { $field_val = $field_value; } $field_value_str = "$rln " . $field_val; break; } default: { $field_value_str = "$rln " . $field_value; } } return $field_value_str; } // End of function /******************************************************** Description : This function creates a temp.table and fetch a record from sales force for test email. also it fetches the mrge field values from SF */ public function query_test_message($session_id, $sf_location, $table_name, $merge_fields, $query_type_, $reportID, $rcId) { //,$reSendBcastId $sf = new SF(); if ($sf->SF_login_session($session_id, $sf_location)) { } if (strlen($table_name) >= 15) { $sf->bcastID = $table_name; } $sf->error_log("Begin: TEST MESSGE: query_test_message :"); $this->table_name = "express4sf_camp." . $table_name; $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); $sql_str = "DROP TABLE IF EXISTS $this->table_name "; $row = $dbconn_read->query($sql_str); // TEMPORARY $fname = "FirstName"; $lname = "LastName"; $email = "Email"; $field_ext = ""; $validConditions = ",validConditions VARCHAR(5000)"; $sql_str = "CREATE TABLE $this->table_name ( contact_id VARCHAR(21) NOT NULL , `$field_ext$fname` VARCHAR(50) NOT NULL DEFAULT '', `$field_ext$lname` VARCHAR(50) NOT NULL DEFAULT '', `$email` VARCHAR(50) NOT NULL DEFAULT '', msg_sent char(1) NOT NULL DEFAULT 'N', active Integer(1) NOT NULL DEFAULT '1'" . $table_field_str . $validConditions . " ,PRIMARY KEY (contact_id) )"; $sf->error_log("Begin: TEST MESSGE: new table " . $sql_str); $row = $dbconn_read->query($sql_str); $user_info = $sf->user_info(); $org = $user_info->organizationId; $org = substr($org, 0, 15); $sql_str = "select ctrl_flag,sf_eml_prefix,sf_evnt_prefix,sf_srm_prefix from clientmgt.clients where orgid =\"$org\" "; $row = $this->dbconn_read_tx->query($sql_str); $row->fetchinto($result); $ctrl_flag = $result->ctrl_flag; $email_prefix = $result->sf_eml_prefix; $this->event_prefix = $result->sf_evnt_prefix; $this->srm_prefix = $result->sf_srm_prefix; $this->eml_prefix = $result->sf_eml_prefix; $row->free(); if ($query_type_ == "Lead") { $obj_name = "Lead"; $query_type = "L"; } else { $obj_name = "Contact"; $query_type = "C"; } if (substr($table_name, 0, 3) == 'f2f' and strlen($merge_fields) > 5) { if (strpos($merge_fields, 'Lead.') > 0 or strpos($merge_fields, 'lead.') > 0) { $obj_name = "Lead"; $query_type = "L"; } } //true limit 1 if (strlen($rcId) >= 15) { $sub_q = "Select t.Id,t.FirstName,t.LastName,t.Email From $obj_name t where t.HasOptedOutOfEmail <> true and t." . $email_prefix . "HasEmailBounced__c <> true and id in('$rcId')"; } else { $sub_q = "Select t.Id,t.FirstName,t.LastName,t.Email From $obj_name t where t.HasOptedOutOfEmail <> true and t." . $email_prefix . "HasEmailBounced__c <> true limit 1"; } $ret_val = $sf->sfSoqlQuery($sub_q); if ($ret_val < 0) { return -2003; //"Invalid Query value" } unset($result); $result = $sf->results_array; $contact_id = $result[0][$obj_name][0]['Id']; $first_name = $result[0][$obj_name][0]['FirstName']; $last_name = $result[0][$obj_name][0]['LastName']; $email_ = $result[0][$obj_name][0]['Email']; //get the default values $sql_str = "select Id," . $email_prefix . "expEmail_name_def__c," . $email_prefix . "expEmail_fname_def__c," . $email_prefix . "expEmail_lname_def__c from " . $email_prefix . "expSuitepreferences__c limit 1"; $sf->sfSoqlQuery($sql_str); $expEmail_name_def__c = $sf->results_array[0][$email_prefix . 'expSuitepreferences__c'][0][$email_prefix . 'expEmail_name_def__c']; $expEmail_fname_def__c = $sf->results_array[0][$email_prefix . 'expSuitepreferences__c'][0][$email_prefix . 'expEmail_fname_def__c']; $expEmail_lname_def__c = $sf->results_array[0][$email_prefix . 'expSuitepreferences__c'][0][$email_prefix . 'expEmail_lname_def__c']; if (strlen($first_name) < 1) { $first_name = $expEmail_fname_def__c; } if (strlen($last_name) < 1) { $last_name = $expEmail_lname_def__c; } $sql_str = "insert IGNORE into $this->table_name(contact_id,FirstName,LastName, Email) values(\"$contact_id\",\"$first_name\",\"$last_name\",\"$email_\") "; $row = $dbconn_read->query($sql_str); $sf->error_log("TEST MESSGE:" . $sql_str); $sf->error_log("TEST MESSGE: merge fields-->" . $merge_fields); if (strlen($merge_fields) > 5) { $ret_val = $this->getMergeFields($query_type, $sf, $session_id, $merge_fields, $expEmail_name_def__c, $reportID, $this->table_name, 'contact_id', 1); if ($ret_val < 0) { return $ret_val; } } $sf->error_log("End: TEST MESSGE: query_test_message"); return 1; } public function get_recipient_list_service_count( $session_id, $sf_location, $campaign_id, $query_type, $query, $query_category, $report_id, $dedupe_method, $send_once_per_campaign, $use_alt_email, $deselect_members, $email_groups, $max_recipient_count, $query_preview ) { $sf = new SF(); $sf->SF_login_session($session_id, $sf_location); $org_id = $sf->sfUserInfo->organizationId; $send_to_student = false; $send_to_parent = false; if ($query_category == 1) { $send_to_student = true; } else if ($query_category == 2) { $send_to_parent = true; } else if ($query_category == 3) { $send_to_student = true; $send_to_parent = true; } $email_groups_array = array(); if (strlen($email_groups) > 0) { $email_groups_array = explode( ",", preg_replace("/'/", "", stripslashes($email_groups)) ); } $request_body = array( "orgId" => $org_id, "campaignId" => $campaign_id, "queryType" => $query_type, "query" => $query, "reportId" => $report_id, "sendToStudent" => $send_to_student, "sendToParent" => $send_to_parent, "dedupeMethod" => $dedupe_method, "sendOncePerCampaign" => $send_once_per_campaign, "useAlternateEmail" => $use_alt_email, "deSelectMembers" => $deselect_members, "emailGroups" => $email_groups_array, "maxRecipientCount" => $max_recipient_count, "query_preview" => $query_preview, ); $json = json_encode($request_body); $url = SUITE4SF_PY_BASE_URL . "/recipients"; $options = array( 'http' => array( 'header' => array( "Content-type: application/json", "session-id: " . $session_id, "instance: " . $sf_location, "orgId: " . $org_id ), 'method' => 'POST', 'content' => $json, ), ); $context = stream_context_create($options); $result = file_get_contents($url, false, $context); if ($result) { $result_obj = json_decode($result); return $result_obj->count; } else { return -4200; } } /******************************************************** Description : This is the main function to fetch the records from Salesforce based on the user specified criteria. */ public function query($session_id, $sf_location, $bcast_id, $merge_fields, $para_str, $adv_query, $query_type_, $max_email, $campaign, $query_category, $reportID, $deDupe, $deDupe_method, $newsGroupsId, $altEmail) { $sf = new SF(); if ($sf->SF_login_session($session_id, $sf_location)) { } if (strlen($bcast_id) >= 15) { $sf->bcastID = $bcast_id; } $this->logTime($sf, 'start query'); $sf->error_log("\n" . "News Group " . $newsGroupsId); $this->query_category = $query_category; $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); $org = $sf->user_info()->organizationId; $org = substr($org, 0, 15); $sql_str = "select client_id,sf_eml_prefix,sf_srm_prefix,sf_evnt_prefix,followup_eml_flag from clientmgt.clients where orgid = \"$org\" "; $this->logTime($sf, 'call get email prefix'); $row = $this->dbconn_read_tx->query($sql_str); $this->logTime($sf, 'end get email prefix'); $sf->error_log($sql_str); if (DB::iserror($row)) { return -5012; } $row->fetchinto($result); $this->client_id = $result->client_id; $this->srm_prefix = $result->sf_srm_prefix; $this->eml_prefix = $result->sf_eml_prefix; $this->event_prefix = $result->sf_evnt_prefix; $this->followup_eml_flag = $result->followup_eml_flag; $this->merge_fields = false; $this->merge_other_fields = false; $this->merge_relational_queries = false; $this->conditional_content = false; $row->free(); $sf_obj_prefix = $this->srm_prefix; if (empty($para_str) and strlen($bcast_id) > 5) { $followUpStr = ""; if ($this->followup_eml_flag == "Y") { $followUpStr = "," . $this->eml_prefix . "Type__c," . $this->eml_prefix . "FollowupBroadcast__c"; } // get campaign Id. $sql_str = "select " . $this->eml_prefix . "Campaign__c" . $followUpStr . " from " . $this->eml_prefix . "expEmail_Broadcast__c where Id ='" . $bcast_id . "'"; $this->logTime($sf, 'call get Broadcast soql'); $ret_val = $sf->sfSoqlQuery($sql_str); $this->logTime($sf, 'end get Broadcast soql'); if ($ret_val < 0) { if ($ret_val == -2) { return -2004; } else if ($ret_val == -3) { return -2005; } return -1001; } //echo $sql_str; $ff = $this->eml_prefix . 'expEmail_Broadcast__c'; $campaign = $sf->results_array[0][$ff][0][$this->eml_prefix . 'Campaign__c']; $this->followupTypeBroadCast = $sf->results_array[0][$ff][0][$this->eml_prefix . 'type__c']; $followupBroadcast = $sf->results_array[0][$ff][0][$this->eml_prefix . 'FollowupBroadcast__c']; $this->logTime($sf, 'call FieldExists soql'); $this->parent_flag = $sf->sf_isExistField("Campaign", $this->eml_prefix . "query_category__c"); $this->logTime($sf, 'end FieldExists soql'); if (strlen($followupBroadcast) >= 15) { // if no body got the first broadcast , then no follow up emails. $sql_str = "SELECT count(recipient_id) as sentCount from activity.sent_log_" . $this->client_id . " WHERE broadcast_id =\"$followupBroadcast\" and status = \"Sent\""; $this->logTime($sf, 'start check for followup to previous'); $row = $this->dbconn_read_tx->query($sql_str); $this->logTime($sf, 'end check for followup to previous'); $sf->error_log($sql_str); if (DB::iserror($row)) { return -5033; } $row->fetchinto($result); $sentCount = $result->sentCount; $row->free(); if ($sentCount <= 0) { return 0; } } $this->logTime($sf, 'start build campaign fields query'); $sql_str = ""; if ($deDupe_method == null) { $sql_str = "Select " . $this->eml_prefix . "max_email__c," . $this->eml_prefix . "allowMultiReceipts__c," . $this->eml_prefix . "Query__c," . $this->eml_prefix . "query_type__c," . $this->eml_prefix . "de_select_members__c," . $this->eml_prefix . "reportID__c," . $this->eml_prefix . "deDupe__c," . $this->eml_prefix . "emailGroups__c," . $this->eml_prefix . "fromOwnerEmail__c," . $this->eml_prefix . "replyOwnerEmail__c"; } else { $sql_str = "Select " . $this->eml_prefix . "max_email__c," . $this->eml_prefix . "allowMultiReceipts__c," . $this->eml_prefix . "Query__c," . $this->eml_prefix . "query_type__c," . $this->eml_prefix . "de_select_members__c," . $this->eml_prefix . "reportID__c," . $this->eml_prefix . "deDupe__c," . $this->eml_prefix . "Dedupe_Method__c," . $this->eml_prefix . "emailGroups__c," . $this->eml_prefix . "fromOwnerEmail__c," . $this->eml_prefix . "replyOwnerEmail__c"; } if ($this->parent_flag == 1) { $sql_str .= "," . $this->eml_prefix . "query_category__c"; } $altEmlFld = $this->eml_prefix . 'altEmail__c'; if ($sf->sf_isExistField("Campaign", $altEmlFld) == 1) { //$this->altEmailFlag =1; $sql_str .= "," . $altEmlFld; } $this->logTime($sf, 'end build campaign fields query'); // echo $this->followupTypeBroadCast."--**-".$followupBroadcast; $sql_str .= " From Campaign where Id = '" . $campaign . "'"; $this->logTime($sf, 'start campaign soql'); $ret_val = $sf->sfSoqlQuery($sql_str); $this->logTime($sf, 'end campaign soql'); if ($ret_val < 0) { if ($ret_val == -2) { return -2004; } else if ($ret_val == -3) { return -2005; } return -1002; } $para_str = unserialize(base64_decode($sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'Query__c']), array('allowed_classes' => false)); $allowMultiReceipts = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'allowMultiReceipts__c']; $query_type = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'query_type__c']; $deselect_members = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'de_select_members__c']; $max_email = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'max_email__c']; $max_email = $max_email ? (int) $max_email : $max_email; $this->query_category = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'query_category__c']; $reportID = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'reportID__c']; $deDupe = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'deDupe__c']; $deDupe_method = ""; if ($deDupe_method != null) { $deDupe_method = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'DeDupe_Method__c']; } $newsGroupsId = unserialize(base64_decode($sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'emailGroups__c']), array('allowed_classes' => false)); $this->fromOwnerEmail = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'fromOwnerEmail__c']; $this->replyOwnerEmail = $sf->results_array[0]['Campaign'][0][$this->eml_prefix . 'replyOwnerEmail__c']; $altEmail = $sf->results_array[0]['Campaign'][0][$altEmlFld]; } else { $query_type = $query_type_; } $this->altEmail = $altEmail; $this->newsGroupsId = $newsGroupsId; $this->emailBounceField = $this->eml_prefix . "HasEmailBounced__c"; $this->emailField = 'Email'; if ($altEmail == 'true' or $altEmail == 1) { $this->emailField = $this->eml_prefix . 'AltEmail__c'; $this->emailBounceField = $this->eml_prefix . "HasAltEmailBounced__c"; } if ($sf->sf_isExistField('Contact', $this->basePKG_prefix . "Merged_Record_Ids__c")) { $this->TwomergedIdFields = $this->eml_prefix . "mergedRecordIds__c," . $this->basePKG_prefix . "Merged_Record_Ids__c"; } else { $this->TwomergedIdFields = $this->eml_prefix . "mergedRecordIds__c"; } $newsGroupsId = stripslashes($newsGroupsId); if ($query_type == "R") { unset($para_str); $para_str = ''; } // log para string $sf->error_log("\n" . "Broadcast ID is --> " . $bcast_id . " " . $para_str); $contact_all_valid = "Select t.Id,t.FirstName,t.LastName,$this->emailField From Contact t where t.HasOptedOutOfEmail <> true and t." . $this->emailBounceField . " <> true"; $lead_all_valid = "Select t.Id,t.FirstName,t.LastName,t.Email From Lead t where t.HasOptedOutOfEmail <> true and t." . $this->eml_prefix . "HasEmailBounced__c <> true "; if (is_array($para_str)) { for ($k = 0; $k < legacy_count($para_str['name']); $k++) { $para_array[$k] = $para_str['name'][$k] . "_-" . $para_str['field_name'][$k] . "_-" . $para_str['oper'][$k] . "_-" . $para_str['field_value'][$k]; } } else if (strlen($para_str) > 5) { $para_array = explode("~~^^", $para_str); } else if ($para_str != "C" and $para_str != "L") { $para_str = $query_type; } if ($para_str == "L") { $para_array[0] = "Lead" . "_-" . "Email___email" . "_-" . "7" . "_-" . "%@%"; } else if ($para_str == "C") { $para_array[0] = "Contact" . "_-" . "$this->emailField" . "___email" . "_-" . "7" . "_-" . "%@%"; } $this->mainSingleQuery = ''; $this->table_field_str = ''; if (empty($adv_query)) { // PHP 8: $para_str is often a string here ('~~^^' list, 'C', 'L', ''), and string-keyed // offsets on a string now throw TypeError. PHP 5.3 silently returned $para_str[0] (one // char), which never passed the strlen > 3 checks below -- '' is behaviour-identical. $adv_query = (is_array($para_str) && isset($para_str['advanced'])) ? $para_str['advanced'] : ''; } // find whether all the criteria is for one object $this->advSingleQueryFlag = 0; $lastObject = ""; for ($i = 0; $i < legacy_count($para_array); $i++) { unset($my_row); $my_row = explode("_-", $para_array[$i]); if ($my_row[0] == "Select One" or $my_row[1] == "Select One" or trim($my_row[0]) == '' or trim($my_row[0]) == '') { continue; } if ($my_row[0] == "Campaign" and $my_row[2] == 11) { $this->advSingleQueryFlag = 0; break; } if ($i > 0 and strlen($adv_query) > 3) { if ($lastObject == $my_row[0]) { $this->advSingleQueryFlag = 1; //$this->advSingleQueryFlag = 0; } else { $this->advSingleQueryFlag = 0; break; } } $lastObject = $my_row[0]; } if (strlen($adv_query) > 3) { $tot_table_fields = legacy_count($para_array) - 1; $this->logTime($sf, 'start validate adv query'); $table_fields_str = $this->validateAdvQuery($adv_query, $tot_table_fields); $this->logTime($sf, 'end validate adv query'); if ($table_fields_str == -1) { $query_data = ""; $desc = " Adv. query condition is not valid"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-2007", $query_data, $desc); return -2001; } else if ($table_fields_str == -4001) { $query_data = ""; $desc = " Adv. query condition does not contain all the criteria"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-4001", $query_data, $desc); return -4001; } } if ($query_type != "R") { $this->logTime($sf, 'start check field permissions query'); $retVal = $this->checkFieldPermissions($org, $query_type, $para_array, $sf, $bcast_id, $dbconn_read); $this->logTime($sf, 'end check field permissions query'); if ($retVal < 1) { return $retVal; } // try to create a single query if (strlen($adv_query) < 3) { $this->logTime($sf, 'start single query build'); $sub_q = $this->constructSingleQuery($this->client_id, $org, $query_type, $para_array, $sf, "", $newsGroupsId); $this->logTime($sf, 'end single query build'); error_log("single query contruction"); error_log(print_r($sub_q, true)); } else if (strlen($adv_query) > 3 and $this->advSingleQueryFlag == 1) { $this->logTime($sf, 'start single query build - adv'); $sub_q = $this->constructSingleQuery($this->client_id, $org, $query_type, $para_array, $sf, $adv_query, $newsGroupsId); $this->logTime($sf, 'end single query build - adv'); error_log("advanced single query"); error_log(print_r($sub_q, true)); } else { $sub_q = -1; } if ($sub_q != -1) { $table_field_str = " ,field_0 BOOLEAN NOT NULL DEFAULT '0'"; } else { $this->logTime($sf, 'start construct initial queries'); // PHP 8: this call has always been one argument short ($adv_query is missing), so on // PHP 5 $newsGroupsId landed in the unused $adv_query slot and the real $newsGroupsId // param was null -- i.e. NO email-group filter was applied on this path. Passing the // values explicitly preserves that production behaviour exactly. To apply the group // filter here as presumably intended, change the last argument to $newsGroupsId. $sub_q = $this->constructInitailQueries($this->client_id, $org, $query_type, $para_array, $sf, "", null); $this->logTime($sf, 'end construct initial queries'); $table_field_str = $this->table_field_str; error_log("INitial queries"); error_log(print_r($sub_q, true)); } } else if ($query_type == "R") { $table_field_str = " ,field_0 BOOLEAN NOT NULL DEFAULT '1'"; } //print_r($sub_q[query]); /* print_r($para_array); print_r($sub_q[query]); echo "---".$this->mainSingleQuery; */ //exit; $sf->error_log(" mainSingleQuery -->" . $this->mainSingleQuery); //re-fine it $this->logTime($sf, 'start query refinement loop'); for ($i = 0; $i < legacy_count($sub_q['query']); $i++) { // log querys $sf->error_log("\n" . "Sub SOQL s -->`$i " . $bcast_id . " " . $sub_q['query'][$i]); $operator = $sub_q['operator'][$i]; $obj_name = $sub_q['obj'][$i]; $field_name = $sub_q['field'][$i]; if ($operator == 11 and $obj_name != "Lead" and $obj_name != "Contact" and $obj_name != "Campaign" and $obj_name != "Event") { } else if ($obj_name == "Campaign" and ($field_name == "DHTML View" or $field_name == "DInteract")) { $orig_query = $sub_q['query'][$i] . "\n"; if ($field_name == "DHTML View") { $subQueryDe = str_replace("= 'DHTML View'", " = 'HTML View' ", $orig_query); $newOrigQuery = str_replace("activity_log", "sent_log", $orig_query); $newOrigQuery = str_replace("where activity", "where status", $newOrigQuery); $sub_q['query'][$i] = str_replace("= 'DHTML View'", " in('Sent') and recipient_id not in($subQueryDe)", $newOrigQuery); } else if ($field_name == "DInteract") { $subQueryDe = str_replace("= 'DInteract'", " = 'Interact' ", $orig_query); $newOrigQuery = str_replace("activity_log", "sent_log", $orig_query); $newOrigQuery = str_replace("where activity", "where status", $newOrigQuery); $sub_q['query'][$i] = str_replace("= 'DInteract'", " in('Sent') and recipient_id not in($subQueryDe)", $newOrigQuery); } } // else if ( ($obj_name == "Campaign" or $obj_name == "Event") and ($operator == 11 or $operator == 2) ) else if (($obj_name == "Campaign") and ($operator == 11) and (strpos($sub_q['orig_field_name'][$i], "___p") > 0 or strpos($sub_q['orig_field_name'][$i], "___e") > 0)) { if ($query_type == "C") { $upd_q['query'][] = str_replace("not in", "in", $sub_q['query'][$i]); $sub_q['query'][$i] = "Select t.Id,t.FirstName,t.LastName,$this->emailField From Contact t where t.HasOptedOutOfEmail <> true and t." . $this->emailBounceField . " <> true"; $upd_q['obj'][] = $sub_q['obj'][$i]; $sub_q['obj'][$i] = "Contact"; } else if ($query_type == "L") { $upd_q['query'][] = str_replace("not in", "in", $sub_q['query'][$i]); $sub_q['query'][$i] = "Select t.Id,t.FirstName,t.LastName,t.Email From Lead t where t.HasOptedOutOfEmail <> true and t." . $this->eml_prefix . "HasEmailBounced__c <> true"; $upd_q['obj'][] = $sub_q['obj'][$i]; $sub_q['obj'][$i] = "Lead"; } $upd_q['field_'][] = "field_" . $i; $upd_q['fieldNo'][] = $i; $upd_q['field'][] = $sub_q['field'][$i]; $upd_q['orig_field_name'][] = $sub_q['orig_field_name'][$i]; $upd_q['operator'][] = $sub_q['operator'][$i]; } } $this->logTime($sf, 'end query refinement loop'); /* print_r($sub_q[query]); echo "-----***-------"; print_r($upd_q[query]); //echo "
"; echo "--+main single query++-".$this->mainSingleQuery; // exit; */ if (strlen($bcast_id) > 0) { $this->table_name = "express4sf_camp." . $bcast_id . "_temp"; $this->table_name_final = "express4sf_camp." . $bcast_id; } else { $this->table_name = "express4sf_camp." . $sf->user_info()->userId . "_temp"; $this->table_name_final = "express4sf_camp." . $sf->user_info()->userId; } $this->logTime($sf, 'start temp table cleanup'); $sql_str = "DROP TABLE IF EXISTS $this->table_name "; $sf->error_log($sql_str); $row = $dbconn_read->query($sql_str); $sql_str = "DROP TABLE IF EXISTS $this->table_name_final "; $sf->error_log($sql_str); $row = $dbconn_read->query($sql_str); $sql_str = "DROP TABLE IF EXISTS $this->table_name_final" . "_chld"; $sf->error_log($sql_str); $row = $dbconn_read->query($sql_str); $sql_str = "DROP TABLE IF EXISTS $this->table_name_final" . "_parent"; $sf->error_log($sql_str); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end temp table cleanup'); $sql_str = "select Id," . $this->eml_prefix . "expEmail_name_def__c," . $this->eml_prefix . "expEmail_fname_def__c," . $this->eml_prefix . "expEmail_lname_def__c from " . $this->eml_prefix . "expSuitepreferences__c limit 1"; $this->logTime($sf, 'start get preferences soql'); $ret_val = $sf->sfSoqlQuery($sql_str); $this->logTime($sf, 'end get preferences soql'); if ($ret_val < 0) { if ($ret_val == -2) { return -2004; } else if ($ret_val == -3) { return -2005; } return -2007; } $expEmail_name_def__c = $sf->results_array[0][$this->eml_prefix . 'expSuitepreferences__c'][0][$this->eml_prefix . 'expEmail_name_def__c']; $expEmail_fname_def__c = $sf->results_array[0][$this->eml_prefix . 'expSuitepreferences__c'][0][$this->eml_prefix . 'expEmail_fname_def__c']; $expEmail_lname_def__c = $sf->results_array[0][$this->eml_prefix . 'expSuitepreferences__c'][0][$this->eml_prefix . 'expEmail_lname_def__c']; // TEMPORARY $fname = "FirstName"; $lname = "LastName"; $email = "Email"; $field_ext = ""; //if($this->fromOwnerEmail == 'true') //{ $ownerEmailField = ",ownerEmail VARCHAR(50) ,ownerFullName VARCHAR(100) "; //} $validConditions = ",validConditions VARCHAR(5000)"; if (($query_type == "L" or $query_type == "C") or ($query_type == "R" and strtolower($deDupe) != 'true')) { $sql_str = "CREATE TABLE $this->table_name ( contact_id VARCHAR(21) NOT NULL , `$field_ext$fname` VARCHAR(50) NOT NULL DEFAULT '$expEmail_fname_def__c', `$field_ext$lname` VARCHAR(50) NOT NULL DEFAULT '$expEmail_lname_def__c', `$email` VARCHAR(50) NOT NULL DEFAULT '', msg_sent char(1) NOT NULL DEFAULT 'N', active Integer(1) NOT NULL DEFAULT '1',mergedIds VARCHAR(250)" . $ownerEmailField . $validConditions . $table_field_str . " ,PRIMARY KEY (contact_id) )"; } else if ($query_type == "R" and strtolower($deDupe) == 'true') { $sql_str = "CREATE TABLE $this->table_name ( contact_id VARCHAR(21) , serial int(11) AUTO_INCREMENT NOT NULL, `$field_ext$fname` VARCHAR(50) NOT NULL DEFAULT '$expEmail_fname_def__c', `$field_ext$lname` VARCHAR(50) NOT NULL DEFAULT '$expEmail_lname_def__c', `$email` VARCHAR(50) NOT NULL DEFAULT '', msg_sent char(1) NOT NULL DEFAULT 'N', active Integer(1) NOT NULL DEFAULT '1',mergedIds VARCHAR(250)" . $ownerEmailField . $validConditions . $table_field_str . " ,PRIMARY KEY (serial) ,INDEX (`contact_id`) )"; } $sf->error_log($sql_str); $this->logTime($sf, 'start create temp table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end create temp table query'); unset($sql_str); if (DB::iserror($row)) { return -5009; } $sql_str = "CREATE INDEX part_of_email ON $this->table_name ($email(5))"; $sf->error_log($sql_str); $this->logTime($sf, 'start create temp index query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end create temp index query'); if (DB::iserror($row)) { $query_data = $sql_str . "**"; $desc = " cretae index failed on email field"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5010", $query_data, $desc); return -5010; } if ($this->query_category == 2 or $this->query_category == 3) { $sql_str = "ALTER TABLE $this->table_name ADD `relation_id` varchar(50) NULL "; $this->logTime($sf, 'start add relation_id query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end add relation_id query'); if (DB::iserror($row)) { print_r($row); } } if ($query_type == "R") { if (strlen($reportID) > 0) { $this->logTime($sf, 'start getReportData'); $retVal = $this->getReportData($sf, $session_id, $dbconn_read, $table, $reportID, 0, $bcast_id, $newsGroupsId); $this->logTime($sf, 'end getReportData'); $sf->error_log("Report data Ret val: " . $retVal); if ($retVal <= 0) { return $retVal; } $table_fields[] = "field_" . $i; } } $this->logTime($sf, 'start getReportData'); for ($i = 0; $i < legacy_count($sub_q['query']); $i++) { unset($result); unset($records); $field_name_orig = $sub_q['orig_field_name'][$i]; $obj_name = $sub_q['obj'][$i]; if (strpos($sub_q['query'][$i], "sent_log_") > 0 || strpos($sub_q['query'][$i], "activity_log_") > 0) { $this->logTime($sf, 'start getRecordsFromMysql'); $retval = $this->getRecordsFromMysql($query_type, $sub_q['query'][$i], $sf, $field_name_orig, $obj_name, $i, $this->dbconn_read_new, "Insert", $query_category); $this->logTime($sf, 'end getRecordsFromMysql'); if ($retval < 0) { return $retval; } $table_fields[] = "field_" . $i; continue; } $sfSelectQuery = $sub_q['query'][$i]; $sf->error_log("API counter Main Query::" . $sfSelectQuery); $this->logTime($sf, 'start sfSelectQuery special soql'); $records = $sf->specialQuery1($sfSelectQuery); $this->logTime($sf, 'end sfSelectQuery special soql'); $sf->error_log("API counter:" . $this->API_counter++ . " No of total records --->" . $sf->result_size); if ($records < 0) { $query_data = $sub_q['query'][$i]; $desc = " Failed query"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-2003", $query_data, $desc); if ($records == -2) { return -2004; } else if ($records == -3) { return -2005; } return -2003; //"Invalid Query value" } else if ($records == 0) { $table_fields[] = "field_" . $i; continue; } $queryLocator = $sf->queryLocator; $actual_size = $sf->result_size; $total_itr = ceil($actual_size / 2000); $ii = 0; $k = 2000; $j = 0; $upd_val = 1; $sf->error_log("API counter:" . $this->API_counter++ . " Total No of records fetched--->" . $sf->result_size); do { $this->logTime($sf, 'start specialQuery2 with api counter'); $sf->specialQuery2($records, $ii); $this->logTime($sf, 'end specialQuery2 with api counter'); $sf->error_log("API counter:" . $this->API_counter++ . " No of records fetched--->" . legacy_count($records)); $result = $sf->results_array; if (legacy_count($result) < 0) { return -5021; } $sql_str_2 = $this->getNameStr($result, $obj_name, $query_type, $upd_val, 10, $sf); $sql_str_1 = "insert IGNORE into $this->table_name(contact_id,`$field_ext$fname`,`$field_ext$lname`,`$email`,`ownerEmail`,`ownerFullName`,field_$i,`mergedIds`,`validConditions`) values"; $sql_str_3 = " ON DUPLICATE KEY UPDATE field_$i=$upd_val"; $sql_str = $sql_str_1 . $sql_str_2 . $sql_str_3; $sf->error_log($sql_str); $sf->error_log("BIG SQL --->" . $sql_str); $this->logTime($sf, 'start insert into temp table'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end insert into temp table'); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " Failed query"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5008", $query_data, $desc); return -5008; } if ($actual_size > 2000 and $total_itr > $j + 1) { $this->logTime($sf, 'start queryMore'); $response = $sf->mySforceConnection->queryMore("$queryLocator"); $this->logTime($sf, 'end queryMore'); $queryLocator = $response->queryLocator; $records = $response->records; if (legacy_count($response->records) >= 2000) { $k = $k + 2000; } else { $k = $k + legacy_count($response->records); } } $j++; } while ($total_itr > $j); $table_fields[] = "field_" . $i; } unset($sql_str); unset($sql_str_2); unset($result); unset($records); unset($merArrayUnique); unset($sf->results_array); unset($sf->response); unset($sf->records); // Now update the ( exclude) fields for ($i = 0; $i < legacy_count($upd_q['query']); $i++) { unset($result); unset($records); $obj_name = $upd_q['obj'][$i]; $fieldNo = $upd_q['fieldNo'][$i]; //echo "*****++******".$fieldNo; exit; $field_name_orig = $upd_q['orig_field_name'][$i]; if (strpos($upd_q['query'][$i], "sent_log_") > 0 || strpos($upd_q['query'][$i], "activity_log_") > 0) { $this->logTime($sf, 'start getRecordsFromMysql'); $retval = $this->getRecordsFromMysql($query_type, $upd_q['query'][$i], $sf, $field_name_orig, $obj_name, $fieldNo, $this->dbconn_read_new, "Update", $query_category); $this->logTime($sf, 'end getRecordsFromMysql'); if ($retval < 0) { return $retval; } $table_fields[] = "field_" . $fieldNo; continue; } } //exit; if (legacy_count($table_fields) > 0) { $table_fields_str = @legacy_implode("&", $table_fields); } else { return 0; } if (empty($adv_query)) { // PHP 8: guard string $para_str (see query() for the full note). $adv_query = (is_array($para_str) && isset($para_str['advanced'])) ? $para_str['advanced'] : ''; } if (strlen($adv_query) > 3) { $tot_table_fields = legacy_count(legacy_array_unique($table_fields)); $this->logTime($sf, 'start validateAdvQuery for main'); $table_fields_str = $this->validateAdvQuery($adv_query, $tot_table_fields); $this->logTime($sf, 'end validateAdvQuery for main'); if ($table_fields_str == -1) { $query_data = ""; $desc = " Adv. query condition is not valid"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-2007", $query_data, $desc); return -2001; } else if ($table_fields_str == -4001) { $query_data = ""; $desc = " Adv. query condition does not contain all the criteria"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-4001", $query_data, $desc); return -4001; } } if (strlen($adv_query) > 3 and $this->advSingleQueryFlag == 1) { $table_fields_str = 'field_0'; } if ($table_fields_str != -1) { if ($query_type != "R") { $sql_str = "update $this->table_name set active = if($table_fields_str =1 and length(email) > 1,1,-1 ) "; $sf->error_log($sql_str); $this->logTime($sf, 'start update active for temp query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end update active for temp query'); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " update active = 1 failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5023", $query_data, $desc); return -5023; } } $sql_str = "select * from $this->table_name where active = 1"; $sf->error_log($sql_str); $this->logTime($sf, 'start get active from temp query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end get active from temp query'); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " select where active = 1 failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5022", $query_data, $desc); return -5022; } $sf->error_log(" No of Records -->" . $row->numRows()); if ($row->numRows() == 0) { return 0; } //add the consolidated merge field to the table $sql = "ALTER TABLE $this->table_name ADD `merged_data` Text NULL "; $sf->error_log($sql_str); $this->logTime($sf, 'start add merge data query'); $row = $dbconn_read->query($sql); $this->logTime($sf, 'end add merge data query'); if (DB::iserror($row)) { $query_data = $sql; $desc = " can not add consolidated merge field to local table "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5036", $query_data, $desc); return -5036; } //eleminate the duplicate $this->logTime($sf, 'start eleminateDuplicate'); $ret_val = $this->eleminateDuplicate($sf, $merge_fields, $query_type, $deDupe); $this->logTime($sf, 'end eleminateDuplicate'); if ($ret_val < 0) { return $ret_val; } if ($this->query_category == 2 or $this->query_category == 3) { // get the related records ( parents.).... $this->logTime($sf, 'start getRelatedRecords'); $this->merge_relational_queries = true; $ret_val = $this->getRelatedRecords($sf, $table_fields, $merge_fields); $this->logTime($sf, 'end getRelatedRecords'); if ($ret_val < 0) { return $ret_val; } } //echo "---".$allowMultiReceipts; if (($allowMultiReceipts == "true" and strlen($campaign) > 5 and strlen($bcast_id) > 5) or (strlen($campaign) > 5 and strlen($bcast_id) < 1)) { $sql_str = " SELECT distinct(recipient_id) from activity.sent_log_" . $this->client_id . " WHERE campaign_id = '" . $campaign . "'"; $sf->error_log($sql_str); $this->logTime($sf, 'start get activity contacts for campaign query'); $row = $this->dbconn_read_tx->query($sql_str); $this->logTime($sf, 'end get activity contacts for campaign query'); $no_del_contacts = $row->numRows(); $this->logTime($sf, 'start getContacts 400 loop'); for ($n = 0; $n < $no_del_contacts; $n = $n + 400) { if ($n >= $no_del_contacts) { continue; } unset($del_id_array); $sql_str = " SELECT distinct(recipient_id) from activity.sent_log_" . $this->client_id . " WHERE campaign_id = '" . $campaign . "'"; $sql_str = $sql_str . " limit $n , 400"; $sf->error_log($sql_str); $this->logTime($sf, 'activity queue: start get activity contacts limit 400 query: ' . $n); $row = $this->dbconn_read_tx->query($sql_str); $this->logTime($sf, 'activity queue: end get activity contacts limit 400 query: ' . $n); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " Select from sent log failed"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6007", $query_data, $desc); return -6077; } while ($row->fetchinto($result)) { $del_id_array[] = "'" . $result->contactID . "'"; } $row->free(); if (legacy_count($del_id_array) > 0) { $del_contact_id_str = legacy_implode(",", $del_id_array); $sql_str = "delete from $this->table_name_final where contact_id in ($del_contact_id_str)"; $sf->error_log($sql_str); $this->logTime($sf, 'activity queue: start delete contacts for 400 query: ' . $n); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'activity queue: end delete contacts for 400 query: ' . $n); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " Delete failed"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5014", $query_data, $desc); return -5014; } } } $this->logTime($sf, 'activity queue: end getContacts 400 loop'); unset($result); unset($del_contact_id_str); unset($sql_str); unset($del_id_array); //update merged Ids field in $this->table_name_final // commented the following since we are getting mergedIDs directly from the mail soql /* $retVal1 = $this->getMergedIds($sf,$query_type); if($retVal1 < 0) { return -6179; } */ $retval2 = $this->validateMergedIds($sf, $campaign); if ($retVal2 < 0) { return -6180; } } if (strlen($deselect_members) > 1) { $sql_str = "update $this->table_name_final set active=0 where contact_id in ($deselect_members)"; $sf->error_log($sql_str); $this->logTime($sf, 'start update active 0 query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end update active 0 query'); if (DB::iserror($row)) { return -5002; // print_r($row); $query_data = $sql_str; $desc = " update deselected contact failed"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5002", $query_data, $desc); } } if (strlen($reportID) >= 15 and strlen($bcast_id) <= 15) { $sql_str = "SELECT sum($table_fields_str) AS total_count FROM $this->table_name_final where active=1"; } else { $sql_str = "SELECT sum($table_fields_str) AS total_count FROM $this->table_name_final where length(email) > 0 and active=1"; } $sf->error_log($sql_str); $this->logTime($sf, 'start get total active query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end get total active query'); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " select from final table failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5003", $query_data, $desc); return -5003; } $row->fetchinto($result); $total_count = $result->total_count; $row->free(); $sf->error_log("Current Count:--->" . $total_count); if (strlen($bcast_id) > 5) { // if the selected count is > max. count if ($total_count > $max_email and $max_email > 0) { $this->logTime($sf, 'start call de_select'); $ret_val = $this->de_select($this->table_name_final, $max_email, $total_count); $this->logTime($sf, 'start end de_select'); if ($ret_val < 0) { return $ret_val; } $total_count = intval($max_email); } // get the seed list $this->logTime($sf, 'start call get seed list'); $ret_val = $this->get_seed_list($sf, $campaign); $this->logTime($sf, 'end call get seed list'); if ($ret_val < 0) { return $ret_val; } } //$merge_fields = "M1:Contact.OwnerFullName,M2:Contact.OwnerEmail"; //$merge_fields = "M1:TargetX_SRMb__Application__c.Id,M2:TargetX_SRMb__Application__c.TargetX_SRMb__Stage__c,M3:TargetX_SRMb__Application__c.TargetX_SRMb__Status__c"; //$merge_fields = "M1:SRMemail.firstName,M2:Lead.FirstName,M3:Lead.LastName,M4:Lead.Email"; // $merge_fields = "M1:Lead.FirstName,M2:Lead.LastName,M3:Lead.Email"; //$merge_fields = "M1:SRMemail.firstName"; //$merge_fields = "M1:targetXtestPac__OrgEvent__c.targetXtestPac__custom_field_2_label__c"; //$merge_fields = 'M1:Contact.Name,M2:Contact.Email'; //$merge_fields = 'M1:Contact.Email'; //$merge_fields = "M1:targetXtestPac__ContactScheduleItem__c.targetXtestPac__event_name__c"; //$merge_fields = 'M1:Contact.LastName,M2:Contact.FirstName,M7:Contact.OwnerEmail,M8:Account.Fax,M9:Account.Id,M10:Contact.OwnerId'; //$merge_fields = 'M1:Contact.LastName,M2:Contact.FirstName,M7:Contact.OwnerEmail,M8:Account.Fax,M9:Account.Id,M10:Contact.OwnerId'; unset($result); unset($records); unset($merArrayUnique); unset($sf->results_array); unset($sf->response); unset($sf->records); if (strlen($merge_fields) > 5 and $total_count > 0) { $this->logTime($sf, 'start getMergeFields'); $this->merge_fields = true; if ($this->query_category == 3) { $ret_val = $this->getMergeFields($query_type, $sf, $session_id, $merge_fields, $expEmail_name_def__c, $reportID, $this->table_name_final, 'contact_id', 1); $ret_val = $this->getMergeFields($query_type, $sf, $session_id, $merge_fields, $expEmail_name_def__c, $reportID, $this->table_name_final, 'relation_id', 2); } else if ($this->query_category == 2) { $ret_val = $this->getMergeFields($query_type, $sf, $session_id, $merge_fields, $expEmail_name_def__c, $reportID, $this->table_name_final, 'relation_id', 1); } else if ($this->query_category <= 1) { $ret_val = $this->getMergeFields($query_type, $sf, $session_id, $merge_fields, $expEmail_name_def__c, $reportID, $this->table_name_final, 'contact_id', 1); } $this->logTime($sf, 'end getMergeFields'); if ($ret_val < 0) { $query_data = $merge_fields; $desc = " merge field validation failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, $ret_val, $query_data, $desc); return $ret_val; } if ($this->query_category == 2 or $this->query_category == 3) { // get the related records ( parents.).... $this->logTime($sf, 'start getRelatedRecords2'); $ret_val = $this->getRelatedRecords2($sf, $table_fields, $merge_fields); $this->logTime($sf, 'end getRelatedRecords2'); if ($ret_val < 0) { return $ret_val; } } // consolidate the merge field values if (strlen($this->consolidateMergeValuedStr) > 0) { $sql_str = "update $this->table_name_final set merged_data = concat(" . $this->consolidateMergeValuedStr . ") where length(contact_id) >=15"; $sf->error_log($sql_str); $this->logTime($sf, 'start update merge_data query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end update merge_data query'); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " update failed for consolidated merged data "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5027", $query_data, $desc); return -5027; } } } unset($result); unset($records); unset($merArrayUnique); unset($sf->results_array); unset($sf->response); unset($sf->records); $sf->error_log("memory usage End:" . memory_get_usage()); return $total_count; } else { //echo "Advance query Filter condition is not correct,please check"; return -2001; //"Advance query Filter condition is not correct,please check"; } } // End of function Query public function get_seed_list($sf, $campaign) { $sql_str = "select id, name, " . $this->eml_prefix . "Test_Lists__c from Campaign where Id='" . $campaign . "' LIMIT 1"; $ret_val = $sf->sfSoqlQuery($sql_str); //try the new ui $campaignInfo_array = $sf->results_array; $testLists = stripslashes($campaignInfo_array[0]['Campaign'][0][$this->eml_prefix . 'Test_Lists__c']); if ($ret_val < 0 or strlen($testLists) < 15) { //not on the new ui $sql_str = "select Id," . $this->eml_prefix . "email__c," . $this->eml_prefix . "first_name__c," . $this->eml_prefix . "last_name__c" . " from " . $this->eml_prefix . "expEmailSeedlist__c where " . $this->eml_prefix . "active__c = true and " . $this->eml_prefix . "seed__c = true"; $ret_val = $sf->sfSoqlQuery($sql_str); if ($ret_val < 0) { $query_data = $sql_str; $desc = " select from seed list failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-2011", $query_data, $desc); return -2011; } } else { $testLists = "'" . str_replace(';', '\',\'', $testLists) . "'"; // CC-1960 - fix added active flag $sql_str = "select id, " . $this->eml_prefix . "first_name__c, " . $this->eml_prefix . "last_name__c," . $this->eml_prefix . "email__c from " . $this->eml_prefix . "expEmailSeedlist__c where " . $this->eml_prefix . "test_list__c in(" . $testLists . ") and " . $this->eml_prefix . "active__c = true"; $ret_val = $sf->sfSoqlQuery($sql_str); if ($ret_val < 0) { $query_data = $sql_str; $desc = " select from seed list failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-2011", $query_data, $desc); return -2011; } } $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); $seed_array = $sf->results_array; for ($i = 0; $i < legacy_count($seed_array); $i++) { $fname = stripslashes($seed_array[$i][$this->eml_prefix . 'expEmailSeedlist__c'][0][$this->eml_prefix . 'first_name__c']); $lname = stripslashes($seed_array[$i][$this->eml_prefix . 'expEmailSeedlist__c'][0][$this->eml_prefix . 'last_name__c']); $email = stripslashes($seed_array[$i][$this->eml_prefix . 'expEmailSeedlist__c'][0][$this->eml_prefix . 'email__c']); $contact_id = "seed_" . $i; $sql_str = "insert IGNORE into $this->table_name_final(contact_id,FirstName,LastName,Email) values(\"$contact_id\",\"$fname\",\"$lname\",\"$email\")"; $row = $dbconn_read->query($sql_str); if (DB::iserror($row)) { $query_data = $sql_str; $desc = "inserting seed list into local table failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5020", $query_data, $desc); return -5020; } } } // This function construct a part of the SQL str public function getNameStr($result, $obj_name, $query_type, $upd_val, $flag, $sf) { $sf_obj_prefix = $this->srm_prefix; if (strpos($obj_name, $sf_obj_prefix) === false) { $sf_obj_prefix = ''; } else { $sf_obj_prefix = $this->srm_prefix; } if ($query_type == "C" or $this->report_query_type == "C") { //$con= $sf_obj_prefix."Contact__c"; $parentObj = "Contact"; } else if ($query_type == "L" or $this->report_query_type == "L") { //$con= $sf_obj_prefix."Lead__c"; $parentObj = "Lead"; } $con = $sf->sf_getRelatedIdField($parentObj, $obj_name); for ($j = 0; $j < legacy_count($result); $j++) { $contact_id = $result[$j][$parentObj][0]['Id']; $first_name = $result[$j][$parentObj][0]['FirstName']; $last_name = $result[$j][$parentObj][0]['LastName']; $email_ = $result[$j][$parentObj][0][$this->emailField]; $ownerEmailaddress = $result[$j]['User'][0]['Email']; $mergedRecordIds = $result[$j][$parentObj][0][$this->eml_prefix . "mergedRecordIds__c"]; if (strlen($result[$j][$parentObj][0][$this->basePKG_prefix . "Merged_Record_Ids__c"]) > 15) { if (strlen($mergedRecordIds) > 10) { $mergedRecordIds .= "," . $result[$j][$parentObj][0][$this->basePKG_prefix . "Merged_Record_Ids__c"]; } else { $mergedRecordIds = $result[$j][$parentObj][0][$this->basePKG_prefix . "Merged_Record_Ids__c"]; } } $validConditions = $result[$j][$parentObj][0][$this->eml_prefix . "validConditions__c"]; if (strlen($validConditions) > 1) { //echo "here" ;exit; } if (strlen($ownerEmailaddress) < 5) { // this is for Lead obj $ownerEmailaddress = $result[$j]['Name'][0]['Email']; } $ownerFullName = $result[$j]['User'][0]['Name']; if (strlen($ownerFullName) < 5) { // this is for Lead obj $ownerFullName = $result[$j]['Name'][0]['Name']; } //echo "---".$contact_id ."-->>-".$ownerEmailaddress."\n"; //print_r($result[$j]); //if($j == 3){exit;} if ($flag == 12) { // to handle cases where email address is getting from object like family relation $contact_id = $result[$j][$obj_name][0]['Id']; $email_ = $result[$j][$obj_name][0][$sf_obj_prefix . 'Email__c']; $first_name = $result[$j][$obj_name][0][$sf_obj_prefix . 'First_Name__c']; $last_name = $result[$j][$obj_name][0][$sf_obj_prefix . 'Last_Name__c']; $relation_id = $result[$j][$obj_name][0][$sf_obj_prefix . 'Contact__c']; } // US3302 //$first_name = preg_replace("/[^ -~]/", " ",$first_name); //$last_name = preg_replace("/[^ -~]/", " ",$last_name); if ($flag != 12) { $sql_str_2 .= "(\"$contact_id\",\"" . addslashes($first_name) . "\",\"" . addslashes($last_name) . "\",\"$email_\",\"$ownerEmailaddress\",\"$ownerFullName\",$upd_val,\"$mergedRecordIds\",\"$validConditions\")"; } else if ($flag == 12) { $sql_str_2 .= "(\"$contact_id\",\"" . addslashes($first_name) . "\",\"" . addslashes($last_name) . "\",\"$email_\",\"$ownerEmailaddress\",\"$ownerFullName\",\"$relation_id\",\"$mergedRecordIds\",\"$validConditions\")"; } $contact_id_str .= "\"$contact_id\""; if ($j < legacy_count($result) - 1) { $sql_str_2 .= ","; $contact_id_str .= ","; } } // end j //echo $sql_str_2; exit; if ($flag == 10 or $flag == 12) { return $sql_str_2; } elseif ($flag == 11) { return $contact_id_str; } } // This function validate the adv. query function public function validateAdvQuery($string_in, $tot_table_fields) { $ret_val = preg_match_all("/and|or|[0-9]{1,2}|\(|\)/i", $string_in, $string_out_array); $open_b = 0; $close_b = 0; $where_clause = ""; for ($j = 1; $j <= $tot_table_fields; $j++) { $pos = strpos($string_in, "$j"); //echo "$$".$pos."\n"; if ($pos === false) { return -4001; } } for ($i = 0; $i < legacy_count($string_out_array[0]); $i++) { $this_oper = $string_out_array[0][$i]; if (($this_oper == $last_oper) and ($this_oper != '(' and $this_oper != ')')) { return -1; } if ($string_out_array[0][$i] == "(") { $open_b++; } if ($string_out_array[0][$i] == ")") { $close_b++; } $last_oper = $string_out_array[0][$i]; if (($i == 0 or $i == legacy_count($string_out_array[0]) - 1) and ($string_out_array[0][$i] == 'and' or $string_out_array[0][$i] == 'or')) { return -1; } if ($this_oper >= 1) { $field_num = $this_oper - 1; $where_clause .= " field_" . $field_num; } else { $where_clause .= " " . $this_oper; } } if ($close_b != $open_b) { return -1; } return $where_clause; } // validate the merge fields in email template for query type reports public function validateReportMergeData($sf, $merge_fields_str, $reportRows) { $merge_fields_array = explode(",", $merge_fields_str); $sf->error_log("In validateReportMergeData func : MERGE STR:" . $merge_fields_str . " REPORT ROWS: " . $reportRows . "ARRAY SIZE :" . legacy_count($merge_fields_array)); for ($i = 0; $i < legacy_count($merge_fields_array); $i++) { $sf->error_log("In the outer loop " . $merge_fields_array[$i]); if (strpos($merge_fields_array[$i], ":") > 0) { $fld_nm_arr = explode(":", $merge_fields_array[$i]); $orig_field_name = $fld_nm_arr[1]; } else { $orig_field_name = $merge_fields_array[$i]; } unset($ObjFldArray); $ObjFldArray = explode(".", $orig_field_name); $sf->error_log("In the loop" . $ObjFldArray[0]); if ($ObjFldArray[0] != "SRMemail") { $tempObjNameArray[] = $ObjFldArray[0]; } } //print_r($unqObjArray); $sf->error_log("In validateReportMergeData func : tempObjNameArray:" . legacy_count($tempObjNameArray)); //$sf->error_log(print_r($tempObjNameArray)); //print_r($tempObjNameArray); for ($i = 0; $i < legacy_count($tempObjNameArray); $i++) { $objPrefix = $sf->getObjectPrefix($tempObjNameArray[$i]); //echo $tempObjNameArray[$i]."---".$objPrefix."****".$i; if ($objPrefix < 1) { //return -320; } //echo "obj prefix-->".$objPrefix."**".$tempObjNameArray[$i]."
"; if ('account' == strtolower($tempObjNameArray[$i])) { continue; } $retval = $this->getReportFieldName($objPrefix, $sf); $sf->error_log("In validateReportMergeData func loop: " . $objPrefix . " retval--> " . $retval . " " . $tempObjNameArray[$i]); if (strlen($retval) < 2 or $retval < 0) { return -320; } } } // validate the merge fields in email template public function validateMergeFields($sf, $session_id, $merge_fields_str, $query_type, $reportID) { $merge_fields_array = explode(",", $merge_fields_str); $sf->error_log("In Validate Merge field func : MERGE STR:" . $merge_fields_str . " QUERY TYPE: " . $query_type . " REPORT ID: " . $reportID); if (strlen($reportID) > 0) { $sf->error_log("About to run validateReportMergeData : "); $retVal = $this->validateReportMergeData($sf, $merge_fields_str, $reportRows); if ($retVal < 0) { return $retVal; } } $sf->error_log("In validateMergeFields func 1: "); for ($i = 0; $i < legacy_count($merge_fields_array); $i++) { if (strpos($merge_fields_array[$i], ":") > 0) { $fld_nm_arr = explode(":", $merge_fields_array[$i]); $orig_field_name = $fld_nm_arr[1]; } else { $orig_field_name = $merge_fields_array[$i]; } if (strtolower($orig_field_name) == "srmemail.firstname" or strtolower($orig_field_name) == "srmemail.lastname" or strtolower($orig_field_name) == "srmemail.email" or strtolower($orig_field_name) == "srmemail.fullname") { continue; } $obj_field = explode(".", $orig_field_name); if (strpos(strtolower($obj_field[0]), "contactscheduleitem__c") > 0) { if (strlen($reportID) < 10) { // return -310; // break; } } if (strpos(strtolower($obj_field[0]), "orgevent__c") > 0) { return -330; break; } //return -6526; //if( strtolower($obj_field[0]) <> "contact" and strtolower($obj_field[0]) <> "lead" and strtolower($obj_field[0]) <> "owner" and strtolower($obj_field[0]) <> "account") if (strtolower($obj_field[0]) != "contact" and strtolower($obj_field[0]) != "lead" and strtolower($obj_field[0]) != "account") { if ($query_type == "C" or $this->report_query_type == "C") { $parentObject = 'Contact'; } else if ($query_type == "L" or $this->report_query_type == "L") { $parentObject = 'Lead'; } //echo $parentObject."^^^^^^^^^^^^^^^^".$obj;exit; $sf->error_log(" About to run SF sf_getRelationName for " . $merge_fields_array[$i] . "---" . $parentObject . " " . $obj_field[0]); $obj_r = $sf->sf_getRelationName($parentObject, $obj_field[0]); $sf->error_log(" checking Reln " . $obj_r); if (strlen($obj_r) < 3) { $sf->error_log(" checking Reln ...1" . $obj_r); $query_data = 'Cant find any relationship from $parentObject to object-->' . $obj_field[0]; $desc = " "; //$this->query_log($dbconn_read,$org,$campaign,$bcast_id,"-6526",$query_data,$desc); // $this->query_log($dbconn_read,$org,$campaign,$bcast_id,"-5018",$query_data,$desc); return -6526; } } if ($query_type == 'C') { if (strtolower($obj_field[0]) == 'lead') { return -31; break; } } else if ($query_type == 'L') { if (strtolower($obj_field[0]) == 'contact') { return -31; break; } } $sf->error_log(" About to run sf_sobject_structure " . $obj_field[0]); $fields_arr = $sf->sf_sobject_structure($obj_field[0]); if (legacy_count($fields_arr) < 1) { return -32; } $field_to_validate = $obj_field[1]; // this is to handle cases like the merge fields like ..ownerFirstName,ownerLastName... if (strtolower(substr($obj_field[1], 0, 5)) == 'owner' and stripos($obj_field[1], '__c') === false) { $fields_arr = $sf->sf_sobject_structure("User"); $field_to_validate = substr($obj_field[1], 5); // this is to handle cases like the merge fields like ..ownerFullName if (strtolower($field_to_validate) == "fullname") { $field_to_validate = "Name"; } } $flag = "N"; foreach ((array) $fields_arr['name_label'] as $name => $label) { //echo $name." == >".$field_to_validate."
"; if ($name == $field_to_validate) { $flag = "Y"; } else { } } if ($flag == "N") { return -32; break; } } } // this function get rid of the duplicate records from the temp. broadcast table public function eleminateDuplicate($sf, $merge_fields_str, $query_type, $deDupe) { $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); // old logic to select records based on merge value // commented on Nov 27 2012 ACM /* if(strlen($merge_fields_str) > 5) { $merge_fields_array = @explode(",",$merge_fields_str); for($i=0;$i< count($merge_fields_array);$i++) { // $merge_fields_array[$i] = 'length(A.`'.$merge_fields_array[$i].'`)'; $fld_nm_arr = explode(":",$merge_fields_array[$i]); $local_tbl_field_name = $fld_nm_arr[0]; $merge_fields_array[$i] = 'length(`'.$local_tbl_field_name.'`)'; } if(count($merge_fields_array) > 0) { $length_str = @implode("+",$merge_fields_array); if(strlen($length_str) > 5) { $len_str = ",(".$length_str.")"; $max_length_str = ",max($length_str) as max_merge_field"; } } } $sql_str = "CREATE TABLE $this->table_name_final (unique(email)) ignore select * from $this->table_name where active >=0 order by email $len_str desc"; */ //echo "--".$query_type."--".$deDupe; $sf->error_log("eleminateDuplicate func 1: " . "--" . $query_type . "--" . $deDupe); if ($query_type != "R" or ($query_type == "R" and ($deDupe == 'false' or $deDupe == ''))) { $sql_str = "DROP TABLE IF EXISTS $this->table_name_final "; $this->logTime($sf, 'start eleminateDuplicate drop final table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end eleminateDuplicate drop final table query'); $sql_str = "CREATE TABLE $this->table_name_final (unique(email)) ignore select * from $this->table_name where active >=0 "; $sf->error_log("eleminateDuplicate func 2: " . $sql_str); } else if ($query_type == "R" and $deDupe == 'true') { $sql_str = "DROP TABLE IF EXISTS $this->table_name_final "; $this->logTime($sf, 'start eleminateDuplicate drop final table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end eleminateDuplicate drop final table query'); $sql_str = "CREATE TABLE $this->table_name_final LIKE $this->table_name"; $this->logTime($sf, 'start eleminateDuplicate create final table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end eleminateDuplicate create final table query'); $sql_str = "INSERT $this->table_name_final SELECT * FROM $this->table_name"; $sf->error_log("eleminateDuplicate func 3: " . $sql_str); } $this->logTime($sf, 'start eleminateDuplicate recreate final from temp query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end eleminateDuplicate recreate final from temp query'); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " eleminate duplicate failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5018", $query_data, $desc); return -5018; } $sql_str = "CREATE INDEX contact_id_index ON $this->table_name_final (contact_id)"; $this->logTime($sf, 'start eleminateDuplicate create final index query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end eleminateDuplicate create final index query'); if (DB::iserror($row)) { print_r($row); } } public function getMergeFields($query_type, $sf, $session_id, $merge_fields_str, $expEmail_name_def__c, $reportID, $tableName, $baseField, $flag) { $sf->error_log("memory usage getMergeFields:" . memory_get_usage()); $this->logTime($sf, 'call validateMergeFields'); $ret_val = $this->validateMergeFields($sf, $session_id, $merge_fields_str, $query_type, $reportID); if ($ret_val < 0) { return $ret_val; } $this->logTime($sf, 'return validateMergeFields'); $sf_obj_prefix = $this->srm_prefix; $merge_fields_array = explode(",", $merge_fields_str); $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); // get the prefix of family relation obj $this->logTime($sf, 'call getObjectPrefix'); $familyPrefix = $sf->getObjectPrefix($this->srm_prefix . 'Family_Relationship__c'); $this->logTime($sf, 'return getObjectPrefix'); if ($this->query_category == 2) { $sql_str = "SELECT $baseField FROM $tableName where active = 1 and $baseField not like \"$familyPrefix%\" "; } else { $sql_str = "SELECT $baseField FROM $tableName where active = 1"; } $this->consolidateMergeValuedStr = ''; $sf->error_log($sql_str); $this->logTime($sf, 'call getBaseField'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'return getBaseField'); if ($row->numRows() < 1) { return false; } else { $no_of_contacts = $row->numRows(); } $sf->error_log("Merge fields array-->" . $merge_fields_str . "--" . $merge_fields_array . "*****" . $baseField . "---" . $no_of_contacts); //echo "--->".$merge_fields_str; $this->logTime($sf, 'start MergeField Loop'); for ($i = 0; $i < legacy_count($merge_fields_array); $i++) { $sf->error_log("memory usage --$i:" . memory_get_usage()); $orig_field_name = $merge_fields_array[$i]; $sf->error_log("Merge fields array-->$i " . $orig_field_name); $fld_arr = explode(":", $merge_fields_array[$i]); $local_tbl_fld_nm = $fld_arr[0]; $orig_field_name = $fld_arr[1]; //US3461 $local_tbl_fld_nm_rep = "REPLACE(REPLACE($local_tbl_fld_nm, ':', '--col--'), ';', '--semi--') "; // consolidate the merge field values $orig_field_name__ = str_replace(".", "_", $orig_field_name); if ($i == 0) { //$this->consolidateMergeValuedStr .= "'[".$orig_field_name__."]:',".$local_tbl_fld_nm; $this->consolidateMergeValuedStr .= "'[" . $orig_field_name__ . "]:'," . $local_tbl_fld_nm_rep; } else { //$this->consolidateMergeValuedStr .= "';[".$orig_field_name__."]:',".$local_tbl_fld_nm; $this->consolidateMergeValuedStr .= "';[" . $orig_field_name__ . "]:'," . $local_tbl_fld_nm_rep; } if (legacy_count($merge_fields_array) > ($i + 1)) { $this->consolidateMergeValuedStr .= ","; } if ($flag == 1) { $sql_1 = "ALTER TABLE $tableName ADD `$local_tbl_fld_nm` VARCHAR(250) NOT NULL "; $sf->error_log($sql_1); $this->logTime($sf, 'call MergeField Loop alter table: ' . $i); $row = $dbconn_read->query($sql_1); $this->logTime($sf, 'return MergeField Loop alter table: ' . $i); if (DB::iserror($row)) { $query_data = $sql_1; $desc = " can not add merge field to local table "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5016", $query_data, $desc); return -5016; } unset($my_row); } if (strtolower($orig_field_name) == "srmemail.firstname" or strtolower($orig_field_name) == "srmemail.lastname" or strtolower($orig_field_name) == "srmemail.email" or strtolower($orig_field_name) == "srmemail.fullname" or strtolower($orig_field_name) == "contact.firstname" or strtolower($orig_field_name) == "contact.lastname" or strtolower($orig_field_name) == "contact.name" or strtolower($orig_field_name) == "contact.email" or strtolower($orig_field_name) == "lead.firstname" or strtolower($orig_field_name) == "lead.lastname" or strtolower($orig_field_name) == "lead.name" or strtolower($orig_field_name) == "lead.email") { $this->logTime($sf, 'call MergeField getMergefieldsForSpecialSRMEmailFields: ' . $i); $ret_val = $this->getMergefieldsForSpecialSRMEmailFields($sf, $dbconn_read, $tableName, $merge_fields_array[$i], $baseField); $this->logTime($sf, 'return MergeField getMergefieldsForSpecialSRMEmailFields: ' . $i); $sf->error_log("Merge fields retval 1 --> " . $ret_val); if ($ret_val < 0) { return $ret_val; } continue; } $sf->error_log("Merge fields field 1--> " . $orig_field_name); if (strtolower($orig_field_name) == "contact.id" or strtolower($orig_field_name) == "lead.id") { $sql_str = "update $tableName set $local_tbl_fld_nm = contact_id where active = 1"; $sf->error_log($sql_str); $this->logTime($sf, 'call updateOriginFieldName: ' . $i); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'return updateOriginFieldName: ' . $i); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " Update contact.id ,lead.id merge field failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6426", $query_data, $desc); return -6426; } continue; } $sf->error_log("Merge fields field 2--> " . $orig_field_name); $my_row = explode(".", $orig_field_name); $obj_name = $my_row[0]; $field_name = $my_row[1]; // this is to handle cases like the merge fields like ..ownerFirstName,ownerLastName... $modified_field_name = ''; if (strtolower(substr($field_name, 0, 5)) == 'owner' and stripos($field_name, '__c') === false) { $field_name = "owner." . substr($field_name, 5); $modified_field_name = $field_name; // this is to handle cases like the merge fields like ..ownerFullName if (strtolower($modified_field_name) == "owner.fullname") { $modified_field_name = "owner.Name"; $field_name = "owner.Name"; } } $sf->error_log("Merge fields field 3--> " . $orig_field_name); // group merge fields for unique objects $merArrayUnique[$obj_name][$local_tbl_fld_nm] = $field_name; if (strtolower($obj_name) != "contact" and strtolower($obj_name) != "lead" and strtolower($obj_name) != "owner" and strtolower($obj_name) != "account") { $this->logTime($sf, 'call getObjectPrefix: ' . $i); $objIdPrefix = $sf->getObjectPrefix($obj_name); $this->logTime($sf, 'return getObjectPrefix: ' . $i); $objIdField = $this->getReportFieldName($objIdPrefix, $sf); $this->logTime($sf, 'return getReportFieldName: ' . $i); $objIdFieldArray[$local_tbl_fld_nm] = $objIdField; //$objIdFieldArray_[] = $objIdField; $objIdFieldArray_[] = $objIdField; } $sf->error_log("Merge fields field 4--> " . $orig_field_name); } // End of $i $this->logTime($sf, 'end MergeField Loop'); if (legacy_count($merArrayUnique) < 1) { return; } if (legacy_count($objIdFieldArray_) > 0) { $objIdFieldArrayUnique__ = legacy_array_unique($objIdFieldArray_); } //print_r($objIdFieldArray_); $this->logTime($sf, 'start objIdFieldArrayUnique loop'); for ($n = 0; $n < legacy_count($objIdFieldArrayUnique__) + 10; $n++) { if (strlen($objIdFieldArrayUnique__[$n]) > 2) { $objIdFieldArrayUnique[] = $objIdFieldArrayUnique__[$n]; $sf->error_log("Merge fields Loop $n --> " . $objIdFieldArrayUnique__[$n]); } } $this->logTime($sf, 'end objIdFieldArrayUnique Loop'); //print_r($objIdFieldArrayUnique); exit; //print_r($merArrayUnique);exit; // unset($fldArray); $mainSql = "select id,IsDeleted"; $this->logTime($sf, 'start merArrayUnique Loop'); foreach ((array) $merArrayUnique as $obj => $fldArrayObj) { unset($fldArray); foreach ((array) $fldArrayObj as $localTableField => $actualField) { //$val = $resultSet[$obj][0][$actualField]; //echo "$$$".$obj."----".$actualField."
"; if (strtolower($obj) == "account") { if (strtolower($actualField) == "accountid") { $fldArray[] = $actualField; } else { $fldArray[] = "Account." . $actualField; } } elseif (strtolower($obj) == "owner") { if (strtolower($actualField) == "ownerid") { $fldArray[] = $actualField; } else { $fldArray[] = "Owner." . $actualField; } } else { $fldArray[] = $actualField; } } if (strtolower($obj) != "contact" and strtolower($obj) != "lead" and strtolower($obj) != "owner" and strtolower($obj) != "account") { if (!legacy_in_array("Id", $fldArray)) { $fldArray[] = 'Id'; } $fldStr = @legacy_implode(",", $fldArray); // commented on Feb 8th 2013 ACM //$obj_r = str_replace('__c','__r',$obj); if ($query_type == "C" or $this->report_query_type == "C") { $parentObject = 'Contact'; } else if ($query_type == "L" or $this->report_query_type == "L") { $parentObject = 'Lead'; } //echo $parentObject."^^^^^^^^^^^^^^^^".$obj;exit; $this->logTime($sf, 'call sf_getRelationName'); $obj_r = $sf->sf_getRelationName($parentObject, $obj); $this->logTime($sf, 'end sf_getRelationName'); if (strlen($obj_r) < 3) { $query_data = 'Cant find any relationship from $parentObject to object-->' . $obj; $desc = " "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6526", $query_data, $desc); return -6526; } $subQueryArray[] = "(select $fldStr from $obj_r)"; } else if (strtolower($obj) == "contact" or strtolower($obj) == "lead") { $fldStr = @legacy_implode(",", $fldArray); $mainSql .= ',' . $fldStr; } else if (strtolower($obj) == "account") { $fldStr = @legacy_implode(",", $fldArray); $mainSql .= ',' . $fldStr; } else if (strtolower($obj) == "owner") { $fldStr = @legacy_implode(",", $fldArray); $mainSql .= ',' . $fldStr; } } $this->logTime($sf, 'end merArrayUnique Loop'); if (legacy_count($subQueryArray) > 0) { $subQueryStr = @legacy_implode(",", $subQueryArray); $sql_str = $mainSql . "," . $subQueryStr; } else { $sql_str = $mainSql; } if ($query_type == "C" or $this->report_query_type == "C") { $mergeDataSql = $sql_str . " From Contact "; } else if ($query_type == "L" or $this->report_query_type == "L") { $mergeDataSql = $sql_str . " From Lead "; } $sf->error_log("Merge mergeDataSql + no contacts --> " . $mergeDataSql . "-----" . $no_of_contacts); /* $sql_str = "Select c.FirstName, c.LastName,c.Owner.Name, c.Owner.FirstName, c.OwnerId, c.Id, c.Email, c.Account.Fax, c.AccountId, (Select iD,TargetXDev__Last_Name__c,TargetXDev__First_Name__c,TargetXDev__Gender__c,TargetXDev__Living__c From TargetXDev__Family_Relationship__r),(select iD,TargetXDev__Type__c from TargetXDev__Extra_Curricular_Activity__r) From Contact c where c.id in('0035000000ey9TfAAI','0035000000uNhTYAA0','0035000000uMvnxAAC')"; */ $this->logTime($sf, 'start 400 contacts Loop'); for ($n = 0; $n < $no_of_contacts; $n = $n + 400) { $sf->error_log("memory usage *** $n:" . memory_get_usage()); if ($n >= $no_of_contacts) { continue; } unset($contact_id_array); unset($idArray); if (strlen($reportID) > 10 and legacy_count($objIdFieldArray) > 0) { $objIdFieldStr = @legacy_implode(",", $objIdFieldArrayUnique); $sql_str = "select $baseField," . $objIdFieldStr . " FROM $tableName where active = 1 ADD_CRITERIA ORDER BY contact_id ASC limit $n , 400"; } else { $sql_str = "SELECT $baseField FROM $tableName where active = 1 ADD_CRITERIA ORDER BY contact_id ASC limit $n , 400"; } $sf->error_log("Merge data contatact list --> " . $sql_str); $addCriteria = " "; if ($this->query_category == 2) { $addCriteria = " and $baseField not like \"$familyPrefix%\" "; } $sql_str = str_replace("ADD_CRITERIA", $addCriteria, $sql_str); $sf->error_log("Merge SQL --> " . $sql_str); $this->logTime($sf, 'call contacts getobjIdFieldArray sql: ' . $n); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end contacts getobjIdFieldArray sql: ' . $n); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " select failed in merge field function "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5017", $query_data, $desc); return -5017; } $jj = 0; $this->logTime($sf, 'start loop getobjIdFieldArray sql result: ' . $n); while ($row->fetchinto($result)) { if (strlen($result->$baseField) >= 18) { $contact_id_array[] = "'" . $result->$baseField . "'"; $conLeadId = $result->$baseField; } else { continue; } for ($i = 0; $i < legacy_count($objIdFieldArrayUnique); $i++) { $localIdFld = $objIdFieldArrayUnique[$i]; //$result->$localIdFld if (strlen($localIdFld) > 0) { //echo "\n"."---".$result->$baseField."####".$localIdFld."*****".$result->$localIdFld; $idArray[][$conLeadId][$localIdFld] = $result->$localIdFld; } } } $this->logTime($sf, 'end loop getobjIdFieldArray sql result: ' . $n); $row->free(); $contact_id_str = @legacy_implode(",", $contact_id_array); if (strlen($contact_id_str) < 5) { //return; // commented on sept 26 th 2013 continue; } $sf->error_log("Merge contact_id_str --> " . $contact_id_str); //print_r($idArray); //print_r($objIdFieldArrayUnique); $mergeDataSqlFinal = $mergeDataSql . " where Id in ($contact_id_str)"; $sf->error_log("Merge mergeDataSqlFinal --> " . $mergeDataSqlFinal); //echo "***".$mergeDataSql."^^^";exit; /*$mergeDataSql ="select id,IsDeleted,owner.Email,Name,MailingStreet,Account.Name,(select TargetXDev__Gender__c,Id from TargetXDev__Family_Relationship__r),(select TargetXDev__Application_Type__c,Id from TargetXDev__Application__r) From Contact where Id in ('0035000000bSUrUAAW','0035000000ZpvKmAAJ','0035000000Zpva2AAB','0035000000Zpva3AAB','0035000000ZpwqrAAB','0035000000mZK8xAAG','0035000001Hr9inAAB','0035000001Hr9iYAAR','0035000001Z0aIHAAZ','0035000000gxmzTAAQ','0035000000gxoG8AAI','0035000000gxoESAAY','0035000000gxnzQAAQ','0035000000gxoG3AAI','0035000000gxoGDAAY','0035000000gxoEOAAY','0035000000gxoFRAAY','0035000000gxoGIAAY','0035000000gxoGNAAY','0035000000XnroDAAR','0035000000eyhsGAAQ','0035000000XAG9MAAX','0035000000c4imvAAA','0035000000c4ilEAAQ','0035000000eyfSyAAI','0035000000eyhuuAAA','0035000000gzm8HAAQ','0035000000Z6KitAAF','0035000000ey9SRAAY','0035000000eyCjbAAE','0035000000ey8kpAAA','0035000000XADkVAAX','0035000000lKXfEAAW','0035000000lKXalAAG','0035000000gzm3AAAQ','0035000000gzm5mAAA','0035000000gzmMZAAY','0035000000ez9ANAAY','0035000000bSUbHAAW','0035000000gxn5gAAA','00350000019NGaFAAW','0035000000ey9TfAAI','0035000000gx22fAAA','0035000000gzHYpAAM','0035000000gzHbEAAU','0035000000gxoEHAAY','0035000000gxoB5AAI','0035000000gxoGwAAI','0035000000gxoIwAAI','0035000000gxoImAAI','0035000000gxoPXAAY','0035000000W36ZMAAZ','0035000000VmRKXAA3','0035000000mbz2XAAQ','0035000000mbz3VAAQ','00350000019MGf1AAG','0035000000UTVvXAAX','0035000001c0pQqAAI','0035000000UTVw1AAH')"; $mergeDataSql = "select id,IsDeleted,Account.Name,Account.Id,owner.Id,owner.Email,Name,(select TargetXDev__Gender__c,TargetX_SRMb__Family_Relationship_Key__c,TargetX_SRMb__Email__c from TargetX_SRMb__Family_Relationship__r), (select Name,Id,TargetX_SRMb__Application_Type__c,TargetX_SRMb__Status__c,TargetX_SRMb__CC_Approval_Number__c from TargetX_SRMb__Application__r) From Contact where Id in ('0037000000tCaftAAC')"; $mergeDataSql = "select id,IsDeleted,Account.Id,owner.Id,Name,(select Id from TargetX_SRMb__Family_Relationship__r), (select Name,Id,TargetX_SRMb__Application_Type__c,TargetX_SRMb__Status__c,TargetX_SRMb__CC_Approval_Number__c from TargetX_SRMb__Application__r) From Contact where Id in ('0037000000tCaftAAC')"; */ $table_field_name = $orig_field_name; unset($result); unset($records); $this->logTime($sf, 'call mergedata soql special: ' . $n); $records = $sf->specialQuery1($mergeDataSqlFinal); $this->logTime($sf, 'end mergedata soql special: ' . $n); //echo "###".$mergeDataSqlFinal; $sf->error_log("API counter: SOQL " . $mergeDataSqlFinal); $sf->error_log("API counter:" . $this->API_counter++ . " No of records fetched--->" . $sf->result_size); if ($records < 0) { $query_data = $mergeDataSqlFinal; $desc = " Invalid query in getMergeFields function "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-2009", $query_data, $desc); if ($records == -2) { return -2004; } else if ($records == -3) { return -2005; } return -2009; //"Invalid Query value" } // US3434 -Begin $mergeQueryLocator = $sf->queryLocator; do { unset($moreRecords); if (strlen($mergeQueryLocator) >= 15) { $this->logTime($sf, 'call mergeQueryLocator soql '); $response = $sf->mySforceConnection->queryMore("$mergeQueryLocator"); $this->logTime($sf, 'end mergeQueryLocator soql '); $mergeQueryLocator = $response->queryLocator; $moreRecords = $response->records; //echo "More Rec. size -->".count($moreRecords); $records = legacy_array_merge($records, $moreRecords); } } while (legacy_count($moreRecords) > 0); // US3434 -End $this->logTime($sf, 'call record special soql 2: ' . $n); $sf->specialQuery2($records, $ii); $this->logTime($sf, 'end record special soql 2: ' . $n); $result = $sf->results_array; //print_r($records); //echo "**".count($records)."-->>"."^^^".$sf->result_size."&&&"; //print_r($result); //exit; //$retVal = $this->updateLocal($query_type,$result,$obj_name__,$table_field_name, $dbconn_read,$expEmail_name_def__c,$local_tbl_fld_nm,$sf,$reportID,$parentObj); $this->logTime($sf, 'call updateMergeValues: ' . $n); $retVal = $this->updateMergeValues($sf, $query_type, $result, $reportID, $merArrayUnique, $idArray, $objIdFieldArray, $dbconn_read, $tableName, $baseField); $this->logTime($sf, 'return updateMergeValues: ' . $n); if ($retVal < 0) { return $retVal; } unset($contact_id_str); unset($contact_id_array); unset($records); unset($mergeDataSqlFinal); unset($result); //unset($sf->results_array); //unset($sf->response); //unset($sf->records); } // End of $n $this->logTime($sf, 'end 400 contacts Loop'); } // End of function //update the local table , with merge field values public function updateMergeValues($sf, $query_type, $result, $reportID, $merArrayUnique, $idArray, $objIdFieldArray, $dbconn_read, $tableName, $baseField) { $dbconn_read->autoCommit(false); // loop through the result set $this->logTime($sf, 'start update merge values Loop'); for ($i = 0; $i < legacy_count($result); $i++) { unset($resultSet); unset($subArray); $resultSet = $result[$i]; if (empty($resultSet) and $i == 0) { $resultSet = $result[$k]; } if ($this->report_query_type == "C" or $query_type == "C") { $conLeadId = $resultSet['Contact'][0]['Id']; } else if ($this->report_query_type == "L" or $query_type == "L") { $conLeadId = $resultSet['Lead'][0]['Id']; } // get a sub array from $idArray for $conLeadId // $this->logTime($sf, 'start call getSubArray: '.$i); $subArray = $this->getSubArray($conLeadId, $idArray); if (legacy_count($subArray) > 0) { for ($j = 0; $j < legacy_count($subArray); $j++) { //$this->logTime($sf, 'start call extractMergeData '.$j); $retVal = $this->extractMergeData($sf, $resultSet, $merArrayUnique, $query_type, $reportID, $subArray[$j], $objIdFieldArray, $dbconn_read, $tableName, $baseField); } } else { //$this->logTime($sf, 'start call extractMergeData '.$i); $retVal = $this->extractMergeData($sf, $resultSet, $merArrayUnique, $query_type, $reportID, '', $objIdFieldArray, $dbconn_read, $tableName, $baseField); } $this->merge_other_fields = true; //$this->logTime($sf, 'end call extractMergeData '.$i); $sf->error_log("memory usage :" . memory_get_usage()); if ($retVal < 0) { return $retVal; } } $this->logTime($sf, 'start call dbCommit'); $dbconn_read->commit(); $dbconn_read->autoCommit(true); $this->logTime($sf, 'end call dbCommit'); unset($resultSet); unset($subArray); unset($result); unset($objIdFieldArray); unset($merArrayUnique); } // Get sub array from $idArray ( this happens only for report based query with duplicate records) public function getSubArray($conLeadId, $idArray) { for ($i = 0; $i < legacy_count($idArray); $i++) { if (legacy_count($idArray[$i][$conLeadId]) > 0) { $subArray[] = $idArray[$i]; } } return $subArray; } // This finds the record , which have a matching key $keyId public function getValFromResArray($keyId, $res, $actualField) { if (legacy_count($res) == 1) { return $res[0][$actualField]; } else { for ($i = 0; $i < legacy_count($res); $i++) { //echo "^^^%%%".substr($res[$i][Id],0,15)."-----".$actualField."----".$i."----".$keyId."
"."\n"; if (substr($res[$i]['Id'], 0, 15) == $keyId) { return $res[$i][$actualField]; } } } } // This function extract merge data from $resultSet public function extractMergeData($sf, $resultSet, $merArrayUnique, $query_type, $reportID, $idSubSet, $objIdFieldArray, $dbconn_read, $tableName, $baseField) { if ($query_type == 'C') { $obj = 'Contact'; } else if ($query_type == 'L') { $obj = 'Lead'; } else if ($query_type == "R") { $obj = 'Lead'; if (strlen($resultSet['Contact'][0]['Id']) >= 15) { $obj = 'Contact'; } } $savedMergeKeyId = ''; $id = $resultSet[$obj][0]['Id']; /* echo "############"; print_r($resultSet); print_r($idSubSet); print_r($objIdFieldArray); echo "@@@@@@@@@@@@@"; exit; */ //$this->logTime($sf, 'start extractMergeData merArrayUnique loop'); foreach ((array) $merArrayUnique as $obj => $fldArray) { foreach ((array) $fldArray as $localTableField => $actualField) { if ((strtolower($obj) == 'contact' or strtolower($obj) == 'lead' or strtolower($obj) == 'account') and substr(strtolower($actualField), 0, 6) == 'owner.') { unset($ownerFldArray); $ownerFldArray = @explode(".", $actualField); $obj_ = 'User'; //DE1306 if (strtolower($obj) == 'lead') { $obj_ = 'Name'; } $actualField = $ownerFldArray[1]; } else { $obj_ = $obj; } if (strlen($reportID) >= 15) { if (strtolower($obj) != "contact" and strtolower($obj) != "lead" and strtolower($obj) != "owner" and strtolower($obj) != "account") { $keyFld = $objIdFieldArray[$localTableField]; $mergeKeyId = $idSubSet[$id][$keyFld]; //echo "%%%".$localTableField."***".$keyFld."())".$mergeKeyId."~~~~~".$obj."\n"; //$mergeKeyId = $resultSet[$obj_][0][Id]; $val = $this->getValFromResArray($mergeKeyId, $resultSet[$obj_], $actualField); if (strlen($val) > 0) { $savedMergeKeyId = $mergeKeyId; } } else { $val = $resultSet[$obj_][0][$actualField]; } } else { $val = $resultSet[$obj_][0][$actualField]; } // echo "$$$".$obj_."----".$actualField."----".$localTableField."())".$savedMergeKeyId."~~~~~"."----->".$val."
"."\n"; //$field_val = str_replace('\r', ', ', $field_val); $val = preg_replace('/\n/', ', ', trim($val)); // get rid of carriage returns $val = str_replace(array('\r\n', '\r', '\n', '\n\r'), '', $val); // US3302 - on April 1 2015 //$val = preg_replace("/[^ -~]/", " ", $val); if (strlen($val) > 0) { $updArray[] = "$localTableField = \"" . addslashes($val) . "\" "; } } } //$this->logTime($sf, 'end extractMergeData merArrayUnique loop'); $updStr = @legacy_implode(",", $updArray); //echo "---".$query_type."+++"; // echo "--".$id."***".$updStr."\n"; //if(strlen($mergeKeyId) >= 15 and strlen($reportID) >= 15) if (strlen($savedMergeKeyId) >= 15 and strlen($reportID) >= 15) { $localIdFld = $this->getReportFieldName($savedMergeKeyId, $sf); } // echo "--".$savedMergeKeyId."***".$localIdFld."\n"; //if( strlen($mergeKeyId) >= 15 and strlen($reportID) >= 15 and strlen($localIdFld) >= 5 ) if (strlen($updStr) > 5) { if (strlen($savedMergeKeyId) >= 15 and strlen($reportID) >= 15 and strlen($localIdFld) >= 5) { $sqlStr = "update $tableName set " . $updStr . " where $baseField = \"$id\" and $localIdFld = \"$savedMergeKeyId\"" . $obj; } else { $sqlStr = "update $tableName set " . $updStr . " where $baseField = \"$id\"" . " " . $obj; } //$sf->error_log("extractMergeData--->".$sqlStr); //$this->logTime($sf, 'start extractMergeData update query'); $row = $dbconn_read->query($sqlStr); //$this->logTime($sf, 'end extractMergeData update query'); if (DB::iserror($row)) { $sf->error_log($row); $query_data = $sqlStr; $desc = " Update local failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5116", $query_data, $desc); return -5116; } if ($id == "00QC0000017nygmMAA") { //echo $sqlStr."\n"; } } //return "--".$sqlStr; unset($sqlStr); unset($resultSet); unset($merArrayUnique); unset($idSubSet); } //update the local table public function updateLocal($query_type, $result, $obj_name, $table_field_name, $dbconn_read, $expEmail_name_def__c, $local_tbl_fld_nm, $sf, $reportID, $parentObj) { $my_row = explode(".", $table_field_name); //$sf->error_log(print_r($result)); $sf->error_log("count --->" . legacy_count($result)); //$sf->error_log($result); //print_r($result); $sf_obj_prefix = $this->srm_prefix; $field_name = $my_row[1]; //echo "---".$field_name; if ($obj_name == "User") { $field_name = substr($field_name, 5); } $con = $sf->sf_getRelatedIdField($parentObj, $obj_name); for ($j = 0; $j < legacy_count($result); $j++) { if ($obj_name == "Contact" or $obj_name == "Lead") { $contact_id = $result[$j][$obj_name][0]['Id']; } else if ($obj_name == "Account") { $contact_id = $result[$j]['Contact'][0]['Id']; } else if ($obj_name == "User") { $contact_id = $result[$j]['Contact'][0]['Id']; if (strlen($contact_id) < 15) { $contact_id = $result[$j]['Lead'][0]['Id']; } } else { $contact_id = $result[$j][$obj_name][0][$con]; $objID = substr($result[$j][$obj_name][0]['Id'], 0, 15); } if ($obj_name == "Account") { $field_val = $result[$j]['Account'][0][$field_name]; } else { $field_val = $result[$j][$obj_name][0][$field_name]; // This is to handle fields from Lead line lead.OwnerFirstName if (strlen($field_val) < 1 and $obj_name == "User") { $field_val = $result[$j]['Name'][0][$field_name]; // This is to handle fields from Lead line lead.OwnerFullName if ($field_name == "FullName" and strlen($field_val) < 1) { $field_val = $result[$j]['Name'][0]['Name']; } // This is to handle fields from Lead line ontact.OwnerFullName if ($field_name == "FullName" and strlen($field_val) < 1) { $field_val = $result[$j]['User'][0]['Name']; } } } if (strlen($field_val) < 1 and ($table_field_name == "Contact.Name" or $table_field_name == "Lead.Name")) { $field_val = $expEmail_name_def__c; } // DE271 : Instead of just stripping the carriage return, we should replace it with a comma and a space. (ie. 1 Main Street, Suite 100) if ($field_name == 'MailingStreet' or $field_name == 'OtherStreet') { //$field_val = str_replace('\r', ', ', $field_val); $field_val = preg_replace('/\n/', ', ', trim($field_val)); } // get rid of carriage returns $field_val = str_replace(array('\r\n', '\r', '\n', '\n\r'), '', $field_val); // US3302 - on April 1 2015 //$field_val = preg_replace("/[^ -~]/", " ", $field_val); //and strlen($objID) >= 15 if (strlen($reportID) > 10) { $objIdField = $this->getReportFieldName($objID, $sf); //$contact_id = substr($contact_id,0,15); if (strlen($objIdField) >= 5) { $sql_str = "update $this->table_name set `$local_tbl_fld_nm` = REPLACE(REPLACE(\"" . addslashes($field_val) . "\", '\n', ''),'\r','') where contact_id = \"$contact_id\" and $objIdField = \"$objID\""; //echo $sql_str; } else { $sql_str = "update $this->table_name set `$local_tbl_fld_nm` = REPLACE(REPLACE(\"" . addslashes($field_val) . "\", '\n', ''),'\r','') where contact_id = \"$contact_id\" "; //echo $sql_str; } } else { $sql_str = "update $this->table_name set `$local_tbl_fld_nm` = REPLACE(REPLACE(\"" . addslashes($field_val) . "\", '\n', ''),'\r','') where contact_id = \"$contact_id\" "; //echo $sql_str; } $sf->error_log($sql_str); $row = $dbconn_read->query($sql_str); if (DB::iserror($row)) { $sf->error_log($row); $query_data = $sql_str; $desc = " Update local failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5016", $query_data, $desc); return -5016; } } return; } // This function gets all the deselected id's from the temp. table public function get_deselected_ids($user_id) { $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); $table_name = "express4sf_camp." . $user_id; $sql_str = "select contact_id from $table_name where active =0 "; $row = $dbconn_read->query($sql_str); if (!DB::iserror($row)) { while ($row->fetchinto($result)) { $mem_array[] = '"' . $result->contact_id . '"'; } if (legacy_count($mem_array) > 0) { $deselct_member_str = legacy_implode(",", $mem_array); } } return $deselct_member_str; } // this function de-select ids from the temp. table public function update_deselected_ids($user_id, $deselct_member_str) { if (strlen($deselct_member_str) < 15) { return; } $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); $table_name = "express4sf_camp." . $user_id; $sql_str = "update $table_name set active= 0 where contact_id in ($deselct_member_str)"; $row = $dbconn_read->query($sql_str); if (DB::iserror($row)) { $query_data = $sql_str; $desc = "update_deselected_ids function failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5015", $query_data, $desc); return -5015; } //echo $sql_str; } // this function get the de- selected ids from temp. table public function de_select($table_name, $max_email, $total_count) { $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); $limit_de_select = $total_count - $max_email; $sql_str = "update $table_name set active= -2 where active =1 limit $limit_de_select"; $this->logTime($sf, 'start de_select update active -2 query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end de_select update active -2 query'); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " de_deselect function failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-5014", $query_data, $desc); return -5014; } } // this function get records from mysql table ( for email activity) public function getRecordsFromMysql($query_type, $my_sql_str, $sf, $field_name_orig, $obj_name, $fieldNo, $dbconn_read, $actionFlag, $query_category) { $this->logTime($sf, 'start getRecordsFromMysql query'); $row = $this->dbconn_read_tx->query($my_sql_str); $this->logTime($sf, 'end getRecordsFromMysql query'); $sf->error_log($my_sql_str); if (DB::iserror($row)) { $query_data = $my_sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6017", $query_data, $desc); return -6017; } $no_of_contacts = $row->numRows(); $fname = "FirstName"; $lname = "LastName"; $email = "Email"; $field_ext = ""; $this->logTime($sf, 'start getRecordsFromMysql 400 loop'); for ($n = 0; $n < $no_of_contacts; $n = $n + 400) { if ($n >= $no_of_contacts) { continue; } unset($contact_id_array); $my_sql_str_limit = $my_sql_str . " ORDER BY recipient_id ASC limit $n , 400"; //echo $sql_str; $sf->error_log($sql_str); $this->logTime($sf, 'start getRecordsFromMysql limit 400 query: ' . $n); $row = $this->dbconn_read_tx->query($my_sql_str_limit); $this->logTime($sf, 'end getRecordsFromMysql limit 400 query: ' . $n); if (DB::iserror($row)) { $query_data = $my_sql_str_limit; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6018", $query_data, $desc); return -6018; } while ($row->fetchinto($result)) { $contact_id_array[] = "'" . $result->recipient_id . "'"; } $row->free(); //if the email category is for parents, get the contact Id for each family relation ship records. if ($query_category > 1 and legacy_count($contact_id_array) > 0) { $this->logTime($sf, 'start getRecordsFromMysql getContactForFamilyRelation: ' . $n); $contact_id_array1 = $this->getContactForFamilyRelation($sf, $contact_id_array); $this->logTime($sf, 'end getRecordsFromMysql lgetContactForFamilyRelation: ' . $n); unset($contact_id_array); $contact_id_array = $contact_id_array1; unset($contact_id_array1); } $contact_id_str = legacy_implode(",", $contact_id_array); if ($actionFlag == "Update") { $table_field_name = "field_" . $fieldNo; $sql_str = "update $this->table_name set $table_field_name = 0 where contact_id in ($contact_id_str )"; $sf->error_log($sql_str); $this->logTime($sf, 'start getRecordsFromMysql update field_ query: ' . $n); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRecordsFromMysql update field_ query: ' . $n); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6022", $query_data, $desc); return -6022; } continue; } if (strlen($this->mainSingleQuery) > 5) { $obj_name = "Lead"; if ($query_type == "C") { $obj_name = "Contact"; } $sql_str = "$this->mainSingleQuery and Id in ($contact_id_str)"; } else { if ($query_type == "C") { $obj_name = "Contact"; $sql_str = "Select Id,FirstName,LastName,$this->emailField From $obj_name where HasOptedOutOfEmail <> true and " . $this->emailBounceField . " <> true and $this->emailField != null and Id in ($contact_id_str)"; } else if ($query_type == "L") { $sql_str = "Select Id,FirstName,LastName,Email from Lead where HasOptedOutOfEmail <> true and " . $this->eml_prefix . "HasEmailBounced__c <> true and email != null and Id in ($contact_id_str) "; $obj_name = "Lead"; } } unset($result); unset($records); $ii = 0; $this->logTime($sf, 'start getRecordsFromMysql get object special soql: ' . $n); $records = $sf->specialQuery1($sql_str); $this->logTime($sf, 'end getRecordsFromMysql get object special soql: ' . $n); $sf->error_log("API counter: SOQL " . $sql_str); $sf->error_log("API counter:" . $this->API_counter++ . " No of records fetched--->" . $sf->result_size); if ($records < 0) { $query_data = $sql_str; $desc = " Invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6023", $query_data, $desc); if ($records == -2) { return -2004; } else if ($records == -3) { return -2005; } return -6023; } $this->logTime($sf, 'start getRecordsFromMysql get object special2 soql: ' . $n); $sf->specialQuery2($records, $ii); $this->logTime($sf, 'end getRecordsFromMysql get object special2 soql: ' . $n); $result = $sf->results_array; if (legacy_count($result) < 1) { $query_data = $sql_str; $desc = " no result set "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6021", $query_data, $desc); // DE678 //return 0;// commented on april 9th 2014since not all records from mysql was procession unset($result); unset($records); continue; } $upd_val = 1; $sql_str_2 = $this->getNameStr($result, $obj_name, $query_type, $upd_val, 10, $sf); $sql_str_1 = "insert IGNORE into $this->table_name(contact_id,`$field_ext$fname`,`$field_ext$lname`,`$email`,`ownerEmail`,`ownerFullName`,field_$fieldNo,`mergedIds`,`validConditions`) values"; $sql_str_3 = " ON DUPLICATE KEY UPDATE field_$fieldNo=$upd_val"; $sql_str = $sql_str_1 . $sql_str_2 . $sql_str_3; //echo $sql_str."$$$$$$$"; exit; $sf->error_log($sql_str); $this->logTime($sf, 'start getRecordsFromMysql insert contact query: ' . $n); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRecordsFromMysql insert contact query: ' . $n); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6008", $query_data, $desc); return -6008; } } $this->logTime($sf, 'end getRecordsFromMysql 400 loop'); unset($sql_str); unset($sql_str_2); unset($result); unset($records); unset($contact_id_array); } // this function logs the query/error public function query_log($dbconn_read, $org, $campaign_id, $bcast_id, $error_code, $query_data, $desc) { $query_data = addslashes($query_data); $sql_str = "insert into query_log(org_id,bcast_id,campaign_id,error_code,query_data,query_desc)values(" . " \"$org\",\"$bcast_id\",\"$campaign_id\",\"$error_code\",\"$query_data\",\"$desc\")"; $row = $dbconn_read->query($sql_str); if (DB::iserror($row)) { } } // this function return the error code description( defanition) public function error_message($response) { switch ($response) { case -4001:$message = 'Advanced query does not contain all the criteria'; break; case -5012:$message = 'Invalid orgID'; break; case -1001:$message = 'BroadcastID not found'; break; case -1002:$message = 'CampaignID not found'; break; case -2007:$message = 'Preferences are not set or required data missing from preferences'; break; case -5009:$message = 'Database table creation error'; break; case -5010:$message = 'Can\'t create Index on database table'; break; case -2003:$message = 'Invalid query, value parameter missing.'; break; case -2001:$message = 'Advanced query condition is not correct.'; break; case -2009:$message = 'Invalid query .'; break; case -5016:$message = 'can not add merge field to local table'; break; case -5021:$message = 'Invalid query results. Please check the value parameters.'; break; case -5008:$message = 'Can\'t make entry in database table.'; break; case -5023:$message = 'Invalid advanced query'; break; case -5016:$message = 'can not add merge field to local table'; break; case -5018:$message = 'eleminate duplicate failed.'; break; case -5020:$message = 'inserting seed list into local table failed '; break; case -2011:$message = 'select from seed list failed'; break; case -5022:$message = 'select where active = 1 failed'; break; case -5002:$message = 'update deselected contact failed'; break; case -5001:$message = 'Update failed'; break; case -6077:$message = 'Select from activity queue failed'; break; case -5028:$message = 'update Failed for exclude'; break; case -5008:$message = 'Failed query'; break; case -31:$message = 'Invalid merge fields'; break; case -32:$message = 'Invalid merge fields'; break; case -5017:$message = 'select failed in merge field function'; break; case -5007: case -5001: case -5002:$message = 'Can\'t update database table.'; break; case -310:$message = 'Merge fields from object ContactScheduleItem__c,OrgEvent__c may not be used'; break; case -5003:$message = 'Can\'t select from database table.'; break; default:$message = 'No Records found'; } return $message; } //this function construct a singe query public function constructSingleQuery($client_id, $org, $query_type, $para_array, $sf, $adv_query, $newsGroupsId) { $newsGroupStr = ''; if (strlen($newsGroupsId) > 0) { $newsGroupStr = " and (" . $this->eml_prefix . "emailGroups__c = NULL or " . $this->eml_prefix . "emailGroups__c includes ($newsGroupsId))"; } if ($this->fromOwnerEmail == 'true' or $this->replyOwnerEmail == 'true') { $ownerEmail = ",owner.email,owner.name"; } // temp. commented //$validConditions =",validConditions__c"; $base_sql_lead = "Select t.Id,t.FirstName,t.LastName,t.Email,t." . $this->TwomergedIdFields . $ownerEmail . " from Lead t where HasOptedOutOfEmail <> true and " . $this->eml_prefix . "HasEmailBounced__c <> true and email != null "; $base_sql_contact = "Select t.Id,t.FirstName,t.LastName,$this->emailField,t." . $this->TwomergedIdFields . $ownerEmail . " From Contact t where t.HasOptedOutOfEmail <> true and t." . $this->emailBounceField . " <> true and $this->emailField != null $newsGroupStr "; $curr_sql = ""; if ($query_type == "C") { $base_sql = $base_sql_contact; } else { $base_sql = $base_sql_lead; } $index = 1; $obj_counter = 0; //print_r($para_array); $this->logTime($sf, 'start constructSingleQuery loop'); for ($i = 0; $i < legacy_count($para_array); $i++) { unset($my_row); $my_row = explode("_-", $para_array[$i]); if ($my_row[0] == "Select One" or $my_row[1] == "Select One" or trim($my_row[0]) == '' or trim($my_row[0]) == '') { continue; } //echo "^^^^".$my_row[0]."----------".count($para_array)."
"."\n"; if ($my_row[0] != 'Lead' and $my_row[0] != 'Contact' and $my_row[0] != 'Campaign' and $my_row[0] != 'Travel') { $my_row[0] = str_replace("--P--", $this->srm_prefix, $my_row[0]); } $pos = strpos($my_row[1], '-P--'); if ($pos >= 1 and $my_row[0] != 'Travel') { $my_row[1] = str_replace("--P--", $this->srm_prefix, $my_row[1]); } $obj_name = $my_row[0]; $obj['name'][] = $my_row[0]; $obj['field_name'][] = $my_row[1]; $obj['field_value'][] = $my_row[3]; $obj['oper'][] = $my_row[2]; $from_relation = str_replace("__c", "__r", $obj_name); $field_name = $my_row[1]; $field_name_orig = $my_row[1]; if (strpos($field_name, "___phone") > 0) { $field_type = "string"; $field_name = str_replace("___phone", "", $field_name); } if (strpos($field_name, "___p") > 0) { $field_type = "picklist"; $field_name = str_replace("___p", "", $field_name); } else if (strpos($field_name, "___p") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("___p", "", $field_name); } else if (strpos($field_name, "___o") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("___o", "", $field_name); } else if (strpos($field_name, "___e") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("___e", "", $field_name); } else { $___pos = strpos($field_name, "___"); if ($___pos > 0) { $field_type = substr($field_name, $___pos + 3); $field_name = substr($field_name, 0, $___pos); } } if (strpos($field_name, "~") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("~", " ", $field_name); } $field_value = $my_row[3]; $operator = $my_row[2]; if (($obj_name == "Campaign" or $obj_name == "Event") and strpos($field_value, '~~') > 0) { $first_arry = explode('~~', $field_value); $field_value_str1 = $this->getValueStr($operator, $field_type, $first_arry[0], $field_name); if ($obj_name == "Event") { $field_value_str2 = $this->getValueStr($first_arry[1], 'datetime', $first_arry[2], 'CreatedDate'); $field_value_str = $field_value_str1 . ' and CreatedDate ' . $field_value_str2; } else if ($obj_name == "Campaign") { $field_value_str2 = $this->sfToMysqlDate("time_created", $first_arry[2], $first_arry[1]); $field_value_str = $field_value_str1 . ' and ' . $field_value_str2; } } else if ($obj_name == "Travel" and strpos(strtolower($field_name), 'date') > 0) { $field_value_str = $this->getValueStr($operator, 'date', $field_value, $field_name); } else { $field_value_str = $this->getValueStr($operator, $field_type, $field_value, $field_name); } $not_contain = ""; $not_contain_open = ""; $not_contain_close = ""; if ($operator == 8) { $not_contain = " not ("; $not_contain_open = "("; $not_contain_close = ")"; } if ($operator == 9 or $operator == 7) { $not_contain = ""; $not_contain_open = "("; $not_contain_close = ")"; } if (($operator == 11 and $obj_name != "Lead" and $obj_name != "Contact" and $obj_name != "Travel") or ($obj_name == "Campaign" and ($field_name == "DHTML View" or $field_name == "DInteract")) or (($obj_name == "Campaign") and ($operator == 11 or $operator == 2)) or ($obj_name == "Campaign" and strpos($field_name_orig, "___o") > 0) ) { return -1; } if (($obj_name != "Lead" and $obj_name != "Event" and $obj_name != "Contact" and $obj_name != "Campaign" and $obj_name != "Account" and $obj_name != "Travel")) { //$con_lead_obj_with_prefix = "Lead__c"; $parentObj = "Lead"; if ($query_type == "C") { //$con_lead_obj_with_prefix = "Contact__c"; $parentObj = "Contact"; } /* if(strpos($obj_name,$this->srm_prefix) === false) { } else { $con_lead_obj_with_prefix = $this->srm_prefix.$con_lead_obj_with_prefix; } */ $this->logTime($sf, 'start constructSingleQuery loop generate relatedIdFields: ' . $i); $con_lead_obj_with_prefix = $sf->sf_getRelatedIdField($parentObj, $obj_name); $this->logTime($sf, 'end constructSingleQuery loop generate relatedIdFields: ' . $i); $more_str = "__" . $obj_name . "__"; $advSingleSubQueryParaArray[] = " $not_contain_open $not_contain $field_name $field_value_str $not_contain_close "; if (strlen($curr_sql) < 1) { $obj_counter++; $added_obj[$index++] = $obj_name; $advSingleSubQueryBgn = "select $con_lead_obj_with_prefix from $obj_name "; $curr_sql = $base_sql . " and ID in (" . "select $con_lead_obj_with_prefix from $obj_name where $not_contain_open $not_contain $field_name $field_value_str $not_contain_close $more_str " . " )"; } else { $exist = @legacy_array_search($obj_name, $added_obj); if ($exist) { $replace_str = " and $not_contain_open $not_contain $field_name $field_value_str $not_contain_close $more_str "; $curr_sql = str_replace($more_str, $replace_str, $curr_sql); } else { $obj_counter++; $curr_sql .= " and ID in (" . "select $con_lead_obj_with_prefix from $obj_name where $not_contain_open $not_contain $field_name $field_value_str $not_contain_close $more_str" . " )"; $advSingleSubQueryBgn = "select $con_lead_obj_with_prefix from $obj_name "; $added_obj[$index++] = $obj_name; } } } else if ($obj_name == "Event") { $con_obj_with_prefix = $this->event_prefix . "Contact__c"; $con_item_with_prefix = $this->event_prefix . "ContactScheduleItem__c"; $org_event_with_prefix = $this->event_prefix . "OrgEvent__c"; $true_false = 'true'; $field_name_ = $field_name; $obj_counter++; if ($field_name == 'Registered__c') { $field_name_ = 'Canceled__c'; $true_false = 'false'; $additional_condition = " and " . $this->event_prefix . "Waiting_List__c <> true "; } $field_name_ = $this->event_prefix . $field_name_; $in_string = " in "; $field_value_str_orig = $field_value_str; if (strrpos($field_value_str, "not in") === false) { } else { $field_value_str = str_replace("not in", "in", $field_value_str); $in_string = " not in "; } if (strlen($curr_sql) < 1) { $added_obj[$index++] = $obj_name; $curr_sql = $base_sql . " and ID $in_string (" . "select $con_obj_with_prefix from $con_item_with_prefix where $org_event_with_prefix $field_value_str and $field_name_ = $true_false $additional_condition " . " )"; } else { $curr_sql .= " and ID $in_string (" . "select $con_obj_with_prefix from $con_item_with_prefix where $org_event_with_prefix $field_value_str and $field_name_ = $true_false $additional_condition" . " )"; $added_obj[$index++] = $obj_name; } $advSingleSubQueryBgn = "select $con_obj_with_prefix from $con_item_with_prefix "; $advSingleSubQueryParaArray[] = " ( $org_event_with_prefix $field_value_str_orig and $field_name_ = $true_false $additional_condition )"; } else if ($obj_name == "Contact") { if (strlen($curr_sql) < 1) { $curr_sql = $base_sql . " and " . "$not_contain_open $not_contain $field_name $field_value_str $not_contain_close"; } else { $curr_sql .= " and " . "$not_contain_open $not_contain $field_name $field_value_str $not_contain_close"; } $advSingleSubQueryParaArray[] = "$not_contain_open $not_contain $field_name $field_value_str $not_contain_close"; } else if ($obj_name == "Travel") { unset($school_arr); if (strpos($field_name_orig, "onsortiumtravel") >= 1) { $this->logTime($sf, 'start constructSingleQuery loop getTravelAccounts2: ' . $n); $school_arr = $this->getTravelAccounts2($sf, $field_value_str); $this->logTime($sf, 'start constructSingleQuery loop getTravelAccounts2: ' . $n); } else if (strpos(strtolower($field_name_orig), "date") >= 1) { $this->logTime($sf, 'start constructSingleQuery loop getTravelAccounts: ' . $n); $school_arr = $this->getTravelAccounts($field_value_str, $sql_str, $sf); $this->logTime($sf, 'start constructSingleQuery loop getTravelAccounts: ' . $n); } if (legacy_count($school_arr) > 0) { $school_str = @legacy_implode(",", $school_arr); } else { $school_str = "'a0Q50000001aFaVVVV'"; } if (strlen($curr_sql) < 1) { $curr_sql = $base_sql . " and " . " AccountId in ($school_str ) "; } else { $curr_sql .= " and " . " AccountId in ($school_str ) "; } $advSingleSubQueryParaArray[] = " AccountId in ($school_str ) "; } else if ($obj_name == "Account") { if (strlen($curr_sql) < 1) { $curr_sql = $base_sql . " and " . "$not_contain_open $not_contain Account.$field_name $field_value_str $not_contain_close"; } else { $curr_sql .= " and " . "$not_contain_open $not_contain Account.$field_name $field_value_str $not_contain_close"; } $advSingleSubQueryParaArray[] = "$not_contain_open $not_contain Account.$field_name $field_value_str $not_contain_close"; } else if ($obj_name == "Lead") { if (strlen($curr_sql) < 1) { $curr_sql = $base_sql . " and " . "$not_contain_open $not_contain t.$field_name $field_value_str $not_contain_close"; } else { $curr_sql .= " and " . "$not_contain_open $not_contain Lead.$field_name $field_value_str $not_contain_close"; } $advSingleSubQueryParaArray[] = "$not_contain_open $not_contain Lead.$field_name $field_value_str $not_contain_close"; } else if ($obj_name == "Campaign") { if (strpos($field_name_orig, "___p") > 0 or strpos($field_name_orig, "___e") > 0) { if (strlen($curr_campaign_sql) < 1) { if (legacy_in_array(strtolower($field_name), $this->sent_log_status)) { $base_sql_campaign = "select recipient_id from activity.sent_log_" . $client_id . " where length(recipient_id) >=15 and "; $curr_campaign_sql = $base_sql_campaign . " (status = '$field_name' and campaign_id $field_value_str )"; } else { $base_sql_campaign = "select recipient_id from activity.activity_log_" . $client_id . " where length(recipient_id) >=15 and "; $curr_campaign_sql = $base_sql_campaign . " (activity = '$field_name' and campaign_id $field_value_str )"; } } else { if (legacy_in_array(strtolower($field_name), $this->sent_log_status)) { $curr_campaign_sql .= " and (status = '$field_name' and campaign_id $field_value_str )"; } else { $curr_campaign_sql .= " and (activity = '$field_name' and campaign_id $field_value_str )"; } } } if (legacy_in_array(strtolower($field_name), $this->sent_log_status)) { $advSingleSubQueryParaArray[] = " (status = \"$field_name\" and campaign_id $field_value_str )"; } else { $advSingleSubQueryParaArray[] = " (activity = \"$field_name\" and campaign_id $field_value_str )"; } } } // end of for loop $this->logTime($sf, 'end constructSingleQuery loop'); $this->mainSingleQuery = ''; if ($obj_counter > 3) { return -1; } for ($k = 1; $k <= legacy_count($added_obj); $k++) { $more_str = "__" . $added_obj[$k] . "__"; $curr_sql = str_replace($more_str, '', $curr_sql); $curr_campaign_sql = str_replace($more_str, '', $curr_campaign_sql); } if (strlen($curr_campaign_sql) > 0) { $this->mainSingleQuery = $curr_sql; $sub_q['query'][0] = $curr_campaign_sql; $sub_q['obj'][0] = $obj_name; $sub_q['field'][0] = $field_name; $sub_q['orig_field_name'][0] = $field_name_orig; $sub_q['operator'][0] = $operator; } else { $sub_q['query'][0] = $curr_sql; $sub_q['obj'][0] = $obj_name; } if ($this->advSingleQueryFlag == 1) { $adv_query_condition = $adv_query; for ($k = 1; $k <= 20; $k++) { $adv_query_condition = str_replace($k, "-_" . $k, $adv_query_condition); } for ($k = 1; $k <= legacy_count($advSingleSubQueryParaArray); $k++) { $adv_query_condition = str_replace("-_" . $k, $advSingleSubQueryParaArray[$k - 1], $adv_query_condition); } if ($obj_name == "Campaign") { $this->mainSingleQuery = $base_sql_campaign . " (" . $adv_query_condition . ")"; $sub_q['query'][0] = $this->mainSingleQuery; $sub_q['obj'][0] = $obj_name; $sub_q['field'][0] = $field_name; $sub_q['orig_field_name'][0] = $field_name_orig; $sub_q['operator'][0] = $operator; } else { if (strlen($advSingleSubQueryBgn) > 0) { $this->mainSingleQuery = $base_sql . " and ID in ( " . $advSingleSubQueryBgn . " where (" . $adv_query_condition . ") )"; } else { $this->mainSingleQuery = $base_sql . " and (" . $adv_query_condition . ")"; } $sub_q['query'][0] = $this->mainSingleQuery; $sub_q['obj'][0] = $obj_name; } } return $sub_q; } // end of function //construct queries for adv. queries ( create 1 query per criteria). public function constructInitailQueries($client_id, $org, $query_type, $para_array, $sf, $adv_query, $newsGroupsId) { for ($i = 0; $i < legacy_count($para_array); $i++) { unset($my_row); $my_row = explode("_-", $para_array[$i]); if ($my_row[0] == "Select One" or $my_row[1] == "Select One" or trim($my_row[0]) == '' or trim($my_row[0]) == '') { continue; } if ($my_row[0] != 'Lead' and $my_row[0] != 'Contact' and $my_row[0] != 'Campaign') { $my_row[0] = str_replace("--P--", $this->srm_prefix, $my_row[0]); } $pos = strpos($my_row[1], '-P--'); if ($pos >= 1) { $my_row[1] = str_replace("--P--", $this->srm_prefix, $my_row[1]); } $obj_name = $my_row[0]; $obj['name'][] = $my_row[0]; $obj['field_name'][] = $my_row[1]; $obj['field_value'][] = $my_row[3]; $obj['oper'][] = $my_row[2]; $this->table_field_str .= ",field_$i BOOLEAN NOT NULL DEFAULT '0'"; $from_relation = str_replace("__c", "__r", $obj_name); $field_name = $my_row[1]; $field_name_orig = $my_row[1]; if (strpos($field_name, "___p") > 0) { $field_type = "picklist"; $field_name = str_replace("___p", "", $field_name); } else if (strpos($field_name, "___p") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("___p", "", $field_name); } else if (strpos($field_name, "___o") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("___o", "", $field_name); } else if (strpos($field_name, "___e") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("___e", "", $field_name); } else { $___pos = strpos($field_name, "___"); if ($___pos > 0) { $field_type = substr($field_name, $___pos + 3); $field_name = substr($field_name, 0, $___pos); } } if (strpos($field_name, "~") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("~", " ", $field_name); } $field_value = $my_row[3]; $operator = $my_row[2]; if (($obj_name == "Campaign" or $obj_name == "Event") and strpos($field_value, '~~') > 0) { $first_arry = explode('~~', $field_value); $field_value_str1 = $this->getValueStr($operator, $field_type, $first_arry[0], $field_name); if ($obj_name == "Event") { $field_value_str2 = $this->getValueStr($first_arry[1], 'datetime', $first_arry[2], 'CreatedDate'); $field_value_str = $field_value_str1 . ' and CreatedDate ' . $field_value_str2; } else if ($obj_name == "Campaign") { $field_value_str2 = $this->sfToMysqlDate("time_created", $first_arry[2], $first_arry[1]); $field_value_str = $field_value_str1 . ' and ' . $field_value_str2; } } else if ($obj_name == "Travel" and strpos(strtolower($field_name), 'date') > 0) { $field_value_str = $this->getValueStr($operator, 'datetime', $field_value, $field_name); } else { $field_value_str = $this->getValueStr($operator, $field_type, $field_value, $field_name); } $not_contain = ""; $not_contain_open = ""; $not_contain_close = ""; if ($operator == 8) { $not_contain = " not ("; $not_contain_open = "("; $not_contain_close = ")"; } if ($operator == 9 or $operator == 7) { $not_contain = ""; $not_contain_open = "("; $not_contain_close = ")"; } $sf_obj_prefix = $this->srm_prefix; $newsGroupStr = ''; if (strlen($newsGroupsId) > 0) { $newsGroupStr = " and (" . $this->eml_prefix . "emailGroups__c = NULL or " . $this->eml_prefix . "emailGroups__c includes ($newsGroupsId))"; } if ($this->fromOwnerEmail == 'true' or $this->replyOwnerEmail == 'true') { $ownerEmail = ",owner.email,owner.name"; } // temp. commented //$validConditions =",validConditions__c"; $base_sql_lead = "Select t.Id,t.FirstName,t.LastName,t.Email,t." . $this->TwomergedIdFields . $ownerEmail . " from Lead t where HasOptedOutOfEmail <> true and " . $this->eml_prefix . "HasEmailBounced__c <> true and email != null "; $base_sql_contact = "Select t.Id,t.FirstName,t.LastName,$this->emailField,t." . $this->TwomergedIdFields . $ownerEmail . " From Contact t where t.HasOptedOutOfEmail <> true and t." . $this->emailBounceField . " <> true and $this->emailField != null $newsGroupStr"; $curr_sql = ""; if ($query_type == "C") { $base_sql = $base_sql_contact; } else if ($query_type == "L") { $base_sql = $base_sql_lead; } $connector = " in "; if ($operator == 11 and ($obj_name != "Contact" and $obj_name != "Lead" and $obj_name != "Campaign" and $obj_name != "Account" and $obj_name != "Travel" and $obj_name != "Event")) { $field_value_str = str_replace('not in', 'in', $field_value_str); $field_value_str = str_replace('excludes', 'in', $field_value_str); $connector = " not in "; } if ($obj_name != "Lead" and $obj_name != "Contact" and $obj_name != "Campaign" and $obj_name != "Event" and $obj_name != "Account" and $obj_name != "Travel") { if (strpos($obj_name, $sf_obj_prefix) === false) { $sf_obj_prefix = ''; } else { $sf_obj_prefix = $this->srm_prefix; } $parentObject = "Contact"; if ($query_type == 'L') { $parentObject = "Lead"; } $this->logTime($sf, 'start constructInitialQueries loop getRelatedIdField: ' . $n); $rlnId = $sf->sf_getRelatedIdField($parentObject, $obj_name); $this->logTime($sf, 'end constructInitialQueries loop getRelatedIdField: ' . $n); $rlnName = $sf->childRelationName; $subSoql = "select $rlnId from $obj_name where $not_contain_open $not_contain $field_name $field_value_str $not_contain_close "; if ($query_type == 'C') { //$sub_q[query][$i] = "Select t.$rlnId,t.$rlnName.FirstName,t.$rlnName.LastName,t.$rlnName.Email From $obj_name t where $rlnName.email like '%@%' and $rlnName.HasOptedOutOfEmail <> true and t.$rlnName.$this->eml_prefix"."HasEmailBounced__c <> true and $not_contain_open $not_contain t.$field_name $field_value_str $not_contain_close"; } else if ($query_type == 'L') { //$sub_q[query][$i] = "Select t.$rlnId,t.$rlnName.FirstName,t.$rlnName.LastName,t.$rlnName.Email From $obj_name t where $rlnName.email like '%@%' and $rlnName.HasOptedOutOfEmail <> true and t.$rlnName.$this->eml_prefix"."HasEmailBounced__c <> true and $not_contain_open $not_contain t.$field_name $field_value_str $not_contain_close"; } $sub_q['query'][$i] = $base_sql . " and Id $connector ($subSoql)"; $sub_q['obj'][$i] = $obj_name; $sub_q['field'][$i] = $field_name; $sub_q['orig_field_name'][$i] = $field_name_orig; $sub_q['operator'][$i] = $operator; } else if ($obj_name == "Contact") { $sub_q['query'][$i] = "Select t.Id,t.FirstName,t.LastName,$this->emailField From $obj_name t where t.HasOptedOutOfEmail <> true and t." . $this->emailBounceField . " <> true and $this->emailField != null and $not_contain_open $not_contain t.$field_name $field_value_str $not_contain_close"; $sub_q['obj'][$i] = $obj_name; $sub_q['field'][$i] = $field_name; $sub_q['orig_field_name'][$i] = $field_name_orig; $sub_q['operator'][$i] = $operator; } else if ($obj_name == "Account") { $curr_sql = $base_sql . " and " . " $not_contain_open $not_contain Account.$field_name $field_value_str $not_contain_close"; $sub_q['query'][$i] = $curr_sql; $sub_q['obj'][$i] = $obj_name; $sub_q['field'][$i] = $field_name; $sub_q['orig_field_name'][$i] = $field_name_orig; $sub_q['operator'][$i] = $operator; } else if ($obj_name == "Lead") { $sub_q['query'][$i] = "Select Id,FirstName,LastName,Email from Lead where HasOptedOutOfEmail <> true and " . $this->eml_prefix . "HasEmailBounced__c <> true and email != null and $not_contain_open $not_contain $field_name $field_value_str $not_contain_close"; $sub_q['obj'][$i] = $obj_name; $sub_q['field'][$i] = $field_name; $sub_q['orig_field_name'][$i] = $field_name_orig; $sub_q['operator'][$i] = $operator; } else if ($obj_name == "Campaign") { $type_relation = ' = '; if (strpos($field_name_orig, "___p") > 0 or strpos($field_name_orig, "___e") > 0) { if ($query_type == "C") { if (legacy_in_array(strtolower($field_name), $this->sent_log_status)) { $sub_q['query'][$i] = "select recipient_id from activity.sent_log_" . $client_id . " where status $type_relation '$field_name' and length(recipient_id) >=15 and campaign_id $field_value_str "; } else { $sub_q['query'][$i] = "select recipient_id from activity.activity_log_" . $client_id . " where activity $type_relation '$field_name' and length(recipient_id) >=15 and campaign_id $field_value_str "; } } else if ($query_type == "L") { if (legacy_in_array(strtolower($field_name), $this->sent_log_status)) { $sub_q['query'][$i] = "select recipient_id from activity.sent_log_" . $client_id . " where status $type_relation '$field_name' and length(recipient_id) >=15 and campaign_id $field_value_str "; } else { $sub_q['query'][$i] = "select recipient_id from activity.activity_log_" . $client_id . " where activity $type_relation '$field_name' and length(recipient_id) >=15 and campaign_id $field_value_str "; } } } else if (strpos($field_name_orig, "___o") > 0) { if ($operator == 11) { $field_value_str = str_replace('not in', 'in', $field_value_str); $field_value_str = str_replace('excludes', 'in', $field_value_str); $connector = " not in "; } if ($query_type == "C") { $subSoql = "select ContactId from CampaignMember where Status = '$field_name' and CampaignId $field_value_str"; } else if ($query_type == "L") { $subSoql = "select LeadId from CampaignMember where Status = '$field_name' and CampaignId $field_value_str"; } $sub_q['query'][$i] = $base_sql . " and Id $connector ($subSoql)"; } $sub_q['obj'][$i] = $obj_name; $sub_q['field'][$i] = $field_name; $sub_q['orig_field_name'][$i] = $field_name_orig; $sub_q['operator'][$i] = $operator; } else if ($obj_name == "Event") { $con_obj_with_prefix = $this->event_prefix . "Contact__c"; $con_item_with_prefix = $this->event_prefix . "ContactScheduleItem__c"; $org_event_with_prefix = $this->event_prefix . "OrgEvent__c"; $true_false = 'true'; $field_name_ = $field_name; $obj_counter++; if ($field_name == 'Registered__c') { $field_name_ = 'Canceled__c'; $true_false = 'false'; $additional_condition = " and " . $this->event_prefix . "Waiting_List__c <> true "; } $field_name_ = $this->event_prefix . $field_name_; $in_string = " in "; $field_value_str_orig = $field_value_str; if (strrpos($field_value_str, "not in") === false) { } else { $field_value_str = str_replace("not in", "in", $field_value_str); $in_string = " not in "; } $curr_sql = $base_sql . " and ID $in_string (" . "select $con_obj_with_prefix from $con_item_with_prefix where $org_event_with_prefix $field_value_str and $field_name_ = $true_false $additional_condition " . " )"; $sub_q['query'][$i] = $curr_sql; $sub_q['obj'][$i] = $obj_name; $sub_q['field'][$i] = $field_name; $sub_q['orig_field_name'][$i] = $field_name_orig; $sub_q['operator'][$i] = $operator; } else if ($obj_name == "Travel") { if (strpos($field_name_orig, "onsortiumtravel") >= 1) { $this->logTime($sf, 'start constructInitialQueries loop getTravelAccounts2: ' . $i); $school_arr = $this->getTravelAccounts2($sf, $field_value_str); $this->logTime($sf, 'end constructInitialQueries loop getTravelAccounts2: ' . $i); } else if (strpos(strtolower($field_name_orig), "date") >= 1) { $this->logTime($sf, 'start constructInitialQueries loop getTravelAccounts: ' . $i); $school_arr = $this->getTravelAccounts($field_value_str, $sql_str, $sf); $this->logTime($sf, 'end constructInitialQueries loop getTravelAccounts: ' . $i); } if (legacy_count($school_arr) > 0) { $school_str = @legacy_implode(",", $school_arr); } else { $school_str = "'a0Q50000001aFaVVVV'"; } $sub_q['query'][$i] = "Select t.Id,t.FirstName,t.LastName,$this->emailField From Contact t where t.HasOptedOutOfEmail <> true and t." . $this->emailBounceField . " <> true and $this->emailField != null and " . " AccountId in ($school_str)"; $sub_q['obj'][$i] = $obj_name; $sub_q['field'][$i] = $field_name; $sub_q['orig_field_name'][$i] = $field_name_orig; $sub_q['operator'][$i] = $operator; } } // end of for loop return $sub_q; } // end of function public function getTravelAccounts($field_value_str, $consort_str, $sf) { unset($school_arr); unset($consortium_arr); if (strlen($field_value_str) > 0) { $sql_str = "select " . $this->event_prefix . "Include_School_And_Consortium__c," . $this->event_prefix . "School__c," . $this->event_prefix . "Consortium__c from " . $this->event_prefix . "OrgEvent__c where DAY_ONLY(" . $this->event_prefix . "Start_Date_Time__c) " . $field_value_str; $records = $sf->specialQuery1($sql_str); $sf->specialQuery2($records, 0); $result = $sf->results_array; for ($i = 0; $i < legacy_count($result); $i++) { if (strlen($result[$i][$this->event_prefix . 'OrgEvent__c'][0][$this->event_prefix . 'School__c']) > 0) { $school_arr[] = "'" . $result[$i][$this->event_prefix . 'OrgEvent__c'][0][$this->event_prefix . 'School__c'] . "'"; } if (($result[$i][$this->event_prefix . 'OrgEvent__c'][0][$this->event_prefix . 'Include_School_And_Consortium__c'] == "true") or ((strlen($result[$i][$this->event_prefix . 'OrgEvent__c'][0][$this->event_prefix . 'School__c']) < 5) and ($result[$i][$this->event_prefix . 'OrgEvent__c'][0][$this->event_prefix . 'Include_School_And_Consortium__c'] == "false")) ) { if (strlen($result[$i][$this->event_prefix . 'OrgEvent__c'][0][$this->event_prefix . 'Consortium__c']) > 0) { $consortium_arr[] = "'" . $result[$i][$this->event_prefix . 'OrgEvent__c'][0][$this->event_prefix . 'Consortium__c'] . "'"; } } } if (legacy_count($consortium_arr) > 0) { $consortium_str = @legacy_implode(",", $consortium_arr); $consortium_str = " in ($consortium_str)"; $school_arr2 = $this->getTravelAccounts2($sf, $consortium_str); } if (legacy_count($school_arr) > 0 and legacy_count($school_arr2) > 0) { $final_school_arr = legacy_array_merge($school_arr, $school_arr2); } else if (legacy_count($school_arr) > 0 and legacy_count($school_arr2) < 1) { $final_school_arr = $school_arr; } else if (legacy_count($school_arr) < 1 and legacy_count($school_arr2) > 0) { $final_school_arr = $school_arr2; } } else if (strlen($consort_str) > 0) { $final_school_arr = $this->getTravelAccounts2($sf, $consortium_str); } $final_school_arr = @legacy_array_unique($final_school_arr); return $final_school_arr; } public function getTravelAccounts2($sf, $consortium_str) { unset($school_arr); $sql_str = "select " . $this->event_prefix . "Account__c from " . $this->event_prefix . "Account_Consortium__c where " . $this->event_prefix . "Consortium__c " . $consortium_str; $records = $sf->specialQuery1($sql_str); $sf->specialQuery2($records, 0); $result = $sf->results_array; for ($i = 0; $i < legacy_count($result); $i++) { $acc = $result[$i][$this->event_prefix . 'Account_Consortium__c'][0][$this->event_prefix . 'Account__c']; if (strlen($acc) > 0) { $school_arr[] = "'" . $acc . "'"; } } $school_arr = @legacy_array_unique($school_arr); if (legacy_count($school_arr) > 0) { return $school_arr; } } // Get the records from Family relation table public function getRelatedRecords($sf, $table_fields_array, $merge_fields) { $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); $chld_table = $this->table_name_final . "_chld"; $sql_str = "DROP TABLE IF EXISTS $chld_table "; $this->logTime($sf, 'start getRelatedRecords drop child table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords drop child table query'); //$sql_str = "CREATE INDEX part_of_email ON $this->table_name ($email(5))"; $sql_str = "CREATE TABLE $chld_table SELECT * FROM $this->table_name_final limit 0"; $sf->error_log("getRelatedRecords -->" . $sql_str . " " . $this->query_category); $this->logTime($sf, 'start getRelatedRecords create child table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords create child table query'); if (DB::iserror($row)) { print_r($row); } $sql_str = "CREATE INDEX part_of_email ON $chld_table (Email(5))"; $this->logTime($sf, 'start getRelatedRecords create child index query'); $row = $dbconn_read->query($sql_str); $sql_str = "CREATE INDEX reln_index ON $this->table_name_final (relation_id)"; $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords create child index query'); if (DB::iserror($row)) { print_r($row); } $my_sql_str = "select contact_id from $this->table_name_final where active= 1"; $sf->error_log($my_sql_str); $this->logTime($sf, 'start getRelatedRecords get active contacts query'); $row = $dbconn_read->query($my_sql_str); $this->logTime($sf, 'end getRelatedRecords get active contacts query'); $no_of_contacts = $row->numRows(); $fname = "FirstName"; $lname = "LastName"; $email = "Email"; $field_ext = ""; $this->logTime($sf, 'start getRelatedRecords get sf user info'); $user_info = $sf->user_info(); $this->logTime($sf, 'end getRelatedRecords get sf user info'); $org_id = $user_info->organizationId; $org = $user_info->organizationId; $this->logTime($sf, 'start getRelatedRecords get active contacts 400 loop'); for ($n = 0; $n < $no_of_contacts; $n = $n + 400) { if ($n >= $no_of_contacts) { continue; } unset($contact_id_array); $my_sql_str_limit = $my_sql_str . " ORDER BY contact_id ASC limit $n , 400"; $this->logTime($sf, 'start getRelatedRecords get active contacts 400 limit query: ' . $n); $row = $dbconn_read->query($my_sql_str_limit); $this->logTime($sf, 'end getRelatedRecords get active contacts 400 limit query: ' . $n); if (DB::iserror($row)) { $query_data = $my_sql_str_limit; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6218", $query_data, $desc); return -6218; } while ($row->fetchinto($result)) { $contact_id_array[] = "'" . $result->contact_id . "'"; } $row->free(); $contact_id_str = legacy_implode(",", $contact_id_array); if (substr($org_id, 0, 15) == "00D500000007sJo") { $srm_prefix1 = "targetXtestPac__"; $obj_name = $this->srm_prefix . "Family_Relationship__c"; $sql_str = "Select Id," . $this->srm_prefix . "First_Name__c," . $this->srm_prefix . "Last_Name__c," . $this->srm_prefix . "Email__c," . $this->srm_prefix . "Contact__c" . " From $obj_name where " . $srm_prefix1 . "HasOptedOutOfEmail__c <> true and " . $srm_prefix1 . "HasEmailBounced__c <> true and " . $this->srm_prefix . "Relationship__c in ('Legal Guardian','Mother','Father') and " . $this->srm_prefix . "email__c != null and " . $this->srm_prefix . "Contact__c in ($contact_id_str)"; } else { $obj_name = $this->srm_prefix . "Family_Relationship__c"; $sql_str = "Select Id," . $this->srm_prefix . "First_Name__c," . $this->srm_prefix . "Last_Name__c," . $this->srm_prefix . "Email__c," . $this->srm_prefix . "Contact__c" . " From $obj_name where " . $this->srm_prefix . "HasOptedOutOfEmail__c <> true and " . $this->srm_prefix . "HasEmailBounced__c <> true and " . $this->srm_prefix . "Relationship__c in ('Legal Guardian','Mother','Father','Parent','Other') and " . $this->srm_prefix . "email__c != null and " . $this->srm_prefix . "Contact__c in ($contact_id_str)"; } unset($result); unset($records); $ii = 0; $this->logTime($sf, 'start getRelatedRecords loop get family relationship soql: ' . $n); $records = $sf->specialQuery1($sql_str); $this->logTime($sf, 'end getRelatedRecords loop get family relationship soql: ' . $n); $sf->error_log("API counter: SOQL " . $sql_str); $sf->error_log("API counter:" . $this->API_counter++ . " No of records fetched--->" . $sf->result_size); if ($records < 0) { $query_data = $sql_str; $desc = " Invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6222", $query_data, $desc); if ($records == -2) { return -2004; } else if ($records == -3) { return -2005; } return -6222; } $sf->error_log("getRelatedRecords->00 " . legacy_count($result) . "--->" . $result . "****" . legacy_count($records)); $this->logTime($sf, 'start getRelatedRecords loop special query2 soql: ' . $n); $sf->specialQuery2($records, $ii); $this->logTime($sf, 'end getRelatedRecords loop special query2 soql: ' . $n); $result = $sf->results_array; $sf->error_log("getRelatedRecords->0 " . legacy_count($result) . "--->" . $result); if ($result < 0) { $query_data = $sql_str; $desc = " no records from family relation "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6221", $query_data, $desc); return -6221; } if (legacy_count($result) < 1) { // if the query is to find only parents and no parents found if ($this->query_category == 2) { /* $sql_str = "DROP TABLE IF EXISTS $this->table_name_final"; $row = $dbconn_read->query($sql_str); $sql_str = "RENAME TABLE $chld_table TO $this->table_name_final"; $row = $dbconn_read->query($sql_str); */ } $sf->error_log("getRelatedRecords->000 "); continue; } $upd_val = 1; $sql_str_2 = $this->getNameStr($result, $obj_name, "C", $upd_val, "12", $sf); $sql_str_1 = "insert IGNORE into $chld_table(contact_id,`$field_ext$fname`,`$field_ext$lname`,`$email`,`ownerEmail`,`ownerFullName`,`relation_id`,`mergedIds`,`validConditions`) values"; $sql_str_3 = " ON DUPLICATE KEY UPDATE Email=$upd_val"; $sql_str = $sql_str_1 . $sql_str_2 . $sql_str_31; $sf->error_log("getRelatedRecords->1 " . $sql_str); $this->logTime($sf, 'start getRelatedRecords insert child query: ' . $n); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords insert child query: ' . $n); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6208", $query_data, $desc); return -6208; } } $this->logTime($sf, 'end getRelatedRecords get active contacts 400 loop'); $sf->error_log("getRelatedRecords->2 "); $this->logTime($sf, 'start getRelatedRecords 15 columns loop'); for ($i = 0; $i < 15; $i++) { if ($i == 1) { //continue; } $tableName_ = substr($this->table_name, 16); $fld = 'field_' . $i; $sql = "SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'express4sf_camp' AND TABLE_NAME = \"$tableName_\" AND COLUMN_NAME = \"$fld\""; $sf->error_log("getRelatedRecords **-->" . $sql); //echo $sql; exit; $this->logTime($sf, 'start getRelatedRecords 15 columns temp table query: ' . $i); $row = $dbconn_read->query($sql); $this->logTime($sf, 'end getRelatedRecords 15 columns temp table query: ' . $i); if ($row->numRows() > 0) { $sql_str = "update $chld_table as A ,$this->table_name as B set A.$fld = B.$fld where A.relation_id = B.contact_id and B.active =1"; $sf->error_log("getRelatedRecords -->" . $sql_str); $this->logTime($sf, 'start getRelatedRecords 15 columns update temp table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords 15 columns update temp table query'); } } $this->logTime($sf, 'end getRelatedRecords 15 columns loop'); $sf->error_log("getRelatedRecords->3 "); // update the from and reply owner vanity,email fields $sql_str = "update $chld_table as A ,$this->table_name as B set A.ownerEmail = B.ownerEmail,A.ownerFullName = B.ownerFullName where A.relation_id = B.contact_id and B.active =1"; $sf->error_log("getRelatedRecords updating owner email,vanity fields-->" . $sql_str); $this->logTime($sf, 'start getRelatedRecords update child table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords update child table query'); if ($this->query_category == 2) { $sql_str = "rename TABLE $this->table_name_final TO $this->table_name_final" . "_parent"; $this->logTime($sf, 'start getRelatedRecords rename final table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords rename final table query'); $sql_str = "RENAME TABLE $chld_table TO $this->table_name_final"; $sf->error_log("getRelatedRecords -->" . $sql_str); $this->logTime($sf, 'start getRelatedRecords rename child table query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords rename child table query'); } else if ($this->query_category == 3) { //$sql_str = "ALTER TABLE $chld_table DROP COLUMN `relation_id`"; $sql_str = "CREATE TABLE " . $this->table_name_final . "_parent" . " SELECT * FROM $this->table_name_final limit 0"; $sf->error_log("getRelatedRecords -->" . $sql_str); $this->logTime($sf, 'start getRelatedRecords create final table parent query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords create final table parent query'); $sql_str = "INSERT IGNORE INTO $this->table_name_final SELECT * FROM $chld_table"; $sf->error_log($sql_str); $this->logTime($sf, 'start getRelatedRecords insert final table from child query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getRelatedRecords insert final table from child query'); //$sql_str = "DROP TABLE $chld_table"; //$row = $dbconn_read->query($sql_str); } } // Get the records from Family relation table public function getRelatedRecords2($sf, $table_fields_array, $merge_fields) { $parent_table = $this->table_name_final . "_parent"; $chld_table = $this->table_name_final . "_chld"; $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); if (($this->query_category == 2 or $this->query_category == 3)) { // update merge fields in child table $merge_field_arr = @explode(",", $merge_fields); $this->logTime($sf, 'start getRelatedRecords2 merge field loop'); for ($i = 1; $i <= legacy_count($merge_field_arr); $i++) { $fld = ""; $arrMerField = @explode(":", $merge_field_arr[$i - 1]); $fld = $arrMerField[0]; if (legacy_count($arrMerField) < 2) {continue;} /* $sql_str = "update $parent_table as A ,$this->table_name_final as B set B.$fld = A.$fld where B.relation_id = A.relation_id and B.active =1"; $sf->error_log("getRelatedRecords2 -->".$sql_str); $row = $dbconn_read->query($sql_str); if (DB::iserror($row) ) { $query_data = $sql_str; $desc = " invalid query "; $this->query_log($dbconn_read,$org,$campaign,$bcast_id,"-7318",$query_data,$desc); return -7318; } */ $this->logTime($sf, 'start getRelatedRecords2 getMergefieldsForChldTbl: ' . $i); $ret_val = $this->getMergefieldsForChldTbl($sf, $dbconn_read, $this->table_name_final, $merge_field_arr[$i - 1]); $this->logTime($sf, 'end getRelatedRecords2 getMergefieldsForChldTbl: ' . $i); if ($ret_val < 0) { return $ret_val; } } $this->logTime($sf, 'end getRelatedRecords2 merge field loop'); /* for($i=0;$i < count($table_fields_array);$i++) { $fld = $table_fields_array[$i]; $sql_str = "update $parent_table as A ,$this->table_name_final as B set B.$fld = A.$fld where B.relation_id = A.relation_id and B.active =1"; $sf->error_log("getRelatedRecords2 -->".$sql_str); $row = $dbconn_read->query($sql_str); } */ //$sql_str = "DROP TABLE $chld_table"; //$row = $dbconn_read->query($sql_str); //$sql_str = "DROP TABLE $parent_table"; //$row = $dbconn_read->query($sql_str); //$sql_str = "ALTER TABLE $this->table_name_final DROP COLUMN `relation_id`"; //$row = $dbconn_read->query($sql_str); } } // This function get the contact Id for family relationship object public function getContactForFamilyRelation($sf, $contact_id_array) { $org_array = $sf->sf_sobject_structure($this->srm_prefix . "Family_Relationship__c"); $keyPrefix = $org_array->keyprefix; for ($i = 0; $i < legacy_count($contact_id_array); $i++) { if (substr($contact_id_array[$i], 0, 3) == $keyPrefix) { $family_id_array[] = $contact_id_array[$i]; } else { $contact_array[] = $contact_id_array[$i]; } } unset($contact_id_array); if (legacy_count($family_id_array) > 0) { $family_id_str = @legacy_implode(",", $family_id_array); $sql_str = " select " . $this->srm_prefix . "Contact__c from " . $this->srm_prefix . "Family_Relationship__c where Id in ($family_id_str)"; $records = $sf->specialQuery1($sql_str); $sf->error_log("API counter: SOQL " . $sql_str); $sf->error_log("API counter:" . $this->API_counter++ . " No of records fetched--->" . $sf->result_size); $sf->specialQuery2($records, 0); $result = $sf->results_array; for ($i = 0; $i < legacy_count($result); $i++) { $contact_array[] = $result[$i][$this->srm_prefix . 'Family_Relationship__c'][0][$this->srm_prefix . 'Contact__c']; } } return $contact_array; } public function logTime($sf, $message) { list($sec, $usec) = explode('.', microtime(true)); $time = date('H:i:s', $sec) . $usec; //$sf->error_log($SF->bcastID.':'.$time.' - '.$message); //Logging::info($sf->bcastID . ':' . $time . ' - ' . $message); syslog(LOG_WARNING, $sf->bcastID . ':' . $time . ' - ' . $message); } // This function handle the merge field for child tables( related records like family) public function getMergefieldsForChldTbl($sf, $dbconn_read, $chld_table, $merge_field) { $arr = @explode(":", $merge_field); $tbl_fld_nm = $arr[0]; $obj_fld_nm = $arr[1]; $arr1 = @explode(".", $obj_fld_nm); $obj_name = $arr1[0]; $field_name = $arr1[1]; // or strtolower($obj_fld_nm) == "contact.firstname" or strtolower($obj_fld_nm) == "contact.lastname" or strtolower($obj_fld_nm) == "contact.name" or strtolower($obj_fld_nm) == "contact.email") if (strtolower($obj_fld_nm) == "srmemail.firstname" or strtolower($obj_fld_nm) == "srmemail.lastname" or strtolower($obj_fld_nm) == "srmemail.email" or strtolower($obj_fld_nm) == "srmemail.fullname") { $this->logTime($sf, 'start getMergefieldsForChldTbl getMergefieldsForSpecialSRMEmailFields'); $ret_val = $this->getMergefieldsForSpecialSRMEmailFields($sf, $dbconn_read, $chld_table, $merge_field); $this->logTime($sf, 'end getMergefieldsForChldTbl getMergefieldsForSpecialSRMEmailFields'); if ($ret_val < 0) { return $ret_val; } return; } else if (strtolower($obj_fld_nm) == "contact.firstname" or strtolower($obj_fld_nm) == "contact.lastname" or strtolower($obj_fld_nm) == "contact.name" or strtolower($obj_fld_nm) == "contact.email") { $this->logTime($sf, 'start getMergefieldsForChldTbl getMergefieldValfromTempTable'); $ret_val = $this->getMergefieldValfromTempTable($sf, $dbconn_read, $chld_table, $merge_field); $this->logTime($sf, 'end getMergefieldsForChldTbl getMergefieldValfromTempTable'); if ($ret_val < 0) { return $ret_val; } return; } if ($obj_name != $this->srm_prefix . 'Family_Relationship__c') { return 0; } // get the prefix of family relation obj $this->logTime($sf, 'start getMergefieldsForChldTbl sf object prefix'); $familyPrefix = $sf->getObjectPrefix($this->srm_prefix . 'Family_Relationship__c'); $this->logTime($sf, 'end getMergefieldsForChldTbl sf object prefix'); $my_sql_str = "select contact_id from $chld_table where active= 1 and contact_id like \"$familyPrefix%\" "; $sf->error_log($sql_str); $this->logTime($sf, 'start getMergefieldsForChldTbl get active from child query'); $row = $dbconn_read->query($my_sql_str); $this->logTime($sf, 'end getMergefieldsForChldTbl get active from child query'); $no_of_contacts = $row->numRows(); $this->logTime($sf, 'start getMergefieldsForChldTbl get child active 400 loop'); for ($n = 0; $n < $no_of_contacts; $n = $n + 400) { if ($n >= $no_of_contacts) { continue; } unset($contact_id_array); $my_sql_str_limit = $my_sql_str . " ORDER BY contact_id ASC limit $n , 400"; $this->logTime($sf, 'start getMergefieldsForChldTbl get child active 400 limit query: ' . $n); $row = $dbconn_read->query($my_sql_str_limit); $this->logTime($sf, 'end getMergefieldsForChldTbl get child active 400 limit query: ' . $n); if (DB::iserror($row)) { $query_data = $my_sql_str_limit; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6318", $query_data, $desc); return -6318; } while ($row->fetchinto($result)) { $contact_id_array[] = "'" . $result->contact_id . "'"; } $row->free(); $family_id_str = legacy_implode(",", $contact_id_array); $obj_name = $this->srm_prefix . "Family_Relationship__c"; if (strtolower($field_name) == 'id') { $sql_str = "Select Id From $obj_name where " . "Id in ($family_id_str)"; } else { $sql_str = "Select Id," . $field_name . " From $obj_name where " . "Id in ($family_id_str)"; } unset($result); unset($records); $ii = 0; $this->logTime($sf, 'start getMergefieldsForChldTbl special family relationship soql: ' . $n); $records = $sf->specialQuery1($sql_str); $this->logTime($sf, 'end getMergefieldsForChldTbl special family relationship soql: ' . $n); $sf->error_log("API counter: SOQL " . $sql_str); $sf->error_log("API counter:" . $this->API_counter++ . " No of records fetched--->" . $sf->result_size); if ($records < 0) { $query_data = $sql_str; $desc = " Invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6322", $query_data, $desc); return -6322; } $this->logTime($sf, 'start getMergefieldsForChldTbl specialQuery2 soql: ' . $n); $sf->specialQuery2($records, $ii); $this->logTime($sf, 'end getMergefieldsForChldTbl specialQuery2 soql: ' . $n); $result = $sf->results_array; if (legacy_count($result) < 1) { $query_data = $sql_str; $desc = " no records from family relation "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6321", $query_data, $desc); return 0; } //print_r($result);exit; $this->logTime($sf, 'start getMergefieldsForChldTbl family relation loop: ' . $n); for ($j = 0; $j < legacy_count($result); $j++) { $contact_id = $result[$j][$obj_name][0]['Id']; $field_val = $result[$j][$obj_name][0][$field_name]; $sql_str = "update $chld_table set `$tbl_fld_nm` = REPLACE(REPLACE( \"" . addslashes($field_val) . "\", '\n', ''),'\r','') where contact_id = \"$contact_id\" "; //echo "----".$sql_str."***"; $sf->error_log($sql_str); $this->logTime($sf, 'start getMergefieldsForChldTbl update child table query: ' . $j); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getMergefieldsForChldTbl update child table query: ' . $j); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " Update local failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-7316", $query_data, $desc); return -7316; } } $this->logTime($sf, 'end getMergefieldsForChldTbl family relation loop'); } $this->logTime($sf, 'end getMergefieldsForChldTbl get child active 400 loop'); } // End of function // This function get merge field values for special merge fields ( common for student and parent) public function getMergefieldsForSpecialSRMEmailFields($sf, $dbconn_read, $table, $merge_field) { $arr = @explode(":", $merge_field); $tbl_fld_nm = $arr[0]; $obj_fld_nm = $arr[1]; if (strtolower($obj_fld_nm) == "srmemail.firstname" or strtolower($obj_fld_nm) == "contact.firstname" or strtolower($obj_fld_nm) == "lead.firstname") { $fld_name = "FirstName"; } else if (strtolower($obj_fld_nm) == "srmemail.lastname" or strtolower($obj_fld_nm) == "contact.lastname" or strtolower($obj_fld_nm) == "lead.lastname") { $fld_name = "LastName"; } else if (strtolower($obj_fld_nm) == "srmemail.email" or strtolower($obj_fld_nm) == "contact.email" or strtolower($obj_fld_nm) == "lead.email") { $fld_name = "Email"; } else if (strtolower($obj_fld_nm) == "srmemail.fullname" or strtolower($obj_fld_nm) == "contact.name" or strtolower($obj_fld_nm) == "lead.name") { $fld_name = "concat(FirstName,' ',LastName)"; } $sql_str = "update $table set $tbl_fld_nm = $fld_name where active = 1"; $sf->error_log($sql_str); $this->logTime($sf, 'start getMergefieldsForSpecialSRMEmailFields update merge field query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getMergefieldsForSpecialSRMEmailFields update merge field query'); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " Update special merge field failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6416", $query_data, $desc); return -6416; } } // This function get merge field values of student public function getMergefieldValfromTempTable($sf, $dbconn_read, $table, $merge_field) { $arr = @explode(":", $merge_field); $tbl_fld_nm = $arr[0]; $obj_fld_nm = $arr[1]; if (strtolower($obj_fld_nm) == "contact.firstname") { $fld_name = "B.FirstName"; } else if (strtolower($obj_fld_nm) == "contact.lastname") { $fld_name = "B.LastName"; } else if (strtolower($obj_fld_nm) == "contact.email") { $fld_name = "B.Email"; } else if (strtolower($obj_fld_nm) == "contact.name") { $fld_name = "concat(B.FirstName,' ',B.LastName)"; } $sql_str = "update $table as A,$this->table_name as B set A.$tbl_fld_nm = $fld_name where A.active = 1 and A.relation_id = B.contact_id"; $sf->error_log($sql_str); $this->logTime($sf, 'start getMergefieldValfromTempTable update merge field query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getMergefieldValfromTempTable update merge field query'); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " getMergefieldValfromTempTable failed "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6716", $query_data, $desc); return -6716; } } public function getline($fp, $delim) { $result = ""; while (!feof($fp)) { $tmp = fgetc($fp); if (($tmp == $delim or $tmp == "\r") and $prev == '"' and $secondPrev != "\\") { return $result; } $result .= $tmp; $prev = $tmp; $secondPrev = $tmp; } return $result; } // Function used to generate possible options for the report header first column public function getReportHeaderOptions($sf) { $reportHeaderOptionsArr = array(); $leadLabels = $sf->getObjectLabels('Lead'); if (!empty($leadLabels)) { $leadLabel = $leadLabels['label']; $leadIdLabelArr = $leadLabels['idField']; $reportHeaderOptionsArr = legacy_array_merge($reportHeaderOptionsArr, $leadIdLabelArr); } $contactLabels = $sf->getObjectLabels('Contact'); if (!empty($contactLabels)) { $contactLabel = $contactLabels['label']; $contactIdLabelArr = $contactLabels['idField']; $reportHeaderOptionsArr = legacy_array_merge($reportHeaderOptionsArr, $contactIdLabelArr); } // add combo lead/contact label edge case to id_label_arr if (!empty($leadLabels) && !empty($contactLabels)) { $idLeadContact = $leadLabel . '/' . $contactLabel . ' ID'; legacy_array_push($reportHeaderOptionsArr, $idLeadContact); $idRelatedRecord = 'Related Record ID'; legacy_array_push($reportHeaderOptionsArr, $idRelatedRecord); } return $reportHeaderOptionsArr; } // This function get records for reports public function getReportData($sf, $sessionID, $dbconn_read, $table, $reportID, $forValidationFlag, $bcastID, $newsGroupsId) { // This will happens only in the actual broadcast situation // check whether the report is deleted $sfCommon = new sf_common(); $org = $sf->user_info()->organizationId; $org = substr($org, 0, 15); // why are we checking the broadcast_id? // should this be reportID? if (strlen($bcastID) >= 15) { // check whether report got deleted $sql_str = "SELECT Id,IsDeleted,Name,Description FROM Report where Id = '" . $reportID . "'"; $this->logTime($sf, 'start getReportData - get report to check if deleted'); $retVal = $sf->sfSoqlQuery($sql_str); // $sf->error_log("getReport Data func " . $sql_str); if ($sf->results_array[0]['Report'][0]['IsDeleted'] == "true") { // You are trying to run a report that has been deleted $sf->error_log("Report has been deleted: " . $reportID); return -1200; } $this->logTime($sf, 'end getReportData - get report to check if deleted'); } $location = $sf->getSFLocation(); $reportURL = str_replace('https', 'ssl', $location); unset($this->reportHeaderRowandFirstDatarow); $reportDataFilePath = REPORT_DATA_FILE_PATH . "$reportID" . ".csv"; $this->logTime($sf, 'start getReportData open file: ' . $reportDataFilePath); $fpReportData = fopen($reportDataFilePath, "w+"); if (!$fpReportData) { // cannot open file for report data (out of storage) $sf->error_log("Report file cannot be opened"); return -1206; } $fp = fsockopen($reportURL, 443, $errno, $errstr, 30); $sf->error_log("getReport Data - build request for report " . $reportURL); if (!$fp) { // cannot build request return -1207; } $out = "GET /$reportID?export=1&enc=UTF-8&xf=csv&isdtp=p1 HTTP/1.0\r\n"; $sf->error_log("get ReportData - report request url: " . $out); preg_match("/([A-Za-z0-9.-]{3,50}.salesforce.com)/i", $location, $urlarray); $sf->error_log("getReport Data func Location :" . $location . " " . $urlarray[1]); $out .= "Host: " . $urlarray[1] . "\r\n"; $out .= "Cookie: sid=$sessionID.\r\n"; $out .= "Connection: Close\r\n\r\n"; $reportRanFlag = 0; $k = 0; $reportFirstRow = ''; $is_valid_report = false; $is_past_headers_line_break = false; $reportHeaderOptions = $this->getReportHeaderOptions($sf); $sf->error_log("getReportData - reportHeaderOptions size: " . legacy_count($reportHeaderOptions)); fwrite($fp, $out); $this->logTime($sf, 'getReportData - start file loop'); while (!feof($fp)) { // this fails when there in new line char in a row //$currentRow = fgets($fp); $currentRow = $this->getline($fp, "\n"); // Check for response headers line break. If present, remove it and extract the data after the line break. if (strpos($currentRow, "\r\n\r\n")) { $is_past_headers_line_break = true; $currentRow_split = explode("\r\n\r\n", $currentRow, 2); if (legacy_count($currentRow_split) > 1) { $currentRow = $currentRow_split[1]; } } // Skip lines that are part of response headers if (!$is_past_headers_line_break) { continue; } //$sf->error_log("getReport Data func :::".$currentRow ); $currentRow = str_replace("\n", "", $currentRow); $currentRow = $currentRow . "\n"; //$sf->error_log("**Row**:".$currentRow."--->".$k); //$currentRow1 = fgetcsv($fp); //$currentRow = '"'.implode('","',$currentRow1).'"'.'/n'; // echo "-*-".$currentRow."-*->>".$k."
"; // If $k == 0, this is the header row with field names. Validate that the first column in the header row // is either 'Contact ID' or 'Lead ID' if ($k == 0) { $row_split = str_getcsv($currentRow, ",", '"', "\\"); $first_column_label = $row_split[0]; if (legacy_in_array($first_column_label, $reportHeaderOptions)) { // Kept for future debugging purposes // $sf->error_log('*** Report: First column label match: '.$first_column_label); $is_valid_report = true; } else { break; } } if (strlen($currentRow) >= 15 and strlen($reportFirstRow) < 1) { //echo substr($currentRow,1,3)."
"; if (SF_CONTACT_ID_BGN == substr($currentRow, 1, 3) or SF_LEAD_ID_BGN == substr($currentRow, 1, 3)) { if (SF_CONTACT_ID_BGN == substr($currentRow, 1, 3)) { $this->report_query_type = "C"; } else { $this->report_query_type = "L"; $this->emailBounceField = $this->eml_prefix . "HasEmailBounced__c"; $this->emailField = 'Email'; } $sf->error_log("getReportData - report type: " . $this->report_query_type); //echo substr($currentRow,1,3)."
"; $reportFirstRow = $previousRow; // return the header row and first data row $this->reportHeaderRowandFirstDatarow['header'] = @explode(",", $reportFirstRow); //$this->reportHeaderRowandFirstDatarow[firstRow] = @explode(",",$currentRow); $this->reportHeaderRowandFirstDatarow['firstRow'] = str_getcsv($currentRow, ",", '"', "\\"); $this->logTime($sf, 'start getReportData header row loop'); for ($ii = 0; $ii < legacy_count($this->reportHeaderRowandFirstDatarow['firstRow']); $ii++) { $this->reportHeaderRowandFirstDatarow['firstRow'][$ii] = '"' . $this->reportHeaderRowandFirstDatarow['firstRow'][$ii] . '"'; } $this->logTime($sf, 'end getReportData header row loop'); // This is for validation from the page.. if ($forValidationFlag == 1) { fclose($fp); fclose($fpReportData); return $reportFirstRow . "---" . $currentRow; break; } } } $previousRow = $currentRow; // and substr($currentRow,0,1) == '"' // and substr($currentRow,strlen($currentRow)-1,1) == '"' if (strlen($reportFirstRow) > 0 and substr($currentRow, 0, 1) == '"' and substr($currentRow, strlen($currentRow) - 2, 1) == substr($currentRow, 0, 1)) { //echo "-11-".$currentRow."-11-"."
"; if (strlen($currentRow) >= 15) { //echo "-22-".$currentRow."-22-"."
"; if (SF_CONTACT_ID_BGN == substr($currentRow, 1, 3) or SF_LEAD_ID_BGN == substr($currentRow, 1, 3)) { //echo "-33-".$currentRow."-33-"."
"; $conLeadId15 = substr($currentRow, 1, 15); $conLeadId18 = $sfCommon->sf15to18($conLeadId15); $currentRow = str_replace($conLeadId15, $conLeadId18, $currentRow); $numBiteWrites = fwrite($fpReportData, $currentRow); if (!$numBiteWrites) { return -1206; } } } else { //echo "--^^^".$currentRow."^^^".strlen($currentRow)."---".$k."
"; continue; } } if (strpos($currentRow, ': close') > 0) { $reportRanFlag = 1; //$firstDataRowNo = $k+3; } $k++; } $this->logTime($sf, 'end getReportData file loop'); fclose($fp); fclose($fpReportData); $this->logTime($sf, 'end getReportData close file'); /* if ($info['timed_out']) { // echo "Connection Timed Out!"; return -1205; } */ if (!$is_valid_report) { return -4200; } // validate the first row. $firsrRowFirstCol = $this->reportHeaderRowandFirstDatarow['firstRow'][0]; $hr = @legacy_implode(",", $this->reportHeaderRowandFirstDatarow['header']); $fdr = @legacy_implode(",", $this->reportHeaderRowandFirstDatarow['firstRow']); $hr_length = legacy_count($this->reportHeaderRowandFirstDatarow['header']); $fdr_length = legacy_count($this->reportHeaderRowandFirstDatarow['firstRow']); $sf->error_log("Header Row ::" . $hr); $sf->error_log("First data Row ::" . $fdr); if ($hr_length == 0 or $fdr_length == 0) { return 0; } $reportFirstArray = @explode(",", $reportFirstRow); $fldStr = 'contact_id'; $previousColumn = " after contact_id"; unset($fldArray); $this->logTime($sf, 'start getReportData first row loop'); for ($i = 0; $i < legacy_count($reportFirstArray); $i++) { $k = $i + 1; if ($i == 0) { $fldArray[] = $fldStr; $create_table_str_body .= "field_" . $k . " text"; $this->first_row[$k] = "field_" . $k; } else { $fldArray[] = "field_" . $k; $create_table_str_body .= ",field_" . $k . " text"; $this->first_row[$k] = "field_" . $k; $fldStr .= ",field_" . $k; $sql_str = "ALTER TABLE $this->table_name ADD " . "field_" . $k . " text $previousColumn"; $previousColumn = " after " . "field_" . $k; $this->logTime($sf, 'start getReportData add field query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getReportData add field query'); } } $this->logTime($sf, 'end getReportData first row loop'); // Make contact id field case sensitive // Using utf8_bin for case sensitivity: https://stackoverflow.com/a/4558736 $sql_str = "alter table $this->table_name modify contact_id varchar(21) COLLATE utf8_bin"; $this->logTime($sf, 'start getReportData add contact_id query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getReportData add contact_id query'); if (!DB::iserror($row)) { $tblFields = @legacy_implode(",", $fldArray); //$sql_str = "LOAD DATA LOCAL INFILE '$reportDataFilePath' INTO TABLE $this->table_name FIELDS TERMINATED BY ',' optionally enclosed by '\"' escaped by '' IGNORE 0 LINES"; $sql_str = "LOAD DATA LOCAL INFILE '$reportDataFilePath' INTO TABLE $this->table_name FIELDS TERMINATED BY ',' optionally enclosed by '\"' escaped by '' ($tblFields)"; $sf->error_log("Loading Report Data : " . $sql_str); $this->logTime($sf, 'start getReportData load data query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getReportData load data query'); //echo $sql_str; //echo "here";exit; if (DB::iserror($row)) { echo $row->getMessage(); print_r($row); } $sql_str = "delete from $this->table_name where length(contact_id) < 15 "; $this->logTime($sf, 'start getReportData delete small contact_id query'); $row = $dbconn_read->query($sql_str); $this->logTime($sf, 'end getReportData delete small contact_id query'); if (DB::iserror($row)) { echo $row->getMessage(); print_r($row); } } else { return -1300; } if ($bcastID == "" or strlen($bcastID) < 15) { $this->logTime($sf, 'start getReportData isReportContentValid'); $isReportContentValid = $this->isReportContentValid($sf, $dbconn_read, $reportFirstRow, $newsGroupsId); $this->logTime($sf, 'end getReportData isReportContentValid'); if ($isReportContentValid !== false) { return $isReportContentValid; } } // delete the rows which don't have Email address or is Optedout or Bounceback ( when it is called from the screen) // update the email, first name,last name field and delete the optout bounceback and records with no email( for actual broadcast) $my_sql_str = "select contact_id from $this->table_name"; $this->logTime($sf, 'start getReportData get Contacts query'); $row = $dbconn_read->query($my_sql_str); $this->logTime($sf, 'end getReportData get Contacts query'); $noOfContacts = $row->numRows(); $this->logTime($sf, 'start getReportData get Contacts 400 loop'); for ($n = 0; $n < $noOfContacts; $n = $n + 400) { if ($n >= $noOfContacts) { continue; } unset($contact_id_array); $my_sql_str_limit = $my_sql_str . " ORDER BY contact_id ASC limit $n , 400"; $sf->error_log("****:" . $my_sql_str_limit); $this->logTime($sf, 'start getReportData get Contacts query limit 400: ' . $n); $row = $dbconn_read->query($my_sql_str_limit); $this->logTime($sf, 'end getReportData get Contacts query limit 400: ' . $n); if (DB::iserror($row)) { $query_data = $my_sql_str_limit; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-1018", $query_data, $desc); return -1018; } while ($row->fetchinto($result)) { $contact_id_array[] = "'" . $result->contact_id . "'"; } $row->free(); $contact_id_str = legacy_implode(",", $contact_id_array); $firstId = $contact_id_array[0]; $newsGroupStr = ''; if (strlen($newsGroupsId) > 0) { $newsGroupStr = " and (" . $this->eml_prefix . "emailGroups__c = NULL or " . $this->eml_prefix . "emailGroups__c includes ($newsGroupsId))"; } if ($this->fromOwnerEmail == 'true' or $this->replyOwnerEmail == 'true') { $ownerEmail = ",owner.email,owner.name"; } //temp. commented // $validConditions =",validConditions__c"; if (SF_CONTACT_ID_BGN == substr($firstId, 1, 3)) { $objName = "Contact"; $contactSQLStr = "Select Id,$this->emailField,FirstName,LastName," . $this->TwomergedIdFields . $ownerEmail . " From $objName where (HasOptedOutOfEmail <> true and " . $this->emailBounceField . "<> true and ( $this->emailField != null)) and Id in ($contact_id_str) $newsGroupStr"; } else if (SF_LEAD_ID_BGN == substr($firstId, 1, 3)) { $objName = "Lead"; $contactSQLStr = "Select Id,Email,FirstName,LastName," . $this->TwomergedIdFields . $ownerEmail . " from Lead where (HasOptedOutOfEmail <> true and " . $this->eml_prefix . "HasEmailBounced__c <> true and ( email != null)) and Id in ($contact_id_str) "; } unset($contact_id_array); unset($result); unset($records); $this->logTime($sf, 'start getReportData get object soql: ' . $n); $ret_val = $sf->sfSoqlQuery($contactSQLStr); $this->logTime($sf, 'end getReportData get object soql: ' . $n); $sf->error_log("API counter: SOQL " . $contactSQLStr); $sf->error_log("API counter:" . $this->API_counter++ . " No of records fetched--->" . $sf->result_size); $result = $sf->results_array; if ($ret_val < 0) { $query_data = $contactSQLStr; $desc = " Invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-7022", $query_data, $desc); if ($ret_val == -2) { return -2004; } else if ($ret_val == -3) { return -2005; } return -7022; } unset($contactIdArray); //echo $contactSQLStr; //print_r($result); //exit; $sf->error_log("Count -->" . legacy_count($result)); $this->logTime($sf, 'start getReportData get object results loop: ' . $n); for ($j = 0; $j < legacy_count($result); $j++) { $contact_id = substr($result[$j][$objName][0]['Id'], 0, 15); /* if(strlen($bcastID) < 15) { $contactIdArray[] = "'".substr($result[$j][$objName][0][Id],0,15)."'"; } else { */ $fname = $result[$j][$objName][0]['FirstName']; $lname = $result[$j][$objName][0]['LastName']; $email = $result[$j][$objName][0][$this->emailField]; $iD18Char = $result[$j][$objName][0]['Id']; $ownerEmailAddress = $result[$j]['User'][0]['Email']; $ownerFullName = $result[$j]['User'][0]['Name']; $mergedRecordIds = $result[$j][$objName][0][$this->eml_prefix . "mergedRecordIds__c"]; if (strlen($result[$j][$parentObj][0][$this->basePKG_prefix . "Merged_Record_Ids__c"]) > 15) { if (strlen($mergedRecordIds) > 10) { $mergedRecordIds .= "," . $result[$j][$parentObj][0][$this->basePKG_prefix . "Merged_Record_Ids__c"]; } else { $mergedRecordIds = $result[$j][$parentObj][0][$this->basePKG_prefix . "Merged_Record_Ids__c"]; } } $validConditions = $result[$j][$obj][0][$this->eml_prefix . 'validConditions__c']; if (strlen($ownerEmailAddress) < 5) { $ownerEmailAddress = $result[$j]['Name'][0]['Email']; } if (strlen($ownerFullName) < 5) { $ownerFullName = $result[$j]['Name'][0]['Name']; } if ($this->fromOwnerEmail == 'true' or $this->replyOwnerEmail == 'true') { $ownerEmailUpdateStr = ",ownerEmail =\"$ownerEmailAddress\",ownerFullName=\"$ownerFullName\" "; } $validConditions = ",validConditions =\"$validConditions\""; // contact_id = \"$iD18Char\", $sql_str = "update $this->table_name set FirstName = \"" . addslashes($fname) . "\",LastName = \"" . addslashes($lname) . "\",Email =\"$email\" ,active =1,field_0 =1,mergedIds =\"$mergedRecordIds\" $ownerEmailUpdateStr $validConditions where contact_id =\"$iD18Char\" "; //$sf->error_log($sql_str); $row = $dbconn_read->query($sql_str); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-1008", $query_data, $desc); return -1008; } // } } $this->logTime($sf, 'end getReportData get object results loop'); } $this->logTime($sf, 'end getReportData get Contacts 400 loop'); $deleteContactSQLstr = "delete from $this->table_name where length(email) < 5 "; //echo "--".$deleteContactSQLstr ."::::"; $sf->error_log($deleteContactSQLstr); $this->logTime($sf, 'start getReportData delete small emails query'); $row = $dbconn_read->query($deleteContactSQLstr); $this->logTime($sf, 'end getReportData delete small emails query'); if (DB::iserror($row)) { $query_data = $deleteContactSQLstr; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-3018", $query_data, $desc); return -3018; } unset($records); unset($result); unset($sql_str); unset($contactSQLStr); unset($contactIdArray); return $noOfContacts; } // this function get records from report temp. table public function getAdditionalDataForReportRecords($reportTempTable, $dbconn_read, $reportFirstRow, $mySqlStr) { // get data from SF $row = $dbconn_read->query($mySqlStr); $sf->error_log($mySqlStr); if (DB::iserror($row)) { $query_data = $my_sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-7017", $query_data, $desc); return -7017; } $noOfContacts = $row->numRows(); $fname = "FirstName"; $lname = "LastName"; $email = "Email"; $field_ext = ""; for ($n = 0; $n < $noOfContacts; $n = $n + 400) { if ($n >= $noOfContacts) { continue; } unset($contact_id_array); $my_sql_str_limit = $my_sql_str . " ORDER BY contact_id ASC limit $n , 400"; //echo $sql_str; $sf->error_log($sql_str); $row = $dbconn_read->query($my_sql_str_limit); if (DB::iserror($row)) { $query_data = $my_sql_str_limit; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6018", $query_data, $desc); return -6018; } while ($row->fetchinto($result)) { if (strlen($result->ContactID) == 15) { $contact_id_array[] = "'" . $result->ContactID . "'"; } } $row->free(); $contact_id_str = legacy_implode(",", $contact_id_array); if ($query_type == "R") { $obj_name = "Contact"; $sql_str = "Select Id,FirstName,LastName,$this->emailField From $obj_name where HasOptedOutOfEmail <> true and " . $this->emailBounceField . " <> true and $this->emailField != null and Id in ($contact_id_str)"; } else if ($query_type == "L") { $sql_str = "Select Id,FirstName,LastName,Email from Lead where HasOptedOutOfEmail <> true and " . $this->eml_prefix . "HasEmailBounced__c <> true and email != null and Id in ($contact_id_str) "; $obj_name = "Lead"; } unset($result); unset($records); $ii = 0; $records = $sf->specialQuery1($sql_str); $sf->error_log("API counter: SOQL " . $sql_str); $sf->error_log("API counter:" . $this->API_counter++ . " No of records fetched--->" . $sf->result_size); if ($records < 0) { $query_data = $sql_str; $desc = " Invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-7023", $query_data, $desc); if ($records == -2) { return -2004; } else if ($records == -3) { return -2005; } return -7023; } $sf->specialQuery2($records, $ii); $result = $sf->results_array; if (legacy_count($result) < 1) { $query_data = $sql_str; $desc = " no result set "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-7021", $query_data, $desc); return 0; } /*$upd_val =1; $sql_str_2 = $this->getNameStr($result,$obj_name,$query_type,$upd_val,10,$sf); $sql_str_1 ="insert IGNORE into $this->table_name(contact_id,`$field_ext$fname`,`$field_ext$lname`,`$email`,field_$fieldNo) values"; $sql_str_3 =" ON DUPLICATE KEY UPDATE field_$fieldNo=$upd_val"; */ for ($j = 0; $j < legacy_count($result); $j++) { if ($obj_name == "Contact" or $obj_name == "Lead") { $contact_id = $result[$j][$obj_name][0]['Id']; } $sql_str = "update $this->table_name set FirstName = ,LastName = ,Email = ,active =1 where contact_id =$contact_id"; //$sf->error_log($sql_str); $row = $dbconn_read->query($sql_str); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6008", $query_data, $desc); return -6008; } } } unset($records); unset($result); unset($sql_str); } public function getReportFieldName($Id, $sf) { // look at the first data row $row = $this->reportHeaderRowandFirstDatarow['firstRow']; // print_r($row); for ($i = 0; $i <= legacy_count($row); $i++) { $j = $i + 1; $col = $row[$i]; $col = str_replace('"', '', $col); if (strlen($col) < 15 or strlen($col) > 18) { continue; } //echo substr($col,0,3)."----".substr($Id,0,3)."
"; if (substr($col, 0, 3) == substr($Id, 0, 3)) { if ($i == 0) { return 'contact_id'; } return 'field_' . $j; } } // if by some reason the merge id is null for a certain mrge field , look at the table $fldStr = @legacy_implode(",", $this->first_row); $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); if (stripos($fldStr, "ield_1,") > 0) { $fldStr = str_replace("field_1,", "contact_id,", $fldStr); } else { $fldStr = str_replace("field_1", "contact_id", $fldStr); } $sql_str = "select $fldStr from $this->table_name "; $sf->error_log("In getReportFieldName func: " . $sql_str); $row = $dbconn_read->query($sql_str); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6019", $query_data, $desc); return -6019; } while ($row->fetchinto($result)) { for ($i = 1; $i <= legacy_count($this->first_row); $i++) { if ($i == 1) {$fldname = "contact_id";} else { $fldname = "field_" . $i;} if (substr($result->$fldname, 0, 3) == substr($Id, 0, 3)) { return $fldname; } } } $row->free(); return 0; } // Get the merged Ids public function getMergedIds($sf, $query_type) { if ($this->report_query_type == "C" or $query_type == "C") { $obj_name = "Contact"; $sql_str = "Select Id," . $this->eml_prefix . "mergedRecordIds__c From $obj_name where " . $this->eml_prefix . "mergedRecordIds__c like '%'"; } else if ($this->report_query_type == "L" or $query_type == "L") { $sql_str = "Select Id," . $this->eml_prefix . "mergedRecordIds__c from Lead where " . $this->eml_prefix . "mergedRecordIds__c like '%'"; $obj_name = "Lead"; } $this->getMergedIdsSf($sf, $query_type, $sql_str, $obj_name); } // Get the merged Ids from sales force public function getMergedIdsSf($sf, $query_type, $sql_str, $obj_name) { $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); unset($result); unset($records); $ii = 0; $records = $sf->specialQuery1($sql_str); $sf->error_log("API counter: SOQL " . $sql_str); $sf->error_log("API initial Count:" . $this->API_counter++ . " No of records fetched--->" . $sf->result_size); $sf->error_log(" getMergedIdsSf " . $sql_str . " SIZE---->" . $sf->result_size); if ($records < 0) { $query_data = $sql_str; $desc = " in FUN. getMergedIdsSf "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-7122", $query_data, $desc); if ($records == -2) { return -2004; } else if ($records == -3) { return -2005; } return -7122; } $queryLocator = $sf->queryLocator; $actual_size = $sf->result_size; $total_itr = ceil($actual_size / 2000); $ii = 0; $k = 2000; $j = 0; $upd_val = 1; do { $sf->specialQuery2($records, $ii); $result = $sf->results_array; $sf->error_log("API counter:" . $this->API_counter++ . " No of records fetched--->" . $sf->result_size . " loop size:" . legacy_count($result)); if (legacy_count($result) < 0) { return -5021; } for ($i = 0; $i < legacy_count($result); $i++) { if ($obj_name == "Contact" or $obj_name == "Lead") { $contact_id = $result[$i][$obj_name][0]['Id']; $mergedId = $result[$i][$obj_name][0][$this->eml_prefix . "mergedRecordIds__c"]; } $sql_str = "update $this->table_name_final set mergedIds = \"$mergedId\" where contact_id =\"$contact_id\""; $sf->error_log(" getMergedIdsSf " . $sql_str); $row = $dbconn_read->query($sql_str); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6108", $query_data, $desc); return -6108; } } if ($actual_size > 2000 and $total_itr > $j + 1) { $response = $sf->mySforceConnection->queryMore("$queryLocator"); $sf->error_log("Q locator:" . $queryLocator . " size fetched--->" . legacy_count($response->records)); $queryLocator = $response->queryLocator; $records = $response->records; if (legacy_count($response->records) >= 2000) { $k = $k + 2000; } else { $k = $k + legacy_count($response->records); } } $j++; } while ($total_itr > $j); unset($sql_str); unset($result); unset($records); } // check merged fields are avilable public function validateMergedIds($sf, $campaign_id) { $db_conn_obj = new Db_Connect($dbconn_read, $dbconn_write); $sql_str = " select contact_id,mergedIds from $this->table_name_final where length(mergedIds) >10"; $sf->error_log("In validateMergedIds 1" . $sql_str); $row = $dbconn_read->query($sql_str); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " getMergedIdsSf"; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6177", $query_data, $desc); return -6177; } while ($row->fetchinto($result)) { $retVal = $this->deleteMergedIds($sf, $campaign_id, $result->contact_id, $result->mergedIds); if ($retVal < 0) { return -6178; } } $row->free(); } public function deleteMergedIds($sf, $campaign_id, $contact_id, $mergedIds) { if (strpos($mergedIds, ",") > 0) { $merArray = @explode(",", $mergedIds); for ($i = 0; $i < legacy_count($merArray); $i++) { $merIdArray[] = "\"" . $merArray[$i] . "\""; } $idStr = @legacy_implode(",", $merIdArray); unset($merIdArray); unset($merArray); } else { $idStr = "\"" . $mergedIds . "\""; } $client_id_row = $this->dbconn_read_tx->query( "SELECT client_id FROM broadcast_queue WHERE campaign_id = '$campaign_id' LIMIT 1"); $client_id_row->fetchinto($client_id_result); $client_id = $client_id_row->client_id; $sql_str = "select * from activity.sent_log_$client_id where campaign_id = \"$campaign_id\" and recipient_id in ($idStr)"; $sf->error_log(" in deleteMergedIds " . $sql_str); $row = $this->dbconn_read_tx->query($sql_str); if (DB::iserror($row)) { $query_data = $sql_str; $desc = " invalid query "; $this->query_log($dbconn_read, $org, $campaign, $bcast_id, "-6208", $query_data, $desc); return -6208; } if ($row->numRows() > 0) { $sql_str = " delete from $this->table_name_final where contact_id = \"$contact_id\""; $sf->error_log("In validateMergedIds 1" . $sql_str); $row = $dbconn_read->query($sql_str); } } /* Select c.Owner.Name, c.Owner.FirstName, c.OwnerId, c.Id, c.Email, c.Account.Fax, c.AccountId, (Select TargetX_SRMb__Application__c, TargetX_SRMb__Phone__c From TargetX_SRMb__Recommendation__r), (Select TargetX_SRMb__SRM_ETL_ID__c From TargetX_SRMb__Essays__r) ,(Select TargetXDev__Last_Name__c,TargetXDev__First_Name__c,TargetXDev__Gender__c,TargetXDev__Living__c From TargetXDev__Family_Relationship__r) From Contact c http://192.168.5.30/~mathews/suite4sf-git/email/php/run_campaign.php?session_id=00D500000007sJo!AQYAQO97WdUuLR_XQIrVDz34QvV1UKwvWSOsetYHpkf3EzT319pnbcwnqvL.RaOT6pAWwQZ76Qj0wCt08406FelVR8IMt6oA&broadcast_id=a025000000NPySFAA1&merge_fields=M3:Contact.MailingCity,M2:Contact.FirstName,M1:Contact.Fax&endpointURL=https://na3-api.salesforce.com/services/Soap/u/20.0/00D500000007sJo http://192.168.5.30/~mathews/suite4sf-git/email/php/run_campaign.php?session_id=00D500000007sJo!AQYAQA65CLb.Qo3A9PYOcVb2YkiMQr5YTDQV5CKxVadAlavVk1U8SPrx0KrqjQUhkUD5dmG4ODBoAtb6yL_oORNZjwGMMzcX&broadcast_id=a025000000NY87MAAT&merge_fields=M2:TargetXDev__Application__c.TargetXDev__Start_Term_Year__c,M3:Contact.Name,M1:TargetXDev__Application__c.TargetXDev__Start_Term__c&endpointURL=https://na3-api.salesforce.com/services/Soap/u/20.0/00D500000007sJo http://192.168.5.30/~mathews/suite4sf-git/email/php/run_campaign.php?session_id=00D500000007sJo!AQYAQCxQ4vZ8dpD3zl1t3t.24rni4kOairjoBhEr19_AtS8CEspCo8HD0wHv97tH_lILUCz49JNsJTa6gxRmeY8j7PXApX5S&broadcast_id=a025000000MyCFn&merge_fields=M3:Contact.MailingCity,M2:Contact.FirstName,M1:Contact.Fax&endpointURL=https://na3-api.salesforce.com/services/Soap/u/20.0/00D500000007sJo */ public function firstRowAsArray($firstRow) { /* *Originally written by Brandon Padula on 11/6/15 */ $firstRow = substr($firstRow, strpos($firstRow, '"')); //Finds the first occurance of a double quote in the header row that was sent in as text. $firstRowArray = explode(",", $firstRow); //separates the comma separated string into an array $firstRowArray = str_replace('"', '', $firstRowArray); //removes the double quotes (which happen to be before and after each element in the array) $firstRowArray = legacy_array_map('trim', $firstRowArray); //removes trailing and leading whitespace return $firstRowArray; } public function isReportContentValid($sf, $dbconn_read, $firstRow, $newsGroupsId) { /* *Originally written by Brandon Padula on 11/6/15 */ $firstRowArray = $this->firstRowAsArray($firstRow); //gets first row, so we can figure out what field_2, field_3, etc. mean //array that will have the indices of the associated values $index_of = array(); $firsrRowFirstCol = $this->reportHeaderRowandFirstDatarow['firstRow'][0]; if (SF_CONTACT_ID_BGN == substr($firsrRowFirstCol, 1, 3)) { $fldArray[0] = "Id"; $fldArray[1] = "Email"; $fldArray[2] = "HasOptedOutOfEmail"; $fldArray[3] = $this->eml_prefix . "HasEmailBounced__c"; $fldArray[4] = $this->eml_prefix . "AltEmail__c"; $fldArray[5] = $this->eml_prefix . "HasAltEmailBounced__c"; $fldArray[6] = $this->eml_prefix . "emailGroups__c"; $obj = 'contact'; } else if (SF_LEAD_ID_BGN == substr($firsrRowFirstCol, 1, 3)) { $fldArray[0] = "Id"; $fldArray[1] = "Email"; $fldArray[2] = "HasOptedOutOfEmail"; $fldArray[3] = $this->eml_prefix . "HasEmailBounced__c"; $obj = 'lead'; } if (legacy_count($fldArray) > 0) { $labelsArr = $sf->sf_getlables($obj, $fldArray); } if ($obj == 'lead') { $index_of['Lead ID'] = legacy_array_search($labelsArr['Lead ID'], $firstRowArray); $index_of['Email'] = legacy_array_search($labelsArr['Email'], $firstRowArray); $index_of['Bounced'] = legacy_array_search($labelsArr['Bounced'], $firstRowArray); $index_of['Email Opt Out'] = legacy_array_search($labelsArr['Email Opt Out'], $firstRowArray); //if any of those indices are false, return false if (legacy_in_array(false, $index_of, true)) { return false; } //get indicies, which becomes field name in MYSQL $field_email = 'field_' . intval($index_of['Email'] + 1); $field_bounced = 'field_' . intval($index_of['Bounced'] + 1); $field_email_opt_out = 'field_' . intval($index_of['Email Opt Out'] + 1); } else if ($obj == 'contact') { $index_of['Contact ID'] = legacy_array_search($labelsArr['Contact ID'], $firstRowArray); $index_of['Email'] = legacy_array_search($labelsArr['Email'], $firstRowArray); $index_of['Bounced'] = legacy_array_search($labelsArr['Bounced'], $firstRowArray); $index_of['Email Opt Out'] = legacy_array_search($labelsArr['Email Opt Out'], $firstRowArray); if ($this->altEmail == true) { $index_of['Alt Email'] = legacy_array_search($labelsArr['Alt Email'], $firstRowArray); $index_of['Has Alt Email Bounced'] = legacy_array_search($labelsArr['Has Alt Email Bounced'], $firstRowArray); } //echo '--'.$newsGroupsId; if (strlen($newsGroupsId) > 3) { $index_of['emailGroups'] = legacy_array_search($labelsArr['emailGroups'], $firstRowArray); } //if any of those indices are false, return false if (legacy_in_array(false, $index_of, true)) { return false; } //get indicies, which becomes field name in MYSQL $field_email = 'field_' . intval($index_of['Email'] + 1); $field_bounced = 'field_' . intval($index_of['Bounced'] + 1); $field_email_opt_out = 'field_' . intval($index_of['Email Opt Out'] + 1); $field_alt_email = 'field_' . intval($index_of['Alt Email'] + 1); $field_has_alt_email_bounced = 'field_' . intval($index_of['Has Alt Email Bounced'] + 1); if (legacy_array_search($labelsArr['emailGroups'], $firstRowArray) and strlen($newsGroupsId) > 3) { $index_of['emailGroups'] = legacy_array_search($labelsArr['emailGroups'], $firstRowArray); $field_emailGroups = 'field_' . intval($index_of['emailGroups'] + 1); $newsGroup = str_replace("'", '', $newsGroupsId); //removes the double quotes (which happen to be before and after each element in the array) $newsGroup = str_replace(' ', '', $newsGroup); //removes trailing and leading whitespace if (strpos($newsGroup, ",") > 0) { $newsGroupArr = explode(",", $newsGroup); } else { $newsGroupArr[0] = $newsGroup; } //replace ; and space in email group $sqlStr = "update $this->table_name set $field_emailGroups = REPLACE($field_emailGroups,'; ',',')"; $dbconn_read->query($sqlStr); for ($i = 0; $i < legacy_count($newsGroupArr); $i++) { $grp = "'" . $newsGroupArr[$i] . "'"; $whereGrpArr[$i] = " find_in_set($grp,$field_emailGroups) "; } if (legacy_count($whereGrpArr) > 0) { $grpStr = @legacy_implode(" or ", $whereGrpArr); } // construct the email group where clause $whereGrpStr = "or if(isnull(field_2) or $field_emailGroups = '',1,(" . $grpStr . ")) =0"; } } else { return false; } if ($obj == 'contact' && $this->altEmail == true) { //if we're doing alternate emails on a contact (skip to next if we're doing leads - if $obj=='contact' is false) $dbconn_read->query("DELETE FROM $this->table_name WHERE $field_email_opt_out='1' OR $field_alt_email='' OR $field_has_alt_email_bounced='1' $whereGrpStr"); $dbconn_read->query("UPDATE $this->table_name SET Email=$field_alt_email"); } else { $sql = " DELETE FROM $this->table_name WHERE $field_email_opt_out='1' OR $field_email='' OR $field_bounced='1' $whereGrpStr"; //$sql = " update $this->table_name set field_6 =' deleting' WHERE $field_email_opt_out='1' OR $field_email='' OR $field_bounced='1' $whereGrpStr"; $dbconn_read->query($sql); $dbconn_read->query("UPDATE $this->table_name SET Email=$field_email"); } $row = $dbconn_read->query("SELECT * FROM $this->table_name where active = 1"); return $row->numRows(); } //end function sortValidReportData public function checkFieldPermissions($org, $query_type, $para_array, $sf, $bcastId, $dbconn_read) { $sf->error_log("Begin checkFieldPermissions -->" . legacy_count($para_array)); for ($i = 0; $i < legacy_count($para_array); $i++) { unset($my_row); $my_row = explode("_-", $para_array[$i]); if ($my_row[0] == "Select One" or $my_row[1] == "Select One" or trim($my_row[0]) == '' or trim($my_row[0]) == '') { continue; } //echo "^^^^".$my_row[0]."----------".count($para_array)."
"."\n"; if ($my_row[0] != 'Lead' and $my_row[0] != 'Contact' and $my_row[0] != 'Campaign' and $my_row[0] != 'Travel') { $my_row[0] = str_replace("--P--", $this->srm_prefix, $my_row[0]); } $pos = strpos($my_row[1], '-P--'); if ($pos >= 1 and $my_row[0] != 'Travel') { $my_row[1] = str_replace("--P--", $this->srm_prefix, $my_row[1]); } $obj_name = $my_row[0]; $field_name = $my_row[1]; $field_name_orig = $my_row[1]; if (strpos($field_name, "___phone") > 0) { $field_type = "string"; $field_name = str_replace("___phone", "", $field_name); } if (strpos($field_name, "___p") > 0) { $field_type = "picklist"; $field_name = str_replace("___p", "", $field_name); } else if (strpos($field_name, "___p") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("___p", "", $field_name); } else if (strpos($field_name, "___o") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("___o", "", $field_name); } else if (strpos($field_name, "___e") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("___e", "", $field_name); } else { $___pos = strpos($field_name, "___"); if ($___pos > 0) { $field_type = substr($field_name, $___pos + 3); $field_name = substr($field_name, 0, $___pos); } } if (strpos($field_name, "~") > 0 and $obj_name == "Campaign") { $field_type = "string"; $field_name = str_replace("~", " ", $field_name); } if (($obj_name != "Event" and $obj_name != "Campaign" and $obj_name != "Travel")) { $parentObj = "Lead"; if ($query_type == "C") { $parentObj = "Contact"; } $this->logTime($sf, 'start checkFieldPermissions FieldExists'); if ($sf->sf_isExistField($obj_name, $field_name) != 1) { $this->logTime($sf, 'end checkFieldPermissions FieldExists'); $updText = "This campaign references the field " . $field_name . " In object $obj_name ,which are not included in the permission on your profile . Please contact your CRM administrator"; return -2013; } } } // end of for loop $sf->error_log("End checkFieldPermissions -->"); return 1; } } // End of class