// Author:        Ricardas Stoma
// Company:       Kolmisoft
// Year:          2014
// About:         Script handles background task creation/execution


#define _GNU_SOURCE
#define SCRIPT_VERSION  "1.13"
#define SCRIPT_NAME     "m2_background_tasks"

#include "m2_functions.c"

// path to background_scripts
#define PATH_TO_ARCHIVE_OLD_CALLS  "/usr/local/m2/m2_archive_old_calls"
#define PATH_TO_CDR_RERATE         "/usr/local/m2/m2_cdr_rerate"
#define PATH_TO_TARIFF_GENERATOR   "/usr/local/m2/m2_tariff_generator"
#define PATH_TO_CDR_EXPORT         "/usr/local/m2/m2_cdr_export"
#define PATH_TO_CDR_COMPARE        "/usr/local/m2/m2_cdr_compare"
#define CDR_EXPORTS_PER_RUN        "10"

double server_offset = 0;

// FUNCTION DECLARATIONS

void check_background_tasks();
int check_archive_old_calls();
int check_tariff_generator();
int check_cdr_rerate();
int check_cdr_export();
void check_automatic_cdr_export();
int check_cdr_compare();
void format_next_run_at(char *period, char *next_run_at_arg, char *cdr_export_at_time, char *new_next_run_at, char *next_month);
void format_cdr_export_query_dates(char *period, char *date_from, char *date_till, char *next_run_at_arg);
void to_server_time(char *date, char *buffer, float offset);
void get_server_gmt_offset();
void cleanup_mysql();


// MAIN FUNCTION

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

    m2_init("Starting Background Tasks script\n");

    // check if any background task is in progress at the moment
    check_background_tasks();

    // check if m2_archive_old_calls script should be executed
    if (check_archive_old_calls() == 0) {
        m2_log("Executing script " PATH_TO_ARCHIVE_OLD_CALLS "\n");
        system(PATH_TO_ARCHIVE_OLD_CALLS);
        return 0;
    }

    // check if m2_cdr_rerate script should be executed
    if (check_cdr_rerate() == 0) {
        m2_log("Executing script " PATH_TO_CDR_RERATE "\n");
        system(PATH_TO_CDR_RERATE);
        return 0;
    }

    // check if m2_tariff_generator script should be executed
    if (check_tariff_generator() == 0) {
        m2_log("Executing script " PATH_TO_TARIFF_GENERATOR "\n");
        system(PATH_TO_TARIFF_GENERATOR);
        return 0;
    }

    // check automatic cdr export
    check_automatic_cdr_export();

    // check if m2_cdr_export script should be executed
    if (check_cdr_export() == 0) {
        m2_log("Executing script " PATH_TO_CDR_EXPORT "\n");
        system(PATH_TO_CDR_EXPORT);
        return 0;
    }

    // check if m2_cdr_compare script should be executed
    if (check_cdr_compare() == 0) {
        m2_log("Executing script " PATH_TO_CDR_COMPARE "\n");
        system(PATH_TO_CDR_COMPARE);
        return 0;
    }

    m2_log("Script finished\n");
    return 0;
}

// check if there are active background tasks running

void check_background_tasks() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    int active_tasks = 0;

    // count how many tasks are running
    if (m2_mysql_query("SELECT count(id) FROM background_tasks WHERE status = 'IN PROGRESS' AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)")) {
        exit(1);
    }

    result = mysql_store_result(&mysql);

    while ((row = mysql_fetch_row(result)) != NULL) {
        if (row[0]) active_tasks = atoi(row[0]);
    }

    mysql_free_result(result);

    if (active_tasks > 0) {
        m2_log("There are recent active background tasks (%d). Scripts will not be executed...\n", active_tasks);
        exit(1);
    }

}

// check if archive_old_calls script should be executed

int check_archive_old_calls() {

    m2_log("Checking Archive old calls...\n");

    MYSQL_RES *result;
    MYSQL_ROW row;
    char buffer[1024] = { 0 };
    int older_than = 0;
    int archive_at_h = -1;
    int archive_at_m = -1;
    int archive_till_h = -1;
    int archive_till_m = -1;
    int waiting_tasks = 0;
    int active_tasks = 0;
    int recently_finished = 0;
    char current_date[20] = "";
    char current_time[20] = "";

    // get current time
    m2_get_current_date(current_date);
    strncpy(current_time, current_date + 11, 8);

    // get older_than and archive_at values
    if (m2_mysql_query("SELECT name, value FROM conflines WHERE name = 'Move_to_old_calls_older_than' OR name = 'Archive_at' OR name = 'Archive_till'")) {
        return 1;
    }

    result = mysql_store_result(&mysql);

    // fetch data
    while ((row = mysql_fetch_row(result)) != NULL) {

        if (row[0] && row[1]) {
            if (strcmp(row[0], "Move_to_old_calls_older_than") == 0) {
                older_than = atoi(row[1]);
            }
        }

        if (row[0] && row[1]) {

            if (strcmp(row[0], "-1") != 0 && strcmp(row[1], "-1") != 0 ) {

                if (strcmp(row[0], "Archive_at") == 0) {
                    char buffer[64] = "";
                    strncpy(buffer, row[1], 2);
                    archive_at_h = atoi(buffer);
                    strncpy(buffer, row[1] + 3, 2);
                    archive_at_m = atoi(buffer);
                }

            }

        }

        if (row[0] && row[1]) {

            if (strcmp(row[0], "-1") != 0 && strcmp(row[1], "-1") != 0 ) {

                if (strcmp(row[0], "Archive_till") == 0) {
                    char buffer[64] = "";
                    strncpy(buffer, row[1], 2);
                    archive_till_h = atoi(buffer);
                    strncpy(buffer, row[1] + 3, 2);
                    archive_till_m = atoi(buffer);
                }

            }
        }

    }

    mysql_free_result(result);

    if (archive_at_h == -1) {
        m2_log("[m2_archive_old_calls] Archive_at = -1. Scripts will not be executed...\n");
        return 1;
    }

    if (older_than == 0) {
        m2_log("[m2_archive_old_calls] Move_to_old_calls_older_than = 0. Scripts will not be executed...\n");
        return 1;
    }

    char hour[3] = "00";
    char minute[3] = "00";
    char minute2[3] = "00";

    if (archive_at_h > 9) {
        sprintf(hour, "%d", archive_at_h);
    } else {
        sprintf(hour, "0%d", archive_at_h);
    }

    if (archive_at_m > 9) {
        sprintf(minute, "%d", archive_at_m);
    } else {
        sprintf(minute, "0%d", archive_at_m);
    }

    if (archive_at_m + 5 > 9) {
        sprintf(minute2, "%d", archive_at_m + 5);
    } else {
        sprintf(minute2, "0%d", archive_at_m + 5);
    }

    // check if current time is between 'archive_at' and 'archive_at' + 5 min
    time_t rawtime = 0, current_time_seconds = 0, limit_time_seconds = 0;
    long long int time_diff = 0;
    struct tm tmmm, tmmm2;
    char time_buffer[20] = "";
    char date_buffer1[20] = "";
    char date_buffer2[20] = "";
    m2_get_current_date(date_buffer1);
    sprintf(time_buffer, "%s:%s:00", hour, minute);
    strncpy(date_buffer1 + 11, time_buffer, 8);
    time(&rawtime);
    localtime_r(&rawtime, &tmmm);
    strptime(date_buffer1, "%Y-%m-%d %H:%M:%S", &tmmm);
    limit_time_seconds = mktime(&tmmm) + 300;
    time(&current_time_seconds);
    time_diff = limit_time_seconds - current_time_seconds;
    localtime_r(&limit_time_seconds, &tmmm2);
    strftime(date_buffer2, 20, "%Y-%m-%d %H:%M:%S", &tmmm2);

    if (time_diff < 0 || time_diff > 300) {
        m2_log("[m2_archive_old_calls] Script can only be executed between %s and %s. Current time is: %s\n", date_buffer1 + 11, date_buffer2 + 11, current_time);
        return 1;
    }

    // check if tasks exist
    if (m2_mysql_query("SELECT (SELECT count(id) FROM background_tasks WHERE task_id = 2 AND status = 'WAITING'), (SELECT count(id) FROM background_tasks WHERE task_id = 2 AND status = 'IN PROGRESS' AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR))")) {
        return 1;
    }

    result = mysql_store_result(&mysql);

    // fetch data
    while ((row = mysql_fetch_row(result)) != NULL) {
        if (row[0]) waiting_tasks = atoi(row[0]);
        if (row[1]) active_tasks  = atoi(row[1]);
    }

    mysql_free_result(result);

    if (active_tasks > 0) {
        m2_log("[m2_archive_old_calls] Active_tasks = %d. Scripts will not be executed...\n", active_tasks);
        return 1;
    }

    if (waiting_tasks > 0) {
        m2_log("[m2_archive_old_calls] Waiting_tasks = %d\n", waiting_tasks);
        return 0;
    }

    // check if task was created recently
    if (m2_mysql_query("SELECT count(id) FROM background_tasks WHERE task_id = 2 AND created_at > DATE_SUB(NOW(), INTERVAL 5 MINUTE)")) {
        return 1;
    }

    result = mysql_store_result(&mysql);

    // fetch data
    while ((row = mysql_fetch_row(result)) != NULL) {
        if (row[0]) recently_finished = atoi(row[0]);
    }

    mysql_free_result(result);

    if (recently_finished) {
        m2_log("[m2_archive_old_calls] Another task can not be executed within 5 minutes. Scripts will not be executed...\n");
        return 1;
    }

    char buffer2[64] = "-1:00";
    if (archive_till_h > -1) {
        char hour[3] = "00";
        char minute[3] = "00";

        if (archive_till_h > 9) {
            sprintf(hour, "%d", archive_till_h);
        } else {
            sprintf(hour, "0%d", archive_till_h);
        }

        if (archive_till_m > 9) {
            sprintf(minute, "%d", archive_till_m);
        } else {
            sprintf(minute, "0%d", archive_till_m);
        }

        sprintf(buffer2, "%s:%s", hour, minute);
    }

    // there are no waiting or active tasks, let's create a new one
    sprintf(buffer, "INSERT INTO background_tasks(created_at, task_id, status, data1) VALUES('%s', 2, 'WAITING', '%s')", current_date, buffer2);
    m2_log("[m2_archive_old_calls] Creating task: %s\n", buffer);
    if (m2_mysql_query(buffer)) {
        return 1;
    }

    return 0;

}

int check_cdr_rerate() {

    m2_log("Checking CDR rerate...\n");

    MYSQL_RES *result;
    MYSQL_ROW row;
    int active_tasks = 0;
    int waiting_tasks = 0;

    // check if tasks exist
    if (m2_mysql_query("SELECT (SELECT count(id) FROM background_tasks WHERE task_id = 1 AND status = 'WAITING'), (SELECT count(id) FROM background_tasks WHERE task_id = 1 AND status = 'IN PROGRESS' AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR))")) {
        return 1;
    }

    result = mysql_store_result(&mysql);

    // fetch data
    while ((row = mysql_fetch_row(result)) != NULL) {
        if (row[0]) waiting_tasks = atoi(row[0]);
        if (row[1]) active_tasks  = atoi(row[1]);
    }

    mysql_free_result(result);

    if (active_tasks > 0) {
        m2_log("[m2_cdr_rerate] Active_tasks = %d. Scripts will not be executed...\n", active_tasks);
        return 1;
    }

    if (waiting_tasks == 0) {
        m2_log("[m2_cdr_rerate] Waiting_tasks = %d. Scripts will not be executed...\n", waiting_tasks);
        return 1;
    }

    return 0;

}

int check_tariff_generator() {

    m2_log("Checking Tariff generator...\n");

    MYSQL_RES *result;
    MYSQL_ROW row;
    int active_tasks = 0;
    int waiting_tasks = 0;

    // check if tasks exist
    if (m2_mysql_query("SELECT (SELECT count(id) FROM background_tasks WHERE task_id = 3 AND status = 'WAITING'), (SELECT count(id) FROM background_tasks WHERE task_id = 3 AND status = 'IN PROGRESS' AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR))")) {
        return 1;
    }

    result = mysql_store_result(&mysql);

    // fetch data
    while ((row = mysql_fetch_row(result)) != NULL) {
        if (row[0]) waiting_tasks = atoi(row[0]);
        if (row[1]) active_tasks  = atoi(row[1]);
    }

    mysql_free_result(result);

    if (active_tasks > 0) {
        m2_log("[m2_tariff_generator] Active_tasks = %d. Scripts will not be executed...\n", active_tasks);
        return 1;
    }

    if (waiting_tasks == 0) {
        m2_log("[m2_tariff_generator] Waiting_tasks = %d. Scripts will not be executed...\n", waiting_tasks);
        return 1;
    }

    return 0;

}

int check_cdr_export() {

    m2_log("Checking CDR Export...\n");

    MYSQL_RES *result;
    MYSQL_ROW row;
    int active_tasks = 0;
    int waiting_tasks = 0;

    // check if task exists
    if (m2_mysql_query("SELECT (SELECT count(id) FROM background_tasks WHERE task_id = 7 AND status = 'WAITING'), (SELECT count(id) FROM background_tasks WHERE task_id = 7 AND status = 'IN PROGRESS' AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR))")) {
        return 1;
    }

    result = mysql_store_result(&mysql);

    // fetch data
    while ((row = mysql_fetch_row(result)) != NULL) {
        if (row[0]) waiting_tasks = atoi(row[0]);
        if (row[1]) active_tasks  = atoi(row[1]);
    }

    mysql_free_result(result);

    if (active_tasks > 0) {
        m2_log("[m2_cdr_export] Active_tasks = %d. Script will not be executed...\n", active_tasks);
        return 1;
    }

    if (waiting_tasks == 0) {
        m2_log("[m2_cdr_export] Waiting_tasks = %d. Script will not be executed...\n", waiting_tasks);
        return 1;
    }

    return 0;

}


/*
    Check if automatic CDR export task should be created
*/


void check_automatic_cdr_export() {

    m2_log("Checking Automatic CDR Export...\n");

    get_server_gmt_offset();

    MYSQL_RES *result;
    MYSQL_ROW row;

    int i = 0;
    int cdr_exports_to_run = 0;

    // get all active tasks
    if (m2_mysql_query("SELECT automatic_cdr_exports.id, period, timezone, timezones.offset, "
        "SUBSTR(cdr_sql, INSTR(cdr_sql, 'calldate between') + 18, 19) AS date_from, SUBSTR(cdr_sql, INSTR(cdr_sql, 'calldate between') + 44, 19) AS date_till, "
        "automatic_cdr_exports.name, next_run_at, automatic_cdr_exports.template_id, send_to_email, users.main_email, (UTC_TIMESTAMP() + INTERVAL timezones.offset SECOND) AS 'now_in_selected_timezone', "
        "CONCAT(DATE_FORMAT(DATE_ADD(LAST_DAY((UTC_TIMESTAMP() + INTERVAL timezones.offset SECOND)), INTERVAL 24 HOUR), '%Y-%m-%d'), ' ', cdr_export_at_time) AS 'next_month', send_to_ftp, "
        "cdr_export_at_time, send_to_sftp "
        "FROM automatic_cdr_exports "
        "LEFT JOIN timezones ON timezones.zone = automatic_cdr_exports.timezone "
        "LEFT JOIN users ON users.id = automatic_cdr_exports.send_to_user_id "
        "LEFT JOIN addresses ON addresses.id = users.address_id "
        "WHERE active = 1 AND next_run_at IS NOT NULL "
        "AND next_run_at <= (UTC_TIMESTAMP() + INTERVAL timezones.offset SECOND) "
        "AND ((next_run_at BETWEEN (UTC_TIMESTAMP() + INTERVAL timezones.offset SECOND - INTERVAL 7200 SECOND) AND (UTC_TIMESTAMP() + INTERVAL timezones.offset SECOND + INTERVAL 7200 SECOND) AND period NOT IN ('hourly', 'only_once')) OR period IN ('hourly', 'only_once')) "
        "AND NOT (last_run_at IS NOT NULL AND period = 'only_once') "
        "LIMIT " CDR_EXPORTS_PER_RUN)) {
        return;
    }

    result = mysql_store_result(&mysql);

    if (result) {
        // fetch data
        while ((row = mysql_fetch_row(result)) != NULL) {

            int id = 0;
            int template_id = 0;
            int timezone_offset_seconds = 0;
            double timezone_offset = 0;
            char query[2000] = "";
            char period[256] = "";
            char timezone[256] = "";
            char date_from[256] = "";
            char date_till[256] = "";
            char new_date_till[256] = "";
            char new_date_from[256] = "";
            char localized_new_date_from[256] = "";
            char localized_new_date_till[256] = "";
            char name[256] = "null";
            char next_run_at[256] = "";
            char new_next_run_at[256] = "";
            char localized_next_run_at[256] = "";
            char localized_new_next_run_at[256] = "";
            char email[256] = "";
            char send_to_email[256] = "";
            char send_to_user_email[256] = "";
            char current_date_in_timezone[256] = "";
            char next_month[256] = "";
            char send_to_ftp[256] = "";
            char send_to_sftp[256] = "";
            char cdr_export_at_time[50] = "";

            if (row[0]) id = atoi(row[0]); else { m2_log("id not found!\n"); return; }
            if (row[1]) strcpy(period, row[1]); else { m2_log("period not found!\n"); return; }
            if (row[2]) strcpy(timezone, row[2]); else { m2_log("timezone not found!\n"); return; }
            if (row[3]) timezone_offset = ((float)atoi(row[3]) / 60.0 / 60.0);
            if (row[3]) timezone_offset_seconds = atoi(row[3]);
            if (row[4]) strcpy(date_from, row[4]); else { m2_log("date from not found!\n"); return; }
            if (row[5]) strcpy(date_till, row[5]); else { m2_log("date till not found!\n"); return; }
            if (row[6]) strcpy(name, row[6]);
            if (row[7]) strcpy(next_run_at, row[7]); else { m2_log("next run at not found!\n"); return; }
            if (row[8]) template_id = atoi(row[8]); else { m2_log("template id not found!\n"); return; }
            if (row[9]) strcpy(send_to_email, row[9]);
            if (row[10]) strcpy(send_to_user_email, row[10]);
            if (row[11]) strcpy(current_date_in_timezone, row[11]);
            if (row[12] && strlen(row[12])) strcpy(next_month, row[12]); else { m2_log("next_month is null!\n"); return; }
            if (row[13]) strcpy(send_to_ftp, row[13]);
            if (row[14]) strcpy(cdr_export_at_time, row[14]);
            if (row[15]) strcpy(send_to_sftp, row[15]);

            // copy minutes from next_run_at
            strcpy(current_date_in_timezone + 14, next_run_at + 14);

            // set seconds
            strcpy(current_date_in_timezone + 17, "00");

            if (atoi(send_to_ftp) == 1) {
                strcpy(email, "ftp");
            } else if (atoi(send_to_sftp) == 1) {
                strcpy(email, "sftp");
            } else if (strlen(send_to_email)) {
                strcpy(email, send_to_email);
            } else if (strlen(send_to_user_email)) {
                strcpy(email, send_to_user_email);
            } else {
                m2_log("Email or ftp is empty!\n");
                return;
            }

            strcpy(new_date_from, date_from);
            strcpy(new_date_till, date_till);

            if (strcmp(period, "only_once") != 0) {
                // generate new date from and date till by period variable
                format_cdr_export_query_dates(period, new_date_from, new_date_till, current_date_in_timezone);

                to_server_time(new_date_from, localized_new_date_from, timezone_offset);
                to_server_time(new_date_till, localized_new_date_till, timezone_offset);

                // update cdr_sql with new from and till dates
                sprintf(query, "UPDATE automatic_cdr_exports SET cdr_sql = INSERT(cdr_sql, INSTR(cdr_sql, 'calls.calldate between'), 70, \"calls.calldate BETWEEN '%s' AND '%s'\") WHERE id = %d",
                    localized_new_date_from, localized_new_date_till, id);

                if (m2_mysql_query(query)) {
                    return;
                }
            }

            to_server_time(next_run_at, localized_next_run_at, timezone_offset);

            m2_log("[m2_automatic_cdr_export] Found active Automatic CDR Export '%s' id: %d, run at: %s (in server timezone: %s), period: %s, export date from: %s (in server timezone: %s), export date till: %s (in server timezone: %s), "
                "timezone: %s (offset: %.2f), date_for_next_month: %s, template id: %d, email(or ftp): %s\n",
                name, id, next_run_at, localized_next_run_at, period, new_date_from, localized_new_date_from, new_date_till, localized_new_date_till, timezone,
                timezone_offset, next_month, template_id, email);

            // create new background task for CDR export
            sprintf(query, "INSERT INTO background_tasks(task_id, created_at, data1, data2, data3, data4, data5, data6) "
                "VALUES(7, NOW(), '%d', '%d', '%s', '%s (%s)', '%s (%s)', (SELECT cdr_sql FROM automatic_cdr_exports WHERE id = %d))",
                template_id, id, email, localized_new_date_from, new_date_from, localized_new_date_till, new_date_till, id);

            if (m2_mysql_query(query)) {
                return;
            }

            cdr_exports_to_run++;

            m2_log("[m2_automatic_cdr_export] New background task for CDR Export is created\n");

            if (strcmp(period, "only_once")) {

                format_next_run_at(period, current_date_in_timezone, cdr_export_at_time, new_next_run_at, next_month);

                // something is wrong, disable cron
                if (strcmp(current_date_in_timezone, new_next_run_at) == 0) {
                    m2_log("Next run at is calculated incorrectly! Next run at: %s\n", new_next_run_at);
                    sprintf(query, "UPDATE automatic_cdr_exports SET active = 0 WHERE id = %d", id);
                    m2_mysql_query(query);
                    return;
                }

                to_server_time(new_next_run_at, localized_new_next_run_at, timezone_offset);

                sprintf(query, "UPDATE automatic_cdr_exports SET last_run_at = (UTC_TIMESTAMP() + INTERVAL %d SECOND), next_run_at = '%s' WHERE id = %d", timezone_offset_seconds, new_next_run_at, id);
                m2_log("[m2_automatic_cdr_export] Next '%s' Automatic CDR Export will be executed on %s (in server timezone: %s)\n",
                    name, new_next_run_at, localized_new_next_run_at);
            } else {
                sprintf(query, "UPDATE automatic_cdr_exports SET last_run_at = (UTC_TIMESTAMP() + INTERVAL %d SECOND) WHERE id = %d", timezone_offset_seconds, id);
            }

            if (m2_mysql_query(query)) {
                return;
            }

        }

        mysql_free_result(result);
    }

    for (i = 0; i < cdr_exports_to_run; i++) {
        m2_log("Executing script " PATH_TO_CDR_EXPORT "\n");
        system(PATH_TO_CDR_EXPORT);
    }

}

int check_cdr_compare() {
    m2_log("Checking CDR Compare...\n");

    MYSQL_RES *result;
    MYSQL_ROW row;
    int active_tasks = 0;
    int waiting_tasks = 0;

    // check if task exists
    if (m2_mysql_query("SELECT (SELECT count(id) FROM background_tasks WHERE task_id = 8 AND status = 'WAITING'),"
        "(SELECT count(id) FROM background_tasks WHERE task_id = 8 AND status = 'IN PROGRESS' "
        "AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR))"))
    {
        return 1;
    }

    result = mysql_store_result(&mysql);

    // fetch data
    while ((row = mysql_fetch_row(result)) != NULL) {
        if (row[0]) waiting_tasks = atoi(row[0]);
        if (row[1]) active_tasks  = atoi(row[1]);
    }

    mysql_free_result(result);

    if (active_tasks > 0) {
        m2_log("[m2_cdr_compare] Active_tasks = %d. Script will not be executed...\n", active_tasks);
        return 1;
    }

    if (waiting_tasks == 0) {
        m2_log("[m2_cdr_compare] Waiting_tasks = %d. Script will not be executed...\n", waiting_tasks);
        return 1;
    }

    return 0;
}


/*
    Format next_run_at
*/


void format_next_run_at(char *period, char *next_run_at_arg, char *cdr_export_at_time, char *new_next_run_at, char *next_month) {

    char next_run_at[256] = "";
    char next_run_at_hours[256] = "";

    strcpy(next_run_at, next_run_at_arg);

    // get only hours part of date
    strcpy(next_run_at_hours, next_run_at + 11);

    char *tz;
    tz = getenv("TZ");
    if (tz) tz = strdup(tz);
    setenv("TZ", "UTC", 1);
    tzset();

    time_t timestamp;
    struct tm tmm, new_tmm;

    memset(&tmm, 0, sizeof(struct tm));
    memset(&new_tmm, 0, sizeof(struct tm));

    if (strcmp(period, "hourly") == 0) {
        strcpy(next_run_at + 13, "00:00");
        strptime(next_run_at, DATE_FORMAT, &tmm);
        timestamp = mktime(&tmm) + (1 * 60 * 60);
    } else if (strcmp(period, "daily") == 0) {
        strcpy(next_run_at + 11, next_run_at_hours);
        strptime(next_run_at, DATE_FORMAT, &tmm);
        timestamp = mktime(&tmm) + (24 * 60 * 60);
    } else if (strcmp(period, "weekly") == 0) {
        strcpy(next_run_at + 11, next_run_at_hours);
        strptime(next_run_at, DATE_FORMAT, &tmm);
        timestamp = mktime(&tmm) - (tmm.tm_wday * 24 * 60 * 60) + (1 * 24 * 60 * 60) + (7 * 24 * 60 * 60);
    } else if (strcmp(period, "bi-weekly") == 0) {
        if (tmm.tm_mday <= 15) {
            strcpy(next_run_at + 11, next_run_at_hours);
            strptime(next_run_at, DATE_FORMAT, &tmm);
            timestamp = mktime(&tmm) + (16 - tmm.tm_mday) * 24 * 60 * 60;
        } else {
            strcpy(next_run_at, next_month);
            strptime(next_run_at, DATE_FORMAT, &tmm);
            timestamp = mktime(&tmm);
        }
    } else if (strcmp(period, "monthly") == 0) {
        strcpy(next_run_at, next_month);
        strptime(next_run_at, DATE_FORMAT, &tmm);
        timestamp = mktime(&tmm);
    }

    gmtime_r(&timestamp, &new_tmm);
    strftime(new_next_run_at, 20, DATE_FORMAT, &new_tmm);

    // Set correct hour
    if (strcmp(period, "hourly") != 0) {
        strcpy(new_next_run_at + 11, cdr_export_at_time);
    }

    // restore timezone sessions variable
    if (tz) {
        setenv("TZ", tz, 1);
        free(tz);
    } else {
        unsetenv("TZ");
    }
    tzset();

}


/*
    Format date_from and date_till for cdr export
*/


void format_cdr_export_query_dates(char *period, char *date_from, char *date_till, char *next_run_at_arg) {

    char next_run_at[256] = "";
    strcpy(next_run_at, next_run_at_arg);

    char *tz;
    tz = getenv("TZ");
    if (tz) tz = strdup(tz);
    setenv("TZ", "UTC", 1);
    tzset();

    time_t timestamp;
    struct tm tmm, new_tmm;

    memset(&tmm, 0, sizeof(struct tm));
    memset(&new_tmm, 0, sizeof(struct tm));

    if (strcmp(period, "hourly") == 0) {
        strcpy(next_run_at + 13, "00:00");
        strptime(next_run_at, DATE_FORMAT, &tmm);
        timestamp = mktime(&tmm) - 1 * 60 * 60;
        gmtime_r(&timestamp, &new_tmm);
        strftime(date_from, 20, DATE_FORMAT, &new_tmm);
        timestamp = mktime(&tmm) - 1;
        gmtime_r(&timestamp, &new_tmm);
        strftime(date_till, 20, DATE_FORMAT, &new_tmm);
    } else if (strcmp(period, "daily") == 0) {
        strcpy(next_run_at + 11, "00:00:00");
        strptime(next_run_at, DATE_FORMAT, &tmm);
        timestamp = mktime(&tmm) - 1 * 24 * 60 * 60;
        gmtime_r(&timestamp, &new_tmm);
        strftime(date_from, 20, DATE_FORMAT, &new_tmm);
        timestamp = mktime(&tmm) - 1;
        gmtime_r(&timestamp, &new_tmm);
        strftime(date_till, 20, DATE_FORMAT, &new_tmm);
    } else if (strcmp(period, "weekly") == 0) {
        strcpy(next_run_at + 11, "00:00:00");
        strptime(next_run_at, DATE_FORMAT, &tmm);
        timestamp = mktime(&tmm) - (tmm.tm_wday * 24 * 60 * 60) + (1 * 24 * 60 * 60) - (7 * 24 * 60 * 60);
        gmtime_r(&timestamp, &new_tmm);
        strftime(date_from, 20, DATE_FORMAT, &new_tmm);
        timestamp = mktime(&tmm) - (tmm.tm_wday * 24 * 60 * 60) + (1 * 24 * 60 * 60) - 1;
        gmtime_r(&timestamp, &new_tmm);
        strftime(date_till, 20, DATE_FORMAT, &new_tmm);
    } else if (strcmp(period, "bi-weekly") == 0) {
        strcpy(next_run_at + 11, "00:00:00");
        strptime(next_run_at, DATE_FORMAT, &tmm);
        if (tmm.tm_mday <= 15) {
            strcpy(next_run_at + 8, "01 00:00:00");
            memset(&tmm, 0, sizeof(struct tm));
            strptime(next_run_at, DATE_FORMAT, &tmm);
            timestamp = mktime(&tmm) - 1;
            gmtime_r(&timestamp, &new_tmm);
            strftime(date_till, 20, DATE_FORMAT, &new_tmm);
            strcpy(date_from, date_till);
            strcpy(date_from + 8, "16 00:00:00");
        } else {
            strcpy(date_from, next_run_at);
            strcpy(date_till, next_run_at);
            strcpy(date_from + 8, "01 00:00:00");
            strcpy(date_till + 8, "15 23:59:59");
        }
    } else if (strcmp(period, "monthly") == 0) {
        strcpy(next_run_at + 8, "01 00:00:00");
        memset(&tmm, 0, sizeof(struct tm));
        strptime(next_run_at, DATE_FORMAT, &tmm);
        timestamp = mktime(&tmm) - 1;
        gmtime_r(&timestamp, &new_tmm);
        strftime(date_till, 20, DATE_FORMAT, &new_tmm);
        strcpy(date_from, date_till);
        strcpy(date_from + 8, "01 00:00:00");
    }

    // restore timezone sessions variable
    if (tz) {
        setenv("TZ", tz, 1);
        free(tz);
    } else {
        unsetenv("TZ");
    }
    tzset();

}


/*
    Convert datetime to server time

    for example:

    if user time is 2014-01-22 16:00:33 and GMT offset is +2 and server GMT offset is -1
    then converted datetime to server time is 2014-01-22 13:00:33
*/


void to_server_time(char *date, char *buffer, float offset) {

    char *tz;
    tz = getenv("TZ");
    if (tz) tz = strdup(tz);
    setenv("TZ", "UTC", 1);
    tzset();

    // convert user time to server time
    // calculate user period time in server offset
    time_t user_time;
    struct tm user_tm, server_ptm;

    // adjust period_start according to server time
    memset(&user_tm, 0, sizeof(struct tm));
    strptime(date, DATE_FORMAT, &user_tm);

    user_time = mktime(&user_tm) - (time_t)round((offset - server_offset) * 60.0 * 60.0);

    gmtime_r(&user_time, &server_ptm);
    strftime(buffer, 20, DATE_FORMAT, &server_ptm);

    // restore timezone sessions variable
    if (tz) {
        setenv("TZ", tz, 1);
        free(tz);
    } else {
        unsetenv("TZ");
    }
    tzset();

}


/*
    Get server GMT offset in decimal value
*/


void get_server_gmt_offset() {

    // get server gmt offset
    time_t t = time(NULL);
    struct tm lt = {0};
    localtime_r(&t, &lt);
    server_offset = lt.tm_gmtoff / 60.0 / 60.0;
    m2_log("Server GMT offset: %0.2f\n", server_offset);

}