//Url config spreadsheet----------------------------------------------------------------------------------
//********************************************************************************************************
var ss_config = SpreadsheetApp.openByUrl('config spreadsheet url');
/*********************************************************************************************************
Script:      Merchant control
Version:     09.02.2023
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++)
    {
        var accountId = settings[i][1];
        try {
            if (accountId != "")
            {
                var products = ShoppingContent.Accountstatuses.get(accountId, accountId).products;
                products = products !== undefined ? products : [{country: settings[i][2].toLowerCase(), destination: "Shopping", statistics: {expiring: 0, pending: 0, active: 0, disapproved: 0}}];

                for (var j = 0; j < products.length; j++)
                {
                    if ((products[j].country.toLowerCase() === settings[i][2].toLowerCase() || settings[i][2] === "") && products[j].destination === "Shopping")
                    {
                        var accountName = products.length > 1 && settings[i][2] === "" ? settings[i][0] + ": " + products[j].country + " (id: " + accountId + ")" : settings[i][0] + " (id: " + accountId + ")";
                        var statistics = products[j].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(accountName + " | 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>" + accountName + "</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: " + accountId + ") | " + 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)
}