<?php
/**
1) Make sure we have our Modx Object.
2) Table names - adjust for Evo.
3) SQL with the fields we need - adjust for Evo.
4) Get a file handle.
5) Set the header so it outputs as csv.
6) Loop the results.
7) On the first iteration, set the csv headers from the DB field names.
8) Create csv row.
9) Output to browser.
*/
// 1)
if($modx) {
// 2)
$table1 = $modx->getFullTableName("user_attributes");
$table2 = $modx->getFullTableName("users");
// 3)
$sql = "SELECT
`ua`.`fullname`,
`ua`.`email`,
`ua`.`logincount`,
`ua`.`failedlogincount`,
DATE_FORMAT(FROM_UNIXTIME(`ua`.`lastlogin`),'%W, %M %e, %Y @ %h:%i %p') as `previous_login`,
DATE_FORMAT(FROM_UNIXTIME(`ua`.`thislogin`),'%W, %M %e, %Y @ %h:%i %p') as `last_login`
FROM $table1 `ua`
JOIN $table2 `u`
ON (`u`.`id` = `ua`.`id`)
WHERE `u`.`active` = '1'
ORDER BY `ua`.`fullname` ASC";
// You could check/refine the query in PhpMyAdmin...
// die($sql);
$result = $modx->db->query($sql);
if($modx->db->getRecordCount($result) >= 1) {
// 4)
$stdout = fopen('php://output', 'w');
// 5)
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export_userlogins_' . date("d-m-Y") . '.csv"');
$c = 0;
// 6)
while($row = $modx->db->getRow($result)) {
// 7)
if($c == 0) { fputcsv($stdout, array_keys($row)); }
// 8)
fputcsv($stdout, $row);
$c++;
}
} else {
return "None found";
}
// 9)
fclose($stdout);
exit;
} else {
return "Missing Modx Object";
}
?>
// $stdout = fopen('php://output', 'w');
// Would become...
$stdout = fopen("/var/www/lewis/reports/user/exportusers_" . date("d-m-Y-His") . ".csv", "w");
// Define the correct core path
if (!defined('MODX_CORE_PATH')) define('MODX_CORE_PATH', '/var/www/lewis/core/');
// Grab config file
include(MODX_CORE_PATH . 'config/config.core.php');
// Grab Modx class
include_once MODX_CORE_PATH . 'model/modx/modx.class.php';
// Init Modx
$modx= new modX();
$modx->initialize('mgr');
// Rest of original script here...
/**
1) Make sure we have our Modx Object.
2) Table names - adjust for Evo.
3) SQL with the fields we need - adjust for Evo.
4) Get a file handle.
5) Set the header so it outputs as csv.
6) Loop the results.
7) On the first iteration, set the csv headers from the DB field names.
8) Create csv row.
9) Output to browser.
*/
// 1)
if($modx) {
// 2)
$table1 = $modx->getFullTableName("web_user_attributes");
$table2 = $modx->getFullTableName("web_users");
$table3 = $modx->getFullTableName("foxycart_pins");
$table4 = $modx->getFullTableName("foxycart_subscriptions");
// 3)
$sql = "SELECT
`ua`.`fullname`,
`ua`.`email`,
`ua`.`logincount`,
`ua`.`failedlogincount`,
`ub`.`pin`,
`uc`.`start_date`,
`uc`.`exp_date`,
`uc`.`frequency`,
`uc`.`status`,
DATE_FORMAT(FROM_UNIXTIME(`ua`.`thislogin`),'%W, %M %e, %Y @ %h:%i %p') as `last_login`
FROM $table1 `ua`
JOIN $table3 `ub`, $table4 `uc`, $table2 `u`
ON (`u`.`id` = `ua`.`id`)
ORDER BY `ua`.`fullname` ASC";
// You could check/refine the query in PhpMyAdmin...
// die($sql);
$result = $modx->db->query($sql);
if($modx->db->getRecordCount($result) >= 1) {
// 4)
$stdout = fopen('php://output', 'w');
// 5)
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export_userlogins_' . date("d-m-Y") . '.csv"');
$c = 0;
// 6)
while($row = $modx->db->getRow($result)) {
// 7)
if($c == 0) { fputcsv($stdout, array_keys($row)); }
// 8)
fputcsv($stdout, $row);
$c++;
}
} else {
return "None found";
}
// 9)
fclose($stdout);
exit;
} else {
return "Missing Modx Object";
}