// Author:        Ricardas Stoma
// Company:       Kolmisoft
// Year:          2014
// About:         Script periodically generates invoices


#define _GNU_SOURCE
#define SCRIPT_VERSION      "1.67"
#define SCRIPT_NAME         "m2_invoices"
#define NO_PROCESS_LOCK     1

#include "m2_functions.c"
#include "m2_invoices.h"
#include <curl/curl.h>
#include "cJSON.h"
#include "cJSON.c"

// MAIN FUNCTION

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

    char wait_time_buffer[256] = "-1";
    char show_invoice_lines_buffer[256] = "";
    char skip_missing_invoices[256] = "";

    // starting sript
    m2_init("Starting invoices 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);
    atexit(error_handle);

    get_elasticsearch_host();

    // check arguments
    if (argc > 1 && strcmp(argv[1], "generate") != 0) {
        recalculate = 1;
        // get background task_id
        // it will be used to calculate progress
        m2_task_get(5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
        m2_log("Invoices will be recalculated:\n");
        int i = 0;
        for (i = 1; i < argc; i++) {
            m2_log("Invoice id: %s\n", argv[i]);
        }
    } else if (argc > 1 && strcmp(argv[1], "generate") == 0) {
        if (m2_task_get(4, &owner_id, &generate_user_id, generate_date_from, generate_date_till, generate_issue_date, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) return 1;
        generate_issue_date[19] = '\0';
        check_responsible_manager(owner_id);
        m2_log("Invoices will be generated manually\n");
        m2_log("Date from: %s\n", generate_date_from);
        m2_log("Date till: %s\n", generate_date_till);
        m2_log("Issue date: %s\n", generate_issue_date);
        m2_log("User id: %d\n", generate_user_id);
        m2_log("Owner id: %d\n", owner_id);
        m2_log("Responsible manager: %d\n", responsible_manager);
        generate_invoices_manually = 1;
        // when generating invoices manually, do not check for any missing invoices
        strcpy(skip_missing_invoices, "1");
    }

    if (task_id) {
        m2_task_lock();
        task_failed = 1;
    }

    if (generate_invoices_manually == 0) {
        if (m2_check_process_lock()) exit(1);
    }

    if (get_invoice_settings()) exit(1);

    // get initial variables
    m2_get_current_date(current_date);
    m2_log("Current server time: %s\n", current_date);
    define_timezones();
    get_server_gmt_offset();

    get_confline_date_format();
    m2_log("Date format: %s\n", confline_date_format);

    m2_get_variable("invoice_generate_wait_time", wait_time_buffer);
    m2_get_variable("show_invoice_lines", show_invoice_lines_buffer);

    if (atoi(wait_time_buffer) >= 0 && atoi(wait_time_buffer) <= 23) {
        wait_time = atoi(wait_time_buffer);
    }

    if (strlen(show_invoice_lines_buffer)) {
        show_invoice_lines = atoi(show_invoice_lines_buffer);
    }

    check_es_db_consistency_for_today();

    check_es_fields();

    // do the job
    if (recalculate == 0) {
        if (get_users_data(generate_user_id)) exit(1);
        if (check_completed_invoices()) exit(1);
        if (get_data(-1)) exit(1);

        if (generate_invoices_manually == 0) {
            // check if we need to generate missing invoices
            m2_get_variable("skip_missing_invoices", skip_missing_invoices);

            if (strlen(skip_missing_invoices) && strcmp(skip_missing_invoices, "1") == 0) {
                m2_log("Missing invoices will not be generated!\n");
            } else {
                m2_log("\n");
                m2_log("Checking missing invoices\n");
                if (check_missing_invoices()) exit(1);
            }
        }
    } else {
        // do recalculate
        int i = 0;
        if (get_recalculate_invoices(argc, argv)) exit(1);
        for (i = 0; i < recalculate_invoices_count; i++) {
            m2_log("\n");
            m2_log("<<<<<<<<<<<<< Recalculating invoice (%lld) >>>>>>>>>>>> \n", recalculate_invoices[i].id);
            m2_log("\n");
            // set known invoice data for user
            strcpy(recalculate_from, recalculate_invoices[i].period_start);
            strcpy(recalculate_till, recalculate_invoices[i].period_end);
            m2_log("Date from: %s\n", recalculate_from);
            m2_log("Date till: %s\n", recalculate_till);
            // reset user count
            users_count = 0;
            if (get_users_data(recalculate_invoices[i].user_id)) exit(1);
            // set known invoice data for user
            users[0].invoice_id = recalculate_invoices[i].id;
            if (get_data(0)) exit(1);
        }
    }

    // check if invoice_report script should be executed
    if (new_invoice && recalculate == 0) {
        if (check_invoice_report()) {
            m2_log("New invoices should be reported to admin/manager!\n");
            system("/usr/local/m2/m2_invoices_report &");
        } else {
            m2_log("New invoices should not be reported at once when generated\n");
        }
    }

    if (task_id) m2_task_finish();

    if (users) free(users);
    if (invoice_details) free(invoice_details);
    if (invoices) free(invoices);

    task_failed = 0;

    m2_log("Script completed!\n");

    return 0;

}


/*

    ############  FUNCTIONS #######################################################

*/


/*
    Get all users data related to invoices
*/

int get_billing_period_integer(char *billing_period) {

    // 1 - weekly
    // 2 - bi-weekly
    // 3 - monthly

    int id = 0;

    if (strcmp(billing_period, "weekly") == 0) id = 1;
    if (strcmp(billing_period, "bi-weekly") == 0) id = 2;
    if (strcmp(billing_period, "monthly") == 0) id = 3;
    if (strcmp(billing_period, "bimonthly") == 0) id = 4;
    if (strcmp(billing_period, "quarterly") == 0) id = 5;
    if (strcmp(billing_period, "halfyearly") == 0) id = 6;
    if (strcmp(billing_period, "dynamic") == 0) id = 7;

    if (id == 0) {
        m2_log("Can't determine billing period. Aborting...\n");
        exit(1);
    } else {
        return id;
    }

}

float get_user_timezone_offset(char *zone_name) {

    int i;

    for (i = 0; i < tz_count; i++) {
        if (strstr(tz[i].zone, zone_name)) {
            return tz[i].offset;
        }
    }

    m2_log("Can't determine timezone offset for '%s'. Aborting...\n", zone_name);
    exit(1);

}


/*
    Get users and calculate billing periods
*/


int get_users_data(int user_id) {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[2000] = "";
    char blocked_sql[256] = "";
    char where_sql[128] = "WHERE users.generate_invoice = 1 AND billing_period NOT IN('bimonthly','quarterly','halfyearly','dynamic')";
    char responsible_manager_sql[128] = "";

    // if we have specific user, then ignore generate_invoice setting
    // because by choosing specific user we explicitly tell script to generate invoice
    if (user_id > 0) {
        sprintf(where_sql, "WHERE users.id = %d", user_id);
    }

    if (generate_invoices_manually && user_id == -1) {
        strcpy(where_sql, "WHERE users.generate_invoice_manually = 1");
    }

    if (do_not_generate_invoices_for_blocked_users) {
        strcpy(blocked_sql, " AND users.blocked = 0");
    }

    if (responsible_manager) {
        sprintf(responsible_manager_sql, " AND users.responsible_accountant_id = %d", owner_id);
    }

    sprintf(query, "SELECT users.id, username, currencies.exchange_rate, currencies.name, actions.date, users.billing_period, users.time_zone, "
       "addresses.address, addresses.city, addresses.postcode, addresses.state, addresses.direction_id, addresses.phone, timezones.offset, "
       "users.invoice_grace_period, users.time_zone, users.vat_number, compound_tax, tax1_enabled, tax2_enabled, tax3_enabled, tax4_enabled, "
       "tax1_value, tax2_value, tax3_value, tax4_value, IF(LENGTH(TRIM(CONCAT(users.first_name, ' ', users.last_name))) > 0, TRIM(CONCAT(users.first_name, ' ', users.last_name)), users.username) AS 'nice_username' "
       "FROM users "
       "JOIN currencies ON currencies.id = users.currency_id "
       "LEFT JOIN actions ON actions.target_id = users.id AND actions.action = 'user_created' "
       "LEFT JOIN addresses ON addresses.id = users.address_id "
       "LEFT JOIN timezones ON timezones.zone = users.time_zone "
       "LEFT JOIN taxes ON taxes.id = users.tax_id "
       "%s%s%s AND ((SELECT id FROM devices WHERE op = 1 AND user_id = users.id LIMIT 1) OR (SELECT id FROM subscriptions WHERE user_id = users.id LIMIT 1))", where_sql, blocked_sql, responsible_manager_sql);

    // get user data
    if (m2_mysql_query(query)) {
        return 1;
    }

    // get results
    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {

                // skip admin user
                if (row[0] && atoi(row[0]) > 0) {

                    users = realloc(users, (users_count + 1) * sizeof(users_t));
                    memset(&users[users_count], 0, sizeof(users_t));

                    if (row[0]) users[users_count].id = atoi(row[0]);
                    if (row[1]) strcpy(users[users_count].name, row[1]);
                    if (row[2]) users[users_count].exchange_rate = atof(row[2]);
                    if (row[3]) strcpy(users[users_count].currency, row[3]);
                    if (row[4]) strcpy(users[users_count].created_at, row[4]); else strcpy(users[users_count].created_at, "");
                    if (row[5]) {
                        users[users_count].billing_period = get_billing_period_integer(row[5]);
                    } else {
                        if (row[0]) {
                            m2_log("Billing period is empty for user: %s\n", row[0]);
                        } else {
                            m2_log("Billing period is empty for user\n");
                        }
                        continue;
                    }
                    if (row[7]) strcpy(users[users_count].address, row[7]);
                    if (row[8]) strcpy(users[users_count].city, row[8]);
                    if (row[9]) strcpy(users[users_count].postcode, row[9]);
                    if (row[10]) strcpy(users[users_count].state, row[10]);
                    if (row[11]) users[users_count].direction_id = atoi(row[11]);
                    if (row[12]) strcpy(users[users_count].phone, row[12]);
                    if (row[13]) {
                        users[users_count].timezone_offset = ((float)atoi(row[13]) / 60.0 / 60.0);
                    } else {
                        if (row[6]) {
                            strcpy(users[users_count].timezone, row[6]);
                            users[users_count].timezone_offset = get_user_timezone_offset(row[6]);
                        }
                    }
                    if (row[14]) users[users_count].invoice_grace_period = atoi(row[14]);
                    if (row[15]) strcpy(users[users_count].timezone, row[15]);
                    if (row[16]) strcpy(users[users_count].vat_number, row[16]);
                    if (row[17]) users[users_count].tax_compound = atoi(row[17]);
                    if (row[18]) users[users_count].tax1 = atoi(row[18]);
                    if (row[19]) users[users_count].tax2 = atoi(row[19]);
                    if (row[20]) users[users_count].tax3 = atoi(row[20]);
                    if (row[21]) users[users_count].tax4 = atoi(row[21]);
                    if (row[22]) users[users_count].tax1_value = atof(row[22]);
                    if (row[23]) users[users_count].tax2_value = atof(row[23]);
                    if (row[24]) users[users_count].tax3_value = atof(row[24]);
                    if (row[25]) users[users_count].tax4_value = atof(row[25]);
                    if (row[26]) strcpy(users[users_count].nice_name, row[26]);

                    users_count++;

                }

            }
        }
    }

    mysql_free_result(result);

    if (users_count == 0) {
        m2_log("No suitable users found...\n");
        if (task_id) {
            task_failed = 0;
            m2_task_finish();
        }
        return 1;
    } else if (recalculate == 0) {

        int i;
        char *tz;

        if (generate_invoices_manually == 0) {

            m2_log("\n");
            m2_log("Users found:\n");
            for (i = 0; i < users_count; i++) {
                m2_log("id: %d, username: %s, created_at: %s, currency: %s, exchange_rate: %0.3f, billing_period: %d, timezone: %s (%.1f)\n",
                    users[i].id, users[i].name, users[i].created_at, users[i].currency, users[i].exchange_rate,
                    users[i].billing_period, users[i].timezone, users[i].timezone_offset);
            }
            m2_log("\n");
            m2_log("Total users: %d\n", users_count);
            m2_log("\n");

            if (get_first_calldates()) return 1;
            if (check_if_user_has_calls()) return 1;

            // get server timezone offset at the time when user was created
            for (i = 0; i < users_count; i++) {
                if (!users[i].skip) {
                    users[i].server_offset_at_user_created_date = get_server_gmt_offset_date(users[i].created_at);
                    users[i].user_offset_at_user_created_date = get_timezone_offset_for_date(users[i].created_at, users[i].timezone);
                }
            }

            // set UTC time zone to avoid daylight saving
            tz = getenv("TZ");
            if (tz) tz = strdup(tz);
            setenv("TZ", "UTC", 1);
            tzset();

            // check if invoices can be generated
            for (i = 0; i < users_count; i++) {

                if (!users[i].skip) {

                    m2_log("\n");
                    m2_log("************** Checking user_id: %d ***************\n", users[i].id);

                    time_t rawtime;
                    struct tm ptm;

                    // calculate current user time
                    // if user server time is 2014-01-01 10:00:00 and user GMT offset is +2
                    // then calculated user time is 2014-01-01 12:00:00
                    time(&rawtime);
                    rawtime = rawtime + (time_t)round(users[i].timezone_offset * 60.0 * 60.0);

                    // convert to time structure and format to string
                    gmtime_r(&rawtime, &ptm);
                    char user_date[20] = "";
                    strftime(user_date, sizeof(user_date), DATE_FORMAT, &ptm);

                    // check_date = user_date
                    // check_date will be modified so we use this variable to avoid changing original user_date variable
                    char check_date[20] = "";
                    strcpy(check_date, user_date);
                    strcpy(users[i].user_date, user_date);

                    // check user creation date
                    // adjust server user_created_at time according to user time

                    // save current server offset
                    double old_server_offset = server_offset;
                    server_offset = users[i].server_offset_at_user_created_date;

                    adjust_to_target_time(users[i].created_at, users[i].created_at_user_time, users[i].user_offset_at_user_created_date, 1);

                    // restore server offset
                    server_offset = old_server_offset;

                    // we can generate invoice only for previous billing_period, so we should check if new billing period started
                    // and also we should wait X hours, so calls stared in previous period have time to end

                    // adjust check_date to match new billing_period start + additional time for calls to finish
                    // if billing_period = 1 then check_date is adjusted to this week's monday
                    // if billing_period = 2 then check_date is adjusted to first day of this month, if current day is <= 15
                    // and to this month's 16th day, if current day is < 15
                    // if billing period = 3 then check_date is adjusted to this month's first days

                    // basically, check_date will define when previous period truly ends and new billing period starts
                    // we can generate invoice only for previous period and only when it is finished (we are in the new billing period)

                    // monthly
                    if (users[i].billing_period == 3) {
                        // set first day of this month + X hours
                        char tmp_buffer[256] = "";
                        sprintf(tmp_buffer, "01 %02d:00:00", wait_time);
                        strcpy(check_date + 8, tmp_buffer);
                    // bi-weekly
                    } else if (users[i].billing_period == 2) {
                        if (ptm.tm_mday > 15) {
                            // set 16th day of this month + X hours
                            char tmp_buffer[256] = "";
                            sprintf(tmp_buffer, "16 %02d:00:00", wait_time);
                            strcpy(check_date + 8, tmp_buffer);
                        } else {
                            // set first day of this month + X hours
                            char tmp_buffer[256] = "";
                            sprintf(tmp_buffer, "01 %02d:00:00", wait_time);
                            strcpy(check_date + 8, tmp_buffer);
                        }
                    // weekly
                    } else if (users[i].billing_period == 1) {
                        // set date to this week's monday
                        // copy current time in second to another variable
                        time_t time_tmp = rawtime;
                        struct tm ptm_tmp;
                        // get current week day
                        // if current day is friday (5) then we need to go back 4 (5 - 1) days to get to monday
                        int days = ptm.tm_wday;
                        if (days == 0) days = 7;
                        // subtract 1 day, because if current day is friday (5) then we need to go back 4 (5 - 1) days to get to monday
                        days = days - 1;
                        // subtract X days from current day, to get to monday
                        time_tmp -= days * 24 * 60 * 60;
                        // convert time in seconds to time structure
                        gmtime_r(&time_tmp, &ptm_tmp);
                        // format time to string
                        strftime(check_date, sizeof(check_date), DATE_FORMAT, &ptm_tmp);
                        // set time to 0X:00:00
                        // X is time given for calls to finish which started in the previous period
                        char tmp_buffer[256] = "";
                        sprintf(tmp_buffer, "%02d:00:00", wait_time);
                        strcpy(check_date + 11, tmp_buffer);
                    }

                    m2_log("User's current time: %s, invoice can be generated starting from: %s\n", user_date, check_date);

                    // check if user_date >= check_date (if current user time is in the new billing period)
                    if (compare_dates(user_date, check_date, 0)) {

                        time_t check_time;
                        struct tm check_tm, check_ptm;

                        // reset that X hour (time for calls to finish, which started in the previous period)
                        // we don't need it anymore, because invoices will be generated without that additional hour
                        // now check_date defines current billing period start, without additional hours, just the way users sees it
                        check_date[12] = '0';
                        // convert check_date to seconds
                        memset(&check_tm, 0, sizeof(struct tm));
                        strptime(check_date, DATE_FORMAT, &check_tm);
                        check_time = mktime(&check_tm);

                        // now we need calculate previous billing period start date

                        // check_date now is current billing period start
                        // we need to convert this time to previous billing period
                        // when billing_period = 3 then subtract 1 month
                        // when billing_period = 2 then set day to first day of current month if current day is > 15
                        // set day to 16th day of previous month's if current day is <= 15
                        // when billing_period = 1 then set day to monday of previous week

                        // here we will adjust time structure check_tm to set correct billing period start date
                        // and later we will format it to string

                        // !!! calculate period start

                        // monthly
                        if (users[i].billing_period == 3) {
                            // subtract 1 month from current date
                            // if current month is january, set month to 11 (december) and subtract 1 year
                            if (check_tm.tm_mon > 0) {
                                check_tm.tm_mon -= 1;
                            } else {
                                check_tm.tm_mon = 11;
                                check_tm.tm_year -= 1;
                            }
                        // bi-weekly
                        } else if (users[i].billing_period == 2) {
                            // if current day is > 15
                            // set day to 1st day of this month
                            if (check_tm.tm_mday > 15) {
                                check_tm.tm_mday = 1;
                            } else {
                                // subtract 1 month from current date
                                // if current month is january, set month to 11 (december) and subtract 1 year
                                // and set day to 16th
                                if (check_tm.tm_mon > 0) {
                                    check_tm.tm_mon -= 1;
                                } else {
                                    check_tm.tm_mon = 11;
                                    check_tm.tm_year -= 1;
                                }
                                check_tm.tm_mday = 16;
                            }
                        // weekly
                        } else if (users[i].billing_period == 1) {
                            // calculate previuos week's date at monday
                            struct tm ptm_tmp;
                            time_t time_tmp = check_time;
                            // get current week day
                            int days = check_tm.tm_wday;
                            // sunday = 0, so we adjust it to be 7
                            if (days == 0) days = 7;
                            // add additional 6 days to get to previous week's monday
                            // for example, if current day is friday
                            // then current time minus 5 days is previous week's sunday and minus 6 days, it is monday
                            // so we get previous week's monday
                            days = days + 6;
                            // subtract days to get to monday
                            time_tmp -= days * 24 * 60 * 60;
                            // convert time to string
                            gmtime_r(&time_tmp, &ptm_tmp);
                            strftime(check_date, sizeof(check_date), DATE_FORMAT, &ptm_tmp);
                            // set time to 00:00:00
                            strcpy(check_date + 11, "00:00:00");
                        }

                        // save current day
                        int check_day = ptm.tm_mday;

                        // if billing period is weekly, then we already have formated string
                        // otherwise format date to string
                        if (users[i].billing_period != 1) {
                            check_time = mktime(&check_tm);
                            gmtime_r(&check_time, &check_ptm);
                            // format time string
                            strftime(check_date, sizeof(check_date), DATE_FORMAT, &check_ptm);
                        }

                        // set period_start
                        strcpy(users[i].period_start, check_date);

                        // !!! calculate period end

                        // reset check_date variable
                        // we will temporary store period end datetime here
                        strcpy(check_date, "");

                        // monthly or bi-weekly when day is < 15
                        if (users[i].billing_period == 3 || (users[i].billing_period == 2 && check_day <= 15)) {
                            // get last datetime of previous month
                            // if current datetime is 2014-01-15 12:20:15
                            // then last datetime of previous month is 2013-12-31 23:59:59
                            get_last_datetime_of_month(user_date, check_date, 0);
                        // bi-weekly
                        } else if (users[i].billing_period == 2) {
                            if (check_day > 15) {
                                // simply set date to this month's 15th day and last hour, minute and second
                                strcpy(check_date, user_date);
                                strcpy(check_date + 8, "15 23:59:59");
                            }
                        // weekly
                        } else if (users[i].billing_period == 1) {
                            // we need to set check date to previous period start
                            // for example 2014-01-06 00:00:00
                            // now date points to monday
                            // if we add 6 days, then datetime will be sunday of the same week
                            // after that, we only need to adjust time to last hour, minute and second of that week
                            time_t tmp_time;
                            struct tm tmp_tm, tmp_ptm;
                            // copy period_start to check_date
                            strcpy(check_date, users[i].period_start);
                            memset(&tmp_tm, 0, sizeof(struct tm));
                            strptime(check_date, DATE_FORMAT, &tmp_tm);
                            // get time in seconds
                            tmp_time = mktime(&tmp_tm);
                            // add 6 days
                            tmp_time += 6 * 24 * 60 * 60;
                            // convert to time structure
                            gmtime_r(&tmp_time, &tmp_ptm);
                            // format time string
                            strftime(check_date, sizeof(check_date), DATE_FORMAT, &tmp_ptm);
                            // adjust time to last hour, minute and second of that week
                            strcpy(check_date + 11, "23:59:59");
                        }

                        // set period_end
                        strcpy(users[i].period_end, check_date);

                        // check if user was created after period ended
                        // in this case, we cannot generate invoice for previous billing period
                        if (compare_dates(users[i].created_at_user_time, users[i].period_end, 0)) {
                            m2_log("User was created after after billing period end. User created_time: %s, billing period start: %s, billing period_end: %s\n", users[i].created_at_user_time, users[i].period_start, users[i].period_end);
                            users[i].skip = 1;
                        } else {
                            // if user was created in the previous billing period
                            // we need to adjust billing period start to user creation time
                            if (compare_dates(users[i].created_at_user_time, users[i].period_start, 0)) {
                                m2_log("Users was created after billing period start. Period start date will be set to user_created_time: %s (time is in user's timezone)\n", users[i].created_at_user_time);
                                strcpy(users[i].period_start, users[i].created_at_user_time);
                            }
                        }

                        // adjust period_start according to server time (this will be used in SQL queries)
                        adjust_to_target_time(users[i].period_start, users[i].server_period_start, users[i].timezone_offset, 0);
                        // adjust period_end according to server time (this will be used in SQL queries)
                        adjust_to_target_time(users[i].period_end, users[i].server_period_end, users[i].timezone_offset, 0);

                        // check if we got dates and if we should skip this user
                        if (strlen(users[i].server_period_end) == 19 && strlen(users[i].server_period_end) == 19 && !users[i].skip) {
                            // check if date ranges are correct
                            if (compare_dates(users[i].server_period_start, users[i].server_period_end, 0)) {
                                // skip user
                                m2_log("Period_end < period_start. Skipping this user...\n");
                                users[i].skip = 1;
                                // we should also skip this user when calculating missing invoices
                                users[i].skip_control = 1;
                            }
                        } else {
                            // skip user
                            m2_log("Cannot generate invoice for this user\n");
                            users[i].skip = 1;
                        }
                    } else {
                        // skip user
                        m2_log("Cannot generate invoice for this user\n");
                        users[i].skip = 1;
                        // we should also skip this user when calculating missing invoices
                        users[i].skip_control = 1;
                    }

                    if (users[i].skip != 1) {
                        m2_log("Calculated current_user_time: %s, user_created_at_in_user_time: %s, period_start: %s, period_end: %s, server_period_start: %s, server_period_end: %s, due_date: %s\n",
                                user_date, users[i].created_at_user_time, users[i].period_start, users[i].period_end, users[i].server_period_start, users[i].server_period_end, users[i].due_date);
                    }
                }

            }

            // restore timezone sessions variable
            if (tz) {
                setenv("TZ", tz, 1);
                free(tz);
            } else {
                unsetenv("TZ");
            }
            tzset();
        } else {
            m2_log("\n");
            m2_log("Users found:\n");
            for (i = 0; i < users_count; i++) {

                strcpy(users[i].period_start, generate_date_from);
                strcpy(users[i].period_end, generate_date_till);

                if (server_offset != users[i].timezone_offset) {
                    adjust_to_target_time_with_tz_handle(generate_date_from, users[i].server_period_start, users[i].timezone_offset, 0);
                    adjust_to_target_time_with_tz_handle(generate_date_till, users[i].server_period_end, users[i].timezone_offset, 0);
                } else {
                    strcpy(users[i].server_period_start, generate_date_from);
                    strcpy(users[i].server_period_end, generate_date_till);
                }

                m2_log("id: %d, username: %s, created_at: %s, currency: %s, exchange_rate: %.4f, billing_period: %d, grace_period: %d, "
                    "timezone: %s, offset: %.1f, period_start: %s, period_end: %s, period_start_in_server_time: %s, period_end_in_server_time: %s\n",
                    users[i].id, users[i].name, users[i].created_at, users[i].currency, users[i].exchange_rate, users[i].billing_period, users[i].invoice_grace_period,
                    users[i].timezone, users[i].timezone_offset, generate_date_from, generate_date_till, users[i].server_period_start, users[i].server_period_end);
            }
            m2_log("\n");

        }

    } else {

        char *tz;
        int i = 0;

        m2_log("\n");
        m2_log("Users found:\n");
        for (i = 0; i < users_count; i++) {
            m2_log("id: %d, username: %s, created_at: %s, currency: %s, exchange_rate: %.4f, billing_period: %d, grace_period: %d, timezone: %s, offset: %.1f, phone: %s\n",
                users[i].id, users[i].name, users[i].created_at, users[i].currency, users[i].exchange_rate,
                users[i].billing_period, users[i].invoice_grace_period, users[i].timezone, users[i].timezone_offset, users[i].phone);
        }

        // set UTC time zone to avoid daylight saving
        tz = getenv("TZ");
        if (tz) tz = strdup(tz);
        setenv("TZ", "UTC", 1);
        tzset();

        strcpy(users[0].period_start, recalculate_from);
        strcpy(users[0].period_end, recalculate_till);

        // adjust period_start according to server time (this will be used in SQL queries)
        adjust_to_target_time(recalculate_from, users[0].server_period_start, users[0].timezone_offset, 0);
        // adjust period_end according to server time (this will be used in SQL queries)
        adjust_to_target_time(recalculate_till, users[0].server_period_end, users[0].timezone_offset, 0);

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

    return 0;

}


/*
    Define timezones for specific regions
*/


void define_timezones() {

    // set zone names
    strcpy(tz[0].zone, "Midway Island American International Date Line West");
    strcpy(tz[1].zone, "Hawaii");
    strcpy(tz[2].zone, "Alaska");
    strcpy(tz[3].zone, "Pacific Time (US & Canada) Tijuana");
    strcpy(tz[4].zone, "Chihuahua Mazatlan Mountain Time (US & Canada) Arizona");
    strcpy(tz[5].zone, "Central Time (US & Canada) Monterrey Guadalajara Mexico City Saskatchewan Central America");
    strcpy(tz[6].zone, "Eastern Time (US & Canada) Indiana (East) Lima Quito Bogota");
    strcpy(tz[7].zone, "Caracas");
    strcpy(tz[8].zone, "Georgetown La Paz Santiago Atlantic Time (Canada)");
    strcpy(tz[9].zone, "Newfoundland");
    strcpy(tz[10].zone, "Buenos Aires Greenland Brasilia Montevideo");
    strcpy(tz[11].zone, "Mid-Atlantic");
    strcpy(tz[12].zone, "Cape Verde Is. Azores");
    strcpy(tz[13].zone, "UTC Dublin Edinburgh Lisbon London Monrovia Casablanca");
    strcpy(tz[14].zone, "Belgrade Berlin Madrid Paris Prague Sarajavo Skopje Stockholm Vienna Warsaw West Central Africa Bern Bratislava Brussels Budapest Copenhagen Ljubljana Rome Amsterdam Zagreb");
    strcpy(tz[15].zone, "Cairo Bucharest Harare Helsinki Jerusalem Pretoria Sofia Istanbul Kyiv Riga Tallinn Vilnius Athens");
    strcpy(tz[16].zone, "Kuwait Nairobi Riyadh Baghdad Minsk");
    strcpy(tz[17].zone, "Tehran");
    strcpy(tz[18].zone, "Baku Moscow Muscat Tbilisi Volgograd Yerevan Abu Dhabi St. Petersburg");
    strcpy(tz[19].zone, "Karachi Tashkent Islamabad");
    strcpy(tz[20].zone, "Kolkata Mumbai New Delhi Sri Jayawardenepura Chennai");
    strcpy(tz[21].zone, "Kathmandu");
    strcpy(tz[22].zone, "Astana Dhaka Ekaterinburg Almaty");
    strcpy(tz[23].zone, "Rangoon");
    strcpy(tz[24].zone, "Hanoi Jakarta Novosibirsk Bangkok");
    strcpy(tz[25].zone, "Hong Kong Krasnoyarsk Kuala Lumpur Perth Singapore Taipei Beijing Chongqing Ulaanbaatar Urumqi");
    strcpy(tz[26].zone, "Osaka Sapporo Seoul Tokyo Irkutsk");
    strcpy(tz[27].zone, "Darwin Adelaide");
    strcpy(tz[28].zone, "Canberra Guam Hobart Melbourne Port Moresby Sydney Yakutsk Brisbane");
    strcpy(tz[29].zone, "Vladivostok New Caledonia");
    strcpy(tz[30].zone, "Fiji Kamchatka Magadan Marshall Is. Solomon Is. Wellington Auckland");
    strcpy(tz[31].zone, "Chatham Is.");
    strcpy(tz[32].zone, "Nuku'alofa");
    strcpy(tz[33].zone, "Kabul Samoa Tokelau Is.");

    // set offsets
    tz[0].offset = -11;
    tz[1].offset = -10;
    tz[2].offset = -9;
    tz[3].offset = -8;
    tz[4].offset = -7;
    tz[5].offset = -6;
    tz[6].offset = -5;
    tz[7].offset = -4.5;
    tz[8].offset = -4;
    tz[9].offset = -3.5;
    tz[10].offset = -3;
    tz[11].offset = -2;
    tz[12].offset = -1;
    tz[13].offset = 0;
    tz[14].offset = 1;
    tz[15].offset = 2;
    tz[16].offset = 3;
    tz[17].offset = 3.5;
    tz[18].offset = 4;
    tz[19].offset = 5;
    tz[20].offset = 5.5;
    tz[21].offset = 5.75;
    tz[22].offset = 6;
    tz[23].offset = 6.5;
    tz[24].offset = 7;
    tz[25].offset = 8;
    tz[26].offset = 9;
    tz[27].offset = 9.5;
    tz[28].offset = 10;
    tz[29].offset = 11;
    tz[30].offset = 12;
    tz[31].offset = 12.75;
    tz[32].offset = 13;
    tz[33].offset = 4.3;

}


/*
    Compare two date strings
*/


int compare_dates(const char *date1, const char *date2, int mode) {

    time_t t1, t2;
    struct tm tm1, tm2;

    memset(&tm1, 0, sizeof(struct tm));
    memset(&tm2, 0, sizeof(struct tm));

    strptime(date1, DATE_FORMAT, &tm1);
    strptime(date2, DATE_FORMAT, &tm2);

    t1 = mktime(&tm1);
    t2 = mktime(&tm2);

    if (!mode) {
        if (t1 >= t2) return 1;
    } else {
        if (t1 > t2) return 1;
    }

    return 0;

}


/*
    Check if invoice already exists for user previous user billing period
    these users will be skipped to prevent duplicate insertions to m2_invoices
*/


int check_completed_invoices() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    int i;

    m2_log("\n");
    m2_log("Checking completed invoices:\n");
    m2_log("\n");

    for (i = 0; i < users_count; i++) {

        if (!users[i].skip) {

            // get invoices for previous billing period
            char query[1024] = "";

            if (generate_invoices_manually) {
                sprintf(query, "SELECT id FROM m2_invoices WHERE user_id = %d AND period_start = '%s' AND period_end = '%s'",
                    users[i].id, users[i].period_start, users[i].period_end);
            } else {
                sprintf(query, "SELECT id FROM m2_invoices WHERE user_id = %d AND period_start = '%s' AND period_end = '%s'"
                               "UNION ALL "
                               "SELECT id FROM m2_zero_price_invoices WHERE user_id = %d AND period_start = '%s' AND period_end = '%s'",
                    users[i].id, users[i].period_start, users[i].period_end,
                    users[i].id, users[i].period_start, users[i].period_end);
            }

            // send query
            if (m2_mysql_query(query)) {
                return 1;
            }

            // get results
            result = mysql_store_result(&mysql);
            if (result) {
                if (mysql_num_rows(result)) {
                    row = mysql_fetch_row(result);
                    if (row[0]) {
                        m2_log("Invoice for user_id %d and date between %s and %s already exists. Invoice will not be generated for this period\n",
                            users[i].id, users[i].period_start, users[i].period_end);
                        users[i].skip = 1;
                    }
                }
            }

            mysql_free_result(result);

        }

    }

    return 0;

}


/*
    Insert new record into m2_invoices table and get that record id
*/


int insert_new_invoices(int index) {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[4000] = "";
    char generate_issue_date_for_query[256] = "";
    int i;
    char invoices_table[200] = "";
    char label[100] = "";

    sprintf(generate_issue_date_for_query, "'%s'", generate_issue_date);

    // when we have specific index of users structure
    // then skip iterations and go straight to that user
    if (index > -1) {
        i = index;
        goto single_user;
    }

    for (i = 0; i < users_count; i++) {

        if (!users[i].skip) {

            single_user:;

            if (users[i].zero_price_invoice == 1) {
                strcpy(invoices_table, "m2_zero_price_invoices");
                strcpy(users[i].invoice_number, "zero_price");
                strcpy(label, "hidden zero price ");
            } else {
                strcpy(invoices_table, "m2_invoices");
                // calculate invoice number
                generate_invoice_number(&users[i]);
            }

            new_invoice = 1;

            // escape ' character
            m2_escape_string(users[i].nice_name, '\'');
            m2_escape_string(users[i].timezone, '\'');
            m2_escape_string(users[i].address, '\'');
            m2_escape_string(users[i].city, '\'');
            m2_escape_string(users[i].postcode, '\'');
            m2_escape_string(users[i].state, '\'');
            m2_escape_string(users[i].phone, '\'');
            m2_escape_string(users[i].invoice_number, '\'');
            m2_escape_string(users[i].vat_number, '\'');

            // calculate due_date
            calculate_due_date(users[i].period_end, users[i].due_date, users[i].invoice_grace_period);

            // insert new invoice
            m2_log("Inserting new %sinvoice for user_id: %d, period_start: %s, period_end: %s\n",
                label, users[i].id, users[i].period_start, users[i].period_end);

            sprintf(query, "INSERT INTO %s (user_id, period_start, period_end, status, created_at, timezone, "
               "client_name, currency, currency_exchange_rate, due_date, client_details1, client_details2, "
               "client_details3, client_details4, client_details5, client_details6, client_details7, number, status_changed, issue_date, grace_period) "
               "VALUES(%d, '%s', '%s', 'In process', NOW(), '%s (GMT %s%g)', '%s', '%s', %f, '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', NOW(), %s, '%d')",
               invoices_table, users[i].id, users[i].period_start, users[i].period_end, users[i].timezone, users[i].timezone_offset >= 0 ? "+" : "", users[i].timezone_offset,
               users[i].nice_name, users[i].currency, users[i].exchange_rate, users[i].due_date, users[i].address, users[i].city,
               users[i].postcode, users[i].state, users[i].direction_id, users[i].phone, users[i].vat_number, users[i].invoice_number,
               strlen(generate_issue_date) > 0 ? generate_issue_date_for_query : "NOW()", users[i].invoice_grace_period);

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

            if (users[i].zero_price_invoice == 0) {
                // now get its id
                sprintf(query, "SELECT id FROM m2_invoices WHERE user_id = %d AND period_start = '%s' and period_end = '%s'", users[i].id, users[i].period_start, users[i].period_end);

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

                // get results
                result = mysql_store_result(&mysql);
                if (result) {
                    if (mysql_num_rows(result)) {
                        row = mysql_fetch_row(result);
                        if (row[0]) {
                            users[i].invoice_id = atoll(row[0]);
                            m2_log("Inserted invoice_id: %lld, invoice number: %s\n", users[i].invoice_id, users[i].invoice_number);
                        }
                    }
                }
                mysql_free_result(result);

                // check if we got inserted invoice id
                if (users[i].invoice_id == 0) {
                    m2_log("Can't determine inserted invoice_id\n");
                    exit(1);
                }
            }

        } else {
            m2_log("Invoice already exist or it is too early to generate new one\n");
            m2_log("New invoice will not be created for for user_id: %d, period_start: %s, period_end: %s\n", users[i].id, users[i].period_start, users[i].period_end);
        }

        // when we have specific index of users structure
        // then break the loop
        if (index > -1) goto exit_single_user;

    }

    exit_single_user:

    return 0;

}


/*
    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);

}


/*
    Get server GMT offset in decimal value for given date
*/


double get_server_gmt_offset_date(char *date) {

    struct tm tm, tmm;
    time_t t;

    strptime(date, DATE_FORMAT, &tm);
    t = mktime(&tm);

    // get server gmt offset
    localtime_r(&t, &tmm);
    return tmm.tm_gmtoff / 60.0 / 60.0;

}


/*
    Insert aggregated call data to m2_invoice_lines
*/


int insert_invoice_lines(long long int invoice_id) {

    int i;
    char query[9100] = "";
    char buffer[8900] = "";

    m2_log("Total invoice lines for this user and billing period: %d\n", invoice_details_count);

    // query header
    sprintf(query, "INSERT INTO m2_invoice_lines(m2_invoice_id, destination, name, price, rate, total_time, calls, subscription_id, did_id) VALUES ");

    // format batches
    for (i = 0; i < invoice_details_count; i++) {

        char tmp_buffer[1024] = "";

        // escape ' character
        m2_escape_string(invoice_details[i].prefix, '\'');
        m2_escape_string(invoice_details[i].name, '\'');

        sprintf(tmp_buffer, "(%lld,'%s','%s',%f,%f,%d,%d,%d,%d),",
                       invoice_id, invoice_details[i].prefix,
                       invoice_details[i].name, invoice_details[i].price,
                       invoice_details[i].rate, invoice_details[i].billsec,
                       invoice_details[i].calls, invoice_details[i].subscription_id, invoice_details[i].did_id);

        // check buffer overflow
        if (strlen(buffer) > 8700) {

            // remove last comma separator
            buffer[strlen(buffer) - 1] = ' ';
            strcat(query, buffer);
            // send query
            if (m2_mysql_query(query)) {
                return 1;
            }

            // query header
            sprintf(query, "INSERT INTO m2_invoice_lines(m2_invoice_id, destination, name, price, rate, total_time, calls, subscription_id, did_id) VALUES ");
            strcpy(buffer, "");

        }

        strcat(buffer, tmp_buffer);

    }

    if (strlen(buffer)) {
        // remove last comma separator
        buffer[strlen(buffer) - 1] = ' ';
        strcat(query, buffer);

        // send query
        if (m2_mysql_query(query)) {
            return 1;
        }
    }

    return 0;

}


/*
    Check if user has invoices generated from the date he was created to current date
    if some invoices are missing, we need to generate those
*/


int check_missing_invoices() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[1024] = "";
    int i;
    char *tz;

    m2_log("\n");
    m2_log("##############################\n");
    m2_log("Starting invoice control check\n");
    m2_log("##############################\n");

    for (i = 0; i < users_count; i++) {

        if (users[i].skip_control == 1) continue;

        // should we stop control for this user?
        // we should stop when user's creation date is earlier than billing period date
        // because we only generate invoices up to user's creation date
        int stop_control = 0;

        // get all invoice periods for specific user
        sprintf(query, "SELECT period_start, period_end, 0 FROM m2_invoices WHERE user_id = %d AND manual_payment_invoice = 0 "
                       "UNION ALL "
                       "SELECT period_start, period_end, 1 FROM m2_zero_price_invoices WHERE user_id = %d AND manual_payment_invoice = 0 "
                       "GROUP BY period_start, period_end ORDER BY period_start DESC", users[i].id, users[i].id);

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

        // reset for each user
        invoices_count = 0;
        if (invoices) {
            free(invoices);
            invoices = NULL;
        }

        // get results
        result = mysql_store_result(&mysql);
        if (result) {
            if (mysql_num_rows(result)) {
                while (( row = mysql_fetch_row(result) )) {

                    invoices = realloc(invoices, (invoices_count + 1) * sizeof(invoices_t));
                    memset(&invoices[invoices_count], 0, sizeof(invoices_t));

                    invoices[invoices_count].user_id = users[i].id;
                    if (row[0]) strcpy(invoices[invoices_count].period_start, row[0]);
                    if (row[1]) strcpy(invoices[invoices_count].period_end, row[1]);
                    if (row[2]) invoices[invoices_count].zero_price = atoi(row[2]);

                    invoices_count++;

                }
            }
        }
        mysql_free_result(result);

        m2_log("\n");
        m2_log("CHECKING USER %d\n", users[i].id);

        // if (invoices_count) {
        //     m2_log("-----------------------------------------------------------------------------------------------------\n");
        // }
        // int z = 0;
        // for (z = 0; z < invoices_count; z++) {
        //     m2_log("Existing invoice for user_id: %3d, period_start: %s, period_end: %s\n", users[i].id, invoices[z].period_start, invoices[z].period_end);
        // }
        // if (invoices_count) {
        //     m2_log("-----------------------------------------------------------------------------------------------------\n");
        // }

        // set timezone to UTC to prevent daylight savings
        tz = getenv("TZ");
        if (tz) tz = strdup(tz);
        setenv("TZ", "UTC", 1);
        tzset();

        int j = 0, k = 0, found = 0;
        char label[100] = "";

        char calculated_period_start[20] = "";
        char calculated_period_end[20] = "";
        // when billing_period is bi-weekly, we will check two periods at once
        // these two variable will be used to check second half of the month
        char calculated_period_start2[20] = "";
        char calculated_period_end2[20] = "";
        char user_created_at_adjusted[20] = "";
        char current_date_adjusted[20] = "";

        time_t user_created_time, current_time, diff_time;
        struct tm user_tm, current_tm, ptm;

        // copy dates to save original values
        strcpy(user_created_at_adjusted, users[i].created_at_user_time);
        strcpy(current_date_adjusted, users[i].user_date);

        // for monthly and bi-weekly billing periods dates will be generated starting from previous month
        // but if billing period is bi-weekly and current day is > 15, then we have to handle
        // previous billing period, which is current month's first day, so in this case
        // we have to skip first time decrement
        // this variable will show if we need to handle this case
        int handle_current_month = 0;
        if (users[i].billing_period == 2) {
            struct tm tm_tmp;
            // convert user creation time to seconds
            memset(&tm_tmp, 0, sizeof(struct tm));
            strptime(users[i].user_date, DATE_FORMAT, &tm_tmp);
            if (tm_tmp.tm_mday > 15) handle_current_month = 1;
        }

        // adjust dates to middle of the month
        // we do that because we will decrement dates by 30 days
        // this way we are sure we will get to previous month no matter how many days has that month (as long as between 28 ar 31)
        strcpy(user_created_at_adjusted + 8, "15 00:00:00");
        strcpy(current_date_adjusted + 8, "15 00:00:00");

        // if billing period is weekly, then adjust dates to mondays
        // set user creation time to that week's monday
        // and current time to this week's monday
        // then we will just decrement time by 7 days go get correct periods
        if (users[i].billing_period == 1) {

            int ii;

            for (ii = 0; ii < 2; ii++) {
                // set date to this week's monday
                struct tm ptm_tmp, tm_tmp;
                char date_tmp[20] = "";
                if (ii == 0) {
                    strcpy(date_tmp, users[i].user_date);
                } else {
                     strcpy(date_tmp, users[i].created_at_user_time);
                }
                // convert user creation time to seconds
                memset(&tm_tmp, 0, sizeof(struct tm));
                strptime(date_tmp, DATE_FORMAT, &tm_tmp);
                // copy current time in second to another variable
                time_t time_tmp = mktime(&tm_tmp);;
                // get current week day
                // if current day is friday (5) then we need to go back 4 (5 - 1) days to get to monday
                int days = tm_tmp.tm_wday;
                // sunday = 0, so we adjust it to be 7
                if (days == 0) days = 7;
                // subtract 1 day, because if current day is friday (5) then we need to go back 4 (5 - 1) days to get to monday
                days = days - 1;
                // subtract X days from current day, to get to monday
                time_tmp -= days * 24 * 60 * 60;
                // convert time in seconds to time structure
                gmtime_r(&time_tmp, &ptm_tmp);
                // format time to string
                strftime(date_tmp, sizeof(date_tmp), DATE_FORMAT, &ptm_tmp);
                // set time to 0X:00:00
                // X is time given for calls to finish which started in the previous period
                strcpy(date_tmp + 11, "00:00:00");
                // convert current time to seconds
                memset(&tm_tmp, 0, sizeof(struct tm));
                strptime(date_tmp, DATE_FORMAT, &tm_tmp);
                if (ii == 0) {
                    current_time = mktime(&tm_tmp);
                } else {
                    user_created_time = mktime(&tm_tmp);
                }
            }

        } else {

            // convert user creation time to seconds
            memset(&user_tm, 0, sizeof(struct tm));
            strptime(user_created_at_adjusted, DATE_FORMAT, &user_tm);
            user_created_time = mktime(&user_tm);

            // convert current time to seconds
            memset(&current_tm, 0, sizeof(struct tm));
            strptime(current_date_adjusted, DATE_FORMAT, &current_tm);
            current_time = mktime(&current_tm);

        }

        // calculate month/days difference between current date and user creation time
        if (users[i].billing_period == 1) {
            diff_time = ceil((float)(difftime(current_time, user_created_time))/60.0/60/24/7) + 1;
        } else {
            diff_time = ceil((float)(difftime(current_time, user_created_time))/60.0/60/24/30) + 1;
        }

        // we need one more iteration if current month needs to be handled
        if (handle_current_month) diff_time++;

        m2_log("Total billing periods: %d\n", (int)diff_time - 1);

        // to prevent crazy loops
        if (diff_time < 0) diff_time = 0;
        if (diff_time > 30) diff_time = 30;

        for (j = 0; j < diff_time; j++) {

            if (users[i].billing_period == 3 || users[i].billing_period == 2) {

                if (!(users[i].billing_period == 2 && handle_current_month)) {
                    // subtract 1 month
                    current_time -= 30*60*60*24;
                }

                gmtime_r(&current_time, &ptm);

                // calculate period_start
                strftime(calculated_period_start, sizeof(calculated_period_start), DATE_FORMAT, &ptm);
                strcpy(calculated_period_start + 8, "01 00:00:00");

                // for bi-weekly
                if (users[i].billing_period == 2) {
                    // calculate period_start
                    strftime(calculated_period_start2, sizeof(calculated_period_start2), DATE_FORMAT, &ptm);
                    strcpy(calculated_period_start2 + 8, "16 00:00:00");
                }

                // calculate period_end
                get_last_datetime_of_month(calculated_period_start, calculated_period_end, 1);

                // for bi-weekly
                if (users[i].billing_period == 2) {
                    strcpy(calculated_period_end + 8, "15 23:59:59");
                    get_last_datetime_of_month(calculated_period_start, calculated_period_end2, 1);
                }
            } else {

                // calculate period_end
                time_t time_tmp = current_time - 1;
                gmtime_r(&time_tmp, &ptm);
                strftime(calculated_period_end, sizeof(calculated_period_end), DATE_FORMAT, &ptm);
                strcpy(calculated_period_end + 11, "23:59:59");

                // subtract 7 days
                current_time -= 7*60*60*24;
                gmtime_r(&current_time, &ptm);

                // calculate period_start
                strftime(calculated_period_start, sizeof(calculated_period_start), DATE_FORMAT, &ptm);
                strcpy(calculated_period_start + 11, "00:00:00");

            }

            if (users[i].billing_period == 2 && compare_dates(users[i].created_at_user_time, calculated_period_start2, 0)) {
                stop_control = 1;
                strcpy(calculated_period_start2, users[i].created_at_user_time);
            }

            // check for bi-weekly
            if (users[i].billing_period == 2 && handle_current_month == 0) {

                struct tm tm;
                time_t start_time = 0, end_time = 0;
                time_t calculated_start_time = 0, calculated_end_time = 0;

                memset(&tm, 0, sizeof(struct tm));
                strptime(calculated_period_start2, DATE_FORMAT, &tm);
                start_time = mktime(&tm);

                memset(&tm, 0, sizeof(struct tm));
                strptime(calculated_period_end2, DATE_FORMAT, &tm);
                end_time = mktime(&tm);

                // check if invoice exists for current calculated billiong period
                k = 0;
                found = 0;
                strcpy(label, "");
                for (k = 0; k < invoices_count; k++) {

                    memset(&tm, 0, sizeof(struct tm));
                    strptime(invoices[k].period_start, DATE_FORMAT, &tm);
                    calculated_start_time = mktime(&tm);

                    memset(&tm, 0, sizeof(struct tm));
                    strptime(invoices[k].period_end, DATE_FORMAT, &tm);
                    calculated_end_time = mktime(&tm);

                    // invoice is considered as 'found' if dates do not differ more than 24h
                    if ((abs(start_time - calculated_start_time) < 24*60*60) && (abs(end_time - calculated_end_time) < 24*60*60)) {
                        found = 1;
                        if (invoices[k].zero_price) {
                            strcpy(label, " - ZERO PRICE");
                        }
                        break;
                    }
                }

                if (!found) {

                    // adjust period_start according to server time
                    adjust_to_target_time(calculated_period_start2, users[i].server_period_start, users[i].timezone_offset, 0);
                    // adjust period_end according to server time
                    adjust_to_target_time(calculated_period_end2, users[i].server_period_end, users[i].timezone_offset, 0);

                    // reset old data
                    users[i].invoice_id = 0;
                    strcpy(users[i].period_start, calculated_period_start2);
                    strcpy(users[i].period_end, calculated_period_end2);

                    m2_log("Period_start: %s, period_end: %s [MISSING]\n", calculated_period_start2, calculated_period_end2);
                    if (get_data(i)) exit(1);

                } else {
                    // check next missing date
                    m2_log("Period_start: %s, period_end: %s [OK%s]\n", calculated_period_start2, calculated_period_end2, label);
                }

                if (stop_control) goto stop_control_label;

            }

            // set last date to start from user_created date
            if (compare_dates(users[i].created_at_user_time, calculated_period_start, 0)) {
                stop_control = 1;
                if (!compare_dates(users[i].created_at_user_time, calculated_period_end, 0)) {
                    strcpy(calculated_period_start, users[i].created_at_user_time);
                } else {
                    goto stop_control_label;
                }
            }

            // check if invoice exists for current calculated billing period

            struct tm tm;
            time_t start_time = 0, end_time = 0;
            time_t calculated_start_time = 0, calculated_end_time = 0;

            memset(&tm, 0, sizeof(struct tm));
            strptime(calculated_period_start, DATE_FORMAT, &tm);
            start_time = mktime(&tm);

            memset(&tm, 0, sizeof(struct tm));
            strptime(calculated_period_end, DATE_FORMAT, &tm);
            end_time = mktime(&tm);

            k = 0;
            found = 0;
            strcpy(label, "");
            for (k = 0; k < invoices_count; k++) {

                memset(&tm, 0, sizeof(struct tm));
                strptime(invoices[k].period_start, DATE_FORMAT, &tm);
                calculated_start_time = mktime(&tm);

                memset(&tm, 0, sizeof(struct tm));
                strptime(invoices[k].period_end, DATE_FORMAT, &tm);
                calculated_end_time = mktime(&tm);

                // invoice is considered as 'found' if dates do not differ more than 24h
                if ((abs(start_time - calculated_start_time) < 24*60*60) && (abs(end_time - calculated_end_time) < 24*60*60)) {
                    found = 1;
                    if (invoices[k].zero_price) {
                        strcpy(label, " - ZERO PRICE");
                    }
                    break;
                }
            }

            // printf("Calculated period_start: %s, period_end: %s\n", calculated_period_start, calculated_period_end);
            if (!found) {

                // adjust period_start according to server time
                adjust_to_target_time(calculated_period_start, users[i].server_period_start, users[i].timezone_offset, 0);
                // adjust period_end according to server time
                adjust_to_target_time(calculated_period_end, users[i].server_period_end, users[i].timezone_offset, 0);

                // reset old data
                users[i].invoice_id = 0;
                strcpy(users[i].period_start, calculated_period_start);
                strcpy(users[i].period_end, calculated_period_end);

                m2_log("Period_start: %s, period_end: %s [MISSING]\n", calculated_period_start, calculated_period_end);
                if (get_data(i)) exit(1);

            } else {
                m2_log("Period_start: %s, period_end: %s [OK%s]\n", calculated_period_start, calculated_period_end, label);
                // check next missing date
            }

            // turn of current month handling
            if (users[i].billing_period == 2) {
                handle_current_month = 0;
            }

            stop_control_label:
            if (stop_control) break;

        }

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

    }

    return 0;

}


/*
    Adjust datetime to to server time or to user 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 adjusted datetime to server time is 2014-01-22 13:00:33

    same with server time to user time
*/


void adjust_to_target_time(char *date, char *buffer, float offset, int target) {

    // 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);

    // target = 1, adjust to user time
    // target = 0, adjust to server time
    if (target) {
        user_time = mktime(&user_tm) - (time_t)round((server_offset - offset) * 60.0 * 60.0);
    } else {
        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);

}


/*
    Format current date to last datetime of current month of previous month

    for example: if current datetime is 2014-01-23 15:10:20, then
    last datetime of current month is 2014-01-31 23:59:59
    last datetime of previous month is 2013-12-31 23:59:59

    current_month = 1, get last datetime for current month
    current_month = 0, get last datetime for previous month
*/


int get_last_datetime_of_month(char *date, char *buffer, int current_month) {

    time_t tmp_time;
    struct tm tmp_tm, tmp_ptm;
    char tmp_date[20] = "";

    // copy date to tmp
    strcpy(tmp_date, date);
    // set tmp_date to first day of month
    strcpy(tmp_date + 8, "01 00:00:00");
    // format time structure according to date
    memset(&tmp_tm, 0, sizeof(struct tm));
    strptime(tmp_date, DATE_FORMAT, &tmp_tm);

    // get seconds
    tmp_time = mktime(&tmp_tm);

    // in case we need to know last datetime of current month
    if (current_month) {
        // add 31 days and few hours to advance to next month
        tmp_time += (31*60*60*24 + 2*60*60);
        // convert seconds to time structure
        gmtime_r(&tmp_time, &tmp_ptm);
        // format date string
        strftime(tmp_date, sizeof(tmp_date), DATE_FORMAT, &tmp_ptm);
        // reset back to first day of calculated month
        strcpy(tmp_date + 8, "01 00:00:00");
        // convert date string back to time structure
        memset(&tmp_tm, 0, sizeof(struct tm));
        strptime(tmp_date, DATE_FORMAT, &tmp_tm);
        // get adjusted time in seconds
        tmp_time = mktime(&tmp_tm);
    }

    // go back 10 seconds to get back to previous month
    tmp_time -= 10;
    // convert date to time structure
    gmtime_r(&tmp_time, &tmp_ptm);
    if (buffer != NULL) {
        // format date string
        strftime(buffer, 20, DATE_FORMAT, &tmp_ptm);
        // set last hour, minute, second of calculated month
        strcpy(buffer + 11, "23:59:59");
        return 0;
    } else {
        return tmp_ptm.tm_mday;
    }

}


/*
    If user doen't have creation date, then invoices will be generated starting from user's first call
    This functions finds first calldates for users whithout creation date
*/


int get_first_calldates() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[1024] = "";
    char query_insert[1024] = "";
    int i;

    m2_log("Checking if all users have created_at date\n");

    for (i = 0; i < users_count; i++) {

        if (!strlen(users[i].created_at)) {

            // get calldates for users without creation date
            sprintf(query, "SELECT min(id) FROM calls WHERE user_id = %d", users[i].id);

            // send query
            if (m2_mysql_query(query)) {
                return 1;
            }

            // get results
            result = mysql_store_result(&mysql);
            if (result) {
                if (mysql_num_rows(result)) {
                    row = mysql_fetch_row(result);
                    if (row[0]) strcpy(users[i].created_at, row[0]);
                }
            }

            if (!strlen(users[i].created_at)) {
                m2_log("User id: %d doesn't have created date and first call's date could not be found. Invoices will no be generated for this user\n", users[i].id);
                users[i].skip = 1;
                users[i].skip_control = 1;
            } else {
                m2_log("User id: %d doesn't have created date. First call's date will be uses instead: %s\n", users[i].id, users[i].created_at);
                m2_log("User's created_at date will be set to first call's date\n");

                // insert new record with user new creation date into actions table
                sprintf(query_insert, "INSERT INTO actions(date, action, target_id, target_type) VALUES('%s', 'user_created', %d, 'user')", users[i].created_at, users[i].id);

                // send query
                if (m2_mysql_query(query_insert)) {
                    return 1;
                }

            }

            mysql_free_result(result);

        }

    }


    return 0;

}


/*
    Check if user has calls. If he doesn't have, than do not generate invoice record
*/


int check_if_user_has_calls() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[1024] = "";
    int i;

    m2_log("Checking if users have calls\n");

    for (i = 0; i < users_count; i++) {

        int no_calls = 0;

        if (!users[i].skip) {

            // check if users has atleast 1 call
            sprintf(query, "SELECT id FROM calls WHERE user_id = %d LIMIT 1", users[i].id);

            // send query
            if (m2_mysql_query(query)) {
                return 1;
            }

            // get results
            result = mysql_store_result(&mysql);
            if (result) {
                if (mysql_num_rows(result)) {
                    row = mysql_fetch_row(result);
                    // if user doesn't have calls, then skip this user
                    if (!row[0]) {
                        no_calls = 1;
                    }
                } else {
                    no_calls = 1;
                }
            } else {
                no_calls = 1;
            }

            if (no_calls) {
                m2_log("User id: %d, doesn't have calls. Invoice will not be generated for this user\n", users[i].id);
                users[i].skip = 1;
                users[i].skip_control = 1;
            }

            mysql_free_result(result);

        }

    }

    return 0;

}


/*
    Get timestamp (time in seconds since 1970) from date string
*/


time_t get_timestamp(char *date) {

    time_t timestamp;
    struct tm time_tm;

    // convert date to seconds
    memset(&time_tm, 0, sizeof(struct tm));
    strptime(date, DATE_FORMAT, &time_tm);
    timestamp = mktime(&time_tm);

    return timestamp;

}


/*
    Get date string from timestamp (time in seconds since 1970)
*/


void timestamp_to_string(time_t timestamp, char *date) {
    // current time variables
    struct tm ptm;
    gmtime_r(&timestamp, &ptm);
    strftime(date, 20, DATE_FORMAT, &ptm);
}


/*
    Get parameter from date string
    param 1 - hour
    param 2 - month day
*/


int get_date_param(char *date, int param) {

    // current time variables
    struct tm time_tm;

    // convert date to seconds
    memset(&time_tm, 0, sizeof(struct tm));
    strptime(date, DATE_FORMAT, &time_tm);

    if (param == 1) return time_tm.tm_hour;
    if (param == 2) return time_tm.tm_mday;

    return 0;

}


/*
    Calculate due_date - add grace period (in days) to period_end
*/


void calculate_due_date(char *date, char *grace_period, int days) {

    if (days == 0) {
        strcpy(grace_period, date);
    } else {
        time_t timestamp;
        struct tm time_tm, ptm;
        memset(&time_tm, 0, sizeof(struct tm));
        strptime(date, DATE_FORMAT, &time_tm);
        timestamp = mktime(&time_tm) + days*24*60*60;
        gmtime_r(&timestamp, &ptm);
        strftime(grace_period, 20, DATE_FORMAT, &ptm);
    }

}


/*
    Get invoice settings from conflines table
*/


int get_invoice_settings() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[1024] = "";

    m2_log("Checking invoice settings\n");
    sprintf(query, "SELECT value, name FROM conflines WHERE name IN ('Invoice_Number_Start', 'Invoice_Number_Length', 'Invoice_Number_Type', 'Do_not_generate_Invoices_for_blocked_Users') AND owner_id = 0");
    if (m2_mysql_query(query)) {
        return 1;
    }

    // get results
    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0] && row[1]) {
                    if (strcmp(row[1], "Invoice_Number_Type") == 0) invoice_number_type = atoi(row[0]);
                    if (strcmp(row[1], "Invoice_Number_Length") == 0) invoice_number_length = atoi(row[0]);
                    if (strcmp(row[1], "Invoice_Number_Start") == 0) strcpy(invoice_number_start, row[0]);
                    if (strcmp(row[1], "Do_not_generate_Invoices_for_blocked_Users") == 0) do_not_generate_invoices_for_blocked_users = atoi(row[0]);
                }
            }
        }
    }

    mysql_free_result(result);
    m2_log("Invoice number type: %d, invoice number length: %d, invoice number start: %s, skip blocked users: %d\n",
        invoice_number_type, invoice_number_length, invoice_number_start, do_not_generate_invoices_for_blocked_users);
    if (invoice_number_type != 1 && invoice_number_type != 2) {
        m2_log("Can't determine invoice number type: %d\n", invoice_number_type);
        return 1;
    }

    m2_log("Checking last invoice number\n");

    // default values
    strcpy(invoice_last_number_str, "");
    invoice_last_number = 1;

    if (invoice_number_type == 1) {
        sprintf(query, "SELECT MAX(CAST(SUBSTR(number, %d) AS SIGNED)) FROM m2_invoices WHERE number LIKE '%s%%'", (int)my_strlen_utf8_c(invoice_number_start) + 1, invoice_number_start);
    } else {
        sprintf(query, "SELECT MAX(id) FROM m2_invoices");
    }

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

    // get results
    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            row = mysql_fetch_row(result);
            if (row[0]) strcpy(invoice_last_number_str, row[0]);
        }
    }

    mysql_free_result(result);

    if (strlen(invoice_last_number_str)) {
        invoice_last_number = atoll(invoice_last_number_str) + 1;
    }

    m2_log("Invoice last number: %lld\n", invoice_last_number);

    return 0;

}


/*
    Generate invoice number based on invoice settings
*/

void generate_invoice_number(users_t *user) {

    if (invoice_number_type == 1) {
        sprintf(user->invoice_number, "%s%0*llu", invoice_number_start, invoice_number_length, invoice_last_number);
    } else if (invoice_number_type == 2) {
        char year[3] = "";
        char month[3] = "";
        char day[3] = "";
        strncpy(year, user->period_start + 2, 2);
        strncpy(month, user->period_start + 5, 2);
        strncpy(day, user->period_start + 8, 2);
        sprintf(user->invoice_number, "%s%s%s%s%llu", invoice_number_start, year, month, day, invoice_last_number);
    }

    invoice_last_number++;

}


/*
    Check if invoices should be report at once when generated
*/


int check_invoice_report() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    m2_log("Checking if invoices should be reported\n");

    int send_email = 1;

    int email_send_period = 0;
    int notify_manager = 0;
    int notify_admin = 0;

    // check if invoices should be reported to admin/manager
    if (m2_mysql_query("SELECT value, name FROM conflines WHERE name IN ('Invoice_email_notice_admin', 'Invoice_email_notice_manager', 'How_often_to_send_email_notice') AND owner_id = 0")) {
        return 0;
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0] && row[1]) {
                    if (strcmp(row[1], "How_often_to_send_email_notice") == 0) email_send_period = atoi(row[0]);
                    if (strcmp(row[1], "Invoice_email_notice_manager") == 0) notify_manager = atoi(row[0]);
                    if (strcmp(row[1], "Invoice_email_notice_admin") == 0) notify_admin = atoi(row[0]);
                }
            }
        }
    }

    mysql_free_result(result);

    // reporting is disabled for admin/manager
    if (notify_manager == 0 && notify_admin == 0) {
        send_email = 0;
    }

    if (email_send_period != 1) {
        send_email = 0;
    }

    return send_email;

}


/*
    Get invoices by date and user type to recalculate
*/


int get_recalculate_invoices(int argc, char const *argv[]) {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[10000] = "";
    int i = 0;

    sprintf(query, "SELECT id, user_id, period_start, period_end FROM m2_invoices WHERE manual_payment_invoice = 0 AND id IN (");

    for (i = 1 ; i < argc; i++) {
        char buffer[60] = "";
        if (i < (argc - 1)) {
            sprintf(buffer, "'%s',", argv[i]);
        } else {
            sprintf(buffer, "'%s'", argv[i]);
        }
        strcat(query, buffer);
    }

    strcat(query, ")");

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

    // get results
    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {

                recalculate_invoices = realloc(recalculate_invoices, (recalculate_invoices_count + 1) * sizeof(invoices_t));
                memset(&recalculate_invoices[recalculate_invoices_count], 0, sizeof(invoices_t));

                if (row[0]) recalculate_invoices[recalculate_invoices_count].id = atoll(row[0]);
                if (row[1]) recalculate_invoices[recalculate_invoices_count].user_id = atoi(row[1]);
                if (row[2]) strcpy(recalculate_invoices[recalculate_invoices_count].period_start, row[2]);
                if (row[3]) strcpy(recalculate_invoices[recalculate_invoices_count].period_end, row[3]);

                recalculate_invoices_count++;

            }
        } else {
            m2_log("Invoices not found!\n");
            exit(1);
        }
    } else {
        m2_log("Invoices not found!\n");
        exit(1);
    }

    mysql_free_result(result);
    return 0;

}


/*
    Delete old invoice details by invoice id
*/


int delete_invoicedetails(long long int invoice_id) {

    char query[2048] = "";

    m2_log("Deleting old invoice details for invoice_id = %lld\n", invoice_id);
    sprintf(query, "DELETE FROM m2_invoice_lines WHERE m2_invoice_id = %lld", invoice_id);

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

    return 0;

}


/*
    Get timezone offset for given date
*/


double get_timezone_offset_for_date(char *date, char *timezone) {

    char *tzz;
    double offset = 0;
    char system_timezone[256] = "";
    time_t t;
    struct tm tm, tmm;

    if (strcmp(timezone, "International Date Line West") == 0) {
        strcpy(system_timezone, "Pacific/Midway");
    } else if (strcmp(timezone, "Midway Island") == 0) {
        strcpy(system_timezone, "Pacific/Midway");
    } else if (strcmp(timezone, "American Samoa") == 0) {
        strcpy(system_timezone, "Pacific/Pago_Pago");
    } else if (strcmp(timezone, "Hawaii") == 0) {
        strcpy(system_timezone, "Pacific/Honolulu");
    } else if (strcmp(timezone, "Alaska") == 0) {
        strcpy(system_timezone, "America/Juneau");
    } else if (strcmp(timezone, "Pacific Time (US & Canada)") == 0) {
        strcpy(system_timezone, "America/Los_Angeles");
    } else if (strcmp(timezone, "Tijuana") == 0) {
        strcpy(system_timezone, "America/Tijuana");
    } else if (strcmp(timezone, "Mountain Time (US & Canada)") == 0) {
        strcpy(system_timezone, "America/Denver");
    } else if (strcmp(timezone, "Arizona") == 0) {
        strcpy(system_timezone, "America/Phoenix");
    } else if (strcmp(timezone, "Chihuahua") == 0) {
        strcpy(system_timezone, "America/Chihuahua");
    } else if (strcmp(timezone, "Mazatlan") == 0) {
        strcpy(system_timezone, "America/Mazatlan");
    } else if (strcmp(timezone, "Central Time (US & Canada)") == 0) {
        strcpy(system_timezone, "America/Chicago");
    } else if (strcmp(timezone, "Saskatchewan") == 0) {
        strcpy(system_timezone, "America/Regina");
    } else if (strcmp(timezone, "Guadalajara") == 0) {
        strcpy(system_timezone, "America/Mexico_City");
    } else if (strcmp(timezone, "Mexico City") == 0) {
        strcpy(system_timezone, "America/Mexico_City");
    } else if (strcmp(timezone, "Monterrey") == 0) {
        strcpy(system_timezone, "America/Monterrey");
    } else if (strcmp(timezone, "Central America") == 0) {
        strcpy(system_timezone, "America/Guatemala");
    } else if (strcmp(timezone, "Eastern Time (US & Canada)") == 0) {
        strcpy(system_timezone, "America/New_York");
    } else if (strcmp(timezone, "Indiana (East)") == 0) {
        strcpy(system_timezone, "America/Indiana/Indianapolis");
    } else if (strcmp(timezone, "Bogota") == 0) {
        strcpy(system_timezone, "America/Bogota");
    } else if (strcmp(timezone, "Lima") == 0) {
        strcpy(system_timezone, "America/Lima");
    } else if (strcmp(timezone, "Quito") == 0) {
        strcpy(system_timezone, "America/Lima");
    } else if (strcmp(timezone, "Atlantic Time (Canada)") == 0) {
        strcpy(system_timezone, "America/Halifax");
    } else if (strcmp(timezone, "Caracas") == 0) {
        strcpy(system_timezone, "America/Caracas");
    } else if (strcmp(timezone, "La Paz") == 0) {
        strcpy(system_timezone, "America/La_Paz");
    } else if (strcmp(timezone, "Santiago") == 0) {
        strcpy(system_timezone, "America/Santiago");
    } else if (strcmp(timezone, "Newfoundland") == 0) {
        strcpy(system_timezone, "America/St_Johns");
    } else if (strcmp(timezone, "Brasilia") == 0) {
        strcpy(system_timezone, "America/Sao_Paulo");
    } else if (strcmp(timezone, "Buenos Aires") == 0) {
        strcpy(system_timezone, "America/Argentina/Buenos_Aires");
    } else if (strcmp(timezone, "Montevideo") == 0) {
        strcpy(system_timezone, "America/Montevideo");
    } else if (strcmp(timezone, "Georgetown") == 0) {
        strcpy(system_timezone, "America/Guyana");
    } else if (strcmp(timezone, "Greenland") == 0) {
        strcpy(system_timezone, "America/Godthab");
    } else if (strcmp(timezone, "Mid-Atlantic") == 0) {
        strcpy(system_timezone, "Atlantic/South_Georgia");
    } else if (strcmp(timezone, "Azores") == 0) {
        strcpy(system_timezone, "Atlantic/Azores");
    } else if (strcmp(timezone, "Cape Verde Is.") == 0) {
        strcpy(system_timezone, "Atlantic/Cape_Verde");
    } else if (strcmp(timezone, "Dublin") == 0) {
        strcpy(system_timezone, "Europe/Dublin");
    } else if (strcmp(timezone, "Edinburgh") == 0) {
        strcpy(system_timezone, "Europe/London");
    } else if (strcmp(timezone, "Lisbon") == 0) {
        strcpy(system_timezone, "Europe/Lisbon");
    } else if (strcmp(timezone, "London") == 0) {
        strcpy(system_timezone, "Europe/London");
    } else if (strcmp(timezone, "Casablanca") == 0) {
        strcpy(system_timezone, "Africa/Casablanca");
    } else if (strcmp(timezone, "Monrovia") == 0) {
        strcpy(system_timezone, "Africa/Monrovia");
    } else if (strcmp(timezone, "UTC") == 0) {
        strcpy(system_timezone, "Etc/UTC");
    } else if (strcmp(timezone, "Belgrade") == 0) {
        strcpy(system_timezone, "Europe/Belgrade");
    } else if (strcmp(timezone, "Bratislava") == 0) {
        strcpy(system_timezone, "Europe/Bratislava");
    } else if (strcmp(timezone, "Budapest") == 0) {
        strcpy(system_timezone, "Europe/Budapest");
    } else if (strcmp(timezone, "Ljubljana") == 0) {
        strcpy(system_timezone, "Europe/Ljubljana");
    } else if (strcmp(timezone, "Prague") == 0) {
        strcpy(system_timezone, "Europe/Prague");
    } else if (strcmp(timezone, "Sarajevo") == 0) {
        strcpy(system_timezone, "Europe/Sarajevo");
    } else if (strcmp(timezone, "Skopje") == 0) {
        strcpy(system_timezone, "Europe/Skopje");
    } else if (strcmp(timezone, "Warsaw") == 0) {
        strcpy(system_timezone, "Europe/Warsaw");
    } else if (strcmp(timezone, "Zagreb") == 0) {
        strcpy(system_timezone, "Europe/Zagreb");
    } else if (strcmp(timezone, "Brussels") == 0) {
        strcpy(system_timezone, "Europe/Brussels");
    } else if (strcmp(timezone, "Copenhagen") == 0) {
        strcpy(system_timezone, "Europe/Copenhagen");
    } else if (strcmp(timezone, "Madrid") == 0) {
        strcpy(system_timezone, "Europe/Madrid");
    } else if (strcmp(timezone, "Paris") == 0) {
        strcpy(system_timezone, "Europe/Paris");
    } else if (strcmp(timezone, "Amsterdam") == 0) {
        strcpy(system_timezone, "Europe/Amsterdam");
    } else if (strcmp(timezone, "Berlin") == 0) {
        strcpy(system_timezone, "Europe/Berlin");
    } else if (strcmp(timezone, "Bern") == 0) {
        strcpy(system_timezone, "Europe/Berlin");
    } else if (strcmp(timezone, "Rome") == 0) {
        strcpy(system_timezone, "Europe/Rome");
    } else if (strcmp(timezone, "Stockholm") == 0) {
        strcpy(system_timezone, "Europe/Stockholm");
    } else if (strcmp(timezone, "Vienna") == 0) {
        strcpy(system_timezone, "Europe/Vienna");
    } else if (strcmp(timezone, "West Central Africa") == 0) {
        strcpy(system_timezone, "Africa/Algiers");
    } else if (strcmp(timezone, "Bucharest") == 0) {
        strcpy(system_timezone, "Europe/Bucharest");
    } else if (strcmp(timezone, "Cairo") == 0) {
        strcpy(system_timezone, "Africa/Cairo");
    } else if (strcmp(timezone, "Helsinki") == 0) {
        strcpy(system_timezone, "Europe/Helsinki");
    } else if (strcmp(timezone, "Kyiv") == 0) {
        strcpy(system_timezone, "Europe/Kiev");
    } else if (strcmp(timezone, "Riga") == 0) {
        strcpy(system_timezone, "Europe/Riga");
    } else if (strcmp(timezone, "Sofia") == 0) {
        strcpy(system_timezone, "Europe/Sofia");
    } else if (strcmp(timezone, "Tallinn") == 0) {
        strcpy(system_timezone, "Europe/Tallinn");
    } else if (strcmp(timezone, "Vilnius") == 0) {
        strcpy(system_timezone, "Europe/Vilnius");
    } else if (strcmp(timezone, "Athens") == 0) {
        strcpy(system_timezone, "Europe/Athens");
    } else if (strcmp(timezone, "Istanbul") == 0) {
        strcpy(system_timezone, "Europe/Istanbul");
    } else if (strcmp(timezone, "Minsk") == 0) {
        strcpy(system_timezone, "Europe/Minsk");
    } else if (strcmp(timezone, "Jerusalem") == 0) {
        strcpy(system_timezone, "Asia/Jerusalem");
    } else if (strcmp(timezone, "Harare") == 0) {
        strcpy(system_timezone, "Africa/Harare");
    } else if (strcmp(timezone, "Pretoria") == 0) {
        strcpy(system_timezone, "Africa/Johannesburg");
    } else if (strcmp(timezone, "Kaliningrad") == 0) {
        strcpy(system_timezone, "Europe/Kaliningrad");
    } else if (strcmp(timezone, "Moscow") == 0) {
        strcpy(system_timezone, "Europe/Moscow");
    } else if (strcmp(timezone, "St. Petersburg") == 0) {
        strcpy(system_timezone, "Europe/Moscow");
    } else if (strcmp(timezone, "Volgograd") == 0) {
        strcpy(system_timezone, "Europe/Volgograd");
    } else if (strcmp(timezone, "Samara") == 0) {
        strcpy(system_timezone, "Europe/Samara");
    } else if (strcmp(timezone, "Kuwait") == 0) {
        strcpy(system_timezone, "Asia/Kuwait");
    } else if (strcmp(timezone, "Riyadh") == 0) {
        strcpy(system_timezone, "Asia/Riyadh");
    } else if (strcmp(timezone, "Nairobi") == 0) {
        strcpy(system_timezone, "Africa/Nairobi");
    } else if (strcmp(timezone, "Baghdad") == 0) {
        strcpy(system_timezone, "Asia/Baghdad");
    } else if (strcmp(timezone, "Tehran") == 0) {
        strcpy(system_timezone, "Asia/Tehran");
    } else if (strcmp(timezone, "Abu Dhabi") == 0) {
        strcpy(system_timezone, "Asia/Muscat");
    } else if (strcmp(timezone, "Muscat") == 0) {
        strcpy(system_timezone, "Asia/Muscat");
    } else if (strcmp(timezone, "Baku") == 0) {
        strcpy(system_timezone, "Asia/Baku");
    } else if (strcmp(timezone, "Tbilisi") == 0) {
        strcpy(system_timezone, "Asia/Tbilisi");
    } else if (strcmp(timezone, "Yerevan") == 0) {
        strcpy(system_timezone, "Asia/Yerevan");
    } else if (strcmp(timezone, "Kabul") == 0) {
        strcpy(system_timezone, "Asia/Kabul");
    } else if (strcmp(timezone, "Ekaterinburg") == 0) {
        strcpy(system_timezone, "Asia/Yekaterinburg");
    } else if (strcmp(timezone, "Islamabad") == 0) {
        strcpy(system_timezone, "Asia/Karachi");
    } else if (strcmp(timezone, "Karachi") == 0) {
        strcpy(system_timezone, "Asia/Karachi");
    } else if (strcmp(timezone, "Tashkent") == 0) {
        strcpy(system_timezone, "Asia/Tashkent");
    } else if (strcmp(timezone, "Chennai") == 0) {
        strcpy(system_timezone, "Asia/Kolkata");
    } else if (strcmp(timezone, "Kolkata") == 0) {
        strcpy(system_timezone, "Asia/Kolkata");
    } else if (strcmp(timezone, "Mumbai") == 0) {
        strcpy(system_timezone, "Asia/Kolkata");
    } else if (strcmp(timezone, "New Delhi") == 0) {
        strcpy(system_timezone, "Asia/Kolkata");
    } else if (strcmp(timezone, "Kathmandu") == 0) {
        strcpy(system_timezone, "Asia/Kathmandu");
    } else if (strcmp(timezone, "Astana") == 0) {
        strcpy(system_timezone, "Asia/Dhaka");
    } else if (strcmp(timezone, "Dhaka") == 0) {
        strcpy(system_timezone, "Asia/Dhaka");
    } else if (strcmp(timezone, "Sri Jayawardenepura") == 0) {
        strcpy(system_timezone, "Asia/Colombo");
    } else if (strcmp(timezone, "Almaty") == 0) {
        strcpy(system_timezone, "Asia/Almaty");
    } else if (strcmp(timezone, "Novosibirsk") == 0) {
        strcpy(system_timezone, "Asia/Novosibirsk");
    } else if (strcmp(timezone, "Rangoon") == 0) {
        strcpy(system_timezone, "Asia/Rangoon");
    } else if (strcmp(timezone, "Bangkok") == 0) {
        strcpy(system_timezone, "Asia/Bangkok");
    } else if (strcmp(timezone, "Hanoi") == 0) {
        strcpy(system_timezone, "Asia/Bangkok");
    } else if (strcmp(timezone, "Jakarta") == 0) {
        strcpy(system_timezone, "Asia/Jakarta");
    } else if (strcmp(timezone, "Krasnoyarsk") == 0) {
        strcpy(system_timezone, "Asia/Krasnoyarsk");
    } else if (strcmp(timezone, "Beijing") == 0) {
        strcpy(system_timezone, "Asia/Shanghai");
    } else if (strcmp(timezone, "Chongqing") == 0) {
        strcpy(system_timezone, "Asia/Chongqing");
    } else if (strcmp(timezone, "Hong Kong") == 0) {
        strcpy(system_timezone, "Asia/Hong_Kong");
    } else if (strcmp(timezone, "Urumqi") == 0) {
        strcpy(system_timezone, "Asia/Urumqi");
    } else if (strcmp(timezone, "Kuala Lumpur") == 0) {
        strcpy(system_timezone, "Asia/Kuala_Lumpur");
    } else if (strcmp(timezone, "Singapore") == 0) {
        strcpy(system_timezone, "Asia/Singapore");
    } else if (strcmp(timezone, "Taipei") == 0) {
        strcpy(system_timezone, "Asia/Taipei");
    } else if (strcmp(timezone, "Perth") == 0) {
        strcpy(system_timezone, "Australia/Perth");
    } else if (strcmp(timezone, "Irkutsk") == 0) {
        strcpy(system_timezone, "Asia/Irkutsk");
    } else if (strcmp(timezone, "Ulaanbaatar") == 0) {
        strcpy(system_timezone, "Asia/Ulaanbaatar");
    } else if (strcmp(timezone, "Seoul") == 0) {
        strcpy(system_timezone, "Asia/Seoul");
    } else if (strcmp(timezone, "Osaka") == 0) {
        strcpy(system_timezone, "Asia/Tokyo");
    } else if (strcmp(timezone, "Sapporo") == 0) {
        strcpy(system_timezone, "Asia/Tokyo");
    } else if (strcmp(timezone, "Tokyo") == 0) {
        strcpy(system_timezone, "Asia/Tokyo");
    } else if (strcmp(timezone, "Yakutsk") == 0) {
        strcpy(system_timezone, "Asia/Yakutsk");
    } else if (strcmp(timezone, "Darwin") == 0) {
        strcpy(system_timezone, "Australia/Darwin");
    } else if (strcmp(timezone, "Adelaide") == 0) {
        strcpy(system_timezone, "Australia/Adelaide");
    } else if (strcmp(timezone, "Canberra") == 0) {
        strcpy(system_timezone, "Australia/Melbourne");
    } else if (strcmp(timezone, "Melbourne") == 0) {
        strcpy(system_timezone, "Australia/Melbourne");
    } else if (strcmp(timezone, "Sydney") == 0) {
        strcpy(system_timezone, "Australia/Sydney");
    } else if (strcmp(timezone, "Brisbane") == 0) {
        strcpy(system_timezone, "Australia/Brisbane");
    } else if (strcmp(timezone, "Hobart") == 0) {
        strcpy(system_timezone, "Australia/Hobart");
    } else if (strcmp(timezone, "Vladivostok") == 0) {
        strcpy(system_timezone, "Asia/Vladivostok");
    } else if (strcmp(timezone, "Guam") == 0) {
        strcpy(system_timezone, "Pacific/Guam");
    } else if (strcmp(timezone, "Port Moresby") == 0) {
        strcpy(system_timezone, "Pacific/Port_Moresby");
    } else if (strcmp(timezone, "Magadan") == 0) {
        strcpy(system_timezone, "Asia/Magadan");
    } else if (strcmp(timezone, "Srednekolymsk") == 0) {
        strcpy(system_timezone, "Asia/Srednekolymsk");
    } else if (strcmp(timezone, "Solomon Is.") == 0) {
        strcpy(system_timezone, "Pacific/Guadalcanal");
    } else if (strcmp(timezone, "New Caledonia") == 0) {
        strcpy(system_timezone, "Pacific/Noumea");
    } else if (strcmp(timezone, "Fiji") == 0) {
        strcpy(system_timezone, "Pacific/Fiji");
    } else if (strcmp(timezone, "Kamchatka") == 0) {
        strcpy(system_timezone, "Asia/Kamchatka");
    } else if (strcmp(timezone, "Marshall Is.") == 0) {
        strcpy(system_timezone, "Pacific/Majuro");
    } else if (strcmp(timezone, "Auckland") == 0) {
        strcpy(system_timezone, "Pacific/Auckland");
    } else if (strcmp(timezone, "Wellington") == 0) {
        strcpy(system_timezone, "Pacific/Auckland");
    } else if (strcmp(timezone, "Nuku'alofa") == 0) {
        strcpy(system_timezone, "Pacific/Tongatapu");
    } else if (strcmp(timezone, "Tokelau Is.") == 0) {
        strcpy(system_timezone, "Pacific/Fakaofo");
    } else if (strcmp(timezone, "Chatham Is.") == 0) {
        strcpy(system_timezone, "Pacific/Chatham");
    } else if (strcmp(timezone, "Samoa") == 0) {
        strcpy(system_timezone, "Pacific/Apia");
    }

    // get current timezone and save it somewhere
    tzz = getenv("TZ");
    if (tzz) tzz = strdup(tzz);

    // set timezone from arguments
    setenv("TZ", system_timezone, 1);
    tzset();

    // calculate offset for given date
    strptime(date, DATE_FORMAT, &tm);
    t = mktime(&tm);

    // get server gmt offset
    localtime_r(&t, &tmm);
    offset = tmm.tm_gmtoff / 60.0 / 60.0;

    // restore original timezone
    if (tzz) {
        setenv("TZ", tzz, 1);
        free(tzz);
    } else {
        unsetenv("TZ");
    }
    tzset();

    return offset;

}


/*
    For correct string length when string includes non latin characters
*/


int my_strlen_utf8_c(char *s) {
    int i = 0, j = 0;
    while (s[i]) {
        if ((s[i] & 0xc0) != 0x80) {
            j++;
        }
        i++;
    }
    return j;
}

/*
    Function from http://curl.haxx.se/libcurl/c/getinmemory.html
*/


size_t WriteMemoryCallback(void *contents, size_t size, size_t nmemb, void *userp) {

    size_t realsize = size * nmemb;
    struct MemoryStruct *mem = (struct MemoryStruct *)userp;

    mem->memory = realloc(mem->memory, mem->size + realsize + 1);
    if (mem->memory == NULL) {
        // out of memory!
        m2_log("Not enough memory (realloc returned NULL)\n");
        return 0;
    }

    memcpy(&(mem->memory[mem->size]), contents, realsize);
    mem->size += realsize;
    mem->memory[mem->size] = 0;

    return realsize;

}


/*
    Get destination name by prefix
*/


void get_destination_name_by_prefix(char *prefix, char *name) {

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

    if (!strlen(prefix)) {
        return;
    }

    sprintf(query, "SELECT name FROM destinations WHERE prefix = '%s'", prefix);

    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, row[0]);
                }
            }
        }
    }

    mysql_free_result(result);

}


/*
    Get did by id
*/


void get_did_by_id(int id, char *did) {

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

    if (id <= 0) {
        return;
    }

    sprintf(query, "SELECT did FROM dids WHERE id = %d", 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(did, row[0]);
                }
            }
        }
    }

    mysql_free_result(result);

}


/*
    Format date to ES compatible format
*/


void format_calldate_to_es(char *calldate, char *new_calldate) {

    strcpy(new_calldate, calldate);
    new_calldate[10] = 'T';

}


/*
    Get calls from elasticsearch and subscriptions from db
*/


int get_data(int index) {

    int i = 0;
    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[6000] = "";

    // when we have specific index of users structure
    // then skip iterations and go straight to that user
    if (index > -1) {
        i = index;
        goto single_user2;
    }

    for (i = 0; i < users_count; i++) {
        if (!users[i].skip) {

            single_user2:;

            CURL *curl_handle;
            CURLcode res;

            char json_query[2048] = "";
            char http_request_url[2000] = "";
            struct MemoryStruct chunk;
            char server_period_start_es[256] = "";
            char server_period_end_es[256] = "";
            char did_condition[200] = "";
            int j = 0;
            int z = 0;

            // totals
            double total_price = 0;
            double total_price_with_tax = 0;
            double total_did_price = 0;
            double total_did_price_with_tax = 0;
            double total_subscription_price = 0;
            double total_subscription_price_with_tax = 0;

            m2_log("\n");
            m2_log("<<<<<<<<<<<<< Checking outgoing calls for user_id: %d, period: %s - %s (server time: %s - %s) >>>>>>>>>>>>\n",
                users[i].id, users[i].period_start, users[i].period_end, users[i].server_period_start, users[i].server_period_end);

            // reset variables for each user
            invoice_details_count = 0;

            if (check_es_data_integrity(users[i].server_period_start, users[i].server_period_end)) {
                m2_log("ERROR! Consistency check failed! Skipping user: %d\n", users[i].id);
                goto skip_user;
            }

            // will be grown as needed by the WriteMemoryCallback
            chunk.memory = malloc(1);
            // no data at this point
            chunk.size = 0;

            format_calldate_to_es(users[i].server_period_start, server_period_start_es);
            format_calldate_to_es(users[i].server_period_end, server_period_end_es);

            if (es_did_field_exists) {
                strcat(did_condition, ",{\"bool\":{\"should\":[{\"term\":{\"did_id\":\"0\"}},{\"bool\":{\"must_not\":{\"exists\":{\"field\":\"did_id\"}}}}]}}");
            }

            // set url
            sprintf(http_request_url, "http://%s:9200/m2/calls/_search?search_type=count", elasticsearch_host);
            // format query
            sprintf(json_query, "{\"size\":0,\"query\":{\"filtered\":{\"filter\":{\"bool\":{\"must\":[{\"range\":{\"calldate\":{\"gte\":\"%s\",\"lte\":\"%s\"}}},{\"range\":{\"provider_id\":{\"gt\":\"0\"}}},{\"term\":{\"disposition\":\"ANSWERED\"}},{\"term\":{\"user_id\":\"%d\"}}%s]}}}},\"aggregations\":{\"group_by_prefix\":{\"terms\":{\"field\":\"prefix\",\"size\":0},\"aggregations\":{\"group_by_user_rate\":{\"terms\":{\"field\":\"user_rate\",\"size\":0},\"aggregations\":{\"total_billsec\":{\"sum\":{\"field\":\"user_billsec\"}},\"total_price\":{\"sum\":{\"field\":\"user_price\"}}}}}}}}",
                server_period_start_es, server_period_end_es, users[i].id, did_condition);

            if (global_debug) {
                m2_log("Elasticsearch query:\n");
                m2_log("%s\n", json_query);
            }

            curl_global_init(CURL_GLOBAL_ALL);

            // init the curl session
            curl_handle = curl_easy_init();
            // specify URL to get
            curl_easy_setopt(curl_handle, CURLOPT_URL, http_request_url);
            // set body length
            curl_easy_setopt(curl_handle, CURLOPT_POSTFIELDSIZE, strlen(json_query));
            // send json query
            curl_easy_setopt(curl_handle, CURLOPT_POSTFIELDS, json_query);
            // send all data to this function
            curl_easy_setopt(curl_handle, CURLOPT_WRITEFUNCTION, WriteMemoryCallback);
            // we pass our 'chunk' struct to the callback function
            curl_easy_setopt(curl_handle, CURLOPT_WRITEDATA, (void *)&chunk);
            // some servers don't like requests that are made without a user-agent field, so we provide one
            curl_easy_setopt(curl_handle, CURLOPT_USERAGENT, "libcurl-agent/1.0");
            // get it!
            res = curl_easy_perform(curl_handle);

            // check for errors
            if (res != CURLE_OK) {
                m2_log("curl_easy_perform() failed: %s\n", curl_easy_strerror(res));
                return 1;
            } else {

                // m2_log("%lu bytes retrieved from Elasticsearch\n", (long)chunk.size);
                // m2_log("%s\n", chunk.memory);

                if (!strlen(chunk.memory)) {
                    m2_log("Elasticsearch response is empty\n");
                    goto curl_cleanup;
                }

                cJSON *root = cJSON_Parse(chunk.memory);
                if (root == NULL) {
                    m2_log("JSON root is NULL\n");
                    goto curl_cleanup;
                }

                cJSON *hits = cJSON_GetObjectItem(root, "hits");
                if (hits == NULL) {
                    m2_log("JSON hits is NULL\n");
                    cJSON_Delete(root);
                    goto curl_cleanup;
                }

                if (cJSON_GetObjectItem(hits, "total") == NULL && cJSON_GetObjectItem(hits, "total")->valueint == 0) {
                    m2_log("Zero hits\n");
                    cJSON_Delete(root);
                    goto curl_cleanup;
                }

                cJSON *aggregations = cJSON_GetObjectItem(root, "aggregations");
                if (aggregations == NULL) {
                    m2_log("JSON aggregations is NULL\n");
                    cJSON_Delete(root);
                    goto curl_cleanup;
                }

                cJSON *group_by_prefix = cJSON_GetObjectItem(aggregations, "group_by_prefix");
                if (group_by_prefix == NULL) {
                    m2_log("JSON group_by_prefix is NULL\n");
                    cJSON_Delete(root);
                    goto curl_cleanup;
                }

                cJSON *prefix_buckets = cJSON_GetObjectItem(group_by_prefix, "buckets");
                if (prefix_buckets == NULL) {
                    m2_log("JSON prefix_buckets is NULL\n");
                    cJSON_Delete(root);
                    goto curl_cleanup;
                }

                for (j = 0; j < cJSON_GetArraySize(prefix_buckets); j++) {
                    cJSON *prefix_subitem = cJSON_GetArrayItem(prefix_buckets, j);
                    if (prefix_subitem) {

                        cJSON *prefix = cJSON_GetObjectItem(prefix_subitem, "key");

                        cJSON *group_by_user_rate = cJSON_GetObjectItem(prefix_subitem, "group_by_user_rate");
                        if (group_by_user_rate == NULL) {
                            m2_log("JSON group_by_user_rate is NULL\n");
                            cJSON_Delete(root);
                            goto curl_cleanup;
                        }

                        cJSON *rate_buckets = cJSON_GetObjectItem(group_by_user_rate, "buckets");
                        if (rate_buckets == NULL) {
                            m2_log("JSON rate_buckets is NULL\n");
                            cJSON_Delete(root);
                            goto curl_cleanup;
                        }

                        for (z = 0; z < cJSON_GetArraySize(rate_buckets); z++) {
                            cJSON *subitem = cJSON_GetArrayItem(rate_buckets, z);
                            if (subitem) {

                                cJSON *rate = cJSON_GetObjectItem(subitem, "key");
                                cJSON *count = cJSON_GetObjectItem(subitem, "doc_count");

                                cJSON *price = cJSON_GetObjectItem(subitem, "total_price");
                                cJSON *price_value = NULL;
                                if (price) {
                                    price_value = cJSON_GetObjectItem(price, "value");
                                }

                                cJSON *billsec = cJSON_GetObjectItem(subitem, "total_billsec");
                                cJSON *billsec_value = NULL;
                                if (billsec) {
                                    billsec_value = cJSON_GetObjectItem(billsec, "value");
                                }

                                invoice_details = realloc(invoice_details, (invoice_details_count + 1) * sizeof(invoice_details_t));
                                memset(&invoice_details[invoice_details_count], 0, sizeof(invoice_details_t));

                                if (prefix) strcpy(invoice_details[invoice_details_count].prefix, prefix->valuestring);
                                if (price_value) invoice_details[invoice_details_count].price = price_value->valuedouble;
                                if (rate) invoice_details[invoice_details_count].rate = rate->valuedouble;
                                if (billsec_value) invoice_details[invoice_details_count].billsec = billsec_value->valueint;
                                if (count) invoice_details[invoice_details_count].calls = count->valueint;
                                get_destination_name_by_prefix(invoice_details[invoice_details_count].prefix, invoice_details[invoice_details_count].name);

                                // apply taxes
                                invoice_details[invoice_details_count].price_with_tax = invoice_details[invoice_details_count].price;
                                m2_apply_taxes(&invoice_details[invoice_details_count].price_with_tax,
                                    users[i].tax_compound,
                                    users[i].tax1,
                                    users[i].tax2,
                                    users[i].tax3,
                                    users[i].tax4,
                                    users[i].tax1_value,
                                    users[i].tax2_value,
                                    users[i].tax3_value,
                                    users[i].tax4_value);

                                invoice_details[invoice_details_count].user_id = users[i].id;

                                if (show_invoice_lines) {
                                    m2_log("Destination: %s, prefix: %s, rate: %.3f, price: %.3f, price_with_tax: %.3f, billsec: %d, total_calls: %d\n",
                                        invoice_details[invoice_details_count].name, invoice_details[invoice_details_count].prefix,
                                        invoice_details[invoice_details_count].rate, invoice_details[invoice_details_count].price,
                                        invoice_details[invoice_details_count].price_with_tax, invoice_details[invoice_details_count].billsec,
                                        invoice_details[invoice_details_count].calls);
                                }

                                total_price += invoice_details[invoice_details_count].price;
                                total_price_with_tax += invoice_details[invoice_details_count].price_with_tax;

                                invoice_details_count++;
                            }
                        }

                    }
                }

                if (root) cJSON_Delete(root);

            }

            curl_cleanup:

            // cleanup curl stuff
            curl_easy_cleanup(curl_handle);
            // free received data
            free(chunk.memory);
            // we're done with libcurl, so clean it up
            curl_global_cleanup();

            m2_log("Total price for outgoing calls: %.3f, price_with_tax: %.3f\n", total_price, total_price_with_tax);

            if (es_did_field_exists) {
                m2_log("\n");
                m2_log("<<<<<<<<<<<<< Checking DID calls for user_id: %d, period: %s - %s (server time: %s - %s) >>>>>>>>>>>>\n",
                    users[i].id, users[i].period_start, users[i].period_end, users[i].server_period_start, users[i].server_period_end);

                // will be grown as needed by the WriteMemoryCallback
                chunk.memory = malloc(1);
                // no data at this point
                chunk.size = 0;

                // set url
                sprintf(http_request_url, "http://%s:9200/m2/calls/_search?search_type=count&pretty", elasticsearch_host);
                // format query
                sprintf(json_query, "{\"size\":0,\"query\":{\"filtered\":{\"filter\":{\"bool\":{\"must\":[{\"range\":{\"calldate\":{\"gte\":\"%s\",\"lte\":\"%s\"}}},{\"range\":{\"did_id\":{\"gt\":\"0\"}}},{\"term\":{\"disposition\":\"ANSWERED\"}},{\"term\":{\"did_customer_id\":\"%d\"}}]}}}},\"aggregations\":{\"group_by_did_id\":{\"terms\":{\"field\":\"did_id\",\"size\":0},\"aggregations\":{\"group_by_did_rate\":{\"terms\":{\"field\":\"did_selling_rate\",\"size\": 0},\"aggregations\":{\"total_billsec\":{\"sum\":{\"field\":\"did_selling_billsec\"}},\"total_price\":{\"sum\":{\"field\":\"did_selling_price\"}}}}}}}}",
                    server_period_start_es, server_period_end_es, users[i].id);

                if (global_debug) {
                    m2_log("Elasticsearch query:\n");
                    m2_log("%s\n", json_query);
                }

                curl_global_init(CURL_GLOBAL_ALL);

                // init the curl session
                curl_handle = curl_easy_init();

                // specify URL to get
                curl_easy_setopt(curl_handle, CURLOPT_URL, http_request_url);
                // set body length
                curl_easy_setopt(curl_handle, CURLOPT_POSTFIELDSIZE, strlen(json_query));
                // send json query
                curl_easy_setopt(curl_handle, CURLOPT_POSTFIELDS, json_query);
                // send all data to this function
                curl_easy_setopt(curl_handle, CURLOPT_WRITEFUNCTION, WriteMemoryCallback);
                // we pass our 'chunk' struct to the callback function
                curl_easy_setopt(curl_handle, CURLOPT_WRITEDATA, (void *)&chunk);
                // some servers don't like requests that are made without a user-agent field, so we provide one
                curl_easy_setopt(curl_handle, CURLOPT_USERAGENT, "libcurl-agent/1.0");
                // get it!
                res = curl_easy_perform(curl_handle);

                // check for errors
                if (res != CURLE_OK) {
                    m2_log("curl_easy_perform() failed: %s\n", curl_easy_strerror(res));
                    return 1;
                } else {

                    // m2_log("%lu bytes retrieved from Elasticsearch\n", (long)chunk.size);
                    // m2_log("%s\n", chunk.memory);

                    if (!strlen(chunk.memory)) {
                        m2_log("Elasticsearch response is empty\n");
                        goto curl_cleanup_dids;
                    }

                    cJSON *root = cJSON_Parse(chunk.memory);
                    if (root == NULL) {
                        m2_log("JSON root is NULL\n");
                        goto curl_cleanup_dids;
                    }

                    cJSON *hits = cJSON_GetObjectItem(root, "hits");
                    if (hits == NULL) {
                        m2_log("JSON hits is NULL\n");
                        cJSON_Delete(root);
                        goto curl_cleanup_dids;
                    }

                    if (cJSON_GetObjectItem(hits, "total") == NULL && cJSON_GetObjectItem(hits, "total")->valueint == 0) {
                        m2_log("Zero hits\n");
                        cJSON_Delete(root);
                        goto curl_cleanup_dids;
                    }

                    cJSON *aggregations = cJSON_GetObjectItem(root, "aggregations");
                    if (aggregations == NULL) {
                        m2_log("JSON aggregations is NULL\n");
                        cJSON_Delete(root);
                        goto curl_cleanup_dids;
                    }

                    cJSON *group_by_did_id = cJSON_GetObjectItem(aggregations, "group_by_did_id");
                    if (group_by_did_id == NULL) {
                        m2_log("JSON group_by_did_id is NULL\n");
                        cJSON_Delete(root);
                        goto curl_cleanup_dids;
                    }

                    cJSON *did_buckets = cJSON_GetObjectItem(group_by_did_id, "buckets");
                    if (did_buckets == NULL) {
                        m2_log("JSON did_buckets is NULL\n");
                        cJSON_Delete(root);
                        goto curl_cleanup_dids;
                    }

                    for (j = 0; j < cJSON_GetArraySize(did_buckets); j++) {
                        cJSON *did_subitem = cJSON_GetArrayItem(did_buckets, j);
                        cJSON *did_id = cJSON_GetObjectItem(did_subitem, "key");

                        if (!did_id) continue;

                        cJSON *group_by_did_rate = cJSON_GetObjectItem(did_subitem, "group_by_did_rate");
                        if (group_by_did_rate == NULL) {
                            m2_log("JSON group_by_did_rate is NULL\n");
                            cJSON_Delete(root);
                            goto curl_cleanup_dids;
                        }

                        cJSON *rate_buckets = cJSON_GetObjectItem(group_by_did_rate, "buckets");
                        if (rate_buckets == NULL) {
                            m2_log("JSON rate_buckets is NULL\n");
                            cJSON_Delete(root);
                            goto curl_cleanup_dids;
                        }

                        for (z = 0; z < cJSON_GetArraySize(rate_buckets); z++) {
                            cJSON *rate_subitem = cJSON_GetArrayItem(rate_buckets, z);
                            cJSON *rate = cJSON_GetObjectItem(rate_subitem, "key");

                            if (!rate) continue;

                            cJSON *count = cJSON_GetObjectItem(rate_subitem, "doc_count");

                            cJSON *price = cJSON_GetObjectItem(rate_subitem, "total_price");
                            cJSON *price_value = NULL;
                            if (price) {
                                price_value = cJSON_GetObjectItem(price, "value");
                            }

                            cJSON *billsec = cJSON_GetObjectItem(rate_subitem, "total_billsec");
                            cJSON *billsec_value = NULL;
                            if (billsec) {
                                billsec_value = cJSON_GetObjectItem(billsec, "value");
                            }

                            invoice_details = realloc(invoice_details, (invoice_details_count + 1) * sizeof(invoice_details_t));
                            memset(&invoice_details[invoice_details_count], 0, sizeof(invoice_details_t));

                            if (did_id) invoice_details[invoice_details_count].did_id = did_id->valueint;
                            if (price_value) invoice_details[invoice_details_count].price = price_value->valuedouble;
                            if (billsec_value) invoice_details[invoice_details_count].billsec = billsec_value->valueint;
                            if (count) invoice_details[invoice_details_count].calls = count->valueint;
                            if (rate) invoice_details[invoice_details_count].rate = rate->valuedouble;
                            char did[200] = "";
                            get_did_by_id(invoice_details[invoice_details_count].did_id, did);
                            sprintf(invoice_details[invoice_details_count].name, "DID: %s", did);

                            // apply taxes
                            invoice_details[invoice_details_count].price_with_tax = invoice_details[invoice_details_count].price;
                            m2_apply_taxes(&invoice_details[invoice_details_count].price_with_tax,
                                users[i].tax_compound,
                                users[i].tax1,
                                users[i].tax2,
                                users[i].tax3,
                                users[i].tax4,
                                users[i].tax1_value,
                                users[i].tax2_value,
                                users[i].tax3_value,
                                users[i].tax4_value);

                            invoice_details[invoice_details_count].user_id = users[i].id;

                            if (show_invoice_lines) {
                                m2_log("%s, did_id: %d, price: %.3f, price_with_tax: %.3f, billsec: %d, rate: %.3f, total_calls: %d\n",
                                    invoice_details[invoice_details_count].name, invoice_details[invoice_details_count].did_id,
                                    invoice_details[invoice_details_count].price, invoice_details[invoice_details_count].price_with_tax,
                                    invoice_details[invoice_details_count].billsec, invoice_details[invoice_details_count].rate,
                                    invoice_details[invoice_details_count].calls);
                            }

                            total_did_price += invoice_details[invoice_details_count].price;
                            total_did_price_with_tax += invoice_details[invoice_details_count].price_with_tax;

                            invoice_details_count++;
                        }
                    }

                    if (root) cJSON_Delete(root);
                }

                curl_cleanup_dids:

                // cleanup curl stuff
                curl_easy_cleanup(curl_handle);
                // free received data
                free(chunk.memory);
                // we're done with libcurl, so clean it up
                curl_global_cleanup();

                m2_log("Total price for DID calls: %.3f, price_with_tax: %.3f\n", total_did_price, total_did_price_with_tax);
            }

            m2_log("\n");
            m2_log("<<<<<<<<<<<<< Checking subscriptions for user_id: %d, period: %s - %s (server time: %s - %s) >>>>>>>>>>>>\n",
                users[i].id, users[i].period_start, users[i].period_end, users[i].server_period_start, users[i].server_period_end);

            // format subscriptions query
            sprintf(query,
                "SELECT amount_in_default_currency, period_start, period_end, inv_line, subscription_id, "
                "financial_data_records.id, action, name_for_user "
                "FROM financial_data_records "
                "JOIN subscriptions ON subscriptions.id = financial_data_records.subscription_id "
                "WHERE "
                    "entity = 'subscription' "
                    "AND ((action IN ('charge_periodic', 'charge_activation') AND data1 = 'success') OR (action IN ('charge_cancellation', 'refund'))) "
                    "AND created_at BETWEEN '%s' AND '%s' "
                    "AND financial_data_records.user_id = %d "
                "ORDER BY subscription_id ASC, id ASC",
                users[i].server_period_start, users[i].server_period_end, users[i].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) )) {
                        char inv_line_name[101] = "";
                        double amount = 0;
                        char period_start[20] = "";
                        char period_end[20] = "";
                        int subscription_id = 0;
                        int fdr_id = 0;
                        char name_for_user[256] = "";
                        char date_string[50] = "";
                        char action[100] = "";

                        if (row[0]) amount = atof(row[0]);
                        if (row[1]) strcpy(period_start, row[1]);
                        if (row[2]) strcpy(period_end, row[2]);
                        if (row[3]) strcpy(inv_line_name, row[3]);
                        if (row[4]) subscription_id = atoi(row[4]);
                        if (row[5]) fdr_id = atoi(row[5]);
                        if (row[6]) strcpy(action, row[6]);
                        if (row[7]) strcpy(name_for_user, row[7]);

                        if (strcmp(action, "refund") == 0) {
                            amount = amount * -1;
                        }

                        if (strlen(period_start) && strlen(period_end)) {
                            char formatted_period_start[20] = "";
                            char formatted_period_end[20] = "";

                            m2_format_date(period_start, confline_date_format, formatted_period_start);
                            m2_format_date(period_end, confline_date_format, formatted_period_end);

                            if (strcmp(period_start, period_end) == 0) {
                                sprintf(date_string, " (%s)", formatted_period_start);
                            } else {
                                sprintf(date_string, " (%s - %s)", formatted_period_start, formatted_period_end);
                            }
                        }

                        // Skip 0 price activation
                        if (strcmp(action, "charge_activation") == 0 && amount == 0) {
                            continue;
                        }

                        invoice_details = realloc(invoice_details, (invoice_details_count + 1) * sizeof(invoice_details_t));
                        memset(&invoice_details[invoice_details_count], 0, sizeof(invoice_details_t));

                        invoice_details[invoice_details_count].calls = 1; // count
                        invoice_details[invoice_details_count].price = amount;
                        invoice_details[invoice_details_count].subscription_id = subscription_id;
                        strcpy(invoice_details[invoice_details_count].name, name_for_user);
                        strcat(invoice_details[invoice_details_count].name, " - ");
                        strcat(invoice_details[invoice_details_count].name, inv_line_name);
                        if (strlen(date_string)) {
                            strcat(invoice_details[invoice_details_count].name, date_string);
                        }

                        // apply taxes
                        invoice_details[invoice_details_count].price_with_tax = invoice_details[invoice_details_count].price;
                        m2_apply_taxes(&invoice_details[invoice_details_count].price_with_tax,
                            users[i].tax_compound,
                            users[i].tax1,
                            users[i].tax2,
                            users[i].tax3,
                            users[i].tax4,
                            users[i].tax1_value,
                            users[i].tax2_value,
                            users[i].tax3_value,
                            users[i].tax4_value);

                        invoice_details[invoice_details_count].user_id = users[i].id;

                        if (show_invoice_lines) {
                            m2_log("Subscription_id: %d, name: %s, price: %.3f, price_with_tax: %.3f, fdr_id: %d\n",
                                invoice_details[invoice_details_count].subscription_id, invoice_details[invoice_details_count].name,
                                invoice_details[invoice_details_count].price, invoice_details[invoice_details_count].price_with_tax, fdr_id);
                        }

                        total_subscription_price += invoice_details[invoice_details_count].price;
                        total_subscription_price_with_tax += invoice_details[invoice_details_count].price_with_tax;

                        invoice_details_count++;
                    }
                }

                mysql_free_result(result);
            }

            m2_log("Total price for subscriptions: %.3f, price_with_tax: %.3f\n", total_subscription_price, total_subscription_price_with_tax);

            total_price = total_price + total_subscription_price + total_did_price;
            total_price_with_tax = total_price_with_tax + total_subscription_price_with_tax + total_did_price_with_tax;

            m2_log("Total invoice price: %.3f, price_with_tax: %.3f\n", total_price, total_price_with_tax);

            // insert invoice lines to m2_invoice_lines table
            // insert will be in batches to improve performance
            if (invoice_details_count || recalculate) {
                if (total_price == 0) {
                    users[i].zero_price_invoice = 1;
                } else {
                    users[i].zero_price_invoice = 0;
                }

                if (recalculate) {
                    delete_invoicedetails(users[i].invoice_id);
                }

                if (!recalculate) {
                    if (insert_new_invoices(i)) exit(1);
                }

                if (!users[i].zero_price_invoice) {
                    insert_invoice_lines(users[i].invoice_id);
                }

                if (recalculate) {
                    // escape ' character
                    m2_escape_string(users[i].nice_name, '\'');
                    m2_escape_string(users[i].timezone, '\'');
                    m2_escape_string(users[i].address, '\'');
                    m2_escape_string(users[i].city, '\'');
                    m2_escape_string(users[i].postcode, '\'');
                    m2_escape_string(users[i].state, '\'');
                    m2_escape_string(users[i].phone, '\'');
                    m2_escape_string(users[i].invoice_number, '\'');
                    m2_escape_string(users[i].vat_number, '\'');

                    // when recalculating, update user details as well
                    sprintf(query, "UPDATE m2_invoices SET total_amount = %f, total_amount_with_taxes = %f, "
                        "client_name = '%s', currency_exchange_rate = %f, client_details1 = '%s', client_details2 = '%s', client_details3 = '%s', "
                        "client_details4 = '%s', client_details5 = '%d', client_details6 = '%s', client_details7 = '%s', timezone = '%s (GMT %s%g)', grace_period = '%d' "
                        "WHERE id = %lld",
                        total_price, total_price_with_tax, users[i].nice_name, users[i].exchange_rate, users[i].address, users[i].city, users[i].postcode,
                        users[i].state, users[i].direction_id, users[i].phone, users[i].vat_number, users[i].timezone, users[i].timezone_offset >= 0 ? "+" : "",
                        users[i].timezone_offset, users[i].invoice_grace_period, users[i].invoice_id);
                } else {
                    // update m2_invoices with calculated amount and amount_with_taxes
                    sprintf(query, "UPDATE m2_invoices SET total_amount = %f, total_amount_with_taxes = %f WHERE id = %lld", total_price, total_price_with_tax, users[i].invoice_id);
                }

                // send query
                if (m2_mysql_query(query)) {
                    return 1;
                }
            } else {
                m2_log("Answered calls and subscriptions not found in billing period %s - %s\n", users[i].period_start, users[i].period_end);

                if (!recalculate && !generate_invoices_manually) {
                    users[i].zero_price_invoice = 1;
                    if (insert_new_invoices(i)) exit(1);
                }
            }

            skip_user:

            // when we have specific index of users structure
            // then break the loop
            if (index > -1) {
                goto exit_single_user2;
            }

        }
    }

    exit_single_user2:

    return 0;

}


/*
    Adjust datetime to to server time or to user 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 adjusted datetime to server time is 2014-01-22 13:00:33

    same with server time to user time
*/


void adjust_to_target_time_with_tz_handle(char *date, char *buffer, float offset, int target) {

    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);

    // target = 1, adjust to user time
    // target = 0, adjust to server time
    if (target) {
        user_time = mktime(&user_tm) - (time_t)round((server_offset - offset) * 60.0 * 60.0);
    } else {
        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();

}


/*
    Check if manager is reponsible for specific users
    This is used when invoice is generated for "All" users
    Only users assigned to responsible manager should have invoices generated
*/


void check_responsible_manager(int user_id) {

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

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

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

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0] && atoi(row[0])) responsible_manager = 1;
            }
        }
    }

    mysql_free_result(result);

}


void error_handle() {
    static int marked = 0;

    if (marked == 0) {
        if (task_failed) {
            if (task_id) {
                m2_task_unlock(4);
                m2_log("Task failed\n");
            }
        }
        marked = 1;
    }

}

int check_es_data_integrity(char *start_date, char *end_date) {

    long long int mysql_total_calls = 0;
    long long int elasticsearch_total_calls = 0;
    int retries = 0;
    int current_day_overlap = 0;
    char local_start_date[20] = "";
    char local_end_date[20] = "";
    char current_date_no_hours[11] = "";

    strncpy(current_date_no_hours, current_date, 10);

    check_again:

    strcpy(local_start_date, start_date);
    strcpy(local_end_date, end_date);

    strcpy(local_start_date + 11, "00:00:00");
    strcpy(local_end_date + 11, "23:59:59");

    // special case when end date is the same as current day
    // in this case we do not include current day in the check period
    // check one day less as current day is handled when script starts
    if (strncmp(local_end_date, current_date, 10) == 0) {
        current_day_overlap = 1;

        // subtract one day
        time_t tmp_time;
        struct tm tmp_tm, tmp_ptm;
        char new_local_end_date[20] = "";

        memset(&tmp_tm, 0, sizeof(struct tm));
        strptime(local_end_date, DATE_FORMAT, &tmp_tm);
        // get time in seconds
        tmp_time = mktime(&tmp_tm);
        // add 1 day
        tmp_time -= 24 * 60 * 60;
        // convert to time structure
        gmtime_r(&tmp_time, &tmp_ptm);
        // format time string
        strftime(new_local_end_date, sizeof(new_local_end_date), DATE_FORMAT, &tmp_ptm);

        strcpy(local_end_date, new_local_end_date);

        m2_log("Invoice end date %s overlaps current day %s (which may have ongoing calls)! Invoice end date will be checked till: %s\n",
            end_date, current_date_no_hours, new_local_end_date);
    }

    mysql_total_calls = get_calls_count_from_db_cached(local_start_date, local_end_date, 0);
    elasticsearch_total_calls = get_calls_count_from_elasticsearch(local_start_date, local_end_date);

    if (global_debug) {
        m2_log("MySQL calls count for period %s - %s: %lld\n", local_start_date, local_end_date, mysql_total_calls);
        m2_log("   ES calls count for period %s - %s: %lld\n", local_start_date, local_end_date, elasticsearch_total_calls);
    }

    // count doesn't match? recheck again
    if (mysql_total_calls != elasticsearch_total_calls) {
        if (retries == 0) {
            get_calls_count_from_db_cached(local_start_date, local_end_date, 1);
            mysql_total_calls = 0;
            elasticsearch_total_calls = 0;
            retries++;
            goto check_again;
        }
    }

    if (mysql_total_calls != elasticsearch_total_calls) {
        m2_log("MySQL and Elasticsearch calls count does not match! Data is incorrect and invoice cannot be generated!\n");

        m2_log("MySQL calls count for period %s - %s: %lld\n", local_start_date, local_end_date, mysql_total_calls);
        m2_log("   ES calls count for period %s - %s: %lld\n", local_start_date, local_end_date, elasticsearch_total_calls);

        // create error message in action log
        if (!action_log_reported) {
            if (m2_mysql_query("INSERT INTO actions(action, user_id, target_id, data, data2, data3, date, target_type) VALUES('error', '0', '0', 'Invoice was not generated', 'Inconsistent data between ES and MySQL', 'Please contact support', NOW(), 'Invoices')")) {
                return 1;
            }

            action_log_reported = 1;
        }

        return 1;
    }

    if (current_day_overlap && !current_day_es_db_check_is_ok) {
        m2_log("MySQL and Elasticsearch calls count does not match! Data is incorrect and invoice cannot be generated!\n");
        m2_log("ES and DB data does not match for current day (%s)\n", current_date_no_hours);
        return 1;
    }

    return 0;

}


/*
    Get calls count from elasticsearch
*/


long long int get_calls_count_from_elasticsearch(char *start_date, char *end_date) {

    CURL *curl_handle;
    CURLcode res;

    char json_query[2048] = "";
    char http_request_url[2000] = "";
    struct MemoryStruct chunk;
    char period_start_es[256] = "";
    char period_end_es[256] = "";
    long long int elasticsearch_calls_count = 0;

    // will be grown as needed by the WriteMemoryCallback
    chunk.memory = malloc(1);
    // no data at this point
    chunk.size = 0;

    format_calldate_to_es(start_date, period_start_es);
    format_calldate_to_es(end_date, period_end_es);

    // set url
    sprintf(http_request_url, "http://%s:9200/m2/calls/_search?search_type=count", elasticsearch_host);
    // format query
    sprintf(json_query, "{\"size\":0,\"query\":{\"filtered\":{\"filter\":{\"bool\":{\"must\":[{\"range\":{\"calldate\":{\"gte\":\"%s\",\"lte\":\"%s\"}}}]}}}}}",
        period_start_es, period_end_es);

    if (global_debug) {
        m2_log("Elasticsearch query:\n");
        m2_log("%s\n", json_query);
    }

    curl_global_init(CURL_GLOBAL_ALL);

    // init the curl session
    curl_handle = curl_easy_init();
    // specify URL to get
    curl_easy_setopt(curl_handle, CURLOPT_URL, http_request_url);
    // set body length
    curl_easy_setopt(curl_handle, CURLOPT_POSTFIELDSIZE, strlen(json_query));
    // send json query
    curl_easy_setopt(curl_handle, CURLOPT_POSTFIELDS, json_query);
    // send all data to this function
    curl_easy_setopt(curl_handle, CURLOPT_WRITEFUNCTION, WriteMemoryCallback);
    // we pass our 'chunk' struct to the callback function
    curl_easy_setopt(curl_handle, CURLOPT_WRITEDATA, (void *)&chunk);
    // some servers don't like requests that are made without a user-agent field, so we provide one
    curl_easy_setopt(curl_handle, CURLOPT_USERAGENT, "libcurl-agent/1.0");
    // get it!
    res = curl_easy_perform(curl_handle);

    // check for errors
    if (res != CURLE_OK) {
        m2_log("curl_easy_perform() failed: %s\n", curl_easy_strerror(res));
        return 0;
    } else {

        if (!strlen(chunk.memory)) {
            m2_log("Elasticsearch response is empty\n");
            goto curl_cleanup;
        }

        cJSON *root = cJSON_Parse(chunk.memory);
        if (root == NULL) {
            m2_log("JSON root is NULL\n");
            goto curl_cleanup;
        }

        cJSON *hits = cJSON_GetObjectItem(root, "hits");
        if (hits == NULL) {
            m2_log("JSON hits is NULL\n");
            cJSON_Delete(root);
            goto curl_cleanup;
        }

        if (cJSON_GetObjectItem(hits, "total") == NULL && cJSON_GetObjectItem(hits, "total")->valueint == 0) {
            m2_log("Zero hits\n");
            cJSON_Delete(root);
            goto curl_cleanup;
        } else {
            elasticsearch_calls_count = cJSON_GetObjectItem(hits, "total")->valueint;
        }

        if (root) cJSON_Delete(root);

    }

    curl_cleanup:

    // cleanup curl stuff
    curl_easy_cleanup(curl_handle);
    // free received data
    free(chunk.memory);
    // we're done with libcurl, so clean it up
    curl_global_cleanup();

    return elasticsearch_calls_count;

}

void delete_daily_calls(char *date) {
    char query[1000] = "";
    int i = 0;

    for (i = 0; i < daily_calls_count; i++) {
        if (strcmp(daily_calls[i].date, date) == 0) {
            if (!daily_calls[i].rechecked) {
                strcpy(daily_calls[i].date, "");
                break;
            } else {
                return;
            }
        }
    }

    sprintf(query, "DELETE FROM daily_calls_count WHERE `date` = '%s'", date);

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

long long int get_calls_count_from_db_cached(char *start_date, char *end_date, int recheck) {

    long long int mysql_total_calls = 0;
    int i = 0;
    char date[20] = "";
    char date_to_check[20] = "";

    strcpy(date_to_check, start_date);
    sprintf(date_to_check + 11, "00:00:00");

    for (i = 0; i < 1000; i++) {
        strncpy(date, date_to_check, 10);

        if (recheck) {
            delete_daily_calls(date);
        }

        mysql_total_calls += get_daily_calls(date, recheck);

        if (strncmp(date, end_date, 10) == 0) {
            return mysql_total_calls;
        }

        // iterate next day
        time_t tmp_time;
        struct tm tmp_tm, tmp_ptm;
        char next_day[20] = "";

        memset(&tmp_tm, 0, sizeof(struct tm));
        strptime(date_to_check, DATE_FORMAT, &tmp_tm);
        // get time in seconds
        tmp_time = mktime(&tmp_tm);
        // add 1 day
        tmp_time += 24 * 60 * 60;
        // convert to time structure
        gmtime_r(&tmp_time, &tmp_ptm);
        // format time string
        strftime(next_day, sizeof(next_day), DATE_FORMAT, &tmp_ptm);
        strcpy(date_to_check, next_day);

        if (i == 1000) {
            m2_log("Something is wrong\n");
            exit(1);
        }
    }

    return mysql_total_calls;
}

int get_daily_calls(char *date, int recheck) {

    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[2048] = "";
    int found = 0;
    int calls = 0;
    int i = 0;

    // ### check intenal list
    for (i = 0; i < daily_calls_count; i++) {
        if (strcmp(daily_calls[i].date, date) == 0) {
            return daily_calls[i].calls;
        }
    }

    // ### get from daily_calls table
    sprintf(query, "SELECT calls FROM daily_calls_count WHERE `date` = '%s'", date);

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

    result = mysql_store_result(&mysql);

    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0]) {
                    daily_calls = realloc(daily_calls, (daily_calls_count + 1) * sizeof(daily_calls_t));
                    memset(&daily_calls[daily_calls_count], 0, sizeof(daily_calls_t));

                    strcpy(daily_calls[daily_calls_count].date, date);
                    daily_calls[daily_calls_count].calls = atoi(row[0]);
                    calls = atoi(row[0]);

                    daily_calls_count++;
                    found = 1;
                }
            }
        }
    }

    mysql_free_result(result);

    // ### get straight from calls table
    if (!found) {
        sprintf(query, "SELECT COUNT(id) FROM calls WHERE calldate BETWEEN '%s 00:00:00' AND '%s 23:59:59'", date, date);

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

        result = mysql_store_result(&mysql);

        if (result) {
            if (mysql_num_rows(result)) {
                while (( row = mysql_fetch_row(result) )) {
                    if (row[0]) {
                        daily_calls = realloc(daily_calls, (daily_calls_count + 1) * sizeof(daily_calls_t));
                        memset(&daily_calls[daily_calls_count], 0, sizeof(daily_calls_t));

                        strcpy(daily_calls[daily_calls_count].date, date);
                        daily_calls[daily_calls_count].calls = atoi(row[0]);
                        daily_calls[daily_calls_count].rechecked = recheck;
                        calls = atoi(row[0]);

                        daily_calls_count++;

                        // insert new record to daily_calls table
                        sprintf(query, "INSERT INTO daily_calls_count(`date`, calls) VALUES('%s', %d)", date, calls);

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

        mysql_free_result(result);
    }

    return calls;

}

int get_calls_count_from_db(char *start_date, char *end_date) {

    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[2048] = "";
    int calls = 0;

    sprintf(query, "SELECT COUNT(id) FROM calls WHERE calldate BETWEEN '%s' AND '%s'", start_date, end_date);

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

    result = mysql_store_result(&mysql);

    if (result) {
        if (mysql_num_rows(result)) {
            while (( row = mysql_fetch_row(result) )) {
                if (row[0]) {
                    calls = atoi(row[0]);
                }
            }
        }
    }

    mysql_free_result(result);

    return calls;
}

int check_es_db_consistency_for_today() {

    char start_date[20] = "";
    char end_date[20] = "";
    char end_date_hour[3] = "";
    long long int es_calls = 0;
    long long int db_calls = 0;
    int current_hour = 0;

    current_day_es_db_check_is_ok = 0;

    struct tm tmp_tm;

    memset(&tmp_tm, 0, sizeof(struct tm));
    strptime(current_date, DATE_FORMAT, &tmp_tm);

    current_hour = tmp_tm.tm_hour;

    if (current_hour <= wait_time) {
        return 0;
    }

    m2_log("Checking MySQL and Elasticsearch consistency for current day\n");

    if ((current_hour - wait_time) < 10) {
        sprintf(end_date_hour, "0%d", current_hour - wait_time);
    } else {
        sprintf(end_date_hour, "%d", current_hour - wait_time);
    }

    strcpy(start_date, current_date);
    strcpy(end_date, current_date);
    strcpy(start_date + 11, "00:00:00");
    strcpy(end_date + 11, end_date_hour);
    strcpy(end_date + 13, ":59:59");

    db_calls = get_calls_count_from_db(start_date, end_date);
    es_calls = get_calls_count_from_elasticsearch(start_date, end_date);

    m2_log("MySQL calls count for period %s - %s: %lld\n", start_date, end_date, db_calls);
    m2_log("   ES calls count for period %s - %s: %lld\n", start_date, end_date, es_calls);

    if (db_calls != es_calls) {
        m2_log("MySQL and Elasticsearch calls count does not match!\n");
        current_day_es_db_check_is_ok = 0;
    } else {
        m2_log("MySQL and Elasticsearch calls count matches\n");
        current_day_es_db_check_is_ok = 1;
    }

    return 0;
}

int get_confline_date_format() {
    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[2048] = "";

    sprintf(query, "SELECT value FROM conflines WHERE name = 'Date_format' AND owner_id = 0");

    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] && strlen(row[0]) >= 8) {
                    strncpy(confline_date_format, row[0], 8);
                }
            }
        }

        mysql_free_result(result);
    }

    if (!strlen(confline_date_format)) {
        strcpy(confline_date_format, "%Y-%m-%d");
    }

    return 0;
}

int check_es_fields() {

    char http_request_url[300] = "";
    struct MemoryStruct chunk;
    CURL *curl_handle;
    CURLcode res;

    // will be grown as needed by the WriteMemoryCallback
    chunk.memory = malloc(1);
    // no data at this point
    chunk.size = 0;
    // set url
    sprintf(http_request_url, "http://%s:9200/m2/_mapping/calls", elasticsearch_host);

    curl_global_init(CURL_GLOBAL_ALL);

    // init the curl session
    curl_handle = curl_easy_init();
    // specify URL to get
    curl_easy_setopt(curl_handle, CURLOPT_URL, http_request_url);
    // send all data to this function
    curl_easy_setopt(curl_handle, CURLOPT_WRITEFUNCTION, WriteMemoryCallback);
    // we pass our 'chunk' struct to the callback function
    curl_easy_setopt(curl_handle, CURLOPT_WRITEDATA, (void *)&chunk);
    // some servers don't like requests that are made without a user-agent field, so we provide one
    curl_easy_setopt(curl_handle, CURLOPT_USERAGENT, "libcurl-agent/1.0");
    // get it!
    res = curl_easy_perform(curl_handle);

    // check for errors
    if (res != CURLE_OK) {
        m2_log("curl_easy_perform() failed: %s\n", curl_easy_strerror(res));
        return 1;
    } else {
        if (!strlen(chunk.memory)) {
            m2_log("Elasticsearch response is empty\n");
            goto curl_cleanup;
        }

        cJSON *root = cJSON_Parse(chunk.memory);
        if (root == NULL) {
            m2_log("JSON root is NULL\n");
            goto curl_cleanup;
        }

        cJSON *m2 = cJSON_GetObjectItem(root, "m2");
        if (m2 == NULL) {
            m2_log("JSON m2 is NULL\n");
            cJSON_Delete(root);
            goto curl_cleanup;
        }

        cJSON *mappings = cJSON_GetObjectItem(m2, "mappings");
        if (mappings == NULL) {
            m2_log("JSON mappings is NULL\n");
            cJSON_Delete(root);
            goto curl_cleanup;
        }

        cJSON *calls = cJSON_GetObjectItem(mappings, "calls");
        if (calls == NULL) {
            m2_log("JSON calls is NULL\n");
            cJSON_Delete(root);
            goto curl_cleanup;
        }

        cJSON *properties = cJSON_GetObjectItem(calls, "properties");
        if (properties == NULL) {
            m2_log("JSON properties is NULL\n");
            cJSON_Delete(root);
            goto curl_cleanup;
        }

        int i = 0;
        for (i = 0; i < cJSON_GetArraySize(properties); i++) {
            cJSON *item = cJSON_GetArrayItem(properties, i);
            if (item && item->string) {
                if (strcmp(item->string, "did_id") == 0) es_did_field_exists = 1; // if did_id exists, we assume that other did_xxxx fields exist
            }
        }

        if (root) cJSON_Delete(root);
    }

    curl_cleanup:

    // cleanup curl stuff
    curl_easy_cleanup(curl_handle);
    // free received data
    free(chunk.memory);
    // we're done with libcurl, so clean it up
    curl_global_cleanup();

    return 0;
}