<?php

header("Content-Type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename=spreadsheet.xls");

$link = mysqli_connect("localhost", "root", "root", "sjp");

if (!$link) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

$sql = "SELECT  exp_channel_titles.status, exp_channel_titles.entry_date, exp_channel_data.field_id_27, exp_channel_data.field_id_20, exp_channel_data.field_id_28, exp_channel_data.field_id_3, exp_channel_data.field_id_5, exp_channel_data.field_id_21 FROM exp_channel_titles JOIN exp_channel_data ON exp_channel_titles.entry_id = exp_channel_data.entry_id AND exp_channel_titles.status='open';";

$result = $link->query($sql);

//Set the column headings in the speadsheet
echo   "Status".
        "\t" . 'First Name' .
        "\t" . 'Last Name' .
        "\t" . 'Email' .
        "\t" . 'Partner Name' .
        "\t" . 'Startup' .
        "\t" . 'Cashflow' .
        "\t" . 'Reliefs' .
        "\t" . 'Risks'.
        "\t" . 'Rewarding' .
        "\t" . 'Senior'.
        "\t" . 'Capital'.
        "\t" . 'Aquiring'.
        "\t" . 'Expansion'.
        "\t" . 'Funds'.
        "\t" . 'Extraction'.
        "\t" . 'Sale'.
        "\t" . 'Generation'.
        "\t" . 'Exiting'.
        "\t" . 'Lifestlye' .
        "\t" . 'Date' . "\n";

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {

        $status         = $row["status"];
        $firstname      = $row["field_id_27"];
        $lastname       = $row["field_id_28"];
        $email          = $row["field_id_3"];
        $partner        = $row["field_id_5"];
        $partner2       = $row["field_id_20"];
        $date           = $row["entry_date"];
        $formatted_date = gmdate("d/m/Y", $date);

        $topics         = $row["field_id_21"];

        //Reset all topics to nothing
        $startup        = "";
        $cashflow       = "";
        $reliefs        = "";
        $risks          = "";
        $rewarding      = "";
        $senior         = "";
        $capital        = "";
        $aquiring       = "";
        $expansion      = "";
        $funds          = "";
        $extraction     = "";
        $sale           = "";
        $generation     = "";
        $exiting        = "";
        $lifestyle      = "";

        //If topic keyword exists in topics string, set to "Yes"
        if (strpos($topics, "Startup")      !== false) { $startup       = "Yes"; }
        if (strpos($topics, "Flow")         !== false) { $cashflow      = "Yes"; }
        if (strpos($topics, "Reliefs")      !== false) { $reliefs       = "Yes"; }
        if (strpos($topics, "risks")        !== false) { $risks         = "Yes"; }
        if (strpos($topics, "Rewarding")    !== false) { $rewarding     = "Yes"; }
        if (strpos($topics, "Senior")       !== false) { $senior        = "Yes"; }
        if (strpos($topics, "Efficiently")  !== false) { $Capital       = "Yes"; }
        if (strpos($topics, "Acquiring")    !== false) { $aquiring      = "Yes"; }
        if (strpos($topics, "Expansion")    !== false) { $expansion     = "Yes"; }
        if (strpos($topics, "Funds")        !== false) { $funds         = "Yes"; }
        if (strpos($topics, "Extraction")   !== false) { $extraction    = "Yes"; }
        if (strpos($topics, "Sale")         !== false) { $sale          = "Yes"; }
        if (strpos($topics, "Generation")   !== false) { $generation    = "Yes"; }
        if (strpos($topics, "Exiting")      !== false) { $exiting       = "Yes"; }
        if (strpos($topics, "Lifestyle")    !== false) { $lifestyle     = "Yes"; }

        //Export the data to rows in the spreadsheet
        echo   $status .
        "\t" . $firstname .
        "\t" . $lastname .
        "\t" . $email .
        "\t" . $partner . $partner2 .
        "\t" . $startup .
        "\t" . $cashflow .
        "\t" . $reliefs .
        "\t" . $risks .
        "\t" . $rewarding .
        "\t" . $senior .
        "\t" . $capital .
        "\t" . $aquiring .
        "\t" . $expansion .
        "\t" . $funds .
        "\t" . $extraction .
        "\t" . $sale .
        "\t" . $generation .
        "\t" . $exiting .
        "\t" . $lifestyle .
        "\t" . $formatted_date .  "\n";
    }

} else {
    echo "0 results";
}

?>