//Url config spreadsheet----------------------------------------------------------------------------------
//********************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
/*********************************************************************************************************
Script:      Budget guard
Version:     06.12.2017
Created By:  Stanislav Jilek [standajilek.cz]
Idea:        Google team: Eliska Kubatova, Anna Petrova, Pavel Jasek [https://www.dlouhychvost.cz/budget-guard/]
/********************************************************************************************************/

function main() {
//Mail settings ------------------------------------------------------------------------------------------ 
    var settings_sheet = ss_config.getSheetByName("budget_guard");

    var mail = settings_sheet.getRange("B2").getValue();
    var subject = settings_sheet.getRange("B3").getValue();
    var send_mail = 0;

    var budget_condition = settings_sheet.getRange("B5").getValue();

//HTML body table
    var table = "<table border='1' style='border-collapse: collapse;' cellpadding='5'>";

//--------------------------------------------------------------------------------------------------------    
//ADWORDS*************************************************************************************************
    try {
//Adwords settings  
        //Last row
        var row = 7;
        while (settings_sheet.getRange("C" + row).getValue() != "")
        {
            row += 1;
        }
        row -= 1;

        var adwords_settings = settings_sheet.getRange("C7:C" + row).getValues();

        //Cycle for a number of accounts
        for (var i = 0; i < adwords_settings.length; i++)
        {
            try {
                //Mcc select 
                try
                {
                    MccApp.select(MccApp.accounts().withIds([adwords_settings[i][0]]).get().next());
                } catch (err)
                {
                }

                var account_name = AdWordsApp.currentAccount().getName();
                var currency = AdWordsApp.currentAccount().getCurrencyCode();

                var header = 0;
                var row_number = 0;

                var report = AdWordsApp.report("SELECT AssociatedCampaignName, Cost, Amount FROM BUDGET_PERFORMANCE_REPORT WHERE Cost > 0 DURING YESTERDAY").rows();

                while (report.hasNext())
                {
                    var row = report.next();
                    var campaign_name = row["AssociatedCampaignName"];
                    var cost = parseFloat(row["Cost"].split(",").join(""));
                    var budget = parseFloat(row["Amount"].split(",").join(""));
                    var control = cost / budget;

                    if (control > budget_condition)
                    {
                        if (header == 0)
                        {
                            table += "<tr><td colspan='4' bgcolor='#4fabe5'><strong>ADWORDS: " + account_name + "</strong></td></tr><tr bgcolor='#ffd75d'><th>Campaign</th><th>Budget</th><th>Cost yesterday</th><th>Control</th></tr>";
                            header = 1;
                        }

                        table += "<tr bgcolor='" + row_color(row_number) + "'><td nowrap>" + campaign_name + "</td>" +
                                "<td nowrap align='right'>" + number_format(budget.toFixed(0)) + " " + currency + "</td>" +
                                "<td nowrap align='right'>" + number_format(cost.toFixed(0)) + " " + currency + "</td>" +
                                "<td nowrap align='right'>" + (control * 100).toFixed(0) + " %</td></tr>";

                        row_number++;
                        send_mail = 1;
                    }
                }
            } catch (err) {
                Logger.log("ADWORDS: " + err);
            }
        }
    } catch (err) {
        Logger.log("ADWORDS: " + err);
    }

//--------------------------------------------------------------------------------------------------------     
//SKLIK***************************************************************************************************    
    try {
//Sklik settings    
        //Last row
        var row = 7;
        while (settings_sheet.getRange("A" + row).getValue() != "")
        {
            row += 1;
        }
        row -= 1;

        var sklik_settings = settings_sheet.getRange("A7:A" + row).getValues();

        //Login to Sklik 
        var token = settings_sheet.getRange("B4").getValue();

//--------------------------------------------------------------------------------------------------------       
//client.login      
        var client_login = sklik_api([token], 'client.loginByToken');

//--------------------------------------------------------------------------------------------------------       
//client.get
        var client_get = sklik_api([{'session': client_login.session}], 'client.get')

        var sklik_account = [];

        for (var i = 0; i < sklik_settings.length; i++) //Sklik account
        {
            for (var j = 0; j < client_get.foreignAccounts.length; j++)
            {
                if (sklik_settings[i][0].toLowerCase() == client_get.foreignAccounts[j].username.toLowerCase())
                {
                    sklik_account.push([client_get.foreignAccounts[j].userId, client_get.foreignAccounts[j].username, sklik_settings[i][1]]);
                }
            }
            if (sklik_settings[i][0].toLowerCase() == client_get.user.username.toLowerCase())
            {
                sklik_account.push([client_get.user.userId, client_get.user.username, sklik_settings[i][1]]);
            }
        }

        //Cycle for a number of accounts
        for (var i = 0; i < sklik_account.length; i++)
        {

            var header = 0;
            var row_number = 0;

//Yesterday----------------------------------------------------------------------------------------------
            var start_date = new Date();
            start_date.setUTCDate(start_date.getUTCDate() - 1);
            start_date = Utilities.formatDate(start_date, 'GTM - 1', 'yyyy-MM-dd');

            var end_date = new Date();
            end_date.setUTCDate(end_date.getUTCDate() - 1);
            end_date = Utilities.formatDate(end_date, 'GTM - 1', 'yyyy-MM-dd');

//createReport-------------------------------------------------------------------------------------------
            var createReport = sklik_api([{'session': client_login.session, 'userId': sklik_account[i][0]},
                {'statisticsConditions': [{'columnName': 'money', 'operator': 'GT', 'intValue': 0}],
                    'isDeleted': false,
                    'dateFrom': start_date,
                    'dateTo': end_date}],
                    'campaigns.createReport');

            var report_id = createReport.reportId;
            var limit = createReport.totalCount;

            var offset = Math.ceil(limit / 5000);

            for (var k = 0; k < offset; k++)
            {
                try
                {
//readReport---------------------------------------------------------------------------------------------
                    var readReport = sklik_api([{'session': client_login.session, 'userId': sklik_account[i][0]},
                        report_id,
                        {'offset': k * 5000,
                            'limit': 5000,
                            'allowEmptyStatistics': false,
                            'displayColumns': ['name', 'totalMoney', 'budget.dayBudget']}],
                            'campaigns.readReport');

                    if (readReport.report.length > 0)
                    {
                        for (var j = 0; j < readReport.report.length; j++)
                        {
                            var campaign_name = readReport.report[j].name;
                            var cost = readReport.report[j].stats[0].totalMoney / 100;
                            var budget = readReport.report[j].budget.dayBudget / 100;
                            var control = cost / budget;

                            if (control > budget_condition)
                            {
                                if (header == 0)
                                {
                                    table += "<tr><td colspan='4' bgcolor='#ff4646'><strong>SKLIK: " + sklik_account[i][1] + "</strong></td></tr><tr bgcolor='#ffd75d'><th>Campaign</th><th>Budget</th><th>Cost yesterday</th><th>Control</th></tr>";
                                    header = 1;
                                }

                                table += "<tr bgcolor='" + row_color(row_number) + "'><td nowrap>" + campaign_name + "</td>" +
                                        "<td nowrap align='right'>" + number_format(budget.toFixed(0)) + " CZK</td>" +
                                        "<td nowrap align='right'>" + number_format(cost.toFixed(0)) + " CZK</td>" +
                                        "<td nowrap align='right'>" + (control * 100).toFixed(0) + " %</td></tr>";

                                row_number++;
                                send_mail = 1;
                            }
                        }
                    }
                } catch (err)
                {
                }
                Utilities.sleep(200);
            }
        }

//client.logout-------------------------------------------------------------------------------------------
        var client_logout = sklik_api([{'session': client_login.session}], 'client.logout');

    } catch (err) {
        Logger.log("SKLIK: " + err);
    }

    table += "</table>";

//Send mail-----------------------------------------------------------------------------------------------  
    if (send_mail == 1)
    {
        MailApp.sendEmail({to: mail, subject: subject, htmlBody: table});
    }
  
}
//********************************************************************************************************
function sklik_api(parameters, method) {
    var url = 'https://api.sklik.cz/jsonApi/drak/'  + method;
    var options = {'method': 'post', 'contentType': 'application/json', 'muteHttpExceptions': true, 'payload': JSON.stringify(parameters)};

    try {
        return(JSON.parse(UrlFetchApp.fetch(url, options)));
    } catch (err)
    {
        Utilities.sleep(1000);
        try {
            return(JSON.parse(UrlFetchApp.fetch(url, options)));
        } catch (err)
        {
            Utilities.sleep(1000);
            return(JSON.parse(UrlFetchApp.fetch(url, options)));
        }
    }
}
//--------------------------------------------------------------------------------------------------------
function number_format(number) {
    number = number.toString();
    number = number.split("").reverse().join("");
    number = number.substr(0, 3) + " " + number.substr(3, 3) + " " + number.substr(6, 3) + " " + number.substr(9, 3) + " " + number.substr(12, 3);
    number = number.split("").reverse().join("");
    number = number.trim();
    return(number)
}
//--------------------------------------------------------------------------------------------------------
function row_color(row) {
    if (row % 2 == 0)
    {
        row = "#ffffff";
    } else
    {
        row = "#d5d5d5";
    }
    return(row)
}