//Url config spreadsheet-------------------------------------------------------------------------------------------------------------
//***********************************************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
/************************************************************************************************************************************
Script:      Cost import
Version:     28.03.2018
Created By:  Stanislav Jilek [standajilek.cz] pro import nakladu z vice portalu me kontaktujte
Idea:        Karel Rujzl [rujzl.cz] a Petra Vetrovska [vetrovka.cz]
/***********************************************************************************************************************************/

function main() {
//Settings*************************************************************************************************************************** 
//Spreadsheet
    var settings_sheet = ss_config.getSheetByName("cost_import");
    var settings = settings_sheet.getRange("B4:U" + settings_sheet.getLastRow()).getValues();

    //count of accounts
    for (var i = 0; i < settings.length; i++)
    {
//Google Analytics
        var account_id = settings[i][0], web_property_id = settings[i][1], import_id = settings[i][2], days_back = settings[i][3];
        var data = [["ga:date", "ga:medium", "ga:source", "ga:adCost", "ga:adClicks", "ga:impressions", "ga:campaign"]];

//Sklik.cz
        try {
            var sklik_token = settings[i][4], sklik_account = settings[i][5], sklik_source = settings[i][6], sklik_medium = settings[i][7], sklik_level = settings[i][8], sklik_exchange_rate = (settings[i][9] != "") ? settings[i][9] : 1, sklik_filter = settings[i][10], sklik_condition = settings[i][11];
            if (sklik_token != "") {
                data = data.concat(sklik(sklik_token, sklik_account, sklik_source, sklik_medium, sklik_level, sklik_exchange_rate, sklik_filter, sklik_condition, days_back));
            }
        } catch (err)
        {
        }

//Facebook.com
        try {
            var facebook_token = settings[i][12], facebook_account = settings[i][13], facebook_source = settings[i][14], facebook_medium = settings[i][15], facebook_level = settings[i][16], facebook_exchange_rate = (settings[i][17] != "") ? settings[i][17] : 1, facebook_filter = settings[i][18], facebook_condition = settings[i][19];
            if (facebook_token != "") {
                data = data.concat(facebook(facebook_token, facebook_account, facebook_source, facebook_medium, facebook_level, facebook_exchange_rate, facebook_filter, facebook_condition, days_back));
            }
        } catch (err)
        {
        }

//Google Analytics upload
        analytics_upload(account_id, web_property_id, import_id, data);

    }
}

function facebook(facebook_token, facebook_account, facebook_source, facebook_medium, facebook_level, facebook_exchange_rate, facebook_filter, facebook_condition, days_back) {
    var data = [];

    try
    {
        var offset = 0;
        var limit = 15;

        if (facebook_level == "account")
        {
            limit = 730;
        }

        if (limit > days_back)
        {
            limit = days_back;
        }

        do {

            if (offset + limit > days_back)
            {
                offset += days_back - offset - limit;
            }

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

            var url = "/v3.0/act_" + facebook_account + "/insights?" +
                    "fields=date_start,spend,clicks,impressions,campaign_name" +
                    "&level=" + facebook_level.replace("s", "") +
                    "&time_range[since]=" + start_date + "&time_range[until]=" + end_date +
                    "&sort=date_start_ascending" +
                    "&time_increment=1" +
                    "&limit=1000000" +
                    "&access_token=" + facebook_token;

            var response = fb_api(url);

            for (var i = 0; i < response.data.length; i++)
            {
                var campaign_name = "";
                if (facebook_level == "campaigns")
                {
                    campaign_name = response.data[i].campaign_name;
                }

                if (new RegExp(facebook_filter.toString().toLowerCase(), "i").test(campaign_name.toLowerCase()) == facebook_condition || facebook_filter == "" || facebook_level == "account")
                {

                    if (campaign_name != "")
                    {
                        campaign_name = '"' + campaign_name + '"';
                    }

                    data.push([response.data[i].date_start.replace("-", "").replace("-", "").replace("-", ""),
                        facebook_medium,
                        facebook_source,
                        (response.data[i].spend * facebook_exchange_rate).toFixed(2),
                        response.data[i].clicks,
                        response.data[i].impressions,
                        campaign_name]);
                }
            }
            offset += limit;
        } while (offset < days_back)
    } catch (err)
    {
        Logger.log("FACEBOOK: " + err);
    }

    return data;

}

function sklik(sklik_token, sklik_account, sklik_source, sklik_medium, sklik_level, sklik_exchange_rate, sklik_filter, sklik_condition, days_back) {
    var data = [];

    try
    {
        var start_date = new Date();
        start_date.setUTCDate(start_date.getUTCDate() - days_back);
        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');

        var client_login = sklik_api([sklik_token], 'client.loginByToken');
        var session = client_login.session;

        var client_get = sklik_api([{'session': session}], 'client.get');
        for (var i = 0; i < client_get.foreignAccounts.length; i++)
        {
            if (sklik_account.toLowerCase() == client_get.foreignAccounts[i].username.toLowerCase())
            {
                var sklik_account_id = client_get.foreignAccounts[i].userId;
            }
        }

        if (sklik_level.toLowerCase() == "account")
        {
            var client_stats = sklik_api([{'session': session, 'userId': sklik_account_id}, {'dateFrom': start_date, 'dateTo': end_date, 'granularity': 'daily'}], 'client.stats');
            for (var i = 0; i < client_stats.report.length; i++)
            {
                data.push([(client_stats.report[i].date).substr(0, 8), sklik_medium, sklik_source, (client_stats.report[i].price / 100 * sklik_exchange_rate).toFixed(2), client_stats.report[i].clicks, client_stats.report[i].impressions, ""]);
            }
        }

        if (sklik_level.toLowerCase() == "campaigns")
        {
            var campaigns_createReport = sklik_api([{'session': session, 'userId': sklik_account_id}, {'dateFrom': start_date, 'dateTo': end_date}, {'statGranularity': 'daily'}], 'campaigns.createReport');

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

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

            if (limit > 0)
            {
                for (var i = 0; i < offset; i++)
                {
                    try
                    {
                        var campaigns_readReport = sklik_api([{'session': session, 'userId': sklik_account_id},
                            report_id,
                            {'offset': i * (parseInt(5000 / days_back)),
                                'limit': parseInt(5000 / days_back),
                                'allowEmptyStatistics': false,
                                'displayColumns': ['name', 'totalMoney', 'clicks', 'impressions']}],
                                'campaigns.readReport');

                        for (var k = 0; k < campaigns_readReport.report.length; k++)
                        {
                            for (var l = 0; l < days_back; l++)
                            {
                                try {
                                    if (new RegExp(sklik_filter.toString().toLowerCase(), "i").test(campaigns_readReport.report[k].name.toLowerCase()) == sklik_condition || sklik_filter == "")
                                    {
                                        data.push([campaigns_readReport.report[k].stats[l].date, sklik_medium, sklik_source, (campaigns_readReport.report[k].stats[l].totalMoney / 100 * sklik_exchange_rate).toFixed(2), campaigns_readReport.report[k].stats[l].clicks, campaigns_readReport.report[k].stats[l].impressions, '"' + campaigns_readReport.report[k].name + '"']);
                                    }
                                } catch (err)
                                {
                                }
                            }
                        }
                    } catch (err)
                    {
                        Logger.log("SKLIK: " + err);
                    }
                    Utilities.sleep(200);
                }
            }
        }

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

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

    return data;

}

function analytics_upload(account_id, web_property_id, import_id, data) {
    try
    {
        if (data.length > 1)
        {
            var blob = Utilities.newBlob(data.join("\n"), "application/octet-stream", "Cost import");
            var upload = Analytics.Management.Uploads.uploadData(account_id, web_property_id, import_id, blob);
        }
    } catch (err)
    {
        Logger.log("ANALYTICS: " + err);
    }
}

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 fb_api(settings_url) {
    var url = 'https://graph.facebook.com' + settings_url;
    var options = {'method': 'get', 'contentType': 'application/json', 'muteHttpExceptions': true};

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