// Author:        Ricardas Stoma
// Company:       Kolmisoft
// Year:          2016
// About:         Script exports CDRs by template


#define SCRIPT_NAME      "m2_cdr_export"
#define SCRIPT_VERSION   "1.15"
#define FINAL_CSV        "last_calls_export.csv"
#define TMP_CSV          "tmp_last_calls_export.csv"
#define TGZ_CSV          "last_calls_export.tgz"
#define WORKING_DIR      "/tmp/m2/"
#define TMP_CSV_PATH     WORKING_DIR TMP_CSV
#define MAX_FILE_SIZE_MB 10
#define EMAIL_SEND_RETRIES 3

#include "m2_functions.c"

typedef struct permissions {
    int hide_vendor_rate;
    int hide_vendor_price;
} permissions_t;

permissions_t permissions;

char csv_separator[256] = "";
char export_sql[20000] = "";
char export_sql_to_output[9000] = "";
char final_export_sql[20000] = "";
char template_id_str[256] = "";
char template_columns[1024] = "";
char template_columns_nice[1024] = "";
char template_columns_csv[1024] = "";
char template_columns_sql[1024] = "";
char date_from[256] = "";
char date_from_in_timezone[256] = "";
char date_till[256] = "";
char date_till_in_timezone[256] = "";
char email[256] = "";
char filename_csv[270] = "";
char filename_tgz[270] = "";
char filename_csv_full[400] = "";
char filename_tgz_full[400] = "";
char archive_nice_size[256] = "";
char automatic_cdr_export_id_string[256] = "";
int template_id = 0;
int task_failed = 0;
int user_id = 0;
int owner_id = 0;
int automatic_cdr_export_id = 0;
char usertype[100] = "";
char query_user_username[256] = "";
char query_user_fullname[256] = "";
char query_device_description[256] = "";

// email settings
char email_server[256] = "";
char email_from[256] = "";
char email_login[256] = "";
char email_password[256] = "";
int email_port = 0;
int email_enabled = 0;
char cdr_export_success_body[20000] = "";
char cdr_export_success_subject[256] = "";
int cdr_export_success_template_id = 0;
char cdr_export_error_body[20000] = "";
char cdr_export_error_subject[256] = "";
int cdr_export_error_template_id = 0;

void get_template_columns();
void format_columns();
void error_handle();
void send_error_email();
int send_csv_email();
void get_user_email(char *email, int user_id);
void get_email_settings(int owner_id);
void email_action_log(int user_id, char *email, int status, char *error);
void format_final_filename();
void delete_tmp_files();
void get_email_templates();
void replace_email_variables(char *body);
void get_nice_file_size(char *file, char *size);
void get_csv_settings(int owner_id);
void set_email_variables();
void get_nice_name(char *name_buffer, char *target, int id);
int get_id_from_query(char *target);
void get_usertype(int user_id, char *usertype);
void get_manager_permissions(permissions_t *permissions, int manager_id);
void apply_manager_permissions();
void filter_out_sql_execute_time(char *query);

int main(int argc, char *argv[]) {

    char system_cmd[2000] = "";

    m2_init("Starting M2 CDR Export script\n");

    // mark task as failed on segmentation fault
    struct sigaction sa;
    memset(&sa, 0, sizeof(struct sigaction));
    sigemptyset(&sa.sa_mask);
    sa.sa_sigaction = error_handle;
    sa.sa_flags = SA_SIGINFO;
    sigaction(SIGSEGV, &sa, NULL);
    sigaction(SIGTERM, &sa, NULL);
    sigaction(SIGINT, &sa, NULL);
    // mark task as failed on returns
    atexit(error_handle);

    // get CDR export task
    if (m2_task_get(7, NULL, &user_id, template_id_str, automatic_cdr_export_id_string, email, date_from, date_till, export_sql, NULL, NULL, NULL, NULL)) return 1;

    strncpy(export_sql_to_output, export_sql, sizeof(export_sql_to_output));

    if (strlen(date_from) && strchr(date_from, '(')) {
        strcpy(date_from_in_timezone, strchr(date_from, '(') + 1);
        date_from_in_timezone[19] = 0;
        date_from[19] = 0;
    }

    if (strlen(date_till) && strchr(date_till, '(')) {
        strcpy(date_till_in_timezone, strchr(date_till, '(') + 1);
        date_till_in_timezone[19] = 0;
        date_till[19] = 0;
    }

    m2_task_lock();
    task_failed = 1; // default state

    template_id = atoi(template_id_str);

    if (template_id > 0) {
        get_template_columns();
    } else {
        m2_log("Template id is not set! (id: %d)\n", template_id);
        return 1;
    }

    if (!strlen(template_columns)) {
        m2_log("Template columns are not set!\n");
        return 1;
    }

    if (!strlen(export_sql)) {
        m2_log("CDR export SQL is empty (data6)!\n");
        return 1;
    }

    if (strlen(automatic_cdr_export_id_string)) {
        automatic_cdr_export_id = atoi(automatic_cdr_export_id_string);
    }

    get_usertype(user_id, usertype);

    if (strcmp(usertype, "manager") == 0) {
        get_manager_permissions(&permissions, user_id);
        apply_manager_permissions();
    }

    m2_log("User id: %d\n", user_id);
    m2_log("Usertype: %s\n", usertype);
    if (strcmp(usertype, "manager") == 0) {
        m2_log("Manager permissions:\n");
        m2_log("   Hide Vendor Rate:  %d\n", permissions.hide_vendor_rate);
        m2_log("   Hide Vendor Price: %d\n", permissions.hide_vendor_price);
    }
    m2_log("Owner id: %d\n", owner_id);
    m2_log("Template id: %d\n", template_id);
    m2_log("Template columns: %s\n", template_columns);
    if (strlen(date_from_in_timezone)) {
        m2_log("Date from: %s (%s)\n", date_from, date_from_in_timezone);
        m2_log("Date till: %s (%s)\n", date_till, date_till_in_timezone);
    } else {
        m2_log("Date from: %s\n", date_from);
        m2_log("Date till: %s\n", date_till);
    }
    m2_log("Automatic CDR Export id: %d\n", automatic_cdr_export_id);
    m2_log("Email: %s\n", email);
    m2_log("Export SQL:\n");
    m2_log("------------------------------------\n");
    m2_log("%s\n", export_sql_to_output);
    m2_log("------------------------------------\n");

    if (!strlen(date_from_in_timezone)) strcpy(date_from_in_timezone, date_from);
    if (!strlen(date_till_in_timezone)) strcpy(date_till_in_timezone, date_till);

    if (strcmp(email, "ftp") == 0) {
        // get ftp settings
        m2_get_ftp_settings();
    } else if (strcmp(email, "sftp") == 0) {
        // get sftp settings
        m2_get_sftp_settings();
    } else {
        // get email settings
        get_email_settings(owner_id);
        // get email templates
        get_email_templates();
        // get user's email
        if (!strlen(email)) get_user_email(email, user_id);
    }

    // get CSV separator
    get_csv_settings(owner_id);
    // format columns for SQL and CSV compatible formats
    format_columns();
    // delete old files
    system("rm -fr " TMP_CSV_PATH);
    // filter out mysql comments
    filter_out_sql_execute_time(export_sql);

    // format CDR export SQL query to TMP file
    sprintf(final_export_sql, "SELECT %s %s INTO OUTFILE '%s' FIELDS TERMINATED BY '%s' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'",
        template_columns_sql, export_sql + 10, TMP_CSV_PATH, csv_separator);

    if (m2_mysql_query(final_export_sql)) {
        exit(1);
    }

    // format filename
    format_final_filename();

    // delete old csv file
    sprintf(system_cmd, "rm -fr %s &> /dev/null", filename_csv_full);
    system(system_cmd);
    // delete old tgz file
    sprintf(system_cmd, "rm -fr %s &> /dev/null", filename_tgz_full);
    system(system_cmd);

    // add header
    sprintf(system_cmd, "echo '%s' > %s", template_columns_csv, filename_csv_full);
    m2_log("%s\n", system_cmd);
    system(system_cmd);
    // copy data
    sprintf(system_cmd, "cat %s >> %s", TMP_CSV_PATH, filename_csv_full);
    m2_log("%s\n", system_cmd);
    system(system_cmd);

    // compress file
    m2_log("Compressing CSV to tgz\n");
    sprintf(system_cmd, "cd %s && tar -cvzf %s %s &> /dev/null", WORKING_DIR, filename_tgz, filename_csv);
    m2_log("%s\n", system_cmd);
    system(system_cmd);

    // check archive size
    size_t file_size = 0;
    int status = 0;
    struct stat st;
    status = stat(filename_tgz_full, &st);

    if (status == -1) {
        m2_log("Archive file %s was not found!\n", filename_tgz_full);
        exit(1);
    }

    file_size = st.st_size;

    // get nice size
    get_nice_file_size(filename_tgz_full, archive_nice_size);
    if (file_size < 1024) strcat(archive_nice_size, " bytes");
    m2_log("Archive size: %s (%d bytes)\n", archive_nice_size, (int)file_size);

    if (strcmp(email, "ftp") == 0) {
        // send to FTP
        if (m2_upload_file_to_ftp(filename_tgz_full, ftp_backups_automatic_cdr_export_path) == 0) {
            m2_log("CSV File will be deleted from local server\n");
            sprintf(system_cmd, "rm -f %s", filename_tgz_full);
            m2_log("%s\n", system_cmd);
            system(system_cmd);

            return 1;
        }
    } else if (strcmp(email, "sftp") == 0) {
        // send to SFTP
        if (m2_upload_file_to_sftp(filename_tgz_full, sftp_backups_automatic_cdr_export_path) == 0) {
            m2_log("CSV File will be deleted from local server\n");
            sprintf(system_cmd, "rm -f %s", filename_tgz_full);
            m2_log("%s\n", system_cmd);
            system(system_cmd);

            return 1;
        }
    } else {
        // set template variables
        replace_email_variables(cdr_export_success_body);
        replace_email_variables(cdr_export_error_body);

        // send email
        int i;
        if (file_size > MAX_FILE_SIZE_MB*1024*1024) {
            m2_log("Archive is too big! Archive size: %dMB, allowed size: %dMB\n", (int)file_size/1024/1024, MAX_FILE_SIZE_MB);
            send_error_email();
        } else {
            //send archive to user up to four times
            for (i = 0; i <= EMAIL_SEND_RETRIES; i++) {
                if (send_csv_email() == 0) {
                    break;
                }
                sleep(60);
            }
        }
    }

    task_failed = 0;
    m2_task_finish();

    m2_log("Script completed\n");

    return 0;
}


/*
    Get CDR template fields for CSV
*/


void get_template_columns() {

    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[1024] = "";

    sprintf(query, "SELECT columns, nice_columns FROM cdr_export_templates WHERE id = %d", template_id);

    if (m2_mysql_query(query)) {
        exit(1);
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0]) strcpy(template_columns, row[0]);
                if (row[1]) strcpy(template_columns_nice, row[1]);
            }
        }
        mysql_free_result(result);
    }
}


/*
    Format SQL compatible column format
*/


void format_columns() {

    int i = 0;
    int j = 0;
    int len = strlen(template_columns);
    int nice_len = strlen(template_columns_nice);

    // remove trailing ; symbols
    for (i = (len - 1); i >= 0; i--) {
        if (template_columns[i] != ';') {
            if (i < (len - 1)) {
                template_columns[i + 1] = 0;
            }
            break;
        }
    }

    // remove trailing ; symbols
    for (i = (nice_len - 1); i >= 0; i--) {
        if (template_columns_nice[i] != ';') {
            if (i < (nice_len - 1)) {
                template_columns_nice[i + 1] = 0;
            }
            break;
        }
    }

    strcpy(template_columns_sql, template_columns);
    strcpy(template_columns_csv, template_columns_nice);

    // replace ; with ,
    // also add empty string to sql columns
    for (i = 0; i < len; i++) {
        if (template_columns[i] == ';') {
            template_columns_sql[j] = ',';
        } else {
            template_columns_sql[j] = template_columns[i];
        }
        j++;
        if (i < (len - 1)) {
            if (template_columns[i] == ';' && template_columns[i + 1] == ';') {
                template_columns_sql[j] = '\'';
                j++;
                template_columns_sql[j] = '\'';
                j++;
            }
        }
    }

    for (i = 0; i < nice_len; i++) {
        if (template_columns_nice[i] == ';') {
            template_columns_csv[i] = csv_separator[0];
        } else {
            template_columns_csv[i] = template_columns_nice[i];
        }
    }

    if (template_columns_sql[0] == ',') {
        char tmp_template_columns_sql[2048] = "''";
        strcat(tmp_template_columns_sql, template_columns_sql);
        strcpy(template_columns_sql, tmp_template_columns_sql);
    }

    m2_log("CSV columns format: %s\n", template_columns_csv);
    m2_log("SQL columns format: %s\n", template_columns_sql);

}


/*
    Function that handles segmentation fault and regular returns
*/


void error_handle() {

    static int marked = 0;

    if (marked == 0) {
        if (task_failed && task_id) {
            m2_task_unlock(4); // mark task as failed
        }
        marked = 1;
    }

    delete_tmp_files();

    exit(1);

}


/*
    Get email by user id
*/


void get_user_email(char *email, int user_id) {

    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[1024] = "";

    sprintf(query, "SELECT main_email FROM users WHERE id = %d", user_id);

    if (m2_mysql_query(query)) {
        exit(1);
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0]) {
                    strcpy(email, row[0]);
                }
            }
        }
        mysql_free_result(result);
    }

    if (!strlen(email)) {
        m2_log("User's (id: %d) email is empty!\n", user_id);
        exit(1);
    }

    m2_log("User (id: %d) email: %s\n", user_id, email);

}


/*
    Send error email if file size is too big
*/


void send_error_email() {

    char response[2048] = "";

    m2_log("Sending error email to user (id: %d) email (%s)\n", user_id, email);

    if (m2_send_email(NULL, email, cdr_export_error_subject, cdr_export_error_body, NULL, 0, cdr_export_error_template_id, "cdr_export", 0, response)) {
        m2_log("Failed to send email: %s\n", response);
        email_action_log(0, email, 0, response);
    } else {
        m2_log("Email was sent successfully\n");
        email_action_log(0, email, 1, "");
    }

}


/*
    Send compressed CSV to admin's email
*/


int send_csv_email() {

    char response[2048] = "";
    int return_value = 0;

    m2_log("Sending email to user (id: %d) email (%s)\n", user_id, email);

    if (m2_send_email(NULL, email, cdr_export_success_subject, cdr_export_success_body, filename_tgz_full, 0, cdr_export_success_template_id, "cdr_export", 0, response)) {
        m2_log("Failed to send email: %s\n", response);
        email_action_log(0, email, 0, response);
        return_value = 1;
    } else {
        m2_log("Email was sent successfully\n");
        email_action_log(0, email, 1, "");
        return_value = 0;
    }

    return return_value;
}


/*
    Get email settings
*/


void get_email_settings(int owner_id) {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char sqlcmd[2048] = "";

    sprintf(sqlcmd, "SELECT (SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_Smtp_Server') AS 'server', "
        "(SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_Login') AS 'login', "
        "(SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_Password') AS 'password', "
        "(SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_from'), "
        "(SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_port'),"
        "(SELECT value FROM conflines WHERE owner_id = %d AND name = 'Email_Sending_Enabled')",
        owner_id, owner_id, owner_id, owner_id, owner_id, owner_id);

    if (m2_mysql_query(sqlcmd)) {
        exit(1);
    }

    result = mysql_store_result(&mysql);

    if (result == NULL) {
        m2_log("Query result is empty\n");
        exit(1);
    }

    if ((row = mysql_fetch_row(result)) == NULL) {
        m2_log("MySQL returned empty set\n");
        exit(1);
    }

    if (row[0]) strcpy(email_server, row[0]);
    if (row[1]) strcpy(email_login, row[1]);
    if (row[2]) strcpy(email_password, row[2]);
    if (row[3]) strcpy(email_from, row[3]);
    if (row[4]) email_port = atoi(row[4]);
    if (row[5]) email_enabled = atoi(row[5]);

    if (result) mysql_free_result(result);

    if (!strlen(email_server)) { m2_log("Email server is empty\n"); exit(1); }
    if (!strlen(email_from)) { m2_log("Email from is empty\n"); exit(1); }
    if (!email_port) { m2_log("Email port is empty\n"); exit(1); }
    if (!email_enabled) { m2_log("Emails are disabled globally!\n"); exit(1); }

    m2_log("Email data: server: %s, login: %s, from: %s, port: %d\n", email_server, email_login, email_from, email_port);

}


/*
    Create action log for email sending (failed and successful attempts)
*/


void email_action_log(int user_id, char *email, int status, char *error) {

    char sqlcmd[1024] = "";
    char email_to_db[128] = "no email";
    char error_to_db[128] = "unknown reason";

    if (strlen(email)) {
        strcpy(email_to_db, email);
    }

    if (strlen(error)) {
        strcpy(error_to_db, error);
    }

    m2_escape_string(error_to_db, '\'');

    if (status == 1) {
        sprintf(sqlcmd, "INSERT INTO actions(action, user_id, target_id, data, date, target_type) VALUES('email_send', 0, '%d', '%s', NOW(), 'user')", user_id, email_to_db);
    } else {
        sprintf(sqlcmd, "INSERT INTO actions(action, user_id, target_id, data, data2, data3, date, target_type) VALUES('error', '0', '%d', '%s', \"Can't send email\", '%s', NOW(), 'user')", user_id, error_to_db, email_to_db);
    }

    m2_mysql_query(sqlcmd);

}


/*
    Format CSV archive final filename
*/


void format_final_filename() {

    char date_from_formatted[100] = "";
    char date_till_formatted[100] = "";
    char tmp_buffer[250] = "";
    int i = 0;
    int j = 0;

    for (i = 0; i < strlen(date_from_in_timezone); i++) {
        if (date_from_in_timezone[i] != '-' && date_from_in_timezone[i] != ':') {
            if (date_from_in_timezone[i] == ' ') {
                date_from_formatted[j] = '_';
            } else {
                date_from_formatted[j] = date_from_in_timezone[i];
            }
            j++;
        }
    }

    j = 0;
    for (i = 0; i < strlen(date_till_in_timezone); i++) {
        if (date_till_in_timezone[i] != '-' && date_till_in_timezone[i] != ':') {
            if (date_till_in_timezone[i] == ' ') {
                date_till_formatted[j] = '_';
            } else {
                date_till_formatted[j] = date_till_in_timezone[i];
            }
            j++;
        }
    }

    sprintf(tmp_buffer, "m2_CDR_export_%s-%s", date_from_formatted, date_till_formatted);

    for (i = 0; i < strlen(tmp_buffer); i++) {
        if (tmp_buffer[i] == ' ') tmp_buffer[i] = '_';
        if (tmp_buffer[i] == '-') tmp_buffer[i] = '_';
        if (tmp_buffer[i] == ':') tmp_buffer[i] = '_';
    }

    sprintf(filename_csv, "%s.csv", tmp_buffer);
    sprintf(filename_tgz, "%s.tgz", tmp_buffer);
    sprintf(filename_csv_full, "%s%s", WORKING_DIR, filename_csv);
    sprintf(filename_tgz_full, "%s%s", WORKING_DIR, filename_tgz);

    m2_log("CSV filename: %s\n", filename_csv);
    m2_log("TGZ filename: %s\n", filename_tgz);

}


/*
    Delete tmp files
*/


void delete_tmp_files() {

    char system_cmd[1000] = "";

    // delete tmp csv
    system("rm -fr " TMP_CSV_PATH);
    // delete old csv file
    sprintf(system_cmd, "rm -fr %s &> /dev/null", filename_csv_full);
    system(system_cmd);
    // delete old tgz file
    sprintf(system_cmd, "rm -fr %s &> /dev/null", filename_tgz_full);
    system(system_cmd);

}


/*
    Get email templates
*/


void get_email_templates() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    if (m2_mysql_query("SELECT body, subject, id FROM emails WHERE name = 'cdr_export_success'")) {
        exit(1);
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0]) strcpy(cdr_export_success_body, row[0]);
                if (row[1]) strcpy(cdr_export_success_subject, row[1]);
                if (row[2]) cdr_export_success_template_id = atoi(row[2]);
            }
        }
        mysql_free_result(result);
    }

    if (m2_mysql_query("SELECT body, subject, id FROM emails WHERE name = 'cdr_export_error'")) {
        exit(1);
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0]) strcpy(cdr_export_error_body, row[0]);
                if (row[1]) strcpy(cdr_export_error_subject, row[1]);
                if (row[2]) cdr_export_error_template_id = atoi(row[2]);
            }
        }
        mysql_free_result(result);
    }

    if (!strlen(cdr_export_success_subject)) strcpy(cdr_export_success_subject, "CDR export");
    if (!strlen(cdr_export_error_subject)) strcpy(cdr_export_error_subject, "CDR export error");
    if (!strlen(cdr_export_success_body)) sprintf(cdr_export_success_body, "CDR archive is attached\nDate period: %s - %s\n", date_from_in_timezone, date_till_in_timezone);
    if (!strlen(cdr_export_error_body)) strcpy(cdr_export_error_body, "Size of CDR archive is too big and can not be downloaded\n");

    m2_log("CDR export success template subject: %s\n", cdr_export_success_subject);
    m2_log("CDR export success template body: %.128s\n", cdr_export_success_body);

}


/*
    Replace variables
*/


void replace_email_variables(char *body) {

    char response[10000] = "";
    char system_cmd[1024] = "";
    char line[256];
    FILE *fp = NULL;

    char current_date[20] = "";

    // get current date
    m2_get_current_date(current_date);

    // set email variables
    set_email_variables();

    // write template to file
    fp = fopen("/tmp/m2_tmp_email_replace", "w+");
    if (!fp) return;
    fprintf(fp, "%s", body);
    fclose(fp);

    // escape strings
    if (strlen(query_user_username)) m2_escape_string(query_user_username, '"');
    if (strlen(query_user_fullname)) m2_escape_string(query_user_fullname, '"');
    if (strlen(query_device_description)) m2_escape_string(query_device_description, '"');

    // use sed to replace variables
    sprintf(system_cmd, "sed -i \"s|<%%= current_date %%>|%s|g\" /tmp/m2_tmp_email_replace", current_date);
    system(system_cmd);
    sprintf(system_cmd, "sed -i \"s|<%%= date_from %%>|%s|g\" /tmp/m2_tmp_email_replace", date_from_in_timezone);
    system(system_cmd);
    sprintf(system_cmd, "sed -i \"s|<%%= date_till %%>|%s|g\" /tmp/m2_tmp_email_replace", date_till_in_timezone);
    system(system_cmd);
    sprintf(system_cmd, "sed -i \"s|<%%= archive_size %%>|%s|g\" /tmp/m2_tmp_email_replace", archive_nice_size);
    system(system_cmd);
    sprintf(system_cmd, "sed -i \"s|<%%= search_user_username %%>|%s|g\" /tmp/m2_tmp_email_replace", query_user_username);
    system(system_cmd);
    sprintf(system_cmd, "sed -i \"s|<%%= search_user_fullname %%>|%s|g\" /tmp/m2_tmp_email_replace", query_user_fullname);
    system(system_cmd);
    sprintf(system_cmd, "sed -i \"s|<%%= search_device_description %%>|%s|g\" /tmp/m2_tmp_email_replace", query_device_description);
    system(system_cmd);

    // read everything back
    fp = fopen("/tmp/m2_tmp_email_replace", "r");
    if (!fp) return;

    while (fgets(line, sizeof(line), fp)) {
        strcat(response, line);
    }

    fclose(fp);

    if (strlen(response)) {
        strcpy(body, response);
    }

    unlink("/tmp/m2_tmp_email_replace");

}


/*
    Get file size from ls -lh command
*/


void get_nice_file_size(char *file, char *size) {

    char response[1024] = "";
    char system_cmd[1024] = "";
    FILE *p = NULL;

    sprintf(system_cmd, "ls -lh %s | awk '{print $5}'", file);

    p = popen(system_cmd, "r");
    if (!p) return;
    fgets(response, 1020, p);
    pclose(p);

    if (strlen(response)) {
        response[strlen(response) - 1] = 0;
        strcpy(size, response);
    }

}


/*
    Get CSV settings
*/


void get_csv_settings(int owner_id) {

    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[1024] = "";

    sprintf(query, "SELECT (SELECT value FROM conflines WHERE owner_id = %d AND name = 'CSV_Separator')", owner_id);

    if (m2_mysql_query(query)) {
        exit(1);
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0]) {
                    strcpy(csv_separator, row[0]);
                }
            }
        }
        mysql_free_result(result);
    }

    if (!strlen(csv_separator)) strcpy(csv_separator, ",");

    m2_log("CSV separator: %s\n", csv_separator);

}


/*
    Function extracts various information from sql query
*/


int get_id_from_query(char *target) {

    FILE *pipe = NULL;
    char cmd[512] = "";
    char response[1024] = "";

    sprintf(cmd, "grep -Po '%s = \\d+' /tmp/m2/m2_cdr_export_query.txt | tail -n 1 | grep -Po '\\d+'", target);

    pipe = popen(cmd, "r");

    if (pipe == NULL) {
        m2_log("Can't execute: %s\n", cmd);
        return 0;
    }

    fgets(response, 1020, pipe);
    pclose(pipe);

    return atoi(response);

}


/*
    Function gets nice names for user/device
*/


void get_nice_name(char *name_buffer, char *target, int id) {

    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[1024] = "";

    if (id == 0) return;

    if (strcmp(target, "user_username") == 0) {
        sprintf(query, "SELECT username FROM users WHERE id = %d", id);
    } else if (strcmp(target, "user_fullname") == 0) {
        sprintf(query, "SELECT IF(LENGTH(TRIM(CONCAT(first_name, ' ', last_name))) > 0, TRIM(CONCAT(first_name, ' ', last_name)), username) AS 'nice_username' FROM users WHERE id = %d", id);
    } else if (strcmp(target, "device_description") == 0) {
        sprintf(query, "SELECT description FROM devices WHERE id = %d", id);
    } else {
        return;
    }

    if (m2_mysql_query(query)) {
        exit(1);
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0]) {
                    strcpy(name_buffer, row[0]);
                }
            }
        }
        mysql_free_result(result);
    }

}


/*
    Function sets various email variables
*/


void set_email_variables() {

    int query_user_id = 0;
    int query_device_id = 0;

    // save query to tmp file
    FILE *fp = fopen("/tmp/m2/m2_cdr_export_query.txt", "w+");

    if (fp == NULL) {
        m2_log("Can't open /tmp/m2/m2_cdr_export_query.txt\n");
        return;
    }

    fprintf(fp, "%s", export_sql);
    fclose(fp);

    // extract user_id from query
    query_user_id = get_id_from_query("user_id");
    query_device_id = get_id_from_query("device_id");

    // get nice names
    get_nice_name(query_user_username, "user_username", query_user_id);
    get_nice_name(query_user_fullname, "user_fullname", query_user_id);
    get_nice_name(query_device_description, "device_description", query_device_id);

    // delete tmp file
    unlink("/tmp/m2/m2_cdr_export_query.txt");

}


/*
    Get user type
*/


void get_usertype(int user_id, char *usertype) {

    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[1024] = "";

    if (user_id == 0) return;

    sprintf(query, "SELECT usertype FROM users WHERE id = %d", user_id);

    if (m2_mysql_query(query)) {
        exit(1);
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0]) {
                    strcpy(usertype, row[0]);
                }
            }
        }
        mysql_free_result(result);
    }

}


/*
    Get manager permissions
*/


void get_manager_permissions(permissions_t *permissions, int manager_id) {

    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[1024] = "";

    memset(permissions, 0, sizeof(permissions_t));

    if (user_id == 0) return;

    sprintf(query, "SELECT manager_rights.name, "
                          "manager_group_rights.value "
                     "FROM users "
                     "JOIN manager_group_rights ON manager_group_rights.manager_group_id = users.manager_group_id "
                     "JOIN manager_rights ON manager_rights.id = manager_group_rights.manager_right_id "
                    "WHERE manager_rights.name IN ('REPORTS_Calls_List_Hide_Vendors_Rate', 'REPORTS_Calls_List_Hide_Vendors_Price') AND users.id = %d", manager_id);

    if (m2_mysql_query(query)) {
        exit(1);
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0] && strcmp(row[0], "REPORTS_Calls_List_Hide_Vendors_Rate") == 0) permissions->hide_vendor_rate = atoi(row[1]);
                if (row[0] && strcmp(row[0], "REPORTS_Calls_List_Hide_Vendors_Price") == 0) permissions->hide_vendor_price = atoi(row[1]);
            }
        }
        mysql_free_result(result);
    }

}


/*
    Apply manager permissions
*/


void apply_manager_permissions() {
    // provider price
    if (permissions.hide_vendor_price == 2) {
        m2_str_replace(template_columns, "provider_price;", "");
        m2_str_replace(template_columns_nice, "Terminator Price;", "");
        m2_str_replace(template_columns_nice, "Terminator Price", "");
    }

    // provider rate
    if (permissions.hide_vendor_rate == 2) {
        m2_str_replace(template_columns, "provider_rate;", "");
        m2_str_replace(template_columns_nice, "Terminator Rate;", "");
        m2_str_replace(template_columns_nice, "Terminator Rate", "");
    }
}

/*
    Filter out execute time limits from SQL query
*/

void filter_out_sql_execute_time(char *query)
{
    char *local_query = NULL;
    char max_execution_start_str[] = "/*+ MAX_EXECUTION_TIME(";
    char max_execution_end_str[] = "*/";
    char *start_ptr = NULL;

    local_query = strdup(query);

    // Find the beginning of MAX_EXECUTION_TIME
    start_ptr = strstr(local_query, max_execution_start_str);

    if (start_ptr) {
        // Find the ending of MAX_EXECUTION_TIME
        char *end_ptr = strstr(start_ptr, max_execution_end_str);

        if (end_ptr) {
            // Construct filtered query
            *start_ptr = '\0';
            strcpy(query, local_query);
            strcat(query, end_ptr + strlen(max_execution_end_str));
        }
    }

    if (local_query) {
        free(local_query);
    }
}