ImportFlamingoEntries.php

class ImportFlamingoEntries
{
    public $db;
    public $form;
    public $tags;
    public $track;
    public $vxcf;

    public $imported = 0;

    public function __construct()
    {
        global $wpdb;

        $form = WPCF7_ContactForm::get_instance(2935);
        $vxcf = new vxcf_form();

        $this->db = $wpdb;
        $this->form = $form;
        $this->tags = (array) vxcf_form::get_form_fields('cf_'.$form->id());
        $this->track = $vxcf->track_form_entry('cf', $form->id());
        $this->vxcf = $vxcf;
    }

    public function getOldEntries($from = 0, $to = 100)
    { // 146
        $limit = intval($to) - intval($from);
        $offset = $from;
        return $this->db->get_results("
            SELECT 
                p.ID,
                p.post_status,
                p.post_date,
                p.post_modified,
                m1.meta_value AS your_name,
                m2.meta_value AS your_number,
                m3.meta_value AS your_email,
                m4.meta_value AS neighbourhood,
                m5.meta_value AS your_message,
                m6.meta_value AS meta
            FROM {$this->db->posts} p
            INNER JOIN {$this->db->postmeta} m1 on p.ID = m1.post_id
            INNER JOIN {$this->db->postmeta} m2 on p.ID = m2.post_id
            INNER JOIN {$this->db->postmeta} m3 on p.ID = m3.post_id
            INNER JOIN {$this->db->postmeta} m4 on p.ID = m4.post_id
            INNER JOIN {$this->db->postmeta} m5 on p.ID = m5.post_id
            INNER JOIN {$this->db->postmeta} m6 on p.ID = m6.post_id
            WHERE p.post_type = 'flamingo_inbound'
            AND m1.meta_key = '_field_your-name'
            AND m2.meta_key = '_field_your-number'
            AND m3.meta_key = '_field_your-email'
            AND m4.meta_key = '_field_your-address'
            AND m5.meta_key = '_field_your-message'
            AND m6.meta_key = '_meta'
            LIMIT {$limit}
            OFFSET {$offset}
        ");
    }

    public function getNewEntries($from = 0, $to = 100)
    { // 1077
        $limit = intval($to) - intval($from);
        $offset = $from;
        return $this->db->get_results("
            SELECT 
                p.ID,
                p.post_status,
                p.post_date,
                p.post_modified,
                m1.meta_value AS your_name,
                m2.meta_value AS your_number,
                m3.meta_value AS your_email,
                m4.meta_value AS house_number,
                m5.meta_value AS street_name,
                m6.meta_value AS neighbourhood,
                m7.meta_value AS your_message,
                m8.meta_value AS meta
            FROM {$this->db->posts} p
            INNER JOIN {$this->db->postmeta} m1 on p.ID = m1.post_id
            INNER JOIN {$this->db->postmeta} m2 on p.ID = m2.post_id
            INNER JOIN {$this->db->postmeta} m3 on p.ID = m3.post_id
            INNER JOIN {$this->db->postmeta} m4 on p.ID = m4.post_id
            INNER JOIN {$this->db->postmeta} m5 on p.ID = m5.post_id
            INNER JOIN {$this->db->postmeta} m6 on p.ID = m6.post_id
            INNER JOIN {$this->db->postmeta} m7 on p.ID = m7.post_id
            INNER JOIN {$this->db->postmeta} m8 on p.ID = m8.post_id
            WHERE p.post_type = 'flamingo_inbound'
            AND m1.meta_key = '_field_your-name'
            AND m2.meta_key = '_field_your-number'
            AND m3.meta_key = '_field_your-email'
            AND m4.meta_key = '_field_house-number'
            AND m5.meta_key = '_field_street-name'
            AND m6.meta_key = '_field_neighbourhood'
            AND m7.meta_key = '_field_your-message'
            AND m8.meta_key = '_meta'
            LIMIT {$limit}
            OFFSET {$offset}
        ");
    }

    public function getRemainingEntries()
    {
        return $this->db->get_results("
            SELECT 
                p.ID,
                p.post_status,
                p.post_date,
                p.post_modified,
                m1.meta_value AS your_name,
                m2.meta_value AS your_number,
                m3.meta_value AS your_email,
                m4.meta_value AS house_number,
                m5.meta_value AS street_name,
                m6.meta_value AS neighbourhood,
                m7.meta_value AS your_message,
                m8.meta_value AS meta
            FROM {$this->db->posts} p
            INNER JOIN {$this->db->postmeta} m1 on p.ID = m1.post_id
            INNER JOIN {$this->db->postmeta} m2 on p.ID = m2.post_id
            INNER JOIN {$this->db->postmeta} m3 on p.ID = m3.post_id
            INNER JOIN {$this->db->postmeta} m4 on p.ID = m4.post_id
            INNER JOIN {$this->db->postmeta} m5 on p.ID = m5.post_id
            INNER JOIN {$this->db->postmeta} m6 on p.ID = m6.post_id
            INNER JOIN {$this->db->postmeta} m7 on p.ID = m7.post_id
            INNER JOIN {$this->db->postmeta} m8 on p.ID = m8.post_id
            WHERE p.post_type = 'flamingo_inbound'
            AND p.post_status = 'flamingo-spam'
            AND p.ID in (4430,4627,3915,4377,4585,4510,3570,4211,4325,4344,4282,3085,4268,4326,3731,3732,4503,3263,4537,4431,3844,4686,3964,3862,4164,4721,4620,4702)
            AND m1.meta_key = '_field_your-name'
            AND m2.meta_key = '_field_your-number'
            AND m3.meta_key = '_field_your-email'
            AND m4.meta_key = '_field_house-number'
            AND m5.meta_key = '_field_street-name'
            AND m6.meta_key = '_field_neighbourhood'
            AND m7.meta_key = '_field_your-message'
            AND m8.meta_key = '_meta'
        ");
    }

    public function exists($entry)
    {
        $results = $this->db->get_results(
            $this->db->prepare("
                SELECT l.id
                FROM {$this->db->prefix}vxcf_leads l
                INNER JOIN {$this->db->prefix}vxcf_leads_detail d1 ON l.id = d1.lead_id
                INNER JOIN {$this->db->prefix}vxcf_leads_detail d2 ON l.id = d2.lead_id
                INNER JOIN {$this->db->prefix}vxcf_leads_detail d3 ON l.id = d3.lead_id
                INNER JOIN {$this->db->prefix}vxcf_leads_detail d4 ON l.id = d4.lead_id
                WHERE 1=1
                AND (d1.name = 'your-name' AND d1.value = '%s')
                AND (d2.name = 'your-number' AND d2.value = '%s')
                AND (d3.name = 'your-email' AND d3.value = '%s')
                AND (d4.name = 'your-message' AND d4.value = '%s')
            ", $entry->your_name, $entry->your_number, $entry->your_email, $entry->your_message)
        );
        return !empty($results);
    }

    public function isDuplicate($entry)
    {
        $results = $this->db->get_results(
            $this->db->prepare("
                SELECT p.ID
                FROM {$this->db->posts} p
                INNER JOIN {$this->db->postmeta} m1 ON p.ID = m1.post_id
                INNER JOIN {$this->db->postmeta} m2 ON p.ID = m2.post_id
                INNER JOIN {$this->db->postmeta} m3 ON p.ID = m3.post_id
                INNER JOIN {$this->db->postmeta} m4 ON p.ID = m4.post_id
                WHERE p.post_status = 'publish'
                AND (m1.meta_key = '_field_your-name' AND m1.meta_value = '%s')
                AND (m1.meta_key = '_field_your-number' AND m2.meta_value = '%s')
                AND (m1.meta_key = '_field_your-email' AND m3.meta_value = '%s')
                AND (m1.meta_key = '_field_your-message' AND m4.meta_value = '%s')
            ", $entry->your_name, $entry->your_number, $entry->your_email, $entry->your_message)
        );
        return !empty($results);
    }

    public function migrateNew($from = 0, $to = 100)
    {
        $results = $this->getNewEntries($from, $to);
        foreach ($results as $result) {
            if ($this->exists($result)) {
                continue;
            }
            $this->createEntry($result);
            $this->sendEntry($result);
        }
    }

    public function migrateOld($from = 0, $to = 100)
    {
        $results = $this->getOldEntries($from, $to);
        foreach ($results as $result) {
            if ($this->exists($result)) {
                continue;
            }
            $this->createEntry($result);
            $this->sendEntry($result);
        }
    }

    public function createEntry($entry)
    {
        $lead = [];
        foreach ($this->tags as $tag => $field) {
            $key = str_replace('-', '_', $field['name']);
            $lead[$tag] = isset($entry->{$key})
                ? wp_unslash($entry->{$key})
                : null;
        }
        $entry->entry_id = $this->vxcf->create_entry($lead, [
            'id' => $this->form->id(),
            'name' => $this->form->title(),
            'fields' => $this->tags,
        ], 'cf', '', $this->track);
        $this->cleanup($entry);
    }

    public function sendEntry($entry)
    {
        if ($entry->post_status !== 'flamingo-spam') {
            return;
        }
        $_POST = [
            '_wpcf7' => $this->form->id(),
            '_wpcf7_container_post' => $this->form->id(),
            '_wpcf7_locale' => $this->form->locale(),
            '_wpcf7_quiz_answer_captcha' => 'c6d314ba9edf0215bb827fe0dcbb2a7c',
            '_wpcf7_unit_tag' => 'wpcf7-f2935-p2931-o1',
            '_wpcf7_version' => WPCF7_VERSION,
            'captcha' => 5,
            'house-number' => isset($entry->house_number) ? $entry->house_number : 'n/a',
            'neighbourhood' => isset($entry->neighbourhood) ? $entry->neighbourhood : (isset($entry->your_address) ? $entry->your_address : 'unknown'),
            'street-name' => isset($entry->street_name) ? $entry->street_name : 'n/a',
            'vx_url' => 'https://k1britanniafoundation.test/request-assistance/',
            'your-email' => $entry->your_email,
            'your-message' => $entry->your_message,
            'your-name' => $entry->your_name,
            'your-number' => $entry->your_number,
        ];
        $submission = WPCF7_Submission::get_instance($this->form);
        if ($submission && 'mail_sent' === $submission->get_status()) {
            glsr_log('sent email to: '.$entry->your_email);
            wp_update_post([
                'ID' => $entry->ID,
                'post_status' => 'publish',
            ]);
        }
    }

    public function sendNewEmails($from = 0, $to = 100)
    {
        $results = $this->getNewEntries($from, $to);
        foreach ($results as $result) {
            if (!$this->isDuplicate($result)) {
                $this->sendEntry($result);
            }
        }
    }

    public function sendOldEmails($from = 0, $to = 100)
    {
        $results = $this->getOldEntries($from, $to);
        foreach ($results as $result) {
            if (!$this->isDuplicate($result)) {
                $this->sendEntry($result);
            }
        }
    }

    public function sendRemainingEmails()
    {
        $results = $this->getRemainingEntries();
        foreach ($results as $result) {
            $this->sendEntry($result);
        }
    }

    public function cleanup($entry)
    {
        $meta = wp_parse_args(unserialize($entry->meta), [
            'remote_ip' => null,
            'url' => null,
            'user_agent' => null,
            'user_email' => null,
        ]);
        $browser = wp_parse_args(vxcf_form::browser_info($meta['user_agent']), [
            'name' => null,
            'platform' => null,
        ]);
        $user = get_user_by('email', $meta['user_email']);
        $userId = $user ? $user->ID : 0;
        $visId = $this->db->get_var($this->db->prepare(
            "SELECT vis_id FROM {$this->db->prefix}vxcf_leads WHERE ip = '%s' AND (browser = '%s' AND os = '%s')",
            $meta['remote_ip'],
            $browser['platform'],
            $browser['name']
        ));
        if (empty($visId)) {
            $visId = uniqid().time().rand(9,99999999);
        }
        $this->db->update($this->db->prefix.'vxcf_leads', [
            'browser' => $browser['platform'],
            'created' => $entry->post_date,
            'ip' => $meta['remote_ip'],
            'os' => $browser['name'],
            'screen' => null,
            'updated' => $entry->post_modified,
            'url' => $meta['url'],
            'user_id' => $userId,
            'vis_id' => $visId,
        ], ['id' => $entry->entry_id]);
    }
}

add_action('admin_init', function () {
    $import = new ImportFlamingoEntries();
    // $import->sendRemainingEmails();
});