importer.js

// steemwhales.com data importer
// this was made in the early days of steem
// 
// a lot of things are wrong in it:
// 
// how it doesnt use steemjs (it uses a super old module called steem rpc)
// the way it refreshes every existing accounts without priority for active accounts
// the way it stores the data (especially the history table)
// the way it counts followers (theres a much better API function for it now)
// it doesnt count delegation numbers
// all this logic shouldnt be in a single file


var options = {
    // user: "username",
    // pass: "password",
    url: "wss://steemd-int.steemit.com",
    apis: ["database_api", "follow_api"]
    //debug: true
};
var https = require('https');
var _require = require("steem-rpc");
var Client = _require.Client;
var Api = Client.reset(options);
var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : '',
  user     : 'steemw',
  password : '',
  database : 'steemw'
});
connection.connect();

var toBeAdded = [];
var toBeDetail = [];
var alreadyAdded = [];
var start_author = null;
var start_permlink = null;

function mysql_real_escape_string (str) {
    return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
        switch (char) {
            case "\0":
                return "\\0";
            case "\x08":
                return "\\b";
            case "\x09":
                return "\\t";
            case "\x1a":
                return "\\z";
            case "\n":
                return "\\n";
            case "\r":
                return "\\r";
            case "\"":
            case "'":
            case "\\":
            case "%":
                return "\\"+char; // prepends a backslash to backslash, percent,
                                  // and double/single quotes
        }
    });
}

function encode_utf8(s) {
  return unescape(encodeURIComponent(s));
}

function decode_utf8(s) {
  return decodeURIComponent(escape(s));
}

Api.initPromise.then(response => {
    console.log("Api ready:", response);

    loadExistingAccs(function(){
      console.log(alreadyAdded.length+' accounts loaded');
      updateGlobals();
      var timer1 = setInterval(function() {
        checkTenAccounts();
      }, 800)
      var timer2 = setInterval(function() {
        saveDailyHistory();
        addNewUsernames();
        addUsernamesFromWebsite();
        refreshHundredAccs();
        //addFromRichList();
      }, 8500)

      var timer3 = setInterval(function() {
        updateGlobals();
      }, 1000*60)
    })

    // GET A CONTENT
    // Api.database_api().exec("get_content", ['lukestokes', 'the-steemit-usdusdusd-challenge-prove-to-yourself-why-you-are-here']).then(response => {
    //     console.log("get_content", response);
    // })
});

function refreshHundredAccs() {
  var query = 'SELECT name, moreUpdatedOn FROM accounts WHERE updatedOn < (NOW() - INTERVAL 70 MINUTE) ORDER BY updatedOn ASC LIMIT 1000';
  connection.query(query, function(err, rows, fields) {
    if (err) throw err;
    for (var i = 0; i < rows.length; i++) {
      toBeAdded.push(rows[i].name);
    }
    console.log('Refreshing '+rows.length+' existing accs');
    rows = rows.sort(function(a,b) {
      if (!a.moreUpdatedOn || a.moreUpdatedOn == '0000-00-00 00:00:00')
        a.moreUpdatedOn = '1999-08-16T17:00:00Z'
      if (!b.moreUpdatedOn || b.moreUpdatedOn == '0000-00-00 00:00:00')
        b.moreUpdatedOn = '1999-08-16T17:00:00Z'
      return new Date(a.moreUpdatedOn) - new Date(b.moreUpdatedOn)
    })
    for (var i = 0; i<20 && i < rows.length && toBeDetail.length < 100; i++) {
      toBeDetail.push(rows[i].name);
      //console.log(rows[i]);
    }
  });
}

function updateAccCount() {
  var query = 'UPDATE globals SET accounts_tracked=(SELECT COUNT(*) FROM accounts)';
  connection.query(query, function(err, result) {
    if (err) throw err;
  });
}

function updatePrice() {
  Api.database_api().exec("get_order_book", [10]).then(response => {
    var bestBid = parseFloat(response.bids[0].real_price);
    var bestAsk = parseFloat(response.asks[0].real_price);
    var real_price = (bestBid+bestAsk)/2;
    console.log('Updating 1STEEM='+real_price+' SBD');
    var query = 'UPDATE globals SET real_price='+real_price;
    connection.query(query, function(err, result) {
      if (err) throw err;
    });
  })
  getPriceTicker(function(ticker) {
    console.log('Updating 1STEEM=$'+ticker.price_usd);
    var query = 'UPDATE globals SET steem_price_usd='+ticker.price_usd;
    connection.query(query, function(err, result) {
      if (err) throw err;
    });
  })
  getPriceTickerSBD(function(ticker) {
    console.log('Updating 1SBD=$'+ticker.price_usd);
    var query = 'UPDATE globals SET sbd_price_usd='+ticker.price_usd;
    connection.query(query, function(err, result) {
      if (err) throw err;
    });
  })
}

function updateGlobals() {
  console.log("Updating globals...");
  Api.database_api().exec("get_dynamic_global_properties", []).then(response => {
    var query = 'UPDATE globals SET total_vesting_shares='+parseFloat(response.total_vesting_shares);
    query += ' ,total_vesting_fund_steem='+parseFloat(response.total_vesting_fund_steem);
    connection.query(query, function(err, result) {
      if (err) throw err;
    });
  })
  updatePrice();
  updateAccCount();
}

function addUsernamesFromWebsite() {
  var query = 'SELECT name FROM search';
  connection.query(query, function(err, rows, fields) {
    if (err) throw err;
    for (var i = 0; i < rows.length; i++) {
      toBeAdded.push(rows[i].name);
    }
    console.log('Adding '+rows.length+' accs to be added from website');
    var query = 'DELETE FROM search';
    connection.query(query, function(err) {
      if (err) throw err;
    });
  });
}

function addNewUsernames(cb) {
  var request = {limit:20};
  if (start_author && start_permlink)
    request = {limit: 20, start_author: start_author, start_permlink: start_permlink}
  Api.database_api().exec("get_discussions_by_hot", [request]).then(response => {
      for (var i = 0; i < response.length; i++) {
        addIfNew(response[i].author);
        for (var y = 0; y < response[i].active_votes.length; y++) {
          addIfNew(response[i].active_votes[y].voter);
        }
        if (i == response.length-1) {
          start_author = response[i].author;
          start_permlink = response[i].permlink;
        }
      }
      console.log('Now '+toBeAdded.length+' accs to be added');
      cb();
  })
}

function checkTenAccounts() {
  var manyAccs = [];
  while (manyAccs.length < 100 && toBeAdded.length > 0) {
    manyAccs.push(toBeAdded[0]);
    if (alreadyAdded.indexOf(toBeAdded[0]) == -1)
      alreadyAdded.push(toBeAdded[0]);
    toBeAdded.splice(0,1);
  }
  checkAccounts(manyAccs);
  console.log('Checking '+ manyAccs.length + ' accs');

  // updateDetailUser(toBeDetail[0]);
  // updateDetailUser(toBeDetail[0]);
  // updateDetailUser(toBeDetail[0]);
  // updateDetailUser(toBeDetail[0]);
}

function updateDetailUser(detailUser) {
  if (!detailUser) return;
  toBeDetail.splice(0,1);
  getFollowing(detailUser, null, function(following) {
    getFollowers(detailUser, null, function(followers) {
      console.log(detailUser, followers.length, following.length);
      var queryDetailUser = 'UPDATE accounts';
      queryDetailUser += ' SET moreUpdatedOn=NOW(), followers='+followers.length+',following='+following.length;
      queryDetailUser += ' WHERE name="'+detailUser+'"';
      connection.query(queryDetailUser, function(err, result) {
        if (err) throw err;
      });
    })
  })
}

function loadExistingAccs(cb) {
  var query = 'SELECT name FROM accounts';
  connection.query(query, function(err, rows, fields) {
    if (err) throw err;
    for (var i = 0; i < rows.length; i++) {
      alreadyAdded.push(rows[i].name);
    }
    cb();
  });
}

function addIfNew(username) {
  if (alreadyAdded.indexOf(username) == -1 && toBeAdded.indexOf(username) == -1) {
    toBeAdded.push(username);
  }
}

function checkAccounts(accounts) {
  Api.database_api().exec("get_accounts", [accounts]).then(response => {
    for (var i = 0; i < response.length; i++) {
      var acc = response[i];
      //if (acc.json_metadata)
        //console.log(acc.json_metadata)

      acc.balance = parseFloat(acc.balance);
      acc.sbd_balance = parseFloat(acc.sbd_balance);
      acc.vesting_shares = parseFloat(acc.vesting_shares);
      acc.reputation = parseInt(acc.reputation);
      acc.vesting_withdraw_rate = parseFloat(acc.vesting_withdraw_rate);
      var lastActive = new Date(acc.last_vote_time+'Z');
      var lastPost = new Date(acc.last_post+'Z');
      if (lastPost > lastActive) lastActive = lastPost;
      lastActive = lastActive.toISOString().substring(0,19);
      var query = 'INSERT INTO accounts (name, post_count, balance, sbd_balance, vesting_shares, posting_rewards, curation_rewards, reputation, next_vesting_withdrawal, vesting_withdraw_rate, last_active, json_metadata, updatedOn)'
      query += '  VALUES (\''+acc.name+'\','+acc.post_count+','+acc.balance+','+acc.sbd_balance+','+acc.vesting_shares+','+acc.posting_rewards+','+acc.curation_rewards+','+acc.reputation+',\''+acc.next_vesting_withdrawal+'\','+acc.vesting_withdraw_rate+',\''+lastActive+'\','+connection.escape(encode_utf8(acc.json_metadata))+',NOW())'
      query += '  ON DUPLICATE KEY'
      query += '  UPDATE post_count='+acc.post_count+',balance='+acc.balance+',sbd_balance='+acc.sbd_balance+',vesting_shares='+acc.vesting_shares+',posting_rewards='+acc.posting_rewards+',curation_rewards='+acc.curation_rewards+',reputation='+acc.reputation+',next_vesting_withdrawal=\''+acc.next_vesting_withdrawal+'\',vesting_withdraw_rate='+acc.vesting_withdraw_rate+',last_active=\''+lastActive+'\',json_metadata='+connection.escape(encode_utf8(acc.json_metadata))+',updatedOn=NOW()'
      console.log(query)
      connection.query(query, function(err, result) {
        if (err) throw err;
      });
    }
  })
}

function addFromRichList() {
  var fetchUrl = require("fetch").fetchUrl;
  fetchUrl("https://steemd.com/richlist", function(error, meta, body){
      var html = body.toString();
      while (html.indexOf('@') != -1) {
        html = html.substr(html.indexOf('@')+1);
        var nextUsername = html.substr(0,html.indexOf('\"'));
        addIfNew(nextUsername);
      }
  });
}

function saveDailyHistory() {
  var query = 'SELECT COUNT(*) as result FROM history WHERE date = CURDATE()';
  connection.query(query, function(err, rows, fields) {
    if (rows[0].result == 0) {
      console.log('UPDATING DAILY HISTORY !!');
      query = 'CALL dailyhistory()';
      connection.query(query, function(err, result) {
        if (err) throw err;
      });
    } else {
      console.log('Still same day')
    }
  });
}

// function getAccountVotes() {
//   Api.database_api().exec("get_account_votes", ['heimindanger', 'curator']).then(response => {
//     console.log(response);
//   })
// }


function getFollowers(username, followers, cb) {
  var start_follower = "";
  if (!followers) followers = [];
  if (followers.length > 0) start_follower = followers[followers.length-1];
  Api.follow_api().exec("get_followers", [username, start_follower, "blog", 100]).then(newfollow => {
    var orig_length = followers.length;
    for (var i = 0; i < newfollow.length; i++) {
      if (followers.indexOf(newfollow[i].follower) == -1) {
        followers.push(newfollow[i].follower);
      }
    }
    if (followers.length-98 > orig_length) {
      getFollowers(username, followers, function() {
        cb(followers);
      })
    } else {
      cb(followers);
    }
  })
}

function getFollowing(username, followers, cb) {
  var start_follower = "";
  if (!followers) followers = [];
  if (followers.length > 0) start_follower = followers[followers.length-1];
  Api.follow_api().exec("get_following", [username, start_follower, "blog", 100]).then(newfollow => {
    var orig_length = followers.length;
    for (var i = 0; i < newfollow.length; i++) {
      if (followers.indexOf(newfollow[i].following) == -1) {
        followers.push(newfollow[i].following);
      }
    }
    if (followers.length-98 > orig_length) {
      getFollowing(username, followers, function() {
        cb(followers);
      })
    } else {
      cb(followers);
    }
  })
}

function getPriceTicker(callback) {
  https.get({
      host: 'api.coinmarketcap.com',
      path: '/v1/ticker/steem/'
  }, function(response) {
      var body = '';
      response.on('data', function(d) {
          body += d;
      });
      response.on('end', function() {
          var parsed = JSON.parse(body);
          callback(parsed[0]);
      });
  });
}
function getPriceTickerSBD(callback) {
  https.get({
      host: 'api.coinmarketcap.com',
      path: '/v1/ticker/steem-dollars/'
  }, function(response) {
      var body = '';
      response.on('data', function(d) {
          body += d;
      });
      response.on('end', function() {
          var parsed = JSON.parse(body);
          callback(parsed[0]);
      });
  });
}

//connection.end();