More MySQL purging
[contractdashboard.git] / exportData.csv.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
<?php
 
include_once("./lib/common.inc.php");
setlocale(LC_CTYPE, 'C');
// source: http://stackoverflow.com/questions/81934/easy-way-to-export-a-sql-table-without-access-to-the-server-or-phpmyadmin#81951
 
$unspsc = Array();
$unspscresult = $conn->prepare('select * from "UNSPSCcategories" where "UNSPSC"::text like \'%00000\';');
$unspscresult->execute();
foreach ($unspscresult->fetchAll() as $row) {
    $unspsc[$row['UNSPSC']] = $row['Title'];
}
 
$query = $conn->prepare('
SELECT "CNID",contractnotice."agencyName",agency.abn as "agencyABN",
EXTRACT(EPOCH FROM "publishDate") as "publishDate",
EXTRACT(EPOCH FROM "contractStart") as "contractStart",
EXTRACT(EPOCH FROM "contractEnd") as "contractEnd",
value,description,"procurementMethod",category,"categoryUNSPSC",
(substr( "categoryUNSPSC"::text, 0, 2 ) || \'0000000\'::text) as "cat1",  
 (substr( "categoryUNSPSC"::text, 0, 3 ) || \'000000\'::text) as "cat2",
 (substr( "categoryUNSPSC"::text, 0, 4 ) || \'00000\'::text) as "cat3",
 "supplierABN","supplierName",
(
 case when "supplierABN" != 0 THEN "supplierABN"::text ELSE "supplierName" END) as supplierID,
 
(\'https://www.tenders.gov.au/?event=public.advancedsearch.keyword&keyword=CN\'::text || "CNID"::text) as sourceURL 
FROM contractnotice join agency on contractnotice."agencyName"=agency."agencyName"  
where "childCN" is null'
        , array(PDO::ATTR_CURSOR => PDO::FETCH_ORI_NEXT));
// "supplierCity","supplierPostcode","supplierCountry","contactPostcode",
// 
$query->execute();
$errors = $conn->errorInfo();
if ($errors[2] != "") {
    die("Export terminated, db error" . print_r($errors, true));
}
 
$num_fields = $query->columnCount();
$headers = Array();
for ($i = 0; $i < $num_fields; $i++) { // for each column in query, make a CSV header
    $meta = $query->getColumnMeta($i);
    $headers[] = $meta['name'];
}
$fp = fopen('php://output', 'w');
if ($fp && $query) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.' . date("c") . '.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    while ($row = $query->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
        foreach ($row as $key => &$colvalue) {
 
            $colvalue = preg_replace('/[^[:print:]]/', '', utf8_encode($colvalue));
            if ($headers[$key] == "publishDate" || $headers[$key] == "contractStart"
                    || $headers[$key] == "contractEnd") {
                $colvalue = date("Y-m-d", $colvalue);
            }
            if ($headers[$key] == "cat1" || $headers[$key] == "cat2"
                    || $headers[$key] == "cat3") {
                $colvalue = $unspsc[$colvalue];
            }
        }
        fputcsv($fp, array_values($row));
    }
    die;
}
?>