<?php

class AuditLog extends CActiveRecord
{

    /**
     * This is the model class for table "{{audit_log}}".
     *
     * The followings are the available columns in table '{{audit_log}}':
     * @property integer $id
     * @property string $functionality
     * @property string $performed_by
     * @property string $performed_datetime
     * @property string $changes
     */

    /**
     * @return string the associated database table name
     */
    public function tableName()
    {
        return '{{audit_log}}';
    }

    /**
     * Returns the static model of the specified AR class.
     * Please note that you should have this exact method in all your CActiveRecord descendants!
     * @param string $className active record class name.
     * @return AuditLog the static model class
     */
    public static function model($className = __CLASS__)
    {
        return parent::model($className);
    }

    //fetching audit logs
    public static function getAuditLogs($condition, $orderBy, $limit, $offset)
    {
        try {
            $list = Yii::app()->db->createCommand(
                " SELECT esal.id, esal.performed_by, CONCAT(eu.username, ' - ', CONCAT(eu.first_name, ' ', eu.last_name)) AS full_name,
                                                            esal.performed_datetime, esal.changes, ealfl.symbolic_name
                                                            FROM erp_audit_log AS esal
                                                            LEFT JOIN hrbluesky_2024_live_main.{{user}} AS eu ON esal.performed_by = eu.id
                                                            LEFT JOIN erp_audit_log_functionality_lookup AS ealfl ON esal.functionality = ealfl.id
                                                            WHERE $condition
                                                            ORDER BY $orderBy
                                                            LIMIT $limit
                                                            OFFSET $offset
                                                        "
            )
                ->queryAll();
            return $list;
        } catch (\yii\db\Exception $e) {
            echo 'An error occured. Please try again';
        }
    }

    public static function getAuditLogsCount($condition)
    {
        try {
            $list = Yii::app()->db->createCommand(
                " SELECT esal.id, esal.performed_by, CONCAT(eu.username, ' - ', CONCAT(eu.first_name, ' ', eu.last_name)) AS full_name,
                                                            esal.performed_datetime, esal.changes, ealfl.symbolic_name
                                                            FROM erp_audit_log AS esal
                                                            LEFT JOIN hrbluesky_2024_live_main.{{user}} AS eu ON esal.performed_by = eu.id
                                                            LEFT JOIN erp_audit_log_functionality_lookup AS ealfl ON esal.functionality = ealfl.id
                                                            WHERE $condition
                                                        "
            )
                ->queryAll();
            return $list;
        } catch (\yii\db\Exception $e) {
            echo 'An error occured. Please try again';
        }

    }

    public static function saveAuditLogs($functionality, $user, $type, $changes)
    {
        try {
            // inserting vendor log to general audit log
            $model_audit_log = new AuditLog();
            $model_audit_log->functionality = AuditLog::getSymbolicNameFunctionality($functionality);
            $model_audit_log->performed_by = $user;
            $model_audit_log->performed_datetime = date('Y-m-d H:i:s');
            switch ($type) {
                case 'CREATED':
                    $change_type = 'CTD';
                    break;
                case 'UPDATED':
                    $change_type = 'UTD';
                    break;
                case 'DELETED':
                    $change_type = 'DLTD';
                    break;
                default:
                    $change_type = 'UTD';
                    break;
            }

            $changes = " {$change_type} {$changes} ";
            $model_audit_log->changes = $changes;
            $model_audit_log->save();
            // end audit log for vendor
        } catch (\yii\db\Exception $e) {
            echo 'An error occured. Please try again';
        }
    }

    public static function getSymbolicNameFunctionality($enum_name)
    {
        try {
            $list = Yii::app()->db->createCommand(
                " SELECT id FROM erp_audit_log_functionality_lookup
                        WHERE enum_name = '$enum_name'
                                                    "
            )
                ->queryAll();
            return $list[0]['id'];
        } catch (\yii\db\Exception $e) {
            echo 'An error occured. Please try again';
        }

    }

    public static function getCountryforSalaryLog($country_id)
    {
        try {
            $list = Yii::app()->db->createCommand(
                " SELECT nationality FROM erp_mas_country
                        WHERE id = $country_id
                                                    "
            )
                ->queryAll();
            return $list[0]['nationality'];
        } catch (\yii\db\Exception $e) {
            echo 'An error occured. Please try again';
        }

    }

    public static function getUserInLog($term){
        try {
            $list = Yii::app()->db->createCommand(
                " SELECT DISTINCT eu.id, CONCAT(eu.username, ' - ', CONCAT(eu.first_name, ' ', eu.last_name)) AS value
                        FROM erp_audit_log AS esal
                        INNER JOIN hrbluesky_2024_live_main.{{user}} AS eu ON esal.performed_by = eu.id
                        WHERE eu.first_name LIKE :first_name OR eu.last_name LIKE :last_name OR eu.username LIKE :username
                        GROUP BY esal.performed_by
                                                    "
            )->bindValues(array(':first_name' => '%'.strtolower($term).'%', ':last_name' => '%'.strtolower($term).'%', ':username' => '%'.strtolower($term).'%'))
            ->queryAll();

            return $list;
        } catch (\yii\db\Exception $e) {
            echo 'An error occured. Please try again';
        }
    }

}
