WEB/src/Utility/EprosumeModel.php

<?php
namespace App\Utility;

use Cake\Datasource\ConnectionManager;
use \App\Utility\WalletEprosumeModel;

class EprosumeModel
{
    protected $connection = null;
    protected $conf = null;

    public function __construct()
    {
        $this->connection = ConnectionManager::get('default');
        $conf_rows = $this->connection->execute('select conf_key, conf_value from conf')->fetchAll(\PDO::FETCH_ASSOC);
        foreach($conf_rows as $v) {
            $this->conf[$v['conf_key']] = $v['conf_value'];
        }
    }

    public function blocksList($filters = [], $where = 'true', $values = [], $ascending=true)
    {
        $acceptedRequest = [
            'id' => ' AND id = :id ',
            'blocknumber' => ' AND blocknumber = :blocknumber ',
            'to' => ' AND wallet_to = :to ',
            'from' => ' AND wallet_from = :from ',
            'wallet' => ' AND ( wallet_to = :wallet or wallet_from = :wallet ) ',
            'meter' => ' AND wallet_from = :meter ',
            'date_from' => ' AND timestamp >= :date_from ',
            'date_to' => ' AND timestamp <= :date_to ',
            'asset' => ' AND asset = :asset ',
        ];

        $order = ' ORDER BY timestamp '.($ascending ? "ASC" : "DESC");

        foreach ($acceptedRequest as $request => $sqlrequest) {
            if (!empty($filters[$request])) {
                $where .= $sqlrequest;
                $values[$request] = $filters[$request];
            }
        }

        $selectItem = ' *, DATE_FORMAT(timestamp, "%Y-%m-%d %H:%i") AS timestamp ';
        $groupby = '';
        $getMemo = true;

        if (isset($filters['daily'])) {
            $selectItem = ' wallet_from,wallet_to,asset,sum(amount) AS amount,DATE(timestamp) AS timestamp ';
            $groupby = ' GROUP BY DATE(timestamp),asset,wallet_from,wallet_to ';
            $getMemo = false;
        } else if (isset($filters['monthly'])) {
            $selectItem = ' wallet_from,wallet_to,asset,sum(amount) AS amount,DATE(timestamp) AS timestamp ';
            $groupby = ' GROUP BY MONTH(timestamp),asset,wallet_from,wallet_to ';
            $getMemo = false;
        }

        $blocks = $this->connection->execute("SELECT $selectItem FROM blocks WHERE $where $groupby $order", $values)->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($blocks as $block) {
            $block['memo'] = $getMemo ? $this->memo($block['id']) : [];
            yield $block;
        }
    }

    public function memo($block_id)
    {
        $memo = [];
        if ($block_id > 0) {
            foreach ($this->connection->execute("SELECT * FROM memo WHERE block_id=:block_id", compact('block_id')) as $info) {
                $memo[$info['memo_key']] = $info['memo_value'];
            }
        }
        return $memo;
    }

    public function meterList($meter_id = null)
    {
        $meterList = [];
        $and = $meter_id ? " WHERE h.id = ".((int)$meter_id) : ""; 
        $sql = <<<SQL
SELECT h.id, h.prs AS PRS, h.tft AS TFT, h.timestamp, h.name, h.buy, h.sell, h.location FROM meter h
SQL;
        $sql.= $and;
        $data = ($this->connection->execute($sql))->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($data as $v) {
            $meterList[$v['name']] = [
                'meter' => $v['name'],
                'name' => $v['name'],
                'id' => $v['id'],
                'location' => $v['location'],
                'balance' => [
                    'PRS' => $v['PRS'],
                    'TFT' => $v['TFT'],
                    'timestamp' => $v['timestamp']
                ]
            ];
        }
        if(!empty($and)) {
            $result = null;
            foreach($meterList as $k=>$v) {
                $result = $meterList[$k];
                break;
            }
            $meterList = $result;
        }
        return $meterList;
    }

    public function balanceMeter($meter_id)
    {
        $sql = <<<SQL
select h.name, h.id, h.location, (select b.timestamp from balances b where b.meter_id = h.id order by b.timestamp desc limit 1) as timestamp, (select b.prs from balances b where b.meter_id = h.id order by b.timestamp desc limit 1) as PRS, (select b.tft from balances b where b.meter_id = h.id order by b.timestamp desc limit 1) as TFT from meter h where h.name = :meter_name;
SQL;
        return ($this->connection->execute($sql, [ "meter_name" => $meter_id ] ))->fetch(\PDO::FETCH_ASSOC);
    }

    public function balanceHistory($date_from, $date_to, $meter)
    {
        $sql = <<<SQL
SELECT h.id, h.subid, h.name, h.location, b.TFT, b.PRS, DATE_FORMAT(timestamp, "%%Y-%%m-%%d %%H:%%i") AS timestamp FROM meter h
LEFT JOIN balances b ON b.meter_id = h.id
WHERE 1
%s
AND h.status = 'ACTIVE'
AND b.timestamp BETWEEN :date_from AND :date_to
ORDER BY timestamp ASC
SQL;
        $values = [ 
            "date_from" => $date_from, 
            "date_to" => $date_to, 
        ];
        $meter && $values['meter'] = $meter;

        return ($this->connection->execute(
            sprintf($sql, ($meter ? "AND h.name = :meter" : ""))
            ,
            $values
        ))->fetchAll(\PDO::FETCH_ASSOC);        
    }

    public function blockID($block_id)
    {
        $this->blocksList(['id' => $block_id]);
    }

    public function actualPrice($meter_id = null)
    {
        $prices = [];
        $sql = <<<SQL
SELECT * FROM meter 
SQL;
        if($meter_id) {
            $sql.= " WHERE id = ".$meter_id;
        }
        $meters = $this->connection->execute($sql)->fetchAll(\PDO::FETCH_ASSOC);
        foreach($meters as $meter) {
            $prices[$meter['name']] = [
                'buyPrice' => $meter['buy'],
                'sellPrice' => $meter['sell'],
            ];
        }

        return $prices;
    }

    public function paymentsList($meter_id = null)
    {
        $where = 'true';
        $values = [];

        if ($meter_id) {
            $where .= ' AND ( wallet_from=:meter_id or wallet_to=:meter_id )';
            $values['meter_id'] = $meter_id;
        }

        $order = ' ORDER BY timestamp DESC ';

        $blocks = $this->connection->execute("SELECT b.* FROM blocks b LEFT JOIN memo on memo.block_id = b.id WHERE $where $order", $values);
        foreach ($blocks as $block) {
            yield $block;
        }
    }

    public function dataTableSSP($dataset, $user_meter_id, $draw, $columns, array $order, $start, $length, array $search = null, $meter = null, $txid=null, $from=null, $to=null, $period=null) {
        $filterString = "";
        $groupString = "";
        $params = [];
        $response = [
            "data" => [],
            "draw" => $draw,
            "recordsFiltered"=> 0,
            "recordsTotal"=> 0
        ];
        switch($dataset) {
            case 'history':
            case 'historyTransfer':
                if($period) {
                    if($period == 1) {
                        $timeString = "b.day";
                        $groupTimeString = "b.day";
                    } else {
                        $timeString = "CONCAT(b.yea, '-', b.mon)";
                        $groupTimeString = "b.yea, b.mon";
                    }
                    $selectString = <<<SQL
SELECT $timeString AS timestamp, 
null AS txID, 
b.wallet_from, b.wallet_to, b.asset, b.type,
SUM(b.amount+0) AS amount,
SUM(b.purchased_sold+0) AS energyDiff, 
NULL as mv
FROM blocks b
SQL;
                    $groupString = <<<SQL
GROUP BY $groupTimeString, b.wallet_from, b.wallet_to, b.asset, b.type
SQL;
                } else {
                    $derivedSelect = $dataset == "historyTransfer" ? "m3.memo_value AS mv" : "NULL as mv";
                    $selectString = <<<SQL
SELECT b.id, b.timestamp, b.wallet_from, b.wallet_to, b.asset, b.type,
b.tx_id AS txID,
b.amount,
b.purchased_sold AS energyDiff,
$derivedSelect
FROM blocks b
SQL;
                }
                $joinString = <<<SQL
SQL;
                $countString = <<<SQL
SELECT COUNT(*)
FROM blocks b
SQL;

                $clauseString = <<<SQL
WHERE (b.type = 'measure' OR b.type = 'payment')
SQL;

                $utility_account = $this->conf['utility_account'] ?? null;

                if($dataset == "historyTransfer") {
                    $clauseString = ' WHERE b.type = "transfer"';
                    $joinString = " LEFT JOIN memo m3 ON m3.block_id = b.id AND m3.memo_key = 'Memo'";
                }

                if($txid) {
                    $filterString.= <<<SQL
AND b.tx_id = :txid
SQL;
                    $params["txid"] = $txid;
                }
                break;
            case 'payment':
                if($period) {
                    if($period == 1) {
                        $timeString = "b.day";
                        $groupTimeString = "b.day";
                    } else {
                        $timeString = "CONCAT(b.yea, '-', b.mon)";
                        $groupTimeString = "b.yea, b.mon";
                    }
                    $selectString = <<<SQL
SELECT $timeString AS timestamp, b.wallet_from, b.wallet_to, 
SUM(b.amount+0) AS amount, b.asset
FROM blocks b
SQL;
                    $groupString = <<<SQL
GROUP BY $groupTimeString, b.wallet_from, b.wallet_to, b.asset
SQL;
                } else {
                    $selectString = <<<SQL
SELECT b.id, b.timestamp, b.wallet_from, b.wallet_to, b.amount, b.asset FROM blocks b
SQL;
                }
                $joinString = <<<SQL
SQL;
                $countString = <<<SQL
SELECT COUNT(b.id) FROM blocks b
SQL;
                $clauseString = <<<SQL
WHERE b.asset = "PRS" and b.type = "payment"
SQL;
                break;
            case 'priceHistory':
                $selectString = <<<SQL
SELECT b.id AS id, b.timestamp, b.wallet_from, b.wallet_to, b.buy_price AS buyPrice, b.sell_price AS sellPrice FROM blocks b
SQL;
                $joinString = <<<SQL
SQL;

                $clauseString = <<<SQL
WHERE 1
AND b.asset = "TFT"
AND b.type = "update_price"
SQL;

                $countString = <<<SQL
SELECT COUNT(t1.id) AS id FROM ($selectString $joinString $clauseString) AS t1 WHERE 1 
SQL;
                $countStringAlone = true;

                break;
            default:
                return $response;
        }
        if($user_meter_id) {
            $user_meter_id_clause = <<<SQL

AND (b.wallet_from = '$user_meter_id' OR b.wallet_to = '$user_meter_id')
SQL;

            $clauseString.= $user_meter_id_clause;
        }
        if($meter) {
            $filterString.= <<<SQL

AND (wallet_from = :meter OR wallet_to = :meter)
SQL;
            $params["meter"] = $meter;
        }
        if(!$txid) {
            if($from && $to) {
                $params["from"] = $from;
                $params["to"] = $to;
                $filterString.= <<<SQL
AND timestamp BETWEEN :from AND :to
SQL;
            } else {
                /*
                $now = new \DateTime();
                $params["to"] = $now->format('Y-m-d H:i:s');
                $now->sub(new \DateInterval('P30D'));
                $params["from"] = $now->format('Y-m-d H:i:s');
                */
            }            
        }
        $orderString = empty($order)
            ? ""
            : "ORDER BY ".
            (
                function($order, $columns) {
                    return implode(
                        ", ", 
                        array_filter(
                            array_map(
                                function($v) use($columns) {
                                    return isset($columns[$v["column"]]["data"])
                                        ? sprintf("`%s` %s", $columns[$v["column"]]["data"], $v["dir"])
                                        : null
                                        ;
                                },
                                $order
                            )
                        )
                    );
                }
            )
            ($order, $columns)
        ;
        $limitString = "";
        $limitString.= is_null($length)  ? "" : sprintf("LIMIT %d", $length);
        $limitString.= is_null($start) ? "" : sprintf(" OFFSET %d", $start);
        $recordsQuery = implode(PHP_EOL, [
            $selectString,
            $joinString,
            $clauseString,
            ($filterString ?? null),
            $groupString,
            $orderString,
            $limitString
        ]);
        $records = $this->connection->execute($recordsQuery, $params);
        $response["data"] = (function($records) {
            foreach($records as $key => $record) {
                array_walk(
                    $record,
                    function(&$v,$k) use(&$record) {
                        if(!is_string($k)) {
                            unset($record[$k]);
                        }
                    }
                );
                yield $record; 
                
            }
        })($records);
        if(isset($countStringAlone)) {
            $queryComposition = [
                $countString
            ];
        } else {
            $queryComposition = [
                $countString,
                $joinString,
                $clauseString
            ];    
        }
        /*
        if(empty($groupString)) {
            $recordsTotalQuery = implode(PHP_EOL, $queryComposition);
            $recordsTotalResults = $this->connection->execute($recordsTotalQuery)->fetch();
            isset($recordsTotalResults[0]) && $response["recordsTotal"] = $recordsTotalResults[0];
        } else {
            $queryComposition[] = $groupString;
            $recordsTotalQuery = implode(PHP_EOL, $queryComposition);
            $recordsTotalResults = $this->connection->execute($recordsTotalQuery)->fetchAll();
            $response["recordsTotal"] = count($recordsTotalResults);
            array_pop($queryComposition);
        }
        */
        if($filterString) {
            $queryComposition[] = $filterString;
        }
        if(empty($groupString)) {
            $recordsFilteredQuery = implode(PHP_EOL, $queryComposition);
            $recordsFilteredResults = $this->connection->execute($recordsFilteredQuery, $params)->fetch();
            isset($recordsFilteredResults[0]) && $response["recordsFiltered"] = $recordsFilteredResults[0];
        } else {
            $queryComposition[] = $groupString;
            $recordsFilteredQuery = implode(PHP_EOL, $queryComposition);
            $recordsFilteredResults = $this->connection->execute($recordsFilteredQuery, $params)->fetchAll();
            $response["recordsFiltered"] = count($recordsFilteredResults);
        }
        $response["recordsTotal"] = $response["recordsFiltered"];
        return $response;
    }

    public function getAssetsType() {
        $assetsType = [
            "PRS",
            "TFT",
        ];

        return $assetsType;
    }

    public function getUtilityAccount() {
        return $this->conf['utility_account'];
    }

    public function charts($type, $meter_name, $from, $to, $period) {
        $sql = null;
        switch($type) {
            case 'payments':
                break;
        }
        if($period > 0) {
            $clause = '';
            $amount = '(SUM(amount))';
            $groupby = ' GROUP BY mon';
            if($period == 1) {
                $groupby.= ', day';
            }
        } else if($from && $to) {
            $amount = '(amount)';
            $clause = " AND timestamp BETWEEN '$from' AND '$to'";
            $groupby = '';
        } else {
            $clause = null;
            $groupby = null;
        }
        if($clause == null && $groupby == null) {
            return [];
        }
        $sql = <<<SQL
SELECT timestamp, CONCAT('[',UNIX_TIMESTAMP(timestamp), ',', $amount, ']') AS data FROM blocks WHERE (wallet_from = :meter_name AND wallet_to = :utility_account) AND type = 'payment' $clause $groupby
UNION
SELECT timestamp, CONCAT('[',UNIX_TIMESTAMP(timestamp), ',', ($amount*-1), ']') AS data FROM blocks WHERE (wallet_from = :utility_account AND wallet_to = :meter_name) AND type = 'payment' $clause $groupby
ORDER BY timestamp ASC
SQL;
        $values = [
            'meter_name' => $meter_name,
            'utility_account' => $this->conf['utility_account']
        ];
        if($sql) {
            $data = [];
            $rows = ($this->connection->execute($sql,$values))->fetchAll(\PDO::FETCH_ASSOC);
            foreach($rows as $row) {
                $data[] = json_decode($row['data']);
            }
            return $data;
        } else {
            return [];
        }
    }

    public function setPrice($meter_name, $buyPrice, $sellPrice) {
            $sql = <<<SQL
UPDATE meter SET buy = :buyPrice, sell = :sellPrice WHERE name = :meter_name; 
SQL;
            $values = [
                'meter_name' => $meter_name,
                'buyPrice' => $buyPrice,
                'sellPrice' => $sellPrice
            ];
            return $this->connection->execute($sql,$values);
        }
}