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';

function get_file_json_contents($url) {
    $curl_handle = curl_init();
    curl_setopt($curl_handle, CURLOPT_URL, $url);
    curl_setopt($curl_handle, CURLOPT_CONNECTTIMEOUT, 2);
    curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($curl_handle, CURLOPT_USERAGENT, 'Script');
    $data = json_decode(curl_exec($curl_handle));
    curl_close($curl_handle);
    return $data;
}

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("90 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;
$foundActivities = false;

$data_from_db[0]=array("ID", "Firstname", "Lastname");

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

// Browsing each page's results (syncToken)
while ($clients->items) {
    foreach($clients->items as $client) {
       $activities = get_file_json_contents("https://$server/api/2.0/activities?apiKey=$apiKey&token=$token&startDate=$startDate&endDate=$endDate&clientId=" . $client->id);

        if (isset($activities->items)) {
            $foundActivities = false;

            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
                        $foundActivities = true;
                        break;
                    case "note" :
                        if (! $purge) {
                            echo "NOTE  " . $client->id . " " . $client->firstName . " " . $client->lastName . "\n";
                        }
                        // Note found: skip to next client
                        $foundActivities = true;
                        break;
                }

                if ($foundActivities) {
                    break;
                }
            }

            if (! $foundActivities) {
                $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);

                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";
                }
            }
        } else {
            echo "Failed to get activities: " . "https://$server/api/2.0/activities?apiKey=$apiKey&token=$token&startDate=$startDate&endDate=$endDate&
            clientId=" . $client->id . "\n";
        }
    }

    // Next page (ie: next syncToken)
    $clients = get_file_json_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'] = "Rapport d'effacement (" . $startDate . ") | $nbEntries fiche(s) clients";
    $sendSmtpEmail['htmlContent'] = "<html><body><p>Bonjour,</p><p>Voici les fiches clients supprimées.</p></body></html>";
    $sendSmtpEmail['sender'] = array('name' => 'Agendize', 'email' => '[email protected]');
    $sendSmtpEmail['to'] = array(
        array('email' => $targetEmail)
    );
    /*$sendSmtpEmail['cc'] = array(
        array('email' => '[email protected]', 'name' => 'John Doe')
    );*/
    $sendSmtpEmail['replyTo'] = array('email' => '[email protected]', '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('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"
    }
}

0 Votes