//Url config spreadsheet----------------------------------------------------------------------------------
//********************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
/*********************************************************************************************************
Script:      Merchant control
Version:     30.12.2018
Created By:  Stanislav Jilek [standajilek.cz]
/********************************************************************************************************/

function main() {
//Settings------------------------------------------------------------------------------------------------ 
    var settings_sheet = ss_config.getSheetByName("merchant_control");
    var mail = settings_sheet.getRange("B2").getValue();
    var subject = settings_sheet.getRange("B3").getValue();
    var send_mail = 0;
    var row = 0;
    var settings = settings_sheet.getRange("A5:E" + settings_sheet.getLastRow()).getValues();

//HTML body table
    var table = "<table border='1' style='border-collapse: collapse;' cellpadding='5'>";
    table += "<tr><td colspan='6' bgcolor='#4fabe5'><strong>Google Merchant Center control</strong></td></tr>";
    table += "<tr bgcolor='#ffd75d'><th>Account</th><th>Products</th><th>Active</th><th>Disapproved</th><th>Pending</th><th>Expiring</th></tr>";

 //Cycle for a number of accounts--------------------------------------------------------------------------
    for (var i = 0; i < settings.length; i++)
    {
        try {
            var accountId = settings[i][2];
            var merchantId = settings[i][1] == "" ? settings[i][2] : settings[i][1];

            var statistics = ShoppingContent.Accountstatuses.get(accountId, merchantId).products[0].statistics;

            var expiring = parseFloat(statistics.expiring);
            var pending = parseFloat(statistics.pending);
            var active = parseFloat(statistics.active);
            var disapproved = parseFloat(statistics.disapproved);
            var sum = expiring + pending + active + disapproved;
            var percent = null_control(disapproved / sum, disapproved, sum);

            Logger.log(settings[i][0] + " (id: " + accountId + ") | products: " + sum + " (active: " + active + ", disapproved: " + disapproved + " (" + (percent * 100).toFixed(2) + " %), pending: " + pending + ", expiring: " + expiring + ")");

            if ((percent > settings[i][3]) || (sum < settings[i][4]))
            {
                table += "<tr bgcolor='" + row_color(row) + "'><td nowrap>" + settings[i][0] + " (id: " + accountId + ")</td>" +
                        "<td nowrap align='right'><font color='" + sum_color(sum, settings[i][4]) + "'>" + number_format(sum.toFixed(0)) + "</font></td>" +
                        "<td nowrap align='right'>" + number_format(active.toFixed(0)) + "</td>" +
                        "<td nowrap align='right'><font color='" + disapproved_color(percent, settings[i][3]) + "'>" + number_format(disapproved.toFixed(0)) + " (" + (percent * 100).toFixed(2) + " %)</font></td>" +
                        "<td nowrap align='right'>" + number_format(pending.toFixed(0)) + "</td>" +
                        "<td nowrap align='right'>" + number_format(expiring.toFixed(0)) + "</td></tr>";
                send_mail = 1;
                row++;
            }

        } catch (err)
        {
            Logger.log(settings[i][0] + " (id: " + settings[i][2] + ") | " + err);
        }
    }

//Send mail-----------------------------------------------------------------------------------------------  
    if (send_mail == 1)
    {
        MailApp.sendEmail({to: mail, subject: subject, htmlBody: table});
    }
}
//--------------------------------------------------------------------------------------------------------
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 null_control(number, a, b) {
    if (a == 0 || b == 0) {
        number = 0;
    }
    return (number);
}
//-------------------------------------------------------------------------------------------------------- 
function sum_color(number, control) {
    var color = "black";
    if (control > number)
    {
        color = "red";
    }
    return(color)
}
//-------------------------------------------------------------------------------------------------------- 
function disapproved_color(number, control) {
    var color = "black";
    if (control < number)
    {
        color = "red";
    }
    return(color)
}