function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Insert Selection Field", functionName: "menu_insertSelection"},
                     {name: "Settings", functionName: "menu_settings"} ];
  ss.addMenu("OpenERP", menuEntries);
  var a1 = ss.getRange("O60");
  if (a1.getFormula().indexOf(‘oe_settings‘) > -1 && ScriptProperties.getProperty(‘url‘)){
    a1.setFormula("");
  }
}

function menu_settings() {
  var params = [["url", "URL (with http:// or https://)"], ["dbname", "Database Name"], ["username", "Username"], ["password", "Password"]];
  for (var i = 0; i < params.length; i++){
    var input = Browser.inputBox("Server Settings", params[i][1], Browser.Buttons.OK_CANCEL);
    if (input === "cancel"){
      break;
    }
    else{
      ScriptProperties.setProperty(params[i][0], input);
    }
  }
}
 
function menu_insertSelection() {
  var input = Browser.inputBox(‘Insert Selection‘, ‘Format: model, field, domain‘, Browser.Buttons.OK_CANCEL);
  if (input !== "cancel"){
    input = input.replace(/\s+/g, "").split(",");
    var model = input[0];
    var field = input[1];
    var domain = input.slice(2,input.length).join(",");
    var range = SpreadsheetApp.getActiveRange();
    oe_select(range, model, field, domain);
  }
}

function oe_settings(url, dbname, username, password){
  if (url)ScriptProperties.setProperty(‘url‘, url);
  if (dbname)ScriptProperties.setProperty(‘dbname‘, dbname);
  if (username)ScriptProperties.setProperty(‘username‘, username);
  if (password)ScriptProperties.setProperty(‘password‘, password);
}


function oe_browse(model, fields, domain, sort, limit){
  if(typeof model !== "string"){
    throw "model arg expecting string";
  }
  if(typeof fields !== "string"){
    throw "fields arg expecting comma separated field names";
  }
  if (!domain) domain = "[]";
  if(typeof domain !== "string"){
    throw "domain arg expecting string";
  }
  if(sort && typeof sort !== "string"){
    throw "sort arg expecting string";
  }
  if(limit && typeof limit !== "number"){
    throw "limit arg expecting number";
  }
  
  fields = fields.replace(/\s+/g, ",").split(",");
  if(domain) {
    domain = domain.replace(/\‘/g, ‘"‘);
  }
  domain = Utilities.jsonParse(domain);
  
  var records = seach_read(model, fields, domain, sort, limit);
  return parse_records_for_ss(records, fields);
}

function oe_read_group(model, fields, groupby, domain, orderby, limit){
  if(typeof model !== "string"){
    throw "model arg expecting string";
  }
  if(fields && typeof fields !== "string"){
    throw "fields arg expecting comma separated field names";
  }
  if(groupby && typeof groupby !== "string"){
    throw "groupby arg expecting comma separated field names";
  }
  if (!domain) domain = "[]";
  if(typeof domain !== "string"){
    throw "domain arg expecting string";
  }
  if(orderby && typeof orderby !== "string"){
    throw "orderby arg expecting string";
  }
  if(limit && typeof limit !== "number"){
    throw "limit arg expecting number";
  }
  
  fields = fields ? fields.replace(/\s+/g, ",").split(",") : [];
  var fields_tosend = fields.slice();
  var count_index = fields_tosend.indexOf("_count");
  if (count_index !== -1){
    fields_tosend.splice(count_index, 1);
  }
  groupby = groupby ? groupby.replace(/\s+/g, ",").split(",") : "";
  if(domain) {
    domain = domain.replace(/\‘/g, ‘"‘);
  }
  domain = domain ? Utilities.jsonParse(domain) : [];

  var kwargs = {
    "context" : {"group_by":groupby},
    "domain" : domain,
    "fields" : fields_tosend,
    "groupby": groupby,
    "limit": limit ? limit : 10,
    "offset": 0,
    "orderby": orderby ? orderby : false,
  }
  var records = call_kw(model, "read_group", [], {}, 0, kwargs);
  if (groupby.length > 0){
    for (var i = 0; i < records.length; i++){
      if (records[i]["__context"] && records[i]["__context"]["group_by"].length > 0){
        kwargs["domain"] = records[i]["__domain"]
        kwargs["context"] = records[i]["__context"]
        kwargs["groupby"] = records[i]["__context"]["group_by"]
        var sub_records = call_kw(model, "read_group", [], {}, 0, kwargs);
        sub_records.forEach(function(item){ 
          for(var j = 0; j < this.groupby_fields.length;j++){
            item[this.groupby_fields[j]] = records[i][this.groupby_fields[j]]
          }
        },{
          "groupby_fields" : groupby.slice(0,groupby.indexOf(kwargs["groupby"][0]))
        });
        records.splice.apply(records, [i,1].concat(sub_records));
        i--;
      }
    }
  }
  var count_index = fields.indexOf(‘_count‘);
  if (count_index !== -1){
    fields[count_index] = groupby instanceof Array && groupby.length > 0 ? groupby[groupby.length-1]+"_count" : groupby+"_count";
  }
  return parse_records_for_ss(records, fields);
}

function oe_select(range, model, field, domain){
  if(typeof model !== "string"){
    throw "model arg expecting string";
  }
  if(typeof field !== "string"){
    throw "field arg expecting field name";
  }
  if (!domain) domain = "[]";
  if(typeof domain !== "string"){
    throw "domain arg expecting String";
  }
  var records = oe_read_group(model, field, field, domain);
  var dv = range.getDataValidation();
  var result = [];
  for (var i = 0; i < records.length; i++){
    var value = records[i][0];
    if (value)result.push(value.replace(",", ""));
  }
  result = result.slice(0,10);
  dv.requireValuesInList(result);
  dv.setShowDropDown(true);
  range.setDataValidation(dv);
}

function parse_records_for_ss(records, fields){
  var result = [];
  var types = []; 
  if (fields.length === 0 && records.length > 0){
    fields = Object.keys(records[0]);
    result.push(fields);
  }
  for (var i = 0; i < records.length; i++){
    recordArr = [];
    for (var j = 0; j < fields.length; j++){
      var value = records[i][fields[j]];
      if(typeof value === "number")types[fields[j]] = "number"; 
      if (value instanceof Array && value.length === 2 && typeof value[1] === "string")value = value[1];
      else if(value instanceof Array) value = value.join(‘,‘); //TODO: name_get on ids
      else if(typeof value !== "number" && !(value))value = types[fields[j]] && types[fields[j]] === "number" ? 0 : ‘Undefined‘;
      recordArr.push(value);
    }
    result.push(recordArr);
  }
  return result.length > 0 ? result : ‘No Result‘;
}

function seach_read(model, fields, domain, sort, limit){
  if(!(fields instanceof Array)){
    throw "fields arg expecting an Array, not "+typeof fields;
  }
  if (!domain)domain = [];
  if(!(domain instanceof Array)){
    throw "domain arg expecting an Array, not "+typeof domain;
  }
  var session_id = getScriptProperty("session_id");
  var context = {};
  var params = {
    "model" : model,
    "fields" : fields,
    "limit": limit ? limit : 80,
    "domain" : domain,
    "sort": sort,
    "session_id": session_id,
    "context": context,
  }
  var options =
      {
        "method" : "post",
        "contentType" : "application/json",
        "payload" : {
          "id": 1,
          "jsonrpc": "2.0",
          "method": "googlescript",
          "params" : params,
        }
      };
  var json_result = Utilities.jsonParse(oe_fetch(getScriptProperty(‘url‘)+‘/web/dataset/search_read‘, options));
  if (!!json_result.error){
    throw format_openerp_error(json_result.error);
  }
  return json_result.result.records;
}

function call_kw(model, method, args, context, debug, kwargs){
  if (typeof model !== "string"){
    throw "model arg expecting a String, not "+typeof model;
  }
  if (typeof method !== "string"){
    throw "method arg expecting a String, not "+typeof model;
  }
  if(!(args instanceof Array)){
    throw "args arg expecting an Array, not "+typeof args;
  }
  if(!(context instanceof Object)){
    throw "context arg expecting an Object, not "+typeof context;
  }
  if(typeof debug !== "number"){
    throw "debug arg expecting a boolean Number, not "+typeof debug;
  }
  if(!(kwargs instanceof Object)){
    throw "kwargs arg expecting an Object, not "+typeof kwargs;
  }
  var session_id = getScriptProperty(‘session_id‘);
  var params = {
    "args": args,
    "context": context,
    "debug" : debug,
    "kwargs": kwargs,
    "method": method,
    "model": model,
    "session_id": session_id,
  }
  var options =
      {
        "method" : "post",
        "contentType" : "application/json",
        "payload" : {
          "id": 1,
          "jsonrpc": "2.0",
          "method": "googlescript",
          "params" : params,
        }
      };
  var json_result = Utilities.jsonParse(oe_fetch(getScriptProperty(‘url‘)+‘/web/dataset/call_kw‘, options));
  if (!!json_result.error){
    throw format_openerp_error(json_result.error);
  }
  return json_result.result;
}

function authenticate(){
  Logger.log(‘Authentication requested!‘);
  var url = getScriptProperty("url");
  var dbname = getScriptProperty("dbname");
  var username = getScriptProperty("username");
  var password = getScriptProperty("password"); 
  if (!url || !dbname || !username || !password){
    throw "At least one connection detail is not set. You can set them OpenERP > Settings in the menu bar";
  }
  var params = {
    "db": dbname,
    "login": username,
    "password": password,
  }
  var options ={
    "method" : "post",
    "contentType" : "application/json",
    "payload" : Utilities.jsonStringify({
      "id": 1,
      "jsonrpc": "2.0",
      "method": "googlescript",
      "params" : params,
    })
  };
  var response = UrlFetchApp.fetch(url+‘/web/session/authenticate‘, options);
  var json_response = Utilities.jsonParse(response);
  if (json_response.result.uid){
    var sid = response.getHeaders()["Set-Cookie"].split(" ")[0];
    var session_id = json_response.result.session_id;
    ScriptProperties.setProperty("sid", sid);
    ScriptProperties.setProperty("session_id", session_id)
    return {"sid": sid, "session_id": session_id};
  }
  throw "Authentication Error";
}

function oe_fetch(url, options){
  var sid = getScriptProperty("sid");
  var session_id = getScriptProperty("session_id");
  if (!sid || !session_id){
    var authentication = authenticate();
    sid = authentication.sid;
    session_id = authentication.session_id;
  }
  if (typeof options.headers === ‘undefined‘)options[‘headers‘] = {‘cookie‘: sid};
  else options.headers[‘cookie‘] = sid;
  options.payload.params[‘session_id‘] = session_id;
  options[‘payload‘] = Utilities.jsonStringify(options.payload);
  for (var i = 0; i < 1; i++){
    var result = UrlFetchApp.fetch(url, options);
    var json_result = Utilities.jsonParse(result);
    if (json_result.error && json_result.error.data.type === "client_exception" && json_result.error.data.debug.indexOf("SessionExpiredException") !== -1){
      authentication = authenticate();
      options[‘payload‘] = Utilities.jsonParse(options.payload);
      options.headers[‘cookie‘] = authentication.sid;
      options.payload.params[‘session_id‘] = authentication.session_id;
      options[‘payload‘] = Utilities.jsonStringify(options.payload);
    }
    else if(json_result.error){
      throw format_openerp_error(json_result.error);
    }
    else{
      return result;
    }
  }
  throw "Unable to fetch data due to session expired exception";
}

function getScriptProperty(key) {
  var FailLimit = 100;
  var RetryInterval = 50;
  var ScriptPropertyValue = "";
  var Retries=0;
  var randomnumber = 0;
  var TryAgain=true;
  while (TryAgain)
  {
    Retries++;
    randomnumber=Math.floor(Math.random()*59);
    Utilities.sleep(randomnumber*RetryInterval);
    Logger.log(randomnumber*RetryInterval);
    try
    {
      TryAgain=false;
      ScriptPropertyValue = ScriptProperties.getProperty(key);
    }
    catch(err)
    {
      TryAgain = (Retries<FailLimit);
      if (!TryAgain){
        throw ‘Too many attempts to acces script property‘;
      }
      continue;
  }
  return ScriptPropertyValue;
  }
}

function format_openerp_error(error){
  var error_type = error.data.type;
  var trace = "";
  if (error_type === "client_exception")trace = error.data.debug;
  else if (error_type === "server_exception")trace= error.data.fault_code;
  else trace = Utilities.jsonStringify(error.data);
  return error.message + ": "+error_type+", "+ trace;
}