<?php
//error_reporting(error_reporting() & ~E_NOTICE);
//error_reporting(E_ALL);
error_reporting(0);

require_once($_SERVER["DOCUMENT_ROOT"] . '/library/functions.php');
openDBLIPlayground3309('pareto');

$debug = isset($_GET['debug']);
$reload = isset($_GET['reload']);

/**
 * Class SamplePardotApiClient
 *
 * Example PHP client to call the Pardot API
 */
class QueryListPardotApiClient
{
    const BASE_URL = "https://pi.pardot.com/api/";
    const SALESFORCE_OAUTH_TOKEN_URL = "https://login.salesforce.com/services/oauth2/token";

    /** @var int $apiVersion */
    private $apiVersion;

    /** @var string $format  */
    private $format;

    public function __construct($apiVersion, $format = 'xml')
    {
        $this->apiVersion = $apiVersion;
        $this->format = $format;
    }

    /**
     * @param string $endpoint
     * @param string $operation
     * @param array $data
     * @param array $headers
     * @param array $queryParams
     * @param bool $useSalesforceOAuth
     * @return array
     * @throws Exception
     */
    public function post($endpoint, $operation, $data = [], $headers = [], $queryParams = [], $useSalesforceOAuth = true)
    {
        $curl_handle = $this->initRequest($endpoint, $operation, $headers, $queryParams, $useSalesforceOAuth);
        curl_setopt($curl_handle, CURLOPT_POST, true);
        // Add POST data if given
        if (!empty($data)) {
            curl_setopt($curl_handle, CURLOPT_POSTFIELDS, $data);
        }

        return $this->executeCall($curl_handle);
    }

    /**
     * @param string $endpoint
     * @param string $operation
     * @param array $headers
     * @param array $queryParams
     * @param bool $useSalesforceOAuth
     * @return array
     * @throws Exception
     */
    public function get($endpoint, $operation, $headers = [], $queryParams = [], $useSalesforceOAuth = true)
    {
        $curl_handle = $this->initRequest($endpoint, $operation, $headers, $queryParams, $useSalesforceOAuth);

        return $this->executeCall($curl_handle);
    }

    /**
     * @param string $endpoint
     * @param string $operation
     * @param array $headers
     * @param array $queryParams
     * @param bool $useSalesforceOAuth
     * @return false|resource
     */
    private function initRequest($endpoint, $operation, $headers = [], $queryParams = [], $useSalesforceOAuth = true)
    {
        // Construct our full URL to the Pardot API
        $url = $this->buildUrl($endpoint, $operation, $useSalesforceOAuth);
        // Add desired format to any query string params provided
        $queryParams['format'] = $this->format;
        // Build query string params into an encoded string
        $queryString = http_build_query($queryParams, null, '&');
        $queryString = str_replace('%5B0%5D%5B0%5D','',$queryString);
        // Append query string params to URL
        $url .= "?{$queryString}";

        
        echo $url . '<br/>';
        

        // Init curl handle and set standard curl options: timeouts / require SSL / verify SSL
        $curl_handle = curl_init($url);
        curl_setopt($curl_handle, CURLOPT_CONNECTTIMEOUT, 5);
        curl_setopt($curl_handle, CURLOPT_TIMEOUT, 30);
        curl_setopt($curl_handle, CURLOPT_PROTOCOLS, CURLPROTO_HTTPS);
        curl_setopt($curl_handle, CURLOPT_SSL_VERIFYHOST, 2);
        curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, 1);

        // Add any headers passed in such as Authorization header
        if (!empty($headers)) {
            curl_setopt($curl_handle, CURLOPT_HTTPHEADER, $headers);
        }

        return $curl_handle;
    }

    /**
     * @param string $endpoint
     * @param string $operation
     * @param bool $useSalesforceOAuth
     * @return string
     */
    private function buildUrl($endpoint, $operation = "", $useSalesforceOAuth = true)
    {
        if ($endpoint === 'login') {
            if ($useSalesforceOAuth) {
                return self::SALESFORCE_OAUTH_TOKEN_URL;
            } else {
                return self::BASE_URL . "login";
            }
        }

        return self::BASE_URL . "{$endpoint}/version/{$this->apiVersion}/do/{$operation}";
    }

    /**
     * @param $curl_handle
     * @return array
     * @throws Exception
     */
    private function executeCall($curl_handle)
    {
        // Execute our call to the Pardot API
        $rsp = curl_exec($curl_handle);
        // Gather the HTTP response code and last effective URL called
        $httpCode = curl_getinfo($curl_handle, CURLINFO_HTTP_CODE);
        $url = curl_getinfo($curl_handle, CURLINFO_EFFECTIVE_URL);

        // Handle errors in calls, this could be a log or an exception thrown as written here
        if (!$rsp) {
            $errorMessage = curl_error($curl_handle);
            curl_close($curl_handle);
            throw new Exception("Error calling API. HTTP Code: {$httpCode}. Message: {$errorMessage}");
        }
        curl_close($curl_handle);

        // Output call response for informational purposes
        /*
        echo ("URL: {$url}" . PHP_EOL);
        echo ("HTTP Response Code: {$httpCode}" . PHP_EOL);
        echo ("Response: {$rsp}" . PHP_EOL . PHP_EOL);
        */

        return [$httpCode, $rsp];
    }

    /**
     * Use Pardot API using Api Key and User Key - No longer available
     */
    /*
    public function executeRequestsWithApiKeys()
    {
        // Setup user credentials
        $credentials = [
            'user_key' => '60ef081ba86770e2a43d0a14a1d45029',
            'email' => 'revenuewell@2x.marketing',
            'password' => '2XMarketing2020!'
        ];

        // Authenticate to Pardot - Must be a POST with credentials in the message body
        list($httpCode, $rsp) = $this->post('login', '', $credentials, null, [], false);
        // Capture the api_key from a successful login response
        // api_key is good for 1 hour and can be reused on subsequent calls
        $apiKey = json_decode($rsp, true)['api_key'];

        // Create Authorization Header from api_key
        $authHeader = ["Authorization: Pardot user_key={$credentials['user_key']},api_key={$apiKey}"];

        $strSQL = "select MAX(_pardotID) from pardot_prospect";
        $prevMax = getArrayResult($strSQL);
        if (empty($prevMax)) {
            $prevMax = 0;
        }

        // Call Prospect Query
        $params = array(
            'limit'=>200, 
            'id_greater_than'=>$prevMax, 
            'sort_by'=>'id', 
            'sort_order' => 'ascending'
        );
        
        list($httpCode, $rsp) = $this->get('prospect', 'query', $authHeader, $params);

        return $rsp;
    }
    */

    /**
     * Use Pardot API with a SSO user.
     * Getting the access token and using that to use the Pardot API.
     */
    public function executeRequestsWithSalesforceOAuth($debug, $objConn, $reload)
    {
        // Setup user credentials
        /*
        $credentials = [
            "grant_type" => "password",
            "client_id" => "<your_client_id>",
            "client_secret" => "<your_client_secert>",
            "username" => "<your_salesforce_email>",
            "password" => "<your_password>"
        ];
        */
        $credentials = [
            "grant_type" => "password",
            "client_id" => "3MVG9A2kN3Bn17hviVFf.TwbV0egtvohywPyVe4llySiHm57NrpLQycbSQuG85UU_y6Pl3fnpJAFd6Z5X3PoY",
            "client_secret" => "587BDBE3AC3A047E2B6B33C82033C65D0C5CBC90DE76BFF5D94E64A064749F8A",
            "username" => "jasmine.sim@2x.marketing",
            "password" => 'Pareto_2X22_!!!'
        ];

        //$pardot_business_unit_id = "<Pardot_business_unit_id>";
        $pardot_business_unit_id = "0Uv5A000000006dSAA";

        // Authenticate to Salesforce - Must be a POST with credentials in the message body
        list($httpCode, $rsp) = $this->post('login', '', $credentials, null, [], true);
        //var_dump($rsp);

        // Capture the access_token from a successful login response
        $access_token = json_decode($rsp, true)['access_token'];

        // Create Authorization Header from access_token and business unit
        $authHeader = ["Authorization: Bearer {$access_token}", "Pardot-Business-Unit-Id: {$pardot_business_unit_id}"];


        $curMinute = date('i');
        echo 'curMinute: ' . $curMinute . '<br/>';
        if ($curMinute < 4) {
            echo 'Query List...<br/><br/>';
            // Query List once every hour only 
            $this->queryList($authHeader, $debug, $objConn) ;
        }


        echo 'Query List Membership...<br/><br/>';
        $this->queryListMembership($authHeader, $debug, $objConn, $reload);

    }



    public function queryList($authHeader, $debug, $objConn) 
    {
        
        $start = time();
        $strSQL = "select MAX(_updatedAt) from pardot_list";
        $prevMax = getSingleResult($strSQL);
        if (empty($prevMax)) {
            $prevMax = '2020-01-01 00:00:00';
        }

        // Call List Query
        $params = array(
            'limit'=>200, 
            'updated_after'=>$prevMax, 
            'sort_by'=>'updated_at', 
            'sort_order' => 'ascending'
        );

        /*test
        $params = array(
            'limit'=>200, 
            'updated_after'=>'2020-01-01',
            'sort_by'=>'id', 
            'sort_order' => 'descending'
        );
        */

        // Call List Query
        list($httpCode, $rsp) = $this->get('list', 'query', $authHeader, $params);

        $rspArray = json_decode($rsp, true);

        if ($debug) {
            var_dump($rsp);
        }


        $totalAffected = 0;

        $result = $rspArray['result'];
        $lists = $result['list'];

        if (isset($lists['id'])) { // only 1 result
            $listArr = array(); // Make it into an array
            $listArr[] = $lists;
            $lists = $listArr;
        }

        foreach ($lists as $p) {
            $pardotID = $p['id'];
            $name = $p['name'];
            $isPublic = $objConn->real_escape_string($p['is_public']);
            $isDynamic = $objConn->real_escape_string($p['is_dynamic']);
            $title = $objConn->real_escape_string($p['title']);
            $description = $objConn->real_escape_string($p['description']);
            $isCRMVisible = $objConn->real_escape_string($p['is_crm_visible']);
            $createdAt = $objConn->real_escape_string($p['created_at']);
            $updatedAt = $objConn->real_escape_string($p['updated_at']);    

            $strSQL = "INSERT INTO pardot_list(_pardotListID,
            _name,
            _isPublic,
            _isDynamic,
            _title,
            _description,
            _isCRMVisible,
            _createdAt,
            _updatedAt ) 
            VALUES('$pardotID',
            '$name',
            '$isPublic',
            '$isDynamic',
            '$title',
            '$description',
            '$isCRMVisible',
            '$createdAt',
            '$updatedAt' )
            ON DUPLICATE KEY UPDATE _name='$name',
            _isPublic='$isPublic',
            _isDynamic='$isDynamic',
            _title='$title',
            _description='$description',
            _isCRMVisible='$isCRMVisible',
            _createdAt='$createdAt',
            _updatedAt='$updatedAt' ;
            ";

            $objConn->query($strSQL);

            $affected = $objConn->affected_rows;
            if ($affected > 0) {
                $totalAffected++;
            }

            if ($debug) {
                echo $strSQL . '<br/>';
                echo 'list inserted = ' . $affected . '<br/><br/>';
            }
            
        }



        echo 'Total lists inserted / updated: ' . $totalAffected . '<br/>';
        echo 'Time spent - List: ' . (time() - $start) . '<br/><br/>';;
    }


    public function queryListMembership($authHeader, $debug, $objConn, $reload) 
    {
        // TODO: Truncate once a week

        
        $start = time();
        /* UPDATE
        $strSQL = "select DATE_SUB(MAX(_updatedAt), INTERVAL 1 SECOND) from pardot_list_membership WHERE _deleted = 0";
        $prevMax = getSingleResult($strSQL);
        if (empty($prevMax)) {
            $prevMax = '2020-01-01 00:00:00';
        } 

        if ($debug) {
            echo $strSQL . '<br/>';
        }

        $strSQL = "select MAX(_pardotListMembershipID) from pardot_list_membership WHERE _deleted = 0 AND _updatedAt >= '$prevMax'";
        $prevMaxID = getSingleResult($strSQL);
        if (empty($prevMaxID)) {
            $prevMaxID = '0';
        }

        if ($debug) {
            echo $strSQL . '<br/>';
        }

        // Call List Query
        $params = array(
            'limit'=>200, 
            'updated_after'=>$prevMax, 
            'id_greater_than'=>$prevMaxID,
            'sort_by'=>'id', 
            'sort_order' => 'ascending',
            'deleted' => 'false'
        );
        */

        // /* Create
        $strSQL = "select MAX(_pardotListMembershipID) from pardot_list_membership WHERE _deleted = 0";
        $prevMaxID = getSingleResult($strSQL);
        if (empty($prevMaxID)) {
            $prevMaxID = '0';
        }

        if ($debug) {
            echo $strSQL . '<br/>';
        }

        // Call List Query
        $params = array(
            'limit'=>200, 
            'id_greater_than'=>$prevMaxID,
            'sort_by'=>'id', 
            'sort_order' => 'ascending',
            'deleted' => 'false'
        );
        // */

        /*test
        $params = array(
            'limit'=>200, 
            'updated_after'=>$prevMax, 
            'id_greater_than'=>$prevMaxID,
            'sort_by'=>'id', 
            'sort_order' => 'ascending',
            'list_id' => '18123'
        );
        */

        // Call List Query
        list($httpCode, $rsp) = $this->get('listMembership', 'query', $authHeader, $params);

        $rspArray = json_decode($rsp, true);

        if ($debug) {
            var_dump($rsp);
        }


        $totalAffected = 0;

        $result = $rspArray['result'];
        $listMemberships = $result['list_membership'];

        if (isset($listMemberships['id'])) { // only 1 result
            $listMembershipsArr = array(); // Make it into an array
            $listMembershipsArr[] = $listMemberships;
            $lists = $listMembershipsArr;
        }

        foreach ($listMemberships as $p) {
            $pardotID = $p['id'];
            $listID = $p['list_id'];
            $prospectID = $objConn->real_escape_string($p['prospect_id']);
            $optedOut = $objConn->real_escape_string($p['opted_out']);
            $createdAt = $objConn->real_escape_string($p['created_at']);
            $updatedAt = $objConn->real_escape_string($p['updated_at']);       
            $deleted = 0;

            $strSQL = "INSERT INTO pardot_list_membership (_pardotListMembershipID,
            _pardotListID,
            _pardotProspectID,
            _optedOut,
            _deleted,
            _createdAt,
            _updatedAt ) 
            VALUES('$pardotID',
            '$listID',
            '$prospectID',
            '$optedOut',
            '$deleted',
            '$createdAt',
            '$updatedAt' )
            ON DUPLICATE KEY UPDATE _pardotListID='$listID',
            _pardotProspectID='$prospectID',
            _optedOut='$optedOut',
            _deleted='$deleted',
            _createdAt='$createdAt',
            _updatedAt='$updatedAt' ;
            ";

            $objConn->query($strSQL);

            $affected = $objConn->affected_rows;
            if ($affected > 0) {
                $totalAffected++;
            }

            if ($debug) {
                echo $strSQL . '<br/>';
                echo 'List Membership inserted = ' . $affected . '<br/><br/>';
            }
            
        }



        echo 'Total List Memberships inserted / updated: ' . $totalAffected . '<br/>';
        echo 'Time spent - List Membership: ' . (time() - $start) . '<br/><br/>';

        // Update deleted flag
        $start = time();
        echo 'Query List Membership - Deleted only...<br/>';
        /* UPDATE
        $strSQL = "select DATE_SUB(MAX(_updatedAt), INTERVAL 1 SECOND)  from pardot_list_membership WHERE _deleted = 1";
        $prevMax = getSingleResult($strSQL);
        if (empty($prevMax)) {
            $prevMax = '2020-01-01 00:00:00';
        }

        $strSQL = "select MAX(_pardotListMembershipID) from pardot_list_membership WHERE _deleted = 1 AND _updatedAt >= '$prevMax'";
        $prevMaxID = getSingleResult($strSQL);
        if (empty($prevMaxID)) {
            $prevMaxID = '0';
        }

        // Call List Membership Query - only deleted
        $params = array(
            'limit'=>200, 
            'updated_after'=>$prevMax, 
            'id_greater_than'=>$prevMaxID,
            'sort_by'=>'id', 
            'sort_order' => 'ascending',
            'deleted' => 'true'
        );
        */

        // /* CREATE
        $strSQL = "select MAX(_pardotListMembershipID) from pardot_list_membership WHERE _deleted = 1";
        $prevMaxID = getSingleResult($strSQL);
        if (empty($prevMaxID)) {
            $prevMaxID = '0';
        }

        // Call List Membership Query - only deleted
        $params = array(
            'limit'=>200, 
            'id_greater_than'=>$prevMaxID,
            'sort_by'=>'id', 
            'sort_order' => 'ascending',
            'deleted' => 'true'
        );
        // */

        // Call List Query
        list($httpCode, $rsp) = $this->get('listMembership', 'query', $authHeader, $params);

        $rspArray = json_decode($rsp, true);

        if ($debug) {
            var_dump($rsp);
        }

        $totalAffected = 0;

        $result = $rspArray['result'];
        

        if (isset ($result['list_membership'])) {
            
            $listMemberships = $result['list_membership'];

            if (isset($listMemberships['id'])) { // only 1 result
                $listMembershipsArr = array(); // Make it into an array
                $listMembershipsArr[] = $listMemberships;
                $lists = $listMembershipsArr;
            }

            foreach ($listMemberships as $p) {
                $pardotID = $p['id'];
                $listID = $p['list_id'];
                $prospectID = $objConn->real_escape_string($p['prospect_id']);
                $optedOut = $objConn->real_escape_string($p['opted_out']);
                $createdAt = $objConn->real_escape_string($p['created_at']);
                $updatedAt = $objConn->real_escape_string($p['updated_at']);    
                $deleted = 1;

                $strSQL = "INSERT INTO pardot_list_membership (_pardotListMembershipID,
                _pardotListID,
                _pardotProspectID,
                _optedOut,
                _deleted,
                _createdAt,
                _updatedAt ) 
                VALUES('$pardotID',
                '$listID',
                '$prospectID',
                '$optedOut',
                '$deleted',
                '$createdAt',
                '$updatedAt' )
                ON DUPLICATE KEY UPDATE _pardotListID='$listID',
                _pardotProspectID='$prospectID',
                _optedOut='$optedOut',
                _deleted='$deleted',
                _createdAt='$createdAt',
                _updatedAt='$updatedAt' ;
                ";

                $objConn->query($strSQL);

                $affected = $objConn->affected_rows;
                if ($affected > 0) {
                    $totalAffected++;
                }

                if ($debug) {
                    echo $strSQL . '<br/>';
                    echo 'List Membership - updated "deleted" flag = ' . $affected . '<br/><br/>';
                }
                
            }



            echo 'Total List Memberships inserted / updated: ' . $totalAffected . '<br/>';
            echo 'Time spent - Deleted List Membership: ' . (time() - $start) . '<br/><br/>';
        
        }

        if ((sizeof($listMemberships) > 0) && $reload) {
        ?>    
            
            <script type = "text/javascript">
            window.onload=location.reload();
            </script>
            
        <?php
        }
    }


}




// Prepare to call version 3 or 4 of the API with JSON or XML responses
$client = new QueryListPardotApiClient(4, 'json');

// Authenticate to Pardot - Using API Keys
//$rsp = $client->executeRequestsWithApiKeys();
$rsp = $client->executeRequestsWithSalesforceOAuth($debug, $objConn, $reload);



?>