1:   2:   3:   4:   5:   6:   7:   8:   9:  10:  11:  12:  13:  14:  15:  16:  17:  18:  19:  20:  21:  22:  23:  24:  25:  26:  27:  28:  29:  30:  31:  32:  33:  34:  35:  36:  37:  38:  39:  40:  41:  42:  43:  44:  45:  46:  47:  48:  49:  50:  51:  52:  53:  54:  55:  56:  57:  58:  59:  60:  61:  62:  63:  64:  65:  66:  67:  68:  69:  70:  71:  72:  73:  74:  75:  76:  77:  78:  79:  80:  81:  82:  83:  84:  85:  86:  87:  88:  89:  90:  91:  92:  93:  94:  95:  96:  97:  98:  99: 100: 101: 102: 103: 104: 105: 106: 107: 108: 109: 110: 111: 112: 113: 114: 115: 116: 117: 118: 119: 120: 121: 122: 123: 124: 125: 126: 127: 128: 129: 130: 131: 132: 133: 134: 135: 136: 137: 138: 139: 140: 141: 142: 143: 144: 145: 146: 147: 148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162: 163: 164: 165: 166: 167: 

<?php

/**
 * MvcCore
 *
 * This source file is subject to the BSD 3 License
 * For the full copyright and license information, please view
 * the LICENSE.md file that are distributed with this source code.
 *
 * @copyright   Copyright (c) 2016 Tom Flidr (https://github.com/mvccore)
 * @license  https://mvccore.github.io/docs/mvccore/5.0.0/LICENCE.md
 */

namespace MvcCore\Ext\Models\Db\Providers\Resources;

trait Manipulation {
    
    /**
     * Execute SQL code to insert new database table row in transaction, in default database isolation.
     * @param int|string $connNameOrIndex   Connection name or index in system config.
     * @param string $tableName             Database table name.
     * @param array $dataColumns            Data to use in insert clause, keys are 
     *                                      column names, values are column values.
     * @param string $className             model class full name.
     * @param string $autoIncrColumnName    Auto increment column name.
     * @return array                        First item is boolean result, 
     *                                      second is affected rows count. 
     */
    public function Insert ($connNameOrIndex, $tableName, $dataColumns, $className, $autoIncrColumnName) {
        $sqlItems = [];
        $params = [];
        $index = 0;
        $conn = self::GetConnection($connNameOrIndex);

        foreach ($dataColumns as $dataColumnName => $dataColumnValue) {
            $sqlItems[] = $conn->QuoteName($dataColumnName);
            $params[":p{$index}"] = $dataColumnValue;
            $index++;
        }
        
        $tableName = $conn->QuoteName($tableName);
        $sql = "INSERT INTO {$tableName} (" 
            . implode(", ", $sqlItems) 
            . ") VALUES (" 
            . implode(", ", array_keys($params)) 
            . ");";

        $success = FALSE;
        $error = NULL;

        $transName = 'INSERT:'.str_replace('\\', '_', $className);
        try {
            $conn->BeginTransaction(16, $transName); // 16 means read write

            $reader = $conn
                ->Prepare($sql)
                ->Execute($params);

            $success = $reader->GetExecResult();
            $affectedRows = $reader->GetRowsCount();

            $newId = $conn->LastInsertId($tableName);

            $conn->Commit();

            $success = TRUE;
        } catch (\Exception $e) { // backward compatibility
            $affectedRows = 0;
            $newId = NULL;
            $error = $e;
            if ($conn && $conn->InTransaction())
                $conn->RollBack();
        } catch (\Throwable $e) {
            $affectedRows = 0;
            $newId = NULL;
            $error = $e;
            if ($conn && $conn->InTransaction())
                $conn->RollBack();
        }

        return [
            $success, 
            $affectedRows,
            $newId,
            $error
        ];
    }

    /**
     * Execute SQL code to update database table row by key columns.
     * @param int|string $connNameOrIndex   Connection name or index in system config.
     * @param string $tableName             Database table name.
     * @param array $keyColumns             Data to use in where condition, keys are 
     *                                      column names, values are column values.
     * @param array $dataColumns            Data to use in update set clause, keys are 
     *                                      column names, values are column values.
     * @return array                        First item is boolean result, 
     *                                      second is affected rows count. 
     */
    public function Update ($connNameOrIndex, $tableName, $keyColumns, $dataColumns) {
        $setSqlItems = [];
        $whereSqlItems = [];
        $params = [];
        $index = 0;
        $conn = self::GetConnection($connNameOrIndex);

        foreach ($dataColumns as $dataColumnName => $dataColumnValue) {
            $setSqlItems[] = $conn->QuoteName($dataColumnName) . " = :p{$index}";
            $params[":p{$index}"] = $dataColumnValue;
            $index++;
        }
        foreach ($keyColumns as $keyColumnName => $keyColumnValue) {
            $whereSqlItems[] = $conn->QuoteName($keyColumnName) . " = :p{$index}";
            $params[":p{$index}"] = $keyColumnValue;
            $index++;
        }

        $tableName = $conn->QuoteName($tableName);
        $sql = "UPDATE {$tableName}"
            . " SET " . implode(", ", $setSqlItems)
            . " WHERE " . implode(" AND ", $whereSqlItems) . ";";
        
        $reader = self::GetConnection($connNameOrIndex)
            ->Prepare($sql)
            ->Execute($params);

        return [
            $reader->GetExecResult(), 
            $reader->GetRowsCount()
        ];
    }

    /**
     * Execute SQL code to remove database table row.
     * @param int|string $connNameOrIndex   Connection name or index in system config.
     * @param string $tableName             Database table name.
     * @param array $keyColumns             Data to use in where condition, keys are 
     *                                      column names, values are column values.
     * @return array                        First item is boolean result, 
     *                                      second is affected rows count. 
     */
    public function Delete ($connNameOrIndex, $tableName, $keyColumns) {
        $sqlItems = [];
        $params = [];
        $index = 0;
        $conn = self::GetConnection($connNameOrIndex);

        foreach ($keyColumns as $keyColumnName => $keyColumnValue) {
            $sqlItems[] = $conn->QuoteName($keyColumnName) . " = :p{$index}";
            $params[":p{$index}"] = $keyColumnValue;
            $index++;
        }

        $tableName = $conn->QuoteName($tableName);
        $sql = "DELETE FROM {$tableName} "
            . "WHERE " . implode(" AND ", $sqlItems) . ";";

        $reader = $conn
            ->Prepare($sql)
            ->Execute($params);

        return [
            $reader->GetExecResult(), 
            $reader->GetRowsCount()
        ];
    }
}