// Author:        Ricardas Stoma
// Company:       Kolmisoft
// Year:          2014
// About:         Script periodically checks invoices and sends reports if needed

#define SCRIPT_VERSION     "1.25"
#define INVOICES_PATH      "/tmp/m2/invoices"
#define SCRIPT_NAME        "m2_invoices_report"
#define WAIT_TIME          "6"

#include "m2_functions.c"
#include "m2_invoices_report.h"

// MAIN FUNCTION

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

    // starting script
    m2_init("Starting M2 Invoices Report script\n");

    if (m2_check_process_lock_by_name("/usr/local/m2/m2_invoices")) {
        m2_log("Process m2_invoices is till running, waiting for it to end...\n");
        sleep(5);

        if (m2_check_process_lock_by_name("/usr/local/m2/m2_invoices")) {
            exit(1);
        }
    }

    // get initial variables
    m2_get_current_date(current_date);
    system("mkdir -p " INVOICES_PATH);

    check_invoice_extension();

    if (get_invoice_settings()) exit(1);
    if (get_email_data()) exit(1);
    if (m2_get_web_config(web_url, web_dir)) exit(1);
    if (get_email_template()) exit(1);

    send_invoices_to_admin();
    send_invoices_to_managers();
    send_invoices_to_users();

    m2_log("Script completed!\n");

    return 0;

}


/*

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

*/


/*
    Get invoice settings from conflines table
*/


int get_invoice_settings() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char email_send_period_str[256] = "";

    m2_log(" -- Checking invoice settings --\n");

    if (m2_mysql_query("SELECT value, name FROM conflines WHERE name IN ('Nice_Number_Digits', 'Email_Sending_Enabled', 'Invoice_email_notice_admin', 'Invoice_email_notice_manager', 'How_often_to_send_email_notice') AND owner_id = 0")) {
        return 1;
    }

    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], "Email_Sending_Enabled") == 0) email_enabled = atoi(row[0]);
                    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]);
                    if (strcmp(row[1], "Nice_Number_Digits") == 0 && atoi(row[0]) > 0) nice_number_digits = atoi(row[0]);
                }
            }
        }
    }

    if (email_send_period == 0) strcpy(email_send_period_str, "do not send");
    if (email_send_period == 1) strcpy(email_send_period_str, "at once when generated");
    if (email_send_period == 2) strcpy(email_send_period_str, "Every 3h (00h 03h 06h 09h 12h 15h 18h 21h)");
    if (email_send_period == 3) strcpy(email_send_period_str, "Every 6h (00h 06h 12h 18h)");
    if (email_send_period == 4) strcpy(email_send_period_str, "Every 12h (00h 12h)");
    if (email_send_period == 5) strcpy(email_send_period_str, "Once a day (12h)");
    if (email_send_period == 6) strcpy(email_send_period_str, "Once a week (12h on monday)");
    if (email_send_period == 7) strcpy(email_send_period_str, "Once a month (12h on first day of month)");

    mysql_free_result(result);
    m2_log("Notify admin: %d, notify manager: %d, how often to send email: %s\n", notify_admin, notify_manager, email_send_period_str);

    if (!email_enabled) {
        m2_log("Email sending is disabled\n");
        return 1;
    }

    if (email_send_period == 0) {
        m2_log("Invoice reporting is disabled\n");
        return 1;
    }

    return 0;

}


/*
    Get email data (smtp server, username, password, ...)
*/


int get_email_data() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    m2_log(" -- Checking email data --\n");

    char query[2048] = "";
    sprintf(query, "SELECT (SELECT value FROM conflines WHERE owner_id = 0 AND name = 'Email_Smtp_Server'), "
                          "(SELECT value FROM conflines WHERE owner_id = 0 AND name = 'Email_Login'), "
                          "(SELECT value FROM conflines WHERE owner_id = 0 AND name = 'Email_Password'), "
                          "(SELECT value FROM conflines WHERE owner_id = 0 AND name = 'Email_from'), "
                          "(SELECT value FROM conflines WHERE owner_id = 0 AND name = 'Email_port')");

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

    result = mysql_store_result(&mysql);
    if (result == NULL) {
        return 1;
    }

    if ((row = mysql_fetch_row(result)) == NULL) {
        return 1;
    }

    if (row[0] && row[1] && row[2] && row[3] && row[4]) {

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

        if (!strlen(email_server)) {m2_log("Email server is empty\n"); return 1;}
        if (!strlen(email_from)) {m2_log("Email from is empty\n"); return 1;}
        if (!email_port) {m2_log("Email port is empty\n"); return 1;}

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

    }

    mysql_free_result(result);

    return 0;

}


/*
    Check if invoice file exists
*/


int check_if_file_exists(char *number) {

    char filename[256] = "";
    sprintf(filename, "%s/%s.%s", INVOICES_PATH, number, extension);

    if (access(filename, F_OK) != -1) {
        return 1;
    } else {
        return 0;
    }

}


/*
    Get invoices email template
*/


int get_email_template() {

    MYSQL_RES *result;
    MYSQL_ROW row;
    int found = 0;

    m2_log(" -- Checking invoice email template --\n");

    char query[2048] = "";
    sprintf(query, "SELECT subject, body, id FROM emails WHERE owner_id = 0 AND name = 'invoices'");

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

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            row = mysql_fetch_row(result);
            if (row[0] && row[1]) {
                strcpy(email_template_subject, row[0]);
                strcpy(email_template_body, row[1]);
                if (row[2]) email_template_id = atoi(row[2]);
                found = 1;
            }
        }
    }

    if (!found) {
        m2_log("WARNING: email template for invoices not found. Using default template\n");
    }

    m2_log("Email subject: %s\n", email_template_subject);
    m2_log("Email body: %s\n", email_template_body);

    mysql_free_result(result);
    return 0;

}


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


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

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

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

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

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

    m2_mysql_query(sqlcmd);

}


/*
    Set timezone by user's timezone name
*/


void set_timezone(char *timezone) {

    char system_timezone[256] = "";

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

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

}


/*
    Send invoices to users by their timezone
*/


void send_invoices_to_users() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[2048] = "";
    int invoices_count = 0;
    int reported_invoices_count = 0;
    int last_user_id = -1;
    int skip_user = 0;

    m2_log(" -- Checking invoices that are confirmed to send to Users --\n");

    // get invoices that are confirmed to send
    sprintf(query, "SELECT m2_invoices.id, user_id, users.time_zone, users.username, m2_invoices.number, "
        "users.billing_email, users.main_email, users.rates_email, users.noc_email "
        "FROM m2_invoices "
        "JOIN users ON users.id = m2_invoices.user_id "
        "WHERE confirmed_to_send_to_user = 1 AND (mailed_to_user = 0 OR mailed_to_user IS NULL) "
        "ORDER BY user_id");

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

    result = mysql_store_result(&mysql);
    if (result) {
        while (( row = mysql_fetch_row(result) )) {

            int invoice_id = 0;
            int user_id = 0;
            char username[256] = "";
            char invoice_number[256] = "";
            char user_email[256] = "";
            char user_timezone[256] = "UTC";
            int time_to_send_is_ok = 0;
            struct tm tm;
            char current_user_date[20] = "";
            char *current_system_tz = NULL;

            if (row[0]) invoice_id = atoi(row[0]);
            if (row[1]) user_id = atoi(row[1]);
            if (row[2]) strcpy(user_timezone, row[2]);
            if (row[3]) strcpy(username, row[3]);
            if (row[4]) strcpy(invoice_number, row[4]);
            if (row[5]) strcpy(user_email, row[5]);
            if (row[6] && strlen(user_email) == 0) strcpy(user_email, row[6]);
            if (row[7] && strlen(user_email) == 0) strcpy(user_email, row[7]);
            if (row[8] && strlen(user_email) == 0) strcpy(user_email, row[8]);

            if (last_user_id != user_id) {
                skip_user = 0;
            }

            last_user_id = user_id;

            // we already checked this user and decided to skip him
            if (skip_user) continue;

            // check if current time is suitable to send invoice email
            // set system timezone to user's timezone

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

            // set user's timezone
            set_timezone(user_timezone);

            m2_get_current_date(current_user_date);
            strptime(current_user_date, DATE_FORMAT, &tm);

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

            m2_log("### Checking user: %s (%d) Current user's time: %s\n", username, user_id, current_user_date);

            // check time
            // time_to_send_is_ok = check_if_time_is_ok(tm.tm_hour, tm.tm_wday, tm.tm_mday);
            time_to_send_is_ok = 1;

            // check if invoice should be reported to user
            if (!time_to_send_is_ok) {
                skip_user = 1;
                m2_log("Email to user (id: %d) for invoice (id: %d) is not ready to be sent yet!\n", user_id, invoice_id);
                continue;
            }

            invoices_count++;

            // delete old invoice file
            char delete_file_command[400] = "";
            sprintf(delete_file_command, "rm -fr %s/%s.%s", INVOICES_PATH, invoice_number, extension);
            m2_log("%s\n", delete_file_command);
            system(delete_file_command);

            // check if file exists
            if (!check_if_file_exists(invoice_number)) {
                // file does not exists so let's send API to create that invoice and wait for a while
                // if file will not be generated after API, then something is wrong
                char wgetcmd[1024] = "";
                sprintf(wgetcmd, "wget --no-check-certificate -T 120 --spider -q '%s%s/api/invoice_xlsx_generate?invoice_id=%d&test=1'", web_url, web_dir, invoice_id);
                m2_log("Sending API request to generate XLSX file: %s\n", wgetcmd);
                system(wgetcmd);

                // check if file exists
                if (!check_if_file_exists(invoice_number)) {
                    char error_message[1024] = "";
                    sprintf(error_message, "File %s/%s.%s not found. Invoice [%d] will not bet sent\n", INVOICES_PATH, invoice_number, extension, invoice_id);
                    m2_log("%s", error_message);
                    email_action_log(user_id, user_email, 0, error_message);
                    continue;
                }
            }

            if (strlen(user_email)) {

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

                char response[1035] = "";
                char attachment[1024] = "";
                char user_email_template_body[9000] = "";
                char user_email_template_subject[1000] = "";

                strcpy(user_email_template_body, email_template_body);
                strcpy(user_email_template_subject, email_template_subject);
                set_email_variables(user_email_template_body, user_email, user_id, invoice_id);
                set_email_variables(user_email_template_subject, user_email, user_id, invoice_id);
                sprintf(attachment, "%s/%s.%s", INVOICES_PATH, invoice_number, extension);

                if (m2_send_email(NULL, user_email, user_email_template_subject, user_email_template_body, attachment, 0, email_template_id, "invoices", 0, response)) {
                    m2_log("Invoices should have been reported, but email sending failed: %s\n", response);
                    email_action_log(user_id, user_email, 0, response);
                } else {
                    char query[2048] = "";

                    m2_log("Email was sent successfully\n");
                    reported_invoices_count++;

                    sprintf(query, "UPDATE m2_invoices SET mailed_to_user = 1, status = 'Sent through Email', status_changed = NOW() WHERE id = %d", invoice_id);
                    if (m2_mysql_query(query)) {
                        exit(1);
                    }

                    email_action_log(user_id, user_email, 1, "");
                }
            } else {
                skip_user = 1;
                m2_log("Email is empty for user_id: %d, username: %s\n", user_id, username);
                email_action_log(user_id, "", 0, "Email is empty");
            }

        }
        mysql_free_result(result);
    }

    // do we have something to report?
    if (!invoices_count) {
        m2_log("Nothing to send to users...\n");
        return;
    } else {
        m2_log("Total invoices to report: %d\n", invoices_count);
        m2_log("Reported invoices: %d\n", reported_invoices_count);
    }

}


/*
    Report about new invoices to admin
*/


void send_invoices_to_admin() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[2048] = "";
    int invoices_count = 0;
    char admin_email[256] = "";
    char *email_body = NULL;
    char admin_timezone[256] = "UTC";
    char current_admin_date[20] = "";
    int time_to_send_is_ok = 0;
    char *current_system_tz = NULL;
    int invoice_id = 0;
    char username[256] = "";
    char invoice_number[256] = "";
    char period_start[256] = "";
    char period_end[256] = "";
    char currency[256] = "";
    char buffer[2000] = "";
    double total_amount = 0;
    struct tm tm;

    m2_log(" -- Checking new invoices that should be reported to admin --\n");

    if (!notify_admin) {
        m2_log("Invoices should not be reported to admin\n");
        return;
    }

    // get admin details
    sprintf(query, "SELECT NULL, users.billing_email, users.main_email, users.rates_email, users.noc_email, users.time_zone "
        "FROM users "
        "LEFT JOIN addresses ON addresses.id = users.address_id "
        "WHERE usertype = 'admin' LIMIT 1");

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

    result = mysql_store_result(&mysql);
    if (result) {
        while (( row = mysql_fetch_row(result) )) {
            if (row[0]) strcpy(admin_email, row[0]);
            if (row[1] && !strlen(admin_email)) strcpy(admin_email, row[1]);
            if (row[2] && !strlen(admin_email)) strcpy(admin_email, row[2]);
            if (row[3] && !strlen(admin_email)) strcpy(admin_email, row[3]);
            if (row[4] && !strlen(admin_email)) strcpy(admin_email, row[4]);
            if (row[5]) strcpy(admin_timezone, row[5]);
        }
        mysql_free_result(result);
    }

    if (!strlen(admin_email)) {
        m2_log("Email is empty for admin!\n");
        email_action_log(0, "", 0, "Email is empty");
        return;
    }

    // set admin's timezone
    set_timezone(admin_timezone);
    m2_get_current_date(current_admin_date);
    strptime(current_admin_date, DATE_FORMAT, &tm);

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

    m2_log("Admin current time: %s\n", current_admin_date);

    // check time
    time_to_send_is_ok = check_if_time_is_ok(tm.tm_hour, tm.tm_wday, tm.tm_mday);

    // check if invoice should be reported to admin
    if (!time_to_send_is_ok) {
        m2_log("Email to admin is not ready to be sent yet!\n");
        return;
    }

    // add header
    sprintf(buffer, "Date: %s\n\nNew invoices are generated and waiting for approval\n\n", current_admin_date);
    email_body = realloc(email_body, (strlen(buffer) + 50) * sizeof(char));
    memset(email_body, 0, (strlen(buffer) + 50) * sizeof(char));
    strcpy(email_body, buffer);

    // get new invoices
    sprintf(query, "SELECT m2_invoices.id, number, period_start, period_end, users.username, total_amount, currency "
        "FROM m2_invoices "
        "JOIN users ON users.id = m2_invoices.user_id "
        "WHERE notified_admin = 0 OR notified_admin IS NULL "
        "ORDER BY user_id");

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

    result = mysql_store_result(&mysql);
    if (result) {
        while (( row = mysql_fetch_row(result) )) {

            invoices_count++;

            if (row[0]) invoice_id = atoi(row[0]);
            if (row[1]) strcpy(invoice_number, row[1]);
            if (row[2]) strcpy(period_start, row[2]);
            if (row[3]) strcpy(period_end, row[3]);
            if (row[4]) strcpy(username, row[4]);
            if (row[5]) total_amount = atof(row[5]);
            if (row[6]) strcpy(currency, row[6]);

            sprintf(buffer, "User: %s\nPeriod start: %s\nPeriod end: %s\nAmount: %0.2f %s\n"
                "Invoice number: %s\nURL: %s%s/m2_invoices/edit/%d\n\n", username, period_start, period_end, total_amount, currency,
                invoice_number, web_url, web_dir, invoice_id);
            email_body = realloc(email_body, (strlen(email_body) + strlen(buffer) + 50) * sizeof(char));
            memset(email_body + strlen(email_body), 0, (strlen(buffer) + 50) * sizeof(char));
            strcat(email_body, buffer);

        }
        mysql_free_result(result);
    }

    // do we have something to report?
    if (!invoices_count) {
        m2_log("Nothing to report to admin..\n");
        if (email_body) {
            free(email_body);
            email_body = NULL;
        }
        return;
    }

    // send email to admin

    m2_log("Sending email to admin (email: %s)\n", admin_email);

    int email_sent = 0;
    char response[1000] = "";

    if (m2_send_email(NULL, admin_email, NULL, email_body, NULL, 0, email_template_id, "invoices", 0, response)) {
        email_sent = 0;
    } else {
        email_sent = 1;
    }

    if (email_body) {
        free(email_body);
        email_body = NULL;
    }

    if (email_sent) {
        m2_log("Email to admin was sent successfully\n");
        char query[2048] = "";
        sprintf(query, "UPDATE m2_invoices SET notified_admin = 1 WHERE notified_admin = 0 OR notified_admin IS NULL");
        if (m2_mysql_query(query)) {
            exit(1);
        }
        email_action_log(0, admin_email, 1, "");
    } else {
        m2_log("Invoices should have been reported, but email sending failed: %s\n", response);
        email_action_log(0, admin_email, 0, response);
    }
}


/*
    Report about new invoices to managers
*/


void send_invoices_to_managers() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    char query[2048] = "";
    int invoices_count = 0;
    int invoices_report_count = 0;
    int last_manager_id = -1;
    char last_manager_email[256] = "";
    char last_manager_date[20] = "";
    int last_tm_hour = 0;
    int last_tm_mday = 0;
    int last_tm_wday = 0;
    char system_cmd[2048] = "";
    int last_manager_updated = 0;

    m2_log(" -- Checking new invoices that should be reported to managers --\n");

    if (!notify_manager) {
        m2_log("Invoices should not be reported to managers\n");
        return;
    }

    system("rm -fr /tmp/m2/invoices/manager_email_body*");

    // get invoices that are confirmed to send
    sprintf(query, "SELECT m2_invoices.id, number, period_start, period_end, users.username, total_amount, currency, managers.id, "
        "NULL, managers.billing_email, managers.main_email, managers.rates_email, managers.noc_email, managers.time_zone "
        "FROM m2_invoices "
        "JOIN users ON users.id = m2_invoices.user_id "
        "JOIN users AS managers ON (users.responsible_accountant_id = managers.id AND managers.usertype = 'manager') "
        "LEFT JOIN addresses ON managers.address_id = addresses.id "
        "WHERE notified_manager = 0 OR notified_manager IS NULL "
        "ORDER BY managers.id, users.id");

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

    result = mysql_store_result(&mysql);
    if (result) {
        while (( row = mysql_fetch_row(result) )) {

            int invoice_id = 0;
            int manager_id = 0;
            char username[256] = "";
            char invoice_number[256] = "";
            char period_start[256] = "";
            char period_end[256] = "";
            char currency[256] = "";
            double total_amount = 0;
            char manager_email[256] = "";
            char manager_timezone[256] = "UTC";
            int time_to_send_is_ok = 0;
            struct tm tm;
            char current_manager_date[20] = "";
            char *current_system_tz = NULL;

            if (row[0]) invoice_id = atoi(row[0]);
            if (row[1]) strcpy(invoice_number, row[1]);
            if (row[2]) strcpy(period_start, row[2]);
            if (row[3]) strcpy(period_end, row[3]);
            if (row[4]) strcpy(username, row[4]);
            if (row[5]) total_amount = atof(row[5]);
            if (row[6]) strcpy(currency, row[6]);
            if (row[7]) manager_id = atoi(row[7]);
            if (row[8]) strcpy(manager_email, row[8]);
            if (row[9] && strlen(manager_email) == 0) strcpy(manager_email, row[9]);
            if (row[10] && strlen(manager_email) == 0) strcpy(manager_email, row[10]);
            if (row[11] && strlen(manager_email) == 0) strcpy(manager_email, row[11]);
            if (row[12] && strlen(manager_email) == 0) strcpy(manager_email, row[12]);
            if (row[13]) strcpy(manager_timezone, row[13]);

            // check if current time is suitable to report invoices
            // set system timezone to manager's timezone

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

            // set manager's timezone
            set_timezone(manager_timezone);

            m2_get_current_date(current_manager_date);
            strptime(current_manager_date, DATE_FORMAT, &tm);

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

            if (last_manager_id != manager_id) {
                // add header
                sprintf(system_cmd, "echo 'Date: %s\n\nNew invoices are generated and waiting for approval\n\n' > /tmp/m2/invoices/manager_email_body_%d.txt", current_manager_date, manager_id);
                system(system_cmd);

                // send email to last manager
                if (invoices_count) {

                    update_last_manager:

                    // check time
                    m2_log("Manager's (id: %d) current time: %s\n", last_manager_id, last_manager_date);
                    time_to_send_is_ok = check_if_time_is_ok(last_tm_hour, last_tm_wday, last_tm_mday);

                    // check if invoice should be reported to user
                    if (!time_to_send_is_ok) {
                        m2_log("Email to manager (id: %d) is not ready to be sent yet!\n", last_manager_id);
                    } else {

                        if (strlen(last_manager_email)) {

                            invoices_report_count++;

                            m2_log("Sending email to manager_id: %d, email: %s\n", last_manager_id, last_manager_email);

                            int email_sent = 0;
                            char email_body[10000] = "";
                            char response[1000] = "";

                            FILE* ptr;
                            char ch;
                            char email_file[100] = "";
                            int pos = 0;

                            sprintf(email_file, "/tmp/m2/invoices/manager_email_body_%d.txt", last_manager_id);

                            ptr = fopen(email_file, "r");

                            if (NULL == ptr) {
                                m2_log("Failed to open %s\n", email_file);
                            }

                            while (!feof(ptr)) {
                                ch = fgetc(ptr);
                                email_body[pos] = ch;
                                pos++;
                            }

                            fclose(ptr);

                            if (m2_send_email(NULL, last_manager_email, NULL, email_body, NULL, 0, email_template_id, "invoices", 0, response)) {
                                email_sent = 0;
                            } else {
                                email_sent = 1;
                            }

                            if (email_sent) {
                                char query[2048] = "";
                                sprintf(query, "UPDATE m2_invoices JOIN users ON users.id = m2_invoices.user_id SET notified_manager = 1 "
                                    "WHERE users.responsible_accountant_id = %d", last_manager_id);
                                if (m2_mysql_query(query)) {
                                    exit(1);
                                }
                                email_action_log(last_manager_id, last_manager_email, 1, "");
                            } else {
                                m2_log("Invoices should have been reported, but email sending failed: %s\n", response);
                                email_action_log(last_manager_id, last_manager_email, 0, response);
                            }

                        } else {
                            m2_log("Email is empty for manager_id: %d\n", last_manager_id);
                            email_action_log(last_manager_id, "", 0, "Email is empty");
                        }
                    }

                    if (last_manager_updated) {
                        goto break_from_loop;
                    }

                }
            }

            last_manager_id = manager_id;
            last_tm_hour = tm.tm_hour;
            last_tm_wday = tm.tm_wday;
            last_tm_mday = tm.tm_mday;
            strcpy(last_manager_email, manager_email);
            strcpy(last_manager_date, current_manager_date);

            sprintf(system_cmd, "echo 'User: %s\nPeriod start: %s\nPeriod end: %s\nAmount: %0.2f %s\n"
                "Invoice number: %s\nURL: %s%s/m2_invoices/edit/%d\n\n' >> /tmp/m2/invoices/manager_email_body_%d.txt",
                username, period_start, period_end, total_amount, currency, invoice_number, web_url, web_dir, invoice_id, manager_id);
            system(system_cmd);

            invoices_count++;


        }
        mysql_free_result(result);
    }

    if (invoices_count && last_manager_updated == 0) {
        last_manager_updated = 1;
        goto update_last_manager;
    }

    break_from_loop:;

    // do we have something to report?
    if (!invoices_report_count) {
        m2_log("Nothing to report to managers...\n");
        return;
    }

}


/*
    Check if current user's time is ok to send email
*/


int check_if_time_is_ok(int hour, int wday, int mday) {

    if (email_send_period == 1) {
        return 1;
    } else if (email_send_period == 2) {
        if (hour == 0 || hour == 3 || hour == 6 || hour == 9 || hour == 12 || hour == 15 || hour == 18 || hour == 21) {
            return 1;
        }
    } else if (email_send_period == 3) {
        if (hour == 0 || hour == 06 || hour == 12 || hour == 18) {
            return 1;
        }
    } else if (email_send_period == 4) {
        if (hour == 00 || hour == 12) {
            return 1;
        }
    } else if (email_send_period == 5) {
        if (hour == 12) {
            return 1;
        }
    } else if (email_send_period == 6) {
        if (hour == 12 && wday == 1) {
            return 1;
        }
    } else if (email_send_period == 7) {
        if (hour == 12 && mday == 1) {
            return 1;
        }
    }

    return 0;

}


/*
    Check invoice extension
*/


void check_invoice_extension() {

    MYSQL_RES *result;
    MYSQL_ROW row;

    if (m2_mysql_query("SELECT value FROM conflines WHERE name = 'convert_xlsx_to_pdf' LIMIT 1")) {
        return;
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            row = mysql_fetch_row(result);
            if (row[0] && atoi(row[0]) == 1) {
                strcpy(extension, "pdf");
            }
        }
    }

    m2_log("Invoice format: %s\n", extension);

    mysql_free_result(result);

}


/*
    Set email variables in template
*/


void set_email_variables(char *email, char *user_email, int user_id, int invoice_id) {
    // user variables
    char username[100] = "";
    char first_name[100] = "";
    char last_name[100] = "";
    char full_name[200] = "";
    char balance[20] = "";
    char balance_range_min[20] = "";
    char balance_range_max[20] = "";

    // system variables
    char currency[20] = "";
    char company_email[20] = "";

    // invoice variables
    char invoice_price[20] = "";
    char invoice_price_with_tax[20] = "";
    char invoice_currency[20] = "";
    char invoice_period_start[20] = "";
    char invoice_period_end[20] = "";

    get_user_variables(user_id, username, first_name, last_name, full_name, balance, balance_range_min, balance_range_max);
    get_system_variables(currency, company_email);
    get_invoice_variables(invoice_id, invoice_price, invoice_price_with_tax, invoice_currency, invoice_period_start, invoice_period_end);

    strcpy(email, replace_str(email, "<%= username %>", username));
    strcpy(email, replace_str(email, "<%= first_name %>", first_name));
    strcpy(email, replace_str(email, "<%= last_name %>", last_name));
    strcpy(email, replace_str(email, "<%= full_name %>", full_name));
    strcpy(email, replace_str(email, "<%= balance %>", balance));
    strcpy(email, replace_str(email, "<%= balance_range_min %>", balance_range_min));
    strcpy(email, replace_str(email, "<%= balance_range_max %>", balance_range_max));
    strcpy(email, replace_str(email, "<%= user_email %>", user_email));

    strcpy(email, replace_str(email, "<%= currency %>", currency));
    strcpy(email, replace_str(email, "<%= email %>", company_email));
    strcpy(email, replace_str(email, "<%= company_email %>", company_email));

    strcpy(email, replace_str(email, "<%= invoice_price %>", invoice_price));
    strcpy(email, replace_str(email, "<%= invoice_price_with_tax %>", invoice_price_with_tax));
    strcpy(email, replace_str(email, "<%= invoice_currency %>", invoice_currency));
    strcpy(email, replace_str(email, "<%= invoice_period_start %>", invoice_period_start));
    strcpy(email, replace_str(email, "<%= invoice_period_end %>", invoice_period_end));

}

void get_user_variables(int user_id, char *username, char *first_name, char *last_name, char *full_name, char *balance, char *balance_min, char *balance_max) {
    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[2048] = "";

    sprintf(query, "SELECT username, first_name, last_name, TRIM(CONCAT(users.first_name, ' ', users.last_name)) AS full_name, ROUND(balance, %d), ROUND(balance_min, %d), ROUND(balance_max, %d) FROM users WHERE id = %d", nice_number_digits, nice_number_digits, nice_number_digits, user_id);

    if (m2_mysql_query(query)) {
        return;
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            row = mysql_fetch_row(result);
            if (row[0]) strcpy(username, row[0]);
            if (row[1]) strcpy(first_name, row[1]);
            if (row[2]) strcpy(last_name, row[2]);
            if (row[3]) strcpy(full_name, row[3]);
            if (row[4]) strcpy(balance, row[4]);
            if (row[5]) strcpy(balance_min, row[5]);
            if (row[6]) strcpy(balance_max, row[6]);
        }
    }

    mysql_free_result(result);
}

void get_system_variables(char *currency, char *company_email) {
    MYSQL_RES *result;
    MYSQL_ROW row;

    if (m2_mysql_query("SELECT (SELECT name FROM currencies WHERE id = 1) AS currency, (SELECT value FROM conflines WHERE name = 'Company_Email') AS company_email")) {
        return;
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            row = mysql_fetch_row(result);
            if (row[0]) strcpy(currency, row[0]);
            if (row[1]) strcpy(company_email, row[1]);
        }
    }

    mysql_free_result(result);
}

void get_invoice_variables(int invoice_id, char *invoice_price, char *invoice_price_with_tax, char *invoice_currency, char *invoice_period_start, char *invoice_period_end) {
    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[2048] = "";

    sprintf(query, "SELECT ROUND(total_amount, %d), ROUND(total_amount_with_taxes, %d), currency, period_start, period_end FROM m2_invoices WHERE id = %d", nice_number_digits, nice_number_digits, invoice_id);

    if (m2_mysql_query(query)) {
        return;
    }

    result = mysql_store_result(&mysql);
    if (result) {
        if (mysql_num_rows(result)) {
            row = mysql_fetch_row(result);
            if (row[0]) strcpy(invoice_price, row[0]);
            if (row[1]) strcpy(invoice_price_with_tax, row[1]);
            if (row[2]) strcpy(invoice_currency, row[2]);
            if (row[3]) strcpy(invoice_period_start, row[3]);
            if (row[4]) strcpy(invoice_period_end, row[4]);
        }
    }

    mysql_free_result(result);
}

void update_email_send_counter() {
    if (m2_mysql_query("UPDATE emails SET callcenter = callcenter + 1 WHERE name = 'invoices'")) {
        return;
    }
}

char *_replace_str(char *str, char *orig, char *rep, int *status) {

    static char buffer[10576] = "";
    char *p = NULL;

    if (!(p = strstr(str, orig)))  {        // Is 'orig' even in 'str'?
        return str;
    }

    strncpy(buffer, str, p - str);          // Copy characters from 'str' start to 'orig' st$
    buffer[p - str] = '\0';

    sprintf(buffer + (p - str), "%s%s", rep, p + strlen(orig));
    *status = 0;

    return buffer;

}

char *replace_str(char *str, char *orig, char *rep) {
    int done = 0;
    static char buffer[10576] = "";

    strcpy(buffer, str);

    while (!done) {
        done = 1;
        strcpy(buffer, _replace_str(buffer, orig, rep, &done));
    }

    return buffer;
}