FloatBackOfffice/application/views/member/view_trackedemail.php

<link rel="stylesheet" href="/assets/css/query-builder.default.min.css">
<div id="overlay" style="display:none;">
    <div class="spinner"></div>
    <br />
    Exporting...
</div>
<div class="row">
    <div class="col-lg-12">
        <div class="panel panel-white">
            <div class="search-section">
                <form id="tracked-email-form" action='/member/trackedemail_search' method='GET' autocomplete="off">
                    <div class="form-group">
                        <div class="created form-group__item">
                            <label for="subject" class="font-weight-bold col-form-label">
                                Created
                            </label>
                            <div class="search-by-range">
                                <input type="search" name="start_created" id="start_created" value="<?= isset($start_created) ? $start_created : '' ?>" class="form-control" />
                                <input type="search" name="end_created" id="end_created" value="<?= isset($end_created) ? $end_created : '' ?>" class="form-control" />
                            </div>
                        </div>
                        <div class="message_date form-group__item">
                            <label for="subject" class="font-weight-bold col-form-label">
                                Message Date
                            </label>
                            <div class="search-by-range">
                                <input type="search" name="start_message_date" id="start_message_date" value="<?= isset($start_message_date) ? $start_message_date : '' ?>" class="form-control" />
                                <input type="search" name="end_message_date" id="end_message_date" value="<?= isset($end_message_date) ? $end_message_date : '' ?>" class="form-control" />
                            </div>
                        </div>

                        <div class="member_id_filter form-group__item">
                            <label for="member_id_filter" class="font-weight-bold col-form-label">
                                Member ID
                            </label>
                            <input type="search" name="member_id_filter" id="member_id_filter" value="<?= isset($member_id_filter) ? $member_id_filter : '' ?>" class="form-control" />
                        </div>
                        <div class="subject form-group__item">
                            <label for="subject" class="font-weight-bold col-form-label">
                                Subject
                            </label>
                            <input type="search" name="subject" id="subject" value="<?= isset($subject) ? $subject : '' ?>" class="form-control" />
                        </div>

                        <div class="sender form-group__item">
                            <label for="message_from" class="font-weight-bold col-form-label">
                                Sender
                            </label>
                            <input type="search" name="message_from" id="message_from" value="<?= isset($message_from) ? $message_from : '' ?>" class="form-control" />
                        </div>

                        <input type="hidden" id="sql_raw" name="sql_raw" value="<?= isset($sql_raw) ? $sql_raw : '' ?>">
                    </div>
                </form>

                <div id="query-builder" class="col-lg-6">
                    <div id="builder"></div>
                </div>

                <div class="btn-group">
                    <input type="button" name="search" id="search" value="Search" class="btn btn-info" />
                    <input type="button" id="exportZIP" value="Export" class="btn btn-warning" />
                </div>

                <span style="color:red" id="message"></span>
            </div>

            <div class="row col-lg-12 mt-10">
                <?= isset($link) ? $link : '' ?>
                <div class="panel" style="overflow: auto">
                    <?= $tracked_email_table ?>
                </div>
            </div>
        </div>
    </div>
</div>

<script src="/assets/js/plugins/pickers/datepicker.js"></script>
<script src="/assets/js/app.js"></script>
<script src="/assets/js/query-builder.standalone.min.js"></script>
<script src="/assets/js/plugins/forms/tags/tokenfield.min.js"></script>
<script type="text/javascript">
    $(document).ready(function() {
        // submit form when press enter
        $("#tracked-email-form input").keyup(function(e){
            if(e.which == 13 && !['end_created','start_message_date','end_message_date','start_created'].includes(e.target.id)){
               $('#search').click();
            }
        });
        $('#search').on('click', function(e) {
            e.preventDefault()
            append_sql_raw_to_submit_form()

            $('#tracked-email-form')
                .attr('method', 'GET')
                .submit()
        })

        $('#exportZIP').on('click', function(e) {
            $('#message').empty();
            e.preventDefault();

            append_sql_raw_to_submit_form();

            if ($('#member_id_filter').val().trim() === '') {
                $('#message').append('<b>Please input Member ID</b>');
                return;
            }

            $('#overlay').fadeIn();

            export_zip_file()
                .then((data) => {})
                .catch(error => {

                    $('message').append('<b>Something went wrong !!!</b>');

                }).finally(() => {

                    $('#overlay').fadeOut();

                });
        })

        function export_zip_file() {
            return new Promise(function(resolve, reject) {
                const params = {
                    proc: 'PROCESS',
                    action_name: 'EMAILRECIP',
                    filters: $('#tracked-email-form').serialize()
                };

                const xhr = new XMLHttpRequest();
                xhr.onerror = function() {
                    reject(Error("Network Error"));
                };

                xhr.open('POST', '/member/trackedemail_export_zip', true);
                xhr.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
                xhr.onload = function() {
                    if (xhr.status == 200) {
                        // get file name
                        let filename = "";
                        const disposition = xhr.getResponseHeader('Content-Disposition');
                        if (disposition && disposition.indexOf('attachment') !== -1) {
                            const filenameRegex = /filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/;
                            const matches = filenameRegex.exec(disposition);
                            if (matches != null && matches[1]) {
                                filename = matches[1].replace(/['"]/g, '');
                            }
                        }

                        let a = document.createElement('a');
                        let url = window.URL.createObjectURL(this.response);
                        a.href = url;
                        a.download = filename;
                        document.body.append(a);
                        a.click();
                        a.remove();

                        resolve(xhr.response);
                    } else if (xhr.status == 404) {

                        $('#message').append(`<b>Data Not Found!!!</b>`);
                        $('#overlay').fadeOut();

                    } else {

                        reject(Error(xhr.statusText));

                    }
                };

                xhr.responseType = 'blob';
                xhr.send(new URLSearchParams(params).toString());
            })
        }

        // input multi value
        $('#member_id_filter').tokenfield()

        addDatePicker(
            `#start_created,
            #end_created,
            #start_message_date,
            #end_message_date`
        );

        // query builder
        let DOMCACHESTORE = {};
        DOMCACHE = {
            get: function(selector, force) {
                if (DOMCACHESTORE[selector] !== undefined && !force) {
                    return DOMCACHESTORE[selector];
                }

                DOMCACHESTORE[selector] = $(selector);
                return DOMCACHESTORE[selector];
            }
        };

        const _filters  = [
            {
                id: 'DATE(date_parsed)',
                label: 'date_parsed',
                type: 'date',
                placeholder: '____/__/__',
                validation: {
                    format: 'YYYY/MM/DD'
                },
                operators: ['is_null', 'is_not_null']
            }
        ]

        let init_builder_query = {
            filters: _filters,
            conditions: ['AND'],
            default_group_flags: {
                // condition_readonly: true,
                no_add_group: true,
                no_delete: true
            }
        }

        // If sql_raw is empty then clear localstorage
        if (window.getURLParameter(window.location.href, 'sql_raw') === null) {
            window.localStorage.removeItem('rules');
        }

        const rule_basics = 
            JSON.parse(window.localStorage.getItem('rules'));

        if (rule_basics) {
            init_builder_query.rules = rule_basics;
        }

        DOMCACHE.get('#builder').queryBuilder(init_builder_query)

        // Fix for Bootstrap Datepicker
        DOMCACHE.get('#builder').on('afterUpdateRuleValue.queryBuilder', function(e, rule) {
            const date_picker = [
                'date_parsed'
            ]

            if (date_picker.indexOf(rule.filter.label) !== -1) {
                addDatePicker(rule.$el.find('.rule-value-container input'));
            }
        })

        // query builder
        function append_sql_raw_to_submit_form() {
            // set sql_raw to hidden item
            const result = DOMCACHE.get('#builder').queryBuilder('getSQL');
            window.localStorage.setItem(
                'rules',
                JSON.stringify(DOMCACHE.get('#builder').queryBuilder('getRules'))
            );

            if (result !== null && result.sql.length) {
                DOMCACHE
                    .get('#tracked-email-form')
                    .find('#sql_raw')
                    .val(result.sql);
            } else {
                DOMCACHE
                    .get('#tracked-email-form')
                    .find('#sql_raw')
                    .val('');
            }
        }
    })
</script>
<style>
    .search-section {
        align-items: center;
        display: flex;
    }
    .form-group {
        display: flex;
    }

    .search-by-range {
        display: flex;
    }

    #overlay {
        background: #ffffff;
        color: #666666;
        position: fixed;
        height: 100%;
        width: 100%;
        z-index: 5000;
        top: 0;
        left: 0;
        float: left;
        text-align: center;
        padding-top: 25%;
        opacity: .80;
    }

    .spinner {
        margin: 0 auto;
        height: 64px;
        width: 64px;
        animation: rotate 0.8s infinite linear;
        border: 5px solid firebrick;
        border-right-color: transparent;
        border-radius: 50%;
    }

    @keyframes rotate {
        0% {
            transform: rotate(0deg);
        }

        100% {
            transform: rotate(360deg);
        }
    }
</style>