path: root/admin/survey/classes/tracking/TrackingClass.php

<?php

/***************************************
 * Description:
 * Autor: Robert Šmalc
 * Created date: 02.02.2017
 *****************************************/
class TrackingClass
{
    private $sub;
    private $anketa;
    private $status = '';
	
	private $db_table = "";

    public function __construct($anketa = null)
    {
        if ((isset ($_REQUEST['anketa']) && $_REQUEST['anketa'] > 0) || (isset ($anketa) && $anketa > 0)) {
            $this->anketa = (isset ($_REQUEST['anketa']) && $_REQUEST['anketa'] > 0) ? $_REQUEST['anketa'] : $anketa;
        } else {
            return 'Anketa ID ne obstaja';
        }
		
		# poiščemo aktivno anketo
		SurveyInfo :: getInstance()->SurveyInit($this->anketa);
		$this->db_table = SurveyInfo::getInstance()->getSurveyArchiveDBString();

        if (isset($_GET['m']) && $_GET['m'] == 'tracking_data')
            $this->sub = 'data';
        elseif (isset($_GET['appendMerge']) && $_GET['appendMerge'] == '1')
            $this->sub = 'append';
        else
            $this->sub = 'survey';

        // Filter po statusu
        if (isset($_GET['status']) && in_array($_GET['status'], array('0', '1', '2', '3', '4', '5', '6')))
            $this->status = " AND status = '{$_GET[status]}' ";


        return $this;
    }

    private static $_instance;

    /**
     * V kolikor razred kličemo statično
     *
     * @return instance
     */

    public static function init()
    {
        if (!static::$_instance)
            static::$_instance = new TrackingClass();

        return static::$_instance;
    }

    /**
     * Filter po statusih
     * Filter omogoča, da se prvi parameter ne upošteva statusa, v koliko ni filtra upošteva vse statuse.
     * Drugi parameter naredi inverzno operacijo - išče samo po tem statusu
     *    0 => urejanje
     *    1 => uvoz podatkov
     *    2 => analiza
     *    3 => reporti
     *    4 => podatki
     *    5 => objava - vabila
     *    6 => hierarhija
     *    20 => hierarhija - splošno
     *    21 => hierarhija - gradnja strukture
     *    22 => hierarhija - uporabniki
     *
     * @param (int or array) $exclude_status
     * @param (boolean) $invert_status
     * @return $this
     */
    public function filter($exclude_status = null, $invert_status = false)
    {
        $opcija = '!';
        if ($invert_status)
            $opcija = '';


        if (!is_null($exclude_status) && is_int($exclude_status)) {
            $this->status = " AND status " . $opcija . "= '" . $exclude_status . "' ";
        } elseif (!is_null($exclude_status) && is_array($exclude_status)) {

            if (!empty($opcija)) {
                $this->status = " AND status NOT IN (" . implode(',', $exclude_status) . ")";
            } else {
                $this->status = " AND status IN (" . implode(',', $exclude_status) . ")";
            }


        }

        return $this;
    }


    /**
     * @desc prikaze tracking sprememb
     * status:
     *   -1 => unknown
     *    0 => urejanje
     *    1 => uvoz podatkov
     *    2 => analiza
     *    3 => reporti
     *    4 => podatki
     *    5 => objava - vabila
     *    20 => hierarhija - splošno
     *    21 => hierarhija - gradnja strukture
     *    22 => hierarhija - uporabniki
     */
    public function trackingDisplay()
    {
        global $lang;

        $surveyName = SurveyInfo::getInstance()->getSurveyTitle();

        echo '<fieldset>';
        echo '<legend>' . $lang['srv_survey_archives_tracking_' . $this->sub] . '</legend>';

        // Tabela s podatki o spremembah podatkov
        if ($this->sub == 'data') {

            // Filter po podatkih
            $data = ' AND (`get` LIKE \'%edit_data%\' 
							OR (`get` LIKE \'%a: "data", m: "quick_edit"%\' AND `get` LIKE \'%post: "1"%\')
							OR (`get` LIKE \'%a: "dataCopyRow"%\')
							OR (`get` LIKE \'%a: "dataDeleteMultipleRow"%\')
							OR (`get` LIKE \'%a: "dataDeleteRow"%\')
							OR (`get` LIKE \'%urejanje: "1"%\' AND status=\'4\')
						)';

            
            // Vrstice s podatki
            $sql = sisplet_query("SELECT * FROM srv_tracking".$this->db_table." WHERE ank_id = '$this->anketa' " . $data . " ORDER BY datetime DESC");
            
            if(!empty(mysqli_fetch_array($sql))) {


                echo '<p class="bottom16">'.$lang['srv_survey_archives_tracking_survey_text1'].' '.$lang['srv_survey_archives_tracking_survey_text1b'].' <a href="index.php?anketa=' . $this->anketa . '&a=data">\'<span class="caps">'.$lang['navigation_NAVI_RESULTS'].'</span>\' - \''.$lang['navigation_NAVI_DATA'].'\'</a> '.$lang['srv_survey_archives_tracking_survey_text1c'].' \''.$surveyName.'\'. '.$lang['srv_survey_archives_tracking_survey_text2'].' <a href="index.php?anketa=' . $this->anketa . '&a=' . $_GET['a'] . '&d=download">'.$lang['srv_survey_archives_tracking_survey_text3'].'</a>.</p>';

                // Izvoz v Excel
                echo '<div class="izvoz_nad_tabelo">';
                echo '<a class="noline" href="index.php?anketa=' . $this->anketa . '&a=' . $_GET['a'] . '&d=download"><span class="faicon xls green link-right"></span>'.$lang['srv_export_excel'].'</a>';
                echo '</div>';

                echo '<table id="tracking" class="fullwidth">';
                // Prva vrstica
                echo '<tr>';
                echo '	<th>' . $lang['date'] . '</th>';
                echo '	<th>' . $lang['user'] . '</th>';
                echo '	<th>IP</th>';
                echo '	<th>Respondent</th>';
                echo '	<th>GET</th>';
                echo '	<th>POST</th>';
                echo '</tr>';

                $count = 0;
                while ($row = mysqli_fetch_array($sql)) {
                    echo '<tr '.($count > 24 ? 'class="displayNone"' : '').($count == 24 ? ' class="show_as_last_row"' : '').'>';

                    $sqlu = sisplet_query("SELECT name, surname FROM users WHERE id = '$row[user]'");
                    $rowu = mysqli_fetch_array($sqlu);

                    // Pri podatkih dobimo posebej podatke o editiranem respondentu
                    $usr_id = '';
                    $cookie = '';

                    // Preverimo ce imamo usr_id v GET-u
                    $get_array_temp = explode(', ', $row['get']);
                    foreach ($get_array_temp AS $get_val) {
                        $param = explode(': ', $get_val);
                        $get_array[$param[0]] = $param[1];
                    }

                    if (isset($get_array['usr_id']) && $get_array['usr_id'] != '') {
                        $usr_id = trim($get_array['usr_id'], '"');
                    } // Preverimo ce iammo slucajno cookie
                    elseif (isset($get_array['survey-' . $this->anketa]) && $get_array['survey-' . $this->anketa] != '') {
                        $cookie = trim($get_array['survey-' . $this->anketa], '"');
                    } else {
                        // Preverimo ce imamo usr_id v POST-u
                        $post_array_temp = explode(', ', $row['post']);
                        foreach ($post_array_temp AS $post_val) {
                            $param = explode(': ', $post_val);
                            $post_array[$param[0]] = $param[1];
                        }

                        if (isset($post_array['usr_id']) && $post_array['usr_id'] != '')
                            $usr_id = trim($post_array['usr_id'], '"');
                    }

                    if ($usr_id != '') {
                        $sqlR = sisplet_query("SELECT recnum FROM srv_user WHERE id = '$usr_id'");
                        $rowR = mysqli_fetch_array($sqlR);

                        $recnum = $rowR['recnum'];
                    } elseif ($cookie != '') {
                        $sqlR = sisplet_query("SELECT recnum FROM srv_user WHERE cookie = '$cookie'");
                        $rowR = mysqli_fetch_array($sqlR);

                        $recnum = $rowR['recnum'];
                    } else
                        $recnum = 0;

                    echo '	<td><div class="no_wrap">' . datetime($row['datetime']) . '</div></td>';
                    echo '	<td><div class="no_wrap">' . $rowu['name'] . ' ' . $rowu['surname'] . '</div></td>';
                    echo '	<td>' . $row['ip'] . '</td>';
                    echo '	<td>' . $recnum . '</td>';
                    echo '	<td><div class="postget_wrap">' . $row['get'] . '</div></td>';
                    echo '	<td><div class="postget_wrap">' . (($row['post'] != "") ? $row['post'] : '/') . '</div></td>';

                    echo '</tr>';

                    $count++;
                }
            }
            else {
                echo '<p>'.ucfirst($lang['srv_survey_archives_tracking_survey_text1b']).' <a href="index.php?anketa=' . $this->anketa . '&a=data">\'<span class="caps">'.$lang['navigation_NAVI_RESULTS'].'</span>\' - \''.$lang['navigation_NAVI_DATA'].'\'</a> '.$lang['srv_survey_archives_tracking_survey_text1d'].'.</p>';
            }
            
            
        } // Tabela s podatki o spremembah - vse oz. merge/append
        else {

            // Legenda statusov
            $statuses = array(
                -1 => $lang['srv_unknown'],
                0 => $lang['srv_urejanje'],
                1 => $lang['import_data'],
                2 => $lang['export_analisys'],
                3 => $lang['srv_reporti'],
                4 => $lang['srv_podatki'],
                5 => $lang['srv_inv_nav_email'],
                20 => $lang['srv_hierarchy'], // Splošni podatki o hierarhiji
                21 => $lang['srv_hierarchy_structure'], // Grajenje hierarhije
                22 => $lang['srv_hierarchy_users'], // Urejanje uporabnikov
            );


            // Filter za uvoze
            if ($this->sub == 'append')
                $appendMerge = " AND (`get` LIKE '%appendMerge%' OR status='1') ";
            else
                $appendMerge = "";


            // Vrstice s podatki
            $sql = sisplet_query("SELECT * FROM srv_tracking".$this->db_table." WHERE ank_id = '$this->anketa' " . $this->status . " " . $appendMerge . " ORDER BY datetime DESC");
            
            if(!empty(mysqli_fetch_array($sql))) {

                if($appendMerge == "")
                    echo '<p class="bottom16">'.$lang['srv_survey_archives_tracking_survey_text1'].' '.$lang['srv_survey_archives_tracking_survey_text1a'].' \''. $surveyName .'\'. '.$lang['srv_survey_archives_tracking_survey_text2'].' <a href="index.php?anketa=' . $this->anketa . '&a=' . $_GET['a'] . '&d=download">'.$lang['srv_survey_archives_tracking_survey_text3'].'</a>.</p>';
                else
                    echo '<p class="bottom16">'.$lang['srv_survey_archives_tracking_survey_text1'].', '.$lang['srv_survey_archives_tracking_survey_text_appendmerge'].' '.$lang['srv_survey_archives_tracking_survey_text1a'].' \''. $surveyName .'\'. '.$lang['srv_survey_archives_tracking_survey_text2'].' <a href="index.php?anketa=' . $this->anketa . '&a=' . $_GET['a'] . '&d=download">'.$lang['srv_survey_archives_tracking_survey_text3'].'</a>.</p>';

                // Izvoz v Excel
                echo '<div class="izvoz_nad_tabelo">';
                echo '<a class="noline" href="index.php?anketa=' . $this->anketa . '&a=' . $_GET['a'] . '&d=download"><span class="faicon xls green link-right"></span>'.$lang['srv_export_excel'].'</a>';
                echo '</div>';
                
                echo '<table id="tracking" class="fullwidth">';
                // Prva vrstica
                echo '<tr>';
                echo '	<th>' . $lang['date'] . '</th>';
                echo '	<th>' . $lang['user'] . '</th>';
                echo '	<th>IP</th>';
                echo '	<th>Podstran</th>';
                echo '	<th>GET</th>';
                echo '	<th>POST</th>';
                echo '</tr>';
            
                $count = 0;
                while ($row = mysqli_fetch_array($sql)) {
                    echo '<tr '.($count > 24 ? 'class="displayNone"' : '').($count == 24 ? ' class="show_as_last_row"' : '').'>';

                    $sqlu = sisplet_query("SELECT name, surname FROM users WHERE id = '$row[user]'");
                    $rowu = mysqli_fetch_array($sqlu);

                    echo '	<td><div class="no_wrap">' . datetime($row['datetime']) . '</div></td>';
                    echo '	<td><div class="no_wrap">' . $rowu['name'] . ' ' . $rowu['surname'] . '</div></td>';
                    echo '	<td>' . $row['ip'] . '</td>';
                    echo '	<td>' . $statuses[$row['status']] . '</td>';
                    echo '	<td><div class="postget_wrap">' . $row['get'] . '</div></td>';
                    echo '	<td><div class="postget_wrap">' .  (($row['post'] != "") ? $row['post'] : '/') . '</div></td>';

                    echo '</tr>';
                    $count++;
                }
            }
            else{
                echo $lang['srv_survey_archives_tracking_append_no_data'];
            }
        }

        echo '</table>';

        echo '</fieldset>';
    }

    public function csvExport(){
        
        define('delimiter', ';');

        $podatki = 'datetime' . delimiter;
        $podatki .= 'user_id' . delimiter;
        $podatki .= 'user_name' . delimiter;
        $podatki .= 'ip' . delimiter;
        $podatki .= 'status' . delimiter;
        $podatki .= 'parameter' . delimiter;
        $podatki .= 'value' . delimiter;
        $podatki .= 'parameter' . delimiter;
        $podatki .= 'value' . delimiter;

        $podatki .= "\n";

        $sql = sisplet_query("SELECT t.*, u.name, u.surname 
                                FROM srv_tracking".$this->db_table." t, users u
                                WHERE t.ank_id = '" . $this->anketa."' ".str_replace('status', 't.status', $this->status)."
                                    AND u.id=t.user
                                ORDER BY t.datetime DESC
                            ");
        while ($row = mysqli_fetch_array($sql)) {

            $podatki .= '' . datetime($row['datetime']) . delimiter;
            $podatki .= '' . $row['user'] . delimiter;
            $podatki .= '' . $row['name'].' '.$row['surname'] . delimiter;
            $podatki .= '' . $row['ip'] . delimiter;
            $podatki .= '' . $row['status'] . delimiter;

            foreach (explode(',', $row['get']) AS $value) {
                $value = explode(':', $value);
                $podatki .= trim($value[0]) . delimiter;
                $podatki .= trim($value[1]) . delimiter;
            }

            $podatki .= "\n";
        }

        $ime = str_replace('-', '_', $_GET['a']);

        return Export::init()->csv('Spremembe_' . $ime, $podatki);
    }

    /**
     * Update srv_tracking table
     * @desc vnese spremembo v srv_tracking za sledenje sprememb
     *
     * @param (int) $anketa
     * @param (int) $status
     */
    private static $time_start;

    static function update($anketa, $status = 0)
    {
        global $global_user_id;

		# poiščemo aktivno anketo
		SurveyInfo :: getInstance()->SurveyInit($anketa);
		$db_table = SurveyInfo::getInstance()->getSurveyArchiveDBString();
		
        $get = '';
        foreach ($_GET AS $key => $val) {
            if ($get != '')
                $get .= ', ';
            $get .= $key . ': "' . $val . '"';
        }

        $post = '';
        foreach ($_POST AS $key => $val) {
            if ($post != '')
                $post .= ', ';

            if (is_array($val))
                $val = self::arrayToString($val);

            $post .= $key . ': "' . $val . '"';
        }

        // izracunamo trajanje skripte v sekundah
        if (self::$time_start != null)
            $time_seconds = microtime(true) - self::$time_start;
        else
            $time_seconds = 0;

        // IP uporabnika
        $ip = GetIP();

        $s = sisplet_query("INSERT INTO srv_tracking".$db_table." (ank_id, datetime, ip, user, `get`, post, status, time_seconds) VALUES ('$anketa', NOW(), '".$ip."', '$global_user_id', '$get', '$post','$status', '$time_seconds')");
        if (!$s) echo mysqli_error($GLOBALS['connect_db']);
    }
    
    static function update_user($status = 0)
    {
        global $global_user_id;
		
        $get = '';
        foreach ($_GET AS $key => $val) {
            if ($get != '')
                $get .= ', ';
            $get .= $key . ': "' . $val . '"';
        }

        $post = '';
        foreach ($_POST AS $key => $val) {
            if ($post != '')
                $post .= ', ';

            if (is_array($val))
                $val = self::arrayToString($val);

            $post .= $key . ': "' . $val . '"';
        }

        // izracunamo trajanje skripte v sekundah
        if (self::$time_start != null)
            $time_seconds = microtime(true) - self::$time_start;
        else
            $time_seconds = 0;

        // IP uporabnika
        $ip = GetIP();

        $s = sisplet_query("INSERT INTO user_tracking (datetime, ip, user, `get`, post, status, time_seconds) VALUES (NOW(), '".$ip."', '$global_user_id', '$get', '$post','$status', '$time_seconds')");
        if (!$s) echo mysqli_error($GLOBALS['connect_db']);
    }

    private static function arrayToString($array)
    {
        $string = "";
        if (is_array($array)) {
            foreach ($array as $key => $value) {

                if (is_array($value)) {
                    $string .= ', '.$key .': [' . self::arrayToString($value) . '] ';
                } else {
                    if ($key == 0)
                        $string .= $key . ': ' . $value . ' ';
                }


            }
        }

        return $string;
    }


}