Start a new topic

PHP | How to purge old CRM data and send out a data deletion report

Beyond our Privacy policy settings, here is a short PHP script (relying on Sendinblue API and PhpSpreadsheet) to query your account's CRM (clients) and check for recent activity to purge their personal data:


<?php 
require 'vendor/autoload.php';

if (sizeof($argv) < 5) {
    echo "Usage: php report.php RECIPIENT SERVER APIKEY TOKEN check|purge\n";
    die();
}

$targetEmail = $argv[1];
$server = $argv[2];
$apiKey = $argv[3];
$token = $argv[4];
$purge = ($argv[5] == 'purge' ? true : false);

$startDate = date("Y-m-d", strtotime("30 days ago"));
$endDate = date("Y-m-d", strtotime("2 years"));


/* Excel spreadsheet */

use PhpOffice\PhpSpreadsheet\Spreadsheet; 
use PhpOffice\PhpSpreadsheet\Writer\Xlsx; 

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet(); 
$spreadsheet->getActiveSheet()->getStyle('A1:P1')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('00C8C8C8');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

$data_from_db = array();
$nbEntries = 0;

$data_from_db[0]=array("ID", "Prénom", "Nom");

// Fetching list of companies/locations for the account
$listCompanies = file_get_contents("https://$server/api/2.1/scheduling/companies?apiKey=$apiKey&token=$token");
$companys = json_decode($listCompanies);

// Fetch clients
$url = "https://$server/api/2.0/clients?apiKey=$apiKey&token=$token";
$clients = json_decode(file_get_contents($url));

// Browsing each page's results (syncToken)
while ($clients->items) {
    foreach($clients->items as $client) {
        $activities = json_decode(file_get_contents("https://$server/api/2.0/activities?apiKey=$apiKey&token=$token&startDate=$startDate&endDate=$endDate&clientId=" . $client->id));
        foreach($activities->items as $activity) {
            switch ($activity->type) {
                case "appointment" :
                    if (! $purge) {
                        echo "APPT  " . $client->id . " " . $client->firstName . " " . $client->lastName . "\n";
                    }
                    // Appointment found: skip to next client
                    continue 3;
                case "note" :
                    if (! $purge) {
                        echo "NOTE  " . $client->id . " " . $client->firstName . " " . $client->lastName . "\n";
                    }
                    // Note found: skip to next client
                    continue 3;
            }
        }

        $nbEntries++;
        
        $data_from_db[$nbEntries][] = $client->id;
        $data_from_db[$nbEntries][] = substr($client->firstName, 0, 1);
        $data_from_db[$nbEntries][] = substr($client->lastName, 0, 1);

        $entriesRemoved = true;

        if ($purge) {
            //create a new cURL resource
            $chDelete = curl_init("https://$server/api/2.0/clients/$client->id?apiKey=$apiKey&token=$token");

            curl_setopt($chDelete, CURLOPT_CUSTOMREQUEST, 'DELETE');
            curl_setopt($chDelete, CURLOPT_RETURNTRANSFER, true);

            //set the content type to application/json
            curl_setopt($chDelete, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));

            //execute the POST request
            curl_exec($chDelete);
            
            //close cURL resource
            curl_close($chDelete);
        } else {
            echo "PURGE " . $client->id . " " . $client->firstName . " " . $client->lastName . "\n";
        }
    }
    
    // Next page (ie: next syncToken)
    $clients = json_decode(file_get_contents($url . "&syncToken=" . $clients->nextSyncToken));
}

//set value row
for ($i=0; $i<count($data_from_db); $i++) {
    //set value for indi cell
    $row = $data_from_db[$i];

    //writing cell index start at 1 not 0
    $j = 1;

    foreach ($row as $x => $x_value) {
        $sheet->setCellValueByColumnAndRow($j, $i+1, $x_value);
        $j = $j + 1;
    }
}

if ($nbEntries > 0) {
    // Write an .xlsx file  
    $writer = new Xlsx($spreadsheet); 

    // Save .xlsx file to the files directory 
    $targetFileName = "PURGE_" . $startDate . "_" . $endDate . ".xlsx";
    $writer->save($targetFileName); 


    /* Email */

    $config = SendinBlue\Client\Configuration::getDefaultConfiguration()->setApiKey('api-key', '');

    $apiInstance = new SendinBlue\Client\Api\TransactionalEmailsApi(
        new GuzzleHttp\Client(),
        $config
    );
    $sendSmtpEmail = new \SendinBlue\Client\Model\SendSmtpEmail();
    $sendSmtpEmail['subject'] = "Report (" . $startDate . ") | $nbEntries CRM entries";
    $sendSmtpEmail['htmlContent'] = "<html><body><p>Hello,</p><p>Here are the deleted contacts.</p></body></html>";
    $sendSmtpEmail['sender'] = array('name' => 'Your company', 'email' => 'noreply@yourdomainname.com');
    $sendSmtpEmail['to'] = array(
        array('email' => $targetEmail)
    );
    $sendSmtpEmail['cc'] = array(
        array('email' => 'youremailaddress@yourdomainname.com', 'name' => 'John Doe')
    );
    $sendSmtpEmail['replyTo'] = array('email' => 'youremailaddress@yourdomainname.com', 'name' => 'John Doe');

    $content = chunk_split(base64_encode(file_get_contents($targetFileName)));
    $attachment_item = array(
        'name' => $targetFileName,
        'content' => $content
    );
    $attachment_list = array($attachment_item);
    $sendSmtpEmail['attachment'] = $attachment_list;
    $sendSmtpEmail['tags'] = array('bpce-dcp');

    unlink($targetFileName);

    try {
        $company = $apiInstance->sendTransacEmail($sendSmtpEmail);
    } catch (Exception $e) {
        echo 'Exception when calling TransactionalEmailsApi->sendTransacEmail: ', $e->getMessage(), PHP_EOL;
    }
}
?>



Composer settings:


{
    "name": "script/xlsx",
    "require": {
        "sendinblue/api-v3-sdk": "^7.4",
        "phpoffice/phpspreadsheet": "^1.17"
    }
}


Login or Signup to post a comment