def payouts_to_address(self, address):
resultset = self._cursor.execute_and_fetchall("""
SELECT DISTINCT
trs."{transactions[id]}",
trs."{transactions[amount]}",
trs."{transactions[timestamp]}",
trs."{transactions[recipient_id]}",
trs."{transactions[sender_id]}",
trs."{transactions[type]}",
trs."{transactions[fee]}",
mem."{mem_accounts[username]}",
ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex'),
blocks."{blocks[height]}",
trs."{transactions[vendor_field]}"
FROM {mem_accounts[table]} mem
INNER JOIN {transactions[table]} trs
ON
(trs."{transactions[sender_id]}"=mem."{mem_accounts[address]}")
INNER JOIN {blocks[table]} blocks
ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}")
WHERE trs."{transactions[recipient_id]}" = '{address}'
AND mem."{mem_accounts[is_delegate]}" = 1
ORDER BY blocks."{blocks[height]}" ASC
""".format(transactions=self.scheme['transactions'],
mem_accounts=self.scheme['mem_accounts'],
address=address,
blocks=self.scheme['blocks']))
labelset = [
'tx_id', 'amount', 'timestamp', 'recipient_id', 'sender_id',
'type', 'fee', 'username', 'public_key', 'height', 'vendor_field'
]
return dictionify(resultset, labelset)
def transactions_from_address(self, address):
resultset = self._cursor.execute_and_fetchall("""
SELECT
trs."{transactions[id]}" AS tx_id,
trs."{transactions[amount]}",
trs."{transactions[timestamp]}",
trs."{transactions[recipient_id]}",
trs."{transactions[sender_id]}",
trs."{transactions[type]}",
trs."{transactions[fee]}",
trs."{transactions[rawasset]}",
blocks."{blocks[height]}",
trs."{transactions[vendor_field]}"
FROM
{transactions[table]} AS trs
INNER JOIN {blocks[table]} AS blocks
ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}")
WHERE
trs."{transactions[sender_id]}" = '{address}'
OR
trs."{transactions[recipient_id]}" = '{address}'
ORDER BY
blocks."{blocks[height]}" ASC
""".format(transactions=self.scheme['transactions'],
address=address,
blocks=self.scheme['blocks']))
labelset = [
'tx_id', 'amount', 'timestamp', 'recipient_id', 'sender_id',
'type', 'fee', 'rawasset', 'height', 'vendor_field'
]
return dictionify(resultset, labelset)
def get_historic_voters(self, address):
delegate_public_key = self.account_details(
address=address)['public_key']
plusvote = '{{"votes":["+{0}"]}}'.format(delegate_public_key)
resultset = self._cursor.execute_and_fetchall("""
SELECT
trs."{transactions[sender_id]}",
trs."{transactions[timestamp]}",
trs."{transactions[id]}",
blocks."{blocks[height]}"
FROM
{transactions[table]} AS trs
INNER JOIN {blocks[table]} AS blocks
ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}")
WHERE
trs."{transactions[rawasset]}" = '{plusvote}'
ORDER BY {blocks[table]}."{blocks[height]}" ASC;
""".format(transactions=self.scheme['transactions'],
mem_accounts=self.scheme['mem_accounts'],
address=address,
plusvote=plusvote,
blocks=self.scheme['blocks']))
labelset = ['address', 'timestamp', 'id', 'height']
return dictionify(resultset, labelset)
def get_last_out_transactions(self, address):
resultset = self._cursor.execute_and_fetchall("""
SELECT
trs."{transactions[recipient_id]}",
trs."{transactions[timestamp]}",
blocks."{blocks[height]}",
trs."{transactions[id]}",
trs."{transactions[amount]}",
trs."{transactions[fee]}"
FROM {transactions[table]} trs
INNER JOIN {blocks[table]} blocks
ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}"),
(SELECT
MAX(ts."{transactions[timestamp]}") AS max_timestamp,
ts."{transactions[recipient_id]}"
FROM {transactions[table]} AS ts
WHERE ts."{transactions[sender_id]}" = '{address}'
GROUP BY ts."{transactions[recipient_id]}") AS maxresults
WHERE trs."recipientId" = maxresults."recipientId"
AND trs."timestamp"= maxresults.max_timestamp;
""".format(transactions=self.scheme['transactions'],
address=address,
blocks=self.scheme['blocks']))
labelset = [
'recipient_id', 'timestamp', 'height', 'tx_id', 'amount', 'fee'
]
return dictionify(resultset, labelset)
def all_votes_by_address(self, address):
resultset = self._cursor.execute_and_fetchall("""
SELECT
trs."{transactions[timestamp]}",
votes."{votes[votes]}",
mem."{mem_accounts[username]}",
mem."{mem_accounts[address]}",
ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex'),
blocks."{blocks[height]}"
FROM {transactions[table]} AS trs
INNER JOIN {blocks[table]} blocks
ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}")
INNER JOIN {votes[table]} AS votes
ON (trs."{transactions[id]}" = votes."{votes[transaction_id]}")
INNER JOIN {mem_accounts[table]} mem
ON (TRIM(LEADING '+-' FROM votes."{votes[votes]}") = ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex'))
WHERE trs."{transactions[sender_id]}" = '{address}'
ORDER BY blocks."{blocks[height]}" ASC;
""".format(transactions=self.scheme['transactions'],
votes=self.scheme['votes'],
mem_accounts=self.scheme['mem_accounts'],
address=address,
blocks=self.scheme['blocks']))
labelset = [
'timestamp', 'vote', 'username', 'address', 'public_key', 'height'
]
return dictionify(resultset, labelset)
def current_delegates(self):
resultset = self._cursor.execute_and_fetchall("""
SELECT mem."{mem_accounts[username]}", mem."{mem_accounts[is_delegate]}",
mem."{mem_accounts[second_signature]}", mem."{mem_accounts[address]}",
ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex'), ENCODE(mem."{mem_accounts[second_public_key]}"::BYTEA, 'hex'),
mem."{mem_accounts[balance]}", mem."{mem_accounts[vote]}",
mem."{mem_accounts[rate]}", mem."{mem_accounts[multi_signatures]}"
FROM {mem_accounts[table]} AS mem
WHERE mem."{mem_accounts[is_delegate]}" = 1
ORDER BY mem."{mem_accounts[vote]}" DESC
LIMIT {num_delegates}
""".format(mem_accounts=self.scheme['mem_accounts'],
num_delegates=self.num_delegates))
labelset = [
'username',
'is_delegate',
'second_signature',
'address',
'public_key',
'second_public_key',
'balance',
'vote',
'rate',
'multisignatures',
]
return dictionify(resultset, labelset)
def node_height_details(self):
resultset = self._cursor.execute_and_fetchone("""
SELECT blocks."{blocks[id]}", blocks."{blocks[timestamp]}",
blocks."{blocks[height]}", ENCODE(blocks."{blocks[generator_public_key]}"::BYTEA, 'hex')
FROM {blocks[table]} AS blocks
ORDER BY blocks."{blocks[height]}" DESC
LIMIT 1;
""".format(blocks=self.scheme['blocks']))
labelset = ['block_id', 'timestamp', 'height', 'generator_public_key']
return dictionify(resultset, labelset, single=True)
def account_details(self, address):
resultset = self._cursor.execute_and_fetchone("""
SELECT mem."{mem_accounts[address]}", mem."{mem_accounts[username]}",
mem."{mem_accounts[is_delegate]}", mem."{mem_accounts[second_signature]}",
ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex'), ENCODE(mem."{mem_accounts[second_public_key]}"::BYTEA, 'hex'),
mem."{mem_accounts[balance]}", mem."{mem_accounts[vote]}",
mem."{mem_accounts[rate]}", mem."{mem_accounts[multi_signatures]}"
FROM {mem_accounts[table]} as mem
WHERE mem."{mem_accounts[address]}" = '{address}';
""".format(mem_accounts=self.scheme['mem_accounts'], address=address))
labelset = [
'address', 'username', 'is_delegate', 'second_signature',
'public_key', 'second_public_key', 'balance', 'vote', 'rate',
'multisignatures'
]
return dictionify(
resultset,
labelset,
single=True,
)
def get_current_voters(self, address):
resultset = self._cursor.execute_and_fetchall("""
SELECT
trs."{transactions[sender_id]}",
trs."{transactions[timestamp]}",
trs."{transactions[id]}",
blocks."{blocks[height]}" AS block,
trs."{transactions[id]}"
FROM
{transactions[table]} AS trs
INNER JOIN {blocks[table]} blocks
ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}")
WHERE
trs."{transactions[type]}" = 3
AND
trs."{transactions[sender_id]}" IN (
SELECT mem."{mem_accounts2delegates[account_id]}"
FROM
{mem_accounts2delegates[table]} AS mem
WHERE
mem."{mem_accounts2delegates[dependent_id]}" = (
SELECT ENCODE(mem2."{mem_accounts[public_key]}"::BYTEA, 'hex')
FROM {mem_accounts[table]} AS mem2
WHERE mem2."{mem_accounts[address]}" = '{address}'
))
ORDER BY block DESC
""".format(
transactions=self.scheme['transactions'],
blocks=self.scheme['blocks'],
mem_accounts2delegates=self.scheme['mem_accounts2delegates'],
mem_accounts=self.scheme['mem_accounts'],
address=address))
labelset = ['address', 'timestamp', 'id', 'height']
return dictionify(resultset, labelset)
def get_blocks(self, delegate_address):
resultset = self._cursor.execute_and_fetchall("""
SELECT
blocks."{blocks[height]}",
blocks."{blocks[timestamp]}",
blocks."{blocks[id]}",
blocks."{blocks[total_fee]}",
blocks."{blocks[reward]}"
FROM
{blocks[table]} AS blocks
WHERE
blocks."{blocks[generator_public_key]}" = (
SELECT mem."{mem_accounts[public_key]}"
FROM {mem_accounts[table]} AS mem
WHERE mem."{mem_accounts[address]}" = '{address}')
ORDER BY
blocks."{blocks[height]}" ASC
""".format(blocks=self.scheme['blocks'],
mem_accounts=self.scheme['mem_accounts'],
address=delegate_address))
labelset = ['height', 'timestamp', 'id', 'total_fee', 'reward']
return dictionify(resultset, labelset)