diff options
Diffstat (limited to '')
| -rw-r--r-- | admin/survey/modules/mod_MAZA/class.MAZAExport.php | 325 |
1 files changed, 325 insertions, 0 deletions
diff --git a/admin/survey/modules/mod_MAZA/class.MAZAExport.php b/admin/survey/modules/mod_MAZA/class.MAZAExport.php new file mode 100644 index 0000000..cfb6863 --- /dev/null +++ b/admin/survey/modules/mod_MAZA/class.MAZAExport.php @@ -0,0 +1,325 @@ +<?php
+
+/**
+ * Created on 23.1.2018
+ *
+ * @author: Uroš Podkrižnik
+ *
+ * @desc: for exports of module MAZA - mobile app for respondents
+ *
+ *
+ */
+class MAZAExport {
+
+ var $_ank_id; // id ankete
+
+ function __construct($ank_id = 0) {
+ $this->_ank_id = $ank_id;
+ }
+
+ /*private function getDataInactiveIdentifiers() {
+ $Query = "SELECT au.datetime_inserted, au.identifier FROM maza_app_users AS au " .
+ "LEFT JOIN (SELECT ank_id, maza_user_id FROM maza_user_srv_access) AS sa " .
+ "ON au.id = sa.maza_user_id WHERE sa.ank_id='$this->_ank_id' AND au.datetime_last_active IS NULL;";
+
+ $podatki = array(array('inserted', 'identifier'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($sql_row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($sql_row['datetime_inserted'], $sql_row['identifier']);
+ }
+ }
+
+ return $podatki;
+ }
+
+ private function getDataActiveIdentifiers() {
+ $Query = "SELECT au.datetime_inserted, au.identifier, au.datetime_last_active, au.deviceInfo, au.tracking_log FROM maza_app_users AS au " .
+ "LEFT JOIN (SELECT ank_id, maza_user_id FROM maza_user_srv_access) AS sa " .
+ "ON au.id = sa.maza_user_id WHERE sa.ank_id='$this->_ank_id' AND au.datetime_last_active IS NOT NULL;";
+
+ $podatki = array(array('inserted', 'identifier', 'last_active', 'device_info', 'tracking_log'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($sql_row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($sql_row['datetime_inserted'], $sql_row['identifier'], $sql_row['datetime_last_active'], $sql_row['deviceInfo'], $sql_row['tracking_log']);
+ }
+ }
+
+ return $podatki;
+ }*/
+
+ private function getDataIdentifiers() {
+ $act = isset($_POST['maza_active_ident']) && $_POST['maza_active_ident'] == 1;
+ $inact = isset($_POST['maza_inactive_ident']) && $_POST['maza_inactive_ident'] == 1;
+ $deact = isset($_POST['maza_deactive_ident']) && $_POST['maza_deactive_ident'] == 1;
+
+ $where = '';
+ if($act && !$inact)
+ $where .= ' AND au.datetime_last_active IS NOT NULL';
+ else if(!$act && $inact && $deact)
+ $where .= ' AND (au.datetime_last_active IS NULL OR sa.datetime_unsubscribed IS NOT NULL)';
+ else if(!$act && $inact)
+ $where .= ' AND au.datetime_last_active IS NULL';
+ if($deact && !$act && !$inact)
+ $where .= ' AND sa.datetime_unsubscribed IS NOT NULL';
+ else if(!$deact)
+ $where .= ' AND sa.datetime_unsubscribed IS NULL';
+
+ $Query = "SELECT au.datetime_inserted, au.identifier, au.datetime_last_active, au.deviceInfo, au.tracking_log, sa.datetime_unsubscribed FROM maza_app_users AS au " .
+ "LEFT JOIN (SELECT ank_id, maza_user_id, datetime_unsubscribed FROM maza_user_srv_access) AS sa " .
+ "ON au.id = sa.maza_user_id WHERE sa.ank_id='$this->_ank_id'$where;";
+
+ $podatki = array(array('inserted', 'identifier', 'last_active', 'device_info', 'tracking_log', 'unsubscribed'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($sql_row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($sql_row['datetime_inserted'], $sql_row['identifier'], $sql_row['datetime_last_active'], $sql_row['deviceInfo'], $sql_row['tracking_log'], $sql_row['datetime_unsubscribed']);
+ }
+ }
+
+ return $podatki;
+ }
+
+ private function getDataTrackingLocations() {
+ $Query = "SELECT au.identifier, ml.maza_user_id, ml.lat, ml.lng, ml.provider, ml.timestamp, ml.accuracy, ml.altitude, "
+ . "ml.bearing, ml.speed, ml.vertical_acc, ml.speed_acc, ml.bearing_acc, ml.extras, ml.is_mock FROM maza_user_locations AS ml "
+ . "LEFT JOIN (SELECT ank_id, maza_user_id, datetime_started FROM maza_user_srv_access) AS sa "
+ . "ON ml.maza_user_id = sa.maza_user_id "
+ . "LEFT JOIN (SELECT id, identifier FROM maza_app_users) AS au ON au.id = ml.maza_user_id "
+ . "WHERE sa.ank_id='$this->_ank_id' AND sa.datetime_started IS NOT NULL AND sa.datetime_started <= ml.timestamp "
+ . "ORDER BY sa.datetime_started, au.identifier, ml.timestamp DESC;";
+
+ $podatki = array(array('Identifier', 'timestamp', 'latitude', 'longitude', 'provider', 'accuracy',
+ 'altitude', 'bearing', 'speed', 'vertical_acc', 'speed_acc', 'bearing_acc', 'extras', 'is_mock'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($row['identifier'], $row['timestamp'], $row['lat'], $row['lng'], $row['provider'],
+ $row['accuracy'], $row['altitude'], $row['bearing'], $row['speed'], $row['vertical_acc'], $row['speed_acc'],
+ $row['bearing_acc'], $row['extras'], $row['is_mock']);
+ }
+ }
+ return $podatki;
+ }
+
+ private function getDataTrackingAR() {
+ $Query = "SELECT au.identifier, ml.maza_user_id, ml.timestamp, ml.in_vehicle, ml.on_bicycle, ml.on_foot, ml.walking, "
+ . "ml.running, ml.still, ml.tilting, ml.unknown FROM maza_user_activity_recognition AS ml "
+ . "LEFT JOIN (SELECT ank_id, maza_user_id, datetime_started FROM maza_user_srv_access) AS sa "
+ . "ON ml.maza_user_id = sa.maza_user_id "
+ . "LEFT JOIN (SELECT id, identifier FROM maza_app_users) AS au ON au.id = ml.maza_user_id "
+ . "WHERE sa.ank_id='$this->_ank_id' AND sa.datetime_started IS NOT NULL AND sa.datetime_started <= ml.timestamp "
+ . "ORDER BY sa.datetime_started, au.identifier, ml.timestamp DESC;";
+
+ $podatki = array(array('identifier', 'timestamp', 'in_vehicle', 'on_bicycle', 'on_foot', 'walking', 'running',
+ 'still', 'tilting', 'unknown'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($row['identifier'], $row['timestamp'], $row['in_vehicle'], $row['on_bicycle'], $row['on_foot'],
+ $row['walking'], $row['running'], $row['still'], $row['tilting'], $row['unknown']);
+ }
+ }
+
+ return $podatki;
+ }
+
+ private function getDataAlarmRespondents() {
+ $Query = "SELECT au.identifier, su.maza_user_id, su.srv_user_id, su.mode, su.srv_version_datetime, res.id, res.recnum, re.ank_id FROM maza_srv_users AS su "
+ . "LEFT JOIN (SELECT ank_id, maza_user_id, datetime_started FROM maza_user_srv_access) AS sa ON su.maza_user_id = sa.maza_user_id "
+ . "LEFT JOIN (SELECT ank_id FROM maza_srv_repeaters) AS re ON re.ank_id = sa.ank_id "
+ . "LEFT JOIN (SELECT id, identifier FROM maza_app_users) AS au ON au.id = su.maza_user_id "
+ . "LEFT JOIN (SELECT id, recnum FROM srv_user) AS res ON res.id = su.srv_user_id "
+ . "WHERE sa.ank_id='$this->_ank_id' AND re.ank_id='$this->_ank_id' AND sa.datetime_started IS NOT NULL AND (mode='repeater' OR (su.geof_id IS NULL AND su.activity_id IS NULL)) "//todo zadnji OR je samo zacasno, pobrisi enkrat po APP verziji 25
+ . "ORDER BY sa.datetime_started, au.identifier, su.srv_version_datetime DESC;";
+
+ $podatki = array(array('identifier', 'recnum', 'time_version'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($row['identifier'], $row['recnum'], $row['srv_version_datetime']);
+ }
+ }
+ return $podatki;
+ }
+
+ private function getDataTriggeredGeofences() {
+ $Query = "SELECT tg.id, tg.maza_user_id, tg.geof_id, tg.triggered_timestamp, tg.enter_timestamp, tg.dwell_timestamp, au.identifier, ge.name, su.maza_user_id, su.srv_user_id, su.srv_version_datetime, su.mode, su.tgeof_id, res.id, res.recnum "
+ . ", ml.maza_user_id, ml.lat, ml.lng, ml.provider, ml.timestamp, ml.accuracy, ml.altitude, ml.bearing, ml.speed, ml.vertical_acc, ml.speed_acc, ml.bearing_acc, ml.extras, ml.is_mock"
+ . " FROM maza_srv_triggered_geofences AS tg "
+ . "LEFT JOIN (SELECT tgeof_id, maza_user_id, lat, lng, provider, timestamp, accuracy, altitude, bearing, speed, vertical_acc, speed_acc, bearing_acc, extras, is_mock FROM maza_user_locations) AS ml ON tg.id = ml.tgeof_id "
+ . "LEFT JOIN (SELECT ank_id, id, name FROM maza_srv_geofences) AS ge ON ge.id = tg.geof_id "
+ . "LEFT JOIN (SELECT ank_id, maza_user_id, datetime_started FROM maza_user_srv_access) AS sa ON tg.maza_user_id = sa.maza_user_id "
+ . "LEFT JOIN (SELECT id, identifier FROM maza_app_users) AS au ON au.id = tg.maza_user_id "
+ . "LEFT JOIN (SELECT maza_user_id, srv_user_id, srv_version_datetime, tgeof_id, mode FROM maza_srv_users) AS su ON su.maza_user_id = tg.maza_user_id AND su.tgeof_id = tg.id "
+ . "LEFT JOIN (SELECT id, recnum FROM srv_user) AS res ON res.id = su.srv_user_id "
+ . "WHERE sa.ank_id='$this->_ank_id' AND ge.ank_id='$this->_ank_id' AND sa.datetime_started IS NOT NULL "
+ . "ORDER BY sa.datetime_started, au.identifier, tg.triggered_timestamp DESC;";
+
+ $podatki = array(array('identifier', 'recnum', 'geofence_id', 'geofence_name', 'triggered_timestamp', 'enter_timestamp', 'dwell_timestamp', 'location_timestamp', 'latitude', 'longitude', 'provider', 'accuracy',
+ 'altitude', 'bearing', 'speed', 'vertical_acc', 'speed_acc', 'bearing_acc', 'extras', 'is_mock'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($row['identifier'], $row['recnum'], $row['geof_id'], $row['name'], $row['triggered_timestamp'], $row['enter_timestamp'], $row['dwell_timestamp'],
+ $row['timestamp'], $row['lat'], $row['lng'], $row['provider'], $row['accuracy'], $row['altitude'], $row['bearing'], $row['speed'], $row['vertical_acc'], $row['speed_acc'],
+ $row['bearing_acc'], $row['extras'], $row['is_mock']);
+ }
+ }
+ return $podatki;
+ }
+
+ private function getDataTriggeredGeofencesAnswers() {
+ $Query = "SELECT tg.id, tg.maza_user_id, tg.geof_id, tg.triggered_timestamp, au.identifier, ge.name, su.maza_user_id, su.srv_user_id, su.srv_version_datetime, su.mode, su.tgeof_id, res.id, res.recnum "
+ . ", ml.maza_user_id, ml.lat, ml.lng, ml.provider, ml.timestamp, ml.accuracy, ml.altitude, ml.bearing, ml.speed, ml.vertical_acc, ml.speed_acc, ml.bearing_acc, ml.extras, ml.is_mock"
+ . " FROM maza_srv_triggered_geofences AS tg "
+ . "LEFT JOIN (SELECT tgeof_id, maza_user_id, lat, lng, provider, timestamp, accuracy, altitude, bearing, speed, vertical_acc, speed_acc, bearing_acc, extras, is_mock FROM maza_user_locations) AS ml ON tg.id = ml.tgeof_id "
+ . "LEFT JOIN (SELECT ank_id, id, name FROM maza_srv_geofences) AS ge ON ge.id = tg.geof_id "
+ . "LEFT JOIN (SELECT ank_id, maza_user_id, datetime_started FROM maza_user_srv_access) AS sa ON tg.maza_user_id = sa.maza_user_id "
+ . "LEFT JOIN (SELECT id, identifier FROM maza_app_users) AS au ON au.id = tg.maza_user_id "
+ . "LEFT JOIN (SELECT maza_user_id, srv_user_id, srv_version_datetime, tgeof_id, mode FROM maza_srv_users) AS su ON su.maza_user_id = tg.maza_user_id AND su.tgeof_id = tg.id "
+ . "LEFT JOIN (SELECT id, recnum FROM srv_user) AS res ON res.id = su.srv_user_id "
+ . "WHERE sa.ank_id='$this->_ank_id' AND ge.ank_id='$this->_ank_id' AND sa.datetime_started IS NOT NULL AND su.mode='geofence' "
+ . "ORDER BY res.recnum DESC;";
+
+ $podatki = array(array('identifier', 'recnum', 'geofence_id', 'geofence_name', 'triggered_timestamp', 'location_timestamp', 'latitude', 'longitude', 'provider', 'accuracy',
+ 'altitude', 'bearing', 'speed', 'vertical_acc', 'speed_acc', 'bearing_acc', 'extras', 'is_mock'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($row['identifier'], $row['recnum'], $row['geof_id'], $row['name'], $row['triggered_timestamp'], $row['timestamp'], $row['lat'], $row['lng'], $row['provider'],
+ $row['accuracy'], $row['altitude'], $row['bearing'], $row['speed'], $row['vertical_acc'], $row['speed_acc'],
+ $row['bearing_acc'], $row['extras'], $row['is_mock']);
+ }
+ }
+ return $podatki;
+ }
+
+ private function getDataEntryLocations() {
+ $Query = "SELECT au.identifier, su.maza_user_id, su.srv_user_id, su.srv_version_datetime, su.mode, res.id, res.recnum, su.loc_id, ml.id "
+ . ", ml.maza_user_id, ml.lat, ml.lng, ml.provider, ml.timestamp, ml.accuracy, ml.altitude, ml.bearing, ml.speed, ml.vertical_acc, ml.speed_acc, ml.bearing_acc, ml.extras, ml.is_mock"
+ . " FROM maza_srv_users AS su "
+ . "LEFT JOIN (SELECT ank_id, maza_user_id, datetime_started FROM maza_user_srv_access) AS sa ON su.maza_user_id = sa.maza_user_id "
+ . "LEFT JOIN (SELECT id, identifier FROM maza_app_users) AS au ON au.id = su.maza_user_id "
+ . "LEFT JOIN (SELECT * FROM maza_user_locations) AS ml ON su.maza_user_id = ml.maza_user_id AND su.loc_id = ml.id "
+ . "LEFT JOIN (SELECT id, recnum FROM srv_user) AS res ON res.id = su.srv_user_id "
+ . "WHERE sa.ank_id='$this->_ank_id' AND sa.datetime_started IS NOT NULL AND su.mode='entry' "
+ . "ORDER BY res.recnum DESC;";
+
+ $podatki = array(array('identifier', 'recnum', 'entry_timestamp', 'location_timestamp', 'latitude', 'longitude', 'provider', 'accuracy',
+ 'altitude', 'bearing', 'speed', 'vertical_acc', 'speed_acc', 'bearing_acc', 'extras', 'is_mock'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($row['identifier'], $row['recnum'], $row['srv_version_datetime'], $row['timestamp'], $row['lat'], $row['lng'], $row['provider'],
+ $row['accuracy'], $row['altitude'], $row['bearing'], $row['speed'], $row['vertical_acc'], $row['speed_acc'],
+ $row['bearing_acc'], $row['extras'], $row['is_mock']);
+ }
+ }
+ return $podatki;
+ }
+
+ private function getDataGeofences() {
+ $Query = "SELECT * FROM maza_srv_geofences WHERE ank_id='$this->_ank_id' ORDER BY id;";
+
+ $podatki = array(array('id', 'survey_id', 'geofence_on', 'center_latitude', 'center_longitude', 'radius', 'center_address', 'name',
+ 'notification_title', 'notification_message', 'notification_sound', 'on_transition', 'after_seconds', 'location_after_triggered'));
+
+ $sqlQuery = sisplet_query($Query);
+
+ if (mysqli_num_rows($sqlQuery)) {
+ while ($row = mysqli_fetch_assoc($sqlQuery)) {
+ $podatki[] = array($row['id'], $row['ank_id'], $row['geofence_on'], $row['lat'], $row['lng'], $row['radius'], $row['address'], $row['name'], $row['notif_title'],
+ $row['notif_message'], $row['notif_sound'], $row['on_transition'], $row['after_seconds'], $row['location_triggered']);
+ }
+ }
+
+ return $podatki;
+ }
+
+ private function exportCSVTable($data, $name) {
+ // Izvozimo CSV
+ $fp = fopen('php://output', 'w');
+
+ header('Content-Type: application/csv charset=UTF-8');
+ header('Content-Disposition: attachement; filename="'.$name.'_' . time() .'_'. $this->_ank_id . '.csv";');
+ echo "\xEF\xBB\xBF"; // UTF-8 BOM
+
+ foreach ($data as $row) {
+ fputcsv($fp, $row, ',');
+ }
+
+ fclose($fp);
+ }
+
+ public function exportCSVIdentifiers(){
+ $data = $this->getDataIdentifiers();
+ $this->exportCSVTable($data, 'maza_identifiers');
+ }
+
+ /*public function exportCSVInactiveIdentifiers(){
+ $data = $this->getDataInactiveIdentifiers();
+ $this->exportCSVTable($data, 'maza_inactive');
+ }
+
+ public function exportCSVActiveIdentifiers(){
+ $data = $this->getDataActiveIdentifiers();
+ $this->exportCSVTable($data, 'maza_active');
+ }*/
+
+ public function exportCSVTrackingLocations(){
+ $data = $this->getDataTrackingLocations();
+ $this->exportCSVTable($data, 'maza_tracking_locations');
+ }
+
+ public function exportCSVTrackingAR(){
+ $data = $this->getDataTrackingAR();
+ $this->exportCSVTable($data, 'maza_tracking_ar');
+ }
+
+ public function exportCSVEntryLocations(){
+ $data = $this->getDataEntryLocations();
+ $this->exportCSVTable($data, 'maza_entry_locations');
+ }
+
+ public function exportCSVTriggeredGeofences(){
+ $data = $this->getDataTriggeredGeofences();
+ $this->exportCSVTable($data, 'maza_triggered_geofences');
+ }
+
+ public function exportCSVTriggeredGeofencesAnswered(){
+ $data = $this->getDataTriggeredGeofencesAnswers();
+ $this->exportCSVTable($data, 'maza_triggered_geofences_answers');
+ }
+
+ public function exportCSVGeofences(){
+ $data = $this->getDataGeofences();
+ $this->exportCSVTable($data, 'maza_geofences');
+ }
+
+ public function exportCSVAlarmRespondets(){
+ $data = $this->getDataAlarmRespondents();
+ $this->exportCSVTable($data, 'maza_alarm_respondets');
+ }
+}
+
+?>
\ No newline at end of file |