1

I want to build an admin panel that works like this:

User enters a date range and clicks "Show Report". The query results are then displayed in the admin panel. User clicks "Export to CSV" and a CSV file is opened showing the same data as the report currently on the screen.

Here's how it's currently working:

User enters a date range and clicks "Show Report" and query results are displayed. User clicks "Export to CSV" and it opens a CSV file with all the headers and formatting in the first 150 lines or so, then the report data is shown.

I feel like I have all the elements in place, just in the wrong order or something. Any help would be most appreciated!!

ADMINS: I am aware of this post Export data as CSV in back end with proper HTTP headers however this is not the same issue as I am not getting an error, I am not trying to download the file, and the provided solution ("Do not point the URL to admin.php, use admin-post.php instead") does not apply as my code does not post back to admin.php. Please do not close this as duplicate of that post.

Here is my code:

//set admin menu options
add_action('admin_menu', 'daily_payment_report_menu');


//add menu page
function daily_payment_report_menu() {
add_menu_page('Daily Payment Report', 'Daily Payment Report', 'manage_options', 'daily-payment-report', 'pullDailyPaymentReport');
}

function pullDailyPaymentReport() {


    echo "<h1>Daily Payment Report</h1><br>";
    echo '<form action=' . $_SERVER['PHP_SELF'] . '?page=daily-payment-report' . ' method="post">';
    echo '<table>';
        echo '<tr><th>Starting Date</th><th>Ending Date</th></tr>';
        echo '<tr><td><input type="text" name="startdate" placeholder="MM/DD/YYYY"></td><td><input type="text" name="endingdate" placeholder="MM/DD/YYYY"></td></tr>';
    echo '</table>';
    echo '<input type="submit" name="Display" value="Show Report" />   <input type="submit" name="Export" value="Export to Excel" />';
    echo '</form>';

        //echo '<div><form class="form-horizontal" action="" enctype="multipart/form-data" method="post" name="upload_excel">';
        //echo '<div class="form-group">';
        //echo '<div class="col-md-4 col-md-offset-4"><input class="btn btn-success" name="Export" type="submit" value="export to excel" /></div>';
        //echo '</div>';
        //echo '</form></div>';


global $wpdb;
global $query;
global $startdate;
global $endingdate;


//Set global variables for both operations
if (isset($_POST['Display']) or (isset($_POST['Export']))):

    //Store start date and end date from form
    $startdate = $_POST["startdate"];
    $endingdate = $_POST["endingdate"];

    $query = "SELECT 
    DatePaid.meta_value as DatePaid,
    Orders.id as OrderNumber,
    ItemMeta.meta_value as FileNumber,
    Amounts.meta_value as Amount,
    CONCAT(GuarFN.meta_value, ' ', GuarLN.meta_value) as BillingName,
    Auth.meta_value as AuthCode
FROM `wpyw_postmeta` Txns 
    INNER JOIN `wpyw_posts` Orders ON Txns.post_id = Orders.id
    INNER JOIN `wpyw_woocommerce_order_items` Items ON Txns.post_id = Items.order_id
    INNER JOIN `wpyw_woocommerce_order_itemmeta` ItemMeta ON Items.order_item_id = ItemMeta.order_item_id AND ItemMeta.meta_key = 'Eagle Recovery File Number'
    INNER JOIN `wpyw_postmeta` Amounts ON Txns.post_id = Amounts.post_id AND Amounts.meta_key = '_order_total' 
    INNER JOIN `wpyw_postmeta` DatePaid ON Txns.post_id = DatePaid.post_id AND DatePaid.meta_key = '_paid_date' 
INNER JOIN `wpyw_postmeta` GuarFN ON Txns.post_id = GuarFN.post_id AND GuarFN.meta_key = '_billing_first_name'
    INNER JOIN `wpyw_postmeta` GuarLN ON Txns.post_id = GuarLN.post_id AND GuarLN.meta_key = '_billing_last_name'
    INNER JOIN `wpyw_postmeta` Auth ON Txns.post_id = Auth.post_id AND Auth.meta_key = '_wc_usa_epay_credit_card_authorization_code'
WHERE Txns.meta_key = '_order_key' AND STR_TO_DATE(DatePaid.meta_value, '%Y-%m-%d') BETWEEN STR_TO_DATE('" . $startdate . "', '%m/%d/%Y') AND STR_TO_DATE('" . $endingdate . "', '%m/%d/%Y');";//CURDATE() = today


endif;


if (isset($_POST['Display'])):



    $applications = $wpdb->get_results($query);

    //Show results table
    echo '<div>';
    echo "<h1>Daily Payment Report</h1><br>";
    echo $wpdb->num_rows . " record(s) found";
    echo '<table class="widefat fixed" cellspacing="0">';
    echo "<tr><th>Date</th><th>Order Number</th><th>File Number</th><th>Total</th><th>Billing Name</th><th>Auth Code</th></tr>";

    foreach ($applications as $application) {
        echo "<tr><td>" . $application->DatePaid . "</td><td>" . $application->OrderNumber . "</td><td>" . $application->FileNumber . "</td><td>" . $application->Amount . "</td><td>" . $application->BillingName . "</td><td>" . $application->AuthCode . "</td></tr>";
    }

    echo "</table>";
    echo "</div>";         


elseif (isset($_POST['Export'])):

    //Begin attempt at CSV output

        //For debugging only:
        //echo $query;

        $date = date("Y-m-d");
        $filename = "Web Payment Report";
        $output = fopen('php://output', 'w');
        $result = $wpdb->get_results($query, ARRAY_A);
        fputcsv($output, array('DatePaid', 'OrderNumber', 'FileNumber', 'Amount', 'BillingName', 'AuthCode'));
        foreach ($result as $key => $value) {
            $modified_values = array(
                $value['DatePaid'],
                $value['OrderNumber'],
                $value['FileNumber'],
                $value['Amount'],
                $value['BillingName'],
                $value['AuthCode']
            );
            fputcsv($output, $modified_values);
        }
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Cache-Control: private", false);
        header("Content-Type: text/csv; charset=utf-8");
        header("Content-Disposition: attachment; filename=\"" . $date . " " . $filename . ".csv\";");
        header("Content-Transfer-Encoding: binary");
        exit;

endif;

}


?>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.