//Url config spreadsheet----------------------------------------------------------------------------------
//********************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
/*********************************************************************************************************
Script:      Impressions control
Version:     06.12.2017
Created By:  Stanislav Jilek [standajilek.cz]
Idea:        UnicornsLab [ulab.rocks]
/********************************************************************************************************/

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

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

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

    var condition_date = new Date();
    condition_date.setUTCDate(condition_date.getUTCDate() - settings_sheet.getRange("B6").getValue());

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

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

        var adwords_settings = settings_sheet.getRange("C8: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 adwords_impressions_today = adwords_stats("TODAY", condition_date);
                var adwords_impressions_yesterday = adwords_stats("YESTERDAY", condition_date);
                var adwords_impressions_last_7_days = adwords_stats("LAST_7_DAYS", condition_date);

                var adwords_impressions_control = impressions_control(adwords_impressions_today, adwords_impressions_yesterday, adwords_impressions_last_7_days, impressions_condition).sort(sort_array).reverse();

                if (adwords_impressions_control.length > 0)
                {
                    var table_header = "<tr bgcolor='#ffd75d'><th>Campaign</th><th>Impressions today</th><th>Impressions yesterday</th><th>Impressions last 7 days</th><th>Cost</th><th>Conversion Value</th></tr>"

                    table += "<tr><td colspan='6' bgcolor='#4fabe5'><strong>ADWORDS: " + account_name + "</strong></td></tr>" + table_header;

                    table += table_format(adwords_impressions_control, currency);

                    send_mail = 1;
                }

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

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

        var sklik_settings = settings_sheet.getRange("A8: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 sklik_impressions_today = sklik_stats(client_login.session, sklik_account[i][0], 1, 1, condition_date);
            var sklik_impressions_yesterday = sklik_stats(client_login.session, sklik_account[i][0], 2, 2, condition_date);
            var sklik_impressions_last_7_days = sklik_stats(client_login.session, sklik_account[i][0], 7, 1, condition_date);

            var sklik_impressions_control = impressions_control(sklik_impressions_today, sklik_impressions_yesterday, sklik_impressions_last_7_days, impressions_condition).sort(sort_array).reverse();

            if (sklik_impressions_control.length > 0)
            {
                var table_header = "<tr bgcolor='#ffd75d'><th>Campaign</th><th>Impressions yesterday</th><th>Impressions before yesterday</th><th>Impressions last 7 days</th><th>Cost</th><th>Conversion Value</th></tr>"

                table += "<tr><td colspan='6' bgcolor='#ff4646'><strong>SKLIK: " + sklik_account[i][1] + "</strong></td></tr>" + table_header;

                table += table_format(sklik_impressions_control, "CZK");

                send_mail = 1;
            }
        }

//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 adwords_stats(date, condition_date) {
    var result = [];

    var report = AdWordsApp.report("SELECT CampaignId, CampaignName, Impressions, Cost, ConversionValue, StartDate FROM CAMPAIGN_PERFORMANCE_REPORT WHERE CampaignStatus=ENABLED DURING " + date);
    var rows = report.rows();

    while (rows.hasNext())
    {
        var row = rows.next();
        var campaign_id = row["CampaignId"];
        var campaign_name = row["CampaignName"];
        var impressions = parseFloat(row["Impressions"].split(",").join(""));
        var cost = parseFloat(row["Cost"].split(",").join(""));
        var conversion_value = parseFloat(row["ConversionValue"].split(",").join(""));
        var start_date = row["StartDate"];

        var create_date = new Date(start_date.substr(0, 4), start_date.substr(5, 2) - 1, start_date.substr(8, 2));

        if (create_date >= condition_date)
        {
            campaign_name += "<br><strong><font color='green'>[new campaign: " + start_date + "]</strong></font>";
        }

        result.push([campaign_id, campaign_name, impressions, cost, conversion_value, create_date]);
    }

    return(result);
}
//--------------------------------------------------------------------------------------------------------
function sklik_stats(session, sklik_account_id, date_start, date_end, condition_date) {
    var result = [];

//Počet dní zpátky
    var start_date = new Date();
    start_date.setUTCDate(start_date.getUTCDate() - date_start);
    start_date = Utilities.formatDate(start_date, 'GTM - 1', 'yyyy-MM-dd');

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

//createReport
    var createReport = sklik_api([{'session': session, 'userId': sklik_account_id},
        {'isDeleted': false,
            'mixedStatus': ['active', 'active-exhausted_day_budget', 'active-expired_budget', 'active-expired_clicks', 'active-expired_time', 'active-low_budget', 'active-waiting'],
            '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 i = 0; i < offset; i++)
    {
        try
        {
//readReport
            var readReport = sklik_api([{'session': session, 'userId': sklik_account_id},
                report_id,
                {'offset': i * 5000,
                    'limit': 5000,
                    'allowEmptyStatistics': true,
                    'displayColumns': ['id', 'name', 'impressions', 'totalMoney', 'conversionValue', 'createDate']}],
                    'campaigns.readReport');

            //cyklus pro procházení statistik
            if (readReport.report.length > 0)
            {
                for (var j = 0; j < readReport.report.length; j++)
                {

                    var campaign_id = readReport.report[j].id;
                    var campaign_name = readReport.report[j].name;
                    var impressions = readReport.report[j].stats[0].impressions;
                    var cost = readReport.report[j].stats[0].totalMoney / 100;
                    var conversion_value = readReport.report[j].stats[0].conversionValue;
                    var start_date = (readReport.report[j].createDate).substr(0, 4) + '-' + (readReport.report[j].createDate).substr(4, 2) + '-' + (readReport.report[j].createDate).substr(6, 2);

                    var create_date = new Date((readReport.report[j].createDate).substr(0, 4), (readReport.report[j].createDate).substr(4, 2) - 1, (readReport.report[j].createDate).substr(6, 2));

                    if (create_date >= condition_date)
                    {
                        campaign_name += "<br><strong><font color='green'>[new campaign: " + start_date + "]</strong></font>";
                    }

                    result.push([campaign_id, campaign_name, impressions, cost, conversion_value, start_date]);

                }
            }
        } catch (err)
        {
        }
        Utilities.sleep(200);
    }

    return(result);
}
//--------------------------------------------------------------------------------------------------------
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)
}
//--------------------------------------------------------------------------------------------------------
function sort_array(a, b) {
    if (a[3] === b[3]) {
        return 0;
    } else {
        return (a[3] < b[3]) ? -1 : 1;
    }
}
//--------------------------------------------------------------------------------------------------------
function table_format(impressions_control, currency) {
    var result = "";
    var row = 0;
    for (var i = 0; i < impressions_control.length; i++)
    {
        result += "<tr bgcolor='" + row_color(row) + "'><td nowrap>" + impressions_control[i][0] + "</td>" +
                "<td nowrap align='right'>" + number_format(impressions_control[i][1].toFixed(0)) + "</td>" +
                "<td nowrap align='right'>" + number_format(impressions_control[i][2].toFixed(0)) + "</td>" +
                "<td nowrap align='right'>" + number_format(impressions_control[i][3].toFixed(0)) + "</td>" +
                "<td nowrap align='right'>" + number_format(impressions_control[i][4].toFixed(0)) + " " + currency + "</td>" +
                "<td nowrap align='right'>" + number_format(impressions_control[i][5].toFixed(0)) + " " + currency + "</td></tr>";
        row++;
    }

    return(result);
}
//--------------------------------------------------------------------------------------------------------
function impressions_control(impressions_today, impressions_yesterday, impressions_last_7_days, impressions_condition) {
    var result = [];

    for (var i = 0; i < impressions_today.length; i++)
    {
        //TODAY
        if (impressions_today[i][2] == 0)
        {
            //YESTERDAY
            for (var j = 0; j < impressions_yesterday.length; j++)
            {
                if (impressions_yesterday[j][0] == impressions_today[i][0]) //conditions
                {
                    //LAST 7 DAYS
                    for (var k = 0; k < impressions_last_7_days.length; k++)
                    {
                        if ((impressions_last_7_days[k][0] == impressions_yesterday[j][0]) && ((impressions_last_7_days[k][2] >= impressions_condition) || (impressions_today[i][1].indexOf("[new campaign:") >= 0))) //conditions
                        {
                            result.push([impressions_today[i][1], impressions_today[j][2], impressions_yesterday[j][2], impressions_last_7_days[k][2], impressions_last_7_days[k][3], impressions_last_7_days[k][4]]);
                        }
                    }
                }
            }
        }
    }

    return(result);
}