VerticalResponse API Integration

November 29, 2024 ยท AI Conversations

User
$wsdl = "https://api.verticalresponse.com/wsdl/1.0/VRAPI.wsdl"; //location of the wsdl
$ses_time = 20; // duration of session in minutes
// We use the default PHP soap client (http://php.net/manual/en/class.soapclient.php).
$vr = new SoapClient($wsdl, array(/* 'local_cert' => $cert, 'passphrase' => $cert_pass */));

echo("Starting API sample coden");
// Logging in! This call returns a session ID which we'll use in all subsequent calls.

echo("Logging in as $usernamen");
$sid = $vr->login(array('username' => $username, 'password' => $password, 'session_duration_minutes' => $ses_time));
echo("Session ID: $sidn");

echo "";

$list1 = $vr->enumerateLists(array(
'session_id' => $sid,
'type' => 'email',
'include_field_info' => false,
'is_master' => true,
'limit' => 50,
));

echo "Get List";

/**
13 = Character and destiny Report
16 = Astrology for lovers Report
17 = astrology Calendar Report
19 = Yearly Report
*/
/* $SQLQuery = "SELECT
o.email_id As Email, IFNULL(up.`FirstName`, '') AS FirstName, IFNULL(up.`LastName`, '') AS LastName,
(CASE up.`Gender` WHEN 0 OR 2 THEN 'Female' ELSE 'Male' END) As Gender,
IFNULL(up.`Address`, '') AS Address, IFNULL(up.`City`, '') AS City, IFNULL(up.`State`, '') AS State,
STR_TO_DATE(CONCAT_WS('/', ub.`Month`, ub.`Day`, ub.`Year`),'%m/%d/%Y') AS BirthDate, product_item_id, order_date
FROM `user` u
JOIN `userprofile` up ON u.UserId = up.UserId
JOIN `userbirthdetail` ub ON u.UserId = ub.UserId
JOIN `order` o ON o.user_id = u.UserId
WHERE o.email_id NOT IN ('urvish.u@gmail.com', 'adrianrossduncan@gmail.com', 'parmaramit1111@gmail.com', 'pr@world-of-wisdom.com', 'ard@world-of-wisdom.com', 'charlotte@world-of-wisdom.com') AND
o.email_id '' AND up.`FirstName` '' AND up.`LastName` '' AND
o.product_type NOT IN (1,2,3) AND product_item_id IN (13, 16, 17, 19) AND order_date >= :OrderDate
GROUP BY product_item_id, o.email_id
ORDER BY order_date DESC"; */

$SQLQuery = "SELECT od.email As Email,
p.first_name as FirstName,
p.last_name as LastName,
(CASE p.gender WHEN 'M' THEN 'Male' ELSE 'Female' END) As Gender,
p.address as Address,
c.city as City,
c.country as State,
CONCAT(bd.date, ' ', bd.time) as BirthDate,
od.product_id as product_item_id, od.order_date
FROM
orders od, profiles p, cities c, birth_details bd
WHERE od.user_id = p.user_id
AND bd.city_id = c.id
AND od.user_id = bd.user_id
AND od.product_type NOT IN (1,2,3)
AND od.order_host NOT IN (4)
AND od.product_id IN (5, 13, 17, 19,23,80, 81, 82,24,26,28,62,63,64,66)
AND od.order_date >= :OrderDate
GROUP BY od.product_id, od.email
ORDER BY od.order_date DESC";

//die;
//$queryParams = array(':OrderDate' => date('Y-m-d', strtotime("2015-09-01")));

$queryParams = array(':OrderDate' => date('Y-m-d'));

// $queryParams = array(':OrderDate' => '2017-01-24');

$objDB = $db->prepare($SQLQuery);
$objDBResult = $objDB->execute($queryParams);
$eLiteActiveRow = $objDB->fetchAll();

echo "Total Records: " . count($eLiteActiveRow) . '';
//exit();

if ($eLiteActiveRow) {

foreach ($eLiteActiveRow as $CurrentRow) {
$EMAILID = $CurrentRow['Email'];
echo "## ".$EMAILID." ##";
//die;

if (!filter_var($EMAILID, FILTER_VALIDATE_EMAIL) === false) {
// valid emailaddress
$FIRSTNAME = $CurrentRow['FirstName'];
$LASTNAME = $CurrentRow['LastName'];
$PURCHASEYESNO = 'Yes';
$BIRTHDATE = $CurrentRow['BirthDate'];
$COUNTRY = $CurrentRow['State'];
$TOWN = $CurrentRow['City'];
$GENDER = $CurrentRow['Gender'];
$product_id = $CurrentRow['product_item_id'];

$REGISTRANT = "Yes";
$PURCHASES = "Yes";
$FREEPRODUCT = "No";
$SHAREDOWN = "No";

$REPORT = "No";
$REPORTAFL = "No";
$REPORTHI = "No";
$REPORTAC = "No";
$REPORTYR = "No";

$report_ids = array(5,13,17,19,23,80,81,82);
if (in_array($CurrentRow['product_item_id'], $report_ids)) {
$REPORT = "Yes";
$REPORTAFL = $CurrentRow['product_item_id'] == "5" ? "Yes" : "No";
if(in_array($CurrentRow['product_item_id'],array(13,23, 80, 81, 82))){
$REPORTHI = "Yes";
if($CurrentRow['product_item_id'] != 13){
$FREEPRODUCT = "Yes";
}
}
$REPORTAC = $CurrentRow['product_item_id'] == "17" ? "Yes" : "No";
$REPORTYR = $CurrentRow['product_item_id'] == "19" ? "Yes" : "No";
}

$software_ids = array(24,26,28,62,63,64,66);
$SOFTWARE = "No";
$SOFTWAREHI = "No";
$SOFTWAREAC= "No";
$SOFTWAREAFL = "No";
if (in_array($CurrentRow['product_item_id'], $software_ids)) {
$SOFTWARE = "Yes";
$SHAREDOWN = "Yes";
$SOFTWAREHI = ($CurrentRow['product_item_id'] == "24" || $CurrentRow['product_item_id'] == "62" || $CurrentRow['product_item_id'] == "63" || $CurrentRow['product_item_id'] == "66") ? "Yes" : "No";

$SOFTWAREAC = ($CurrentRow['product_item_id'] == "26" || $CurrentRow['product_item_id'] == "64") ? "Yes" : "No";
$SOFTWAREAFL = $CurrentRow['product_item_id'] == "28" ? "Yes" : "No";
}

try {
$searchResult = $vr->searchListMembers(array(
'session_id' => $sid,
'field_name' => 'email_address',
'field_value' => $EMAILID,
'max_records' => 200
));
//echo json_encode($searchResult);die;

if (count($searchResult) == 0) {
$MemberData = array(
array('name' => 'email_address', 'value' => $EMAILID),
array('name' => 'first_name', 'value' => $FIRSTNAME),
array('name' => 'last_name', 'value' => $LASTNAME),
array('name' => 'optin_status', 'value' => 2),
array('name' => 'Birthdate', 'value' => date("Y-m-d H:i:s", strtotime($BIRTHDATE))),
array('name' => 'BirthCountry', 'value' => $COUNTRY),
array('name' => 'BirthTown', 'value' => $TOWN),
array('name' => 'Registrant', 'value' => $REGISTRANT),
array('name' => 'Purchases', 'value' => $PURCHASEYESNO),
array('name' => 'FreeProduct', 'value' => $FREEPRODUCT),
array('name' => 'Software', 'value' => $SOFTWARE),
array('name' => 'SoftwareHI', 'value' => $SOFTWAREHI),
array('name' => 'SoftwareAC', 'value' => $SOFTWAREAC),
array('name' => 'SoftwareAFL', 'value' => $SOFTWAREAFL),
array('name' => 'ReportYR', 'value' => $REPORTYR),
array('name' => 'ReportAFL', 'value' => $REPORTAFL),
array('name' => 'ReportHI', 'value' => $REPORTHI),
array('name' => 'ReportAC', 'value' => $REPORTAC),
array('name' => 'gender', 'value' => $GENDER),
);
} else {

if (isset($searchResult[0]->member_data[34]->value) && strtolower($searchResult[0]->member_data[34]->value) == "no" &&
strtolower($REPORTYR) == "yes") {
$REPORTYR = "Yes";
} else {
$REPORTYR = $searchResult[0]->member_data[34]->value;
}

if (isset($searchResult[0]->member_data[6]->value) && strtolower($searchResult[0]->member_data[6]->value) == "no" &&
strtolower($REPORTAFL) == "yes") {
$REPORTAFL = "Yes";
} else {
$REPORTAFL = $searchResult[0]->member_data[6]->value;
}

if (isset($searchResult[0]->member_data[4]->value) && strtolower($searchResult[0]->member_data[4]->value) == "no" &&
strtolower($REPORTHI) == "yes") {
$REPORTHI = "Yes";
} else {
$REPORTHI = $searchResult[0]->member_data[4]->value;
}

if (isset($searchResult[0]->member_data[21]->value) && strtolower($searchResult[0]->member_data[21]->value) == "no" &&
strtolower($REPORTAC) == "yes") {
$REPORTAC = "Yes";
} else {
$REPORTAC = $searchResult[0]->member_data[21]->value;
}

if (isset($searchResult[0]->member_data[27]->value) && strtolower($searchResult[0]->member_data[27]->value) != "") {
$GENDER = $searchResult[0]->member_data[27]->value;
}

echo $searchResult[0]->member_data[58]->value . " == " . $EMAILID . " ";

$MemberData = array(
array('name' => 'hash', 'value' => $searchResult[0]->member_data[18]->value),
array('name' => 'email_address', 'value' => $searchResult[0]->member_data[58]->value),
array('name' => 'first_name', 'value' => $searchResult[0]->member_data[46]->value),
array('name' => 'last_name', 'value' => $searchResult[0]->member_data[52]->value),
array('name' => 'optin_status', 'value' => 2),
//array('name' => 'Birthdate', 'value' => $searchResult[0]->member_data[9]->value),
array('name' => 'BirthCountry', 'value' => $searchResult[0]->member_data[7]->value),
array('name' => 'BirthTown', 'value' => $searchResult[0]->member_data[61]->value),
array('name' => 'Registrant', 'value' => $searchResult[0]->member_data[0]->value),
array('name' => 'Purchases', $searchResult[0]->member_data[5]->value),
array('name' => 'FreeProduct', 'value' => $searchResult[0]->member_data[15]->value),
array('name' => 'Software', 'value' => $searchResult[0]->member_data[36]->value),
array('name' => 'SoftwareHI', 'value' => $searchResult[0]->member_data[45]->value),
array('name' => 'SoftwareAC', 'value' => $searchResult[0]->member_data[50]->value),
array('name' => 'SoftwareAFL', 'value' => $searchResult[0]->member_data[23]->value),
// array('name' => 'ReportYR', 'value' => $searchResult[0]->member_data[34]->value),
// array('name' => 'ReportAFL', 'value' => $searchResult[0]->member_data[6]->value),
// array('name' => 'ReportHI', 'value' => $searchResult[0]->member_data[21]->value),
// array('name' => 'ReportAC', 'value' => "Yes"),
array('name' => 'ReportYR', 'value' => $REPORTYR),
array('name' => 'ReportAFL', 'value' => $REPORTAFL),
array('name' => 'ReportHI', 'value' => $REPORTHI),
array('name' => 'ReportAC', 'value' => $REPORTAC),
array('name' => 'gender', 'value' => $GENDER),
);

/*
$MemberData = array(
array('name' =>'hash', 'value' => $searchResult[0]["member_data"][18]->value),
array('name' =>'email_address', 'value' => $EMAILID),
array('name' => 'first_name', 'value' => $FIRSTNAME),
array('name' => 'last_name', 'value' => $LASTNAME),

array('name' => 'optin_status', 'value' => 2),
array('name' => 'Birthdate', 'value' => date("Y-m-d", strtotime($BIRTHDATE))),
array('name' => 'BirthCountry', 'value' => $COUNTRY),
array('name' => 'BirthTown', 'value' => $TOWN),

array('name' => 'Registrant', 'value' => $REGISTRANT),
array('name' => 'Purchases', 'value' => $PURCHASEYESNO),
array('name' => 'FreeProduct', 'value' => $FREEPRODUCT),
array('name' => 'Software', 'value' => $SOFTWARE),

array('name' => 'SoftwareHI', 'value' => "Yes"),
array('name' => 'SoftwareAC', 'value' => "Yes"),
array('name' => 'SoftwareAFL', 'value' => "Yes"),

array('name' => 'ReportYR', 'value' => $REPORTYR),
array('name' => 'ReportAFL', 'value' => $REPORTAFL),
array('name' => 'ReportHI', 'value' => $REPORTHI),
array('name' => 'ReportAC', 'value' => $REPORTAC),
);
*/
}

//echo json_encode($MemberData);die;

if (count($searchResult) == 0) {
$test = $vr->addListMember(array(
'session_id' => $sid,
'list_member' => array(
'list_id' => $list1[0]->id,
'member_data' => $MemberData,
)
)
);
} else {
$test = $vr->editListMember(array(
'session_id' => $sid,
'list_member' => array(
'list_id' => $searchResult[0]->list_id,
'member_data' => $MemberData,
)
)
);
}
} catch (Exception $exception) {
echo 'fault: "' . $exception->getCode() . '" – ' . $exception->getMessage() . " ##n";
}
}
}
}

// Here's a helper function for putting the contents of a file into a string.
// It's not really a part of the API, per se, but you might find it helpful for loading your message content.
$servername = 'astrowow-mysql-new.cwlq0bny8ltt.us-west-1.rds.amazonaws.com';
$username = 'awswow';
$password = 'Main4itl123';
$conn = mysqli_connect ($servername, $username, $password);
$db_selected = mysqli_select_db($conn, 'astronew_db');

$list1 = $vr->enumerateLists(array(
'session_id' => $sid,
'type' => 'email',
'include_field_info' => false,
'is_master' => true,
'limit' => 50,
));
$SQLQuery2 = "SELECT email AS Email,id FROM sunsign_emailcron WHERE freecoins = 1 ORDER BY id DESC";
$objDB = $db->prepare($SQLQuery2);
$objDBResult = $objDB->execute($queryParams);
$vrRow = $objDB->fetchAll();

echo "Total Records: " . count($vrRow) . '';
if ($vrRow) {

foreach ($vrRow as $CurrentRow) {
$EMAILID = $CurrentRow['Email'];
echo "## ".$EMAILID." ##";
mysqli_query($conn,"update sunsign_emailcron set freecoins='2' where id='".$CurrentRow['id']."'");
if (!filter_var($EMAILID, FILTER_VALIDATE_EMAIL) === false) {
// valid emailaddress

$REGISTRANT = "Yes";
$PURCHASES = "Yes";
$FREEPRODUCT = "No";
$SHAREDOWN = "No";

$REPORT = "No";
$REPORTAFL = "No";
$REPORTHI = "No";
$REPORTAC = "No";
$REPORTYR = "No";

$software_ids = array(24,26,28,62,63,64,66);
$SOFTWARE = "No";
$SOFTWAREHI = "No";
$SOFTWAREAC= "No";
$SOFTWAREAFL = "No";

try {
$searchResult = $vr->searchListMembers(array(
'session_id' => $sid,
'field_name' => 'email_address',
'field_value' => $EMAILID,
'max_records' => 200
));
//echo json_encode($searchResult);die;
$SOFTWARE = "No";
$SOFTWAREHI = "No";
$SOFTWAREAC= "No";
$SOFTWAREAFL = "No";
$REGISTRANT = "Yes";
$PURCHASES = "Yes";
$FREEPRODUCT = "No";
$SHAREDOWN = "No";

$REPORT = "No";
$REPORTAFL = "No";
$REPORTHI = "No";
$REPORTAC = "No";
$REPORTYR = "No";
if (count($searchResult) == 0) {
$MemberData = array(
array('name' => 'email_address', 'value' => $EMAILID),
array('name' => 'Registrant', 'value' => $REGISTRANT),
array('name' => 'Purchases', 'value' => $PURCHASEYESNO),
array('name' => 'FreeProduct', 'value' => $FREEPRODUCT),
array('name' => 'Software', 'value' => $SOFTWARE),
array('name' => 'SoftwareHI', 'value' => $SOFTWAREHI),
array('name' => 'SoftwareAC', 'value' => $SOFTWAREAC),
array('name' => 'SoftwareAFL', 'value' => $SOFTWAREAFL),
array('name' => 'ReportYR', 'value' => $REPORTYR),
array('name' => 'ReportAFL', 'value' => $REPORTAFL),
array('name' => 'ReportHI', 'value' => $REPORTHI),
array('name' => 'ReportAC', 'value' => $REPORTAC),
array('name' => 'gender', 'value' => 'M'),
);
}

if (count($searchResult) == 0) {
$test = $vr->addListMember(array(
'session_id' => $sid,
'list_member' => array(
'list_id' => $list1[0]->id,
'member_data' => $MemberData,
)
)
);
print_r($test);
}
} catch (Exception $exception) {
echo 'fault: "' . $exception->getCode() . '" – ' . $exception->getMessage() . " ##n";
}
}

}
}
function getContents($file_name) {
$info = "";
if (file_exists($file_name)) {
$handler = fopen($file_name, 'r');
while (!feof($handler)) {
$info .= fgets($handler, 1024);
}
fclose($handler);
}
return $info;
}
we want to send Danish customer to optin Dk and English customer to optin EN

Assistant
This script integrates with the **VerticalResponse API** using SOAP and interacts with a database to retrieve user details and synchronize them with VerticalResponse mailing lists. Here's a breakdown of what this script does:

### Key Components:

1. **SOAP Client Initialization**:

$wsdl = "https://api.verticalresponse.com/wsdl/1.0/VRAPI.wsdl";
   $vr = new SoapClient($wsdl, array());

The `SoapClient` is used to connect to VerticalResponse's SOAP API.

2. **Session Management**:
The script authenticates with the API using:

$sid = $vr->login(array('username' => $username, 'password' => $password, 'session_duration_minutes' => $ses_time));

This session ID (`$sid`) is reused for subsequent API calls.

3. **Fetching Email Lists**:
It retrieves email lists from the VerticalResponse API:

$list1 = $vr->enumerateLists(array(
       'session_id' => $sid,
       'type' => 'email',
       'limit' => 50,
   ));

4. **Database Query for User Data**:
The script runs a SQL query to get user data based on certain filters:

$SQLQuery = "SELECT od.email As Email, ... WHERE od.order_date >= :OrderDate";

It fetches data like email, name, address, birthdate, gender, and product details.

5. **Processing and Synchronizing Data**:
For each valid email, it determines:
– Whether the user is already in the VerticalResponse list.
– If not, it adds the user.
– If already present, it updates their data:

if (count($searchResult) == 0) {
         $vr->addListMember(array(
             'session_id' => $sid,
             'list_member' => array('list_id' => $list1[0]->id, 'member_data' => $MemberData),
         ));
     } else {
         $vr->editListMember(array(
             'session_id' => $sid,
             'list_member' => array('list_id' => $searchResult[0]->list_id, 'member_data' => $MemberData),
         ));
     }

6. **Handling Exceptions**:
Errors in API calls are caught and logged:

catch (Exception $exception) {
       echo 'fault: "' . $exception->getCode() . '" - ' . $exception->getMessage() . " ##n";
   }

### Observations:
– **Security Concerns**:
– Credentials like `$username`, `$password`, and the database password are hardcoded, which is insecure. Consider using environment variables or secure storage.
– Ensure the `password` field is stored and transmitted securely (e.g., hashing for storage, SSL for transmission).
– **Performance**:
– The script fetches a lot of data in one go. If the dataset grows, it may impact performance.
– Pagination in the SQL query and API calls can mitigate performance issues.
– **Modern Alternatives**:
– SOAP APIs are less common today. If VerticalResponse offers a REST API, consider using it.
– Utilize prepared statements and parameter binding (`PDO`) for database queries for better security and performance.

If you're encountering issues, let me know where you're stuck or what you'd like to optimize!

User
insert list in Optin Danish in vertical response
Assistant
To insert a list into the "Optin Danish" list in Vertical Response using the SOAP API, you can modify your code as follows. The main focus is ensuring the correct `list_id` is targeted for the "Optin Danish" list.

### Steps:
1. **Identify the List ID**:
First, fetch all lists available in your Vertical Response account to identify the ID of the "Optin Danish" list.

2. **Insert Members into the List**:
Use the `addListMember` API method with the identified `list_id`.

### Updated Code

Here's an updated segment for fetching the list ID and adding members to "Optin Danish":

// Step 1: Fetch lists to find the "Optin Danish" list
$listResponse = $vr->enumerateLists([
    'session_id' => $sid,
    'type' => 'email',
    'include_field_info' => false,
    'is_master' => true,
    'limit' => 50,
]);

// Find the list ID for "Optin Danish"
$optinDanishListId = null;
foreach ($listResponse as $list) {
    if (isset($list->name) && $list->name === 'Optin Danish') {
        $optinDanishListId = $list->id;
        break;
    }
}

if (!$optinDanishListId) {
    die("Optin Danish list not found.");
}

// Step 2: Insert members into the "Optin Danish" list
foreach ($eLiteActiveRow as $CurrentRow) {
    $EMAILID = $CurrentRow['Email'];
    if (!filter_var($EMAILID, FILTER_VALIDATE_EMAIL)) {
        continue; // Skip invalid emails
    }

    $MemberData = [
        ['name' => 'email_address', 'value' => $EMAILID],
        ['name' => 'first_name', 'value' => $CurrentRow['FirstName']],
        ['name' => 'last_name', 'value' => $CurrentRow['LastName']],
        // Add other custom fields as necessary
    ];

    try {
        $searchResult = $vr->searchListMembers([
            'session_id' => $sid,
            'field_name' => 'email_address',
            'field_value' => $EMAILID,
            'max_records' => 200,
        ]);

        if (count($searchResult) == 0) {
            // Add new member
            $vr->addListMember([
                'session_id' => $sid,
                'list_member' => [
                    'list_id' => $optinDanishListId,
                    'member_data' => $MemberData,
                ],
            ]);
            echo "Added $EMAILID to Optin Danish.n";
        } else {
            // Update existing member
            $vr->editListMember([
                'session_id' => $sid,
                'list_member' => [
                    'list_id' => $optinDanishListId,
                    'member_data' => $MemberData,
                ],
            ]);
            echo "Updated $EMAILID in Optin Danish.n";
        }
    } catch (Exception $exception) {
        echo 'Error for ' . $EMAILID . ': "' . $exception->getCode() . '" - ' . $exception->getMessage() . "n";
    }
}

### Explanation:
1. **Fetching List**:
– The `enumerateLists` call retrieves all available lists.
– The code searches for the name "Optin Danish" and extracts its `id`.

2. **Adding or Updating Members**:
– For each valid email, the code checks if the member already exists using `searchListMembers`.
– If the member does not exist, it uses `addListMember` to add them.
– If the member exists, it uses `editListMember` to update their details.

### Additional Notes:
– Replace any hardcoded field mappings with dynamic mappings if necessary.
– Adjust error handling to suit your needs.
– Validate the fetched list ID by printing it before proceeding with member operations.

Leave a Comment

Your email address will not be published. Required fields are marked *