<?php
/**
 * @copyright (c) Kufer Software GmbH, Loisachstrasse 4, D-84453 Mühldorf
 */

/**
 * Generated by PHPUnit_SkeletonGenerator on 2018-07-10 at 08:07:04.
 */
class SqlBuilderTest extends \PHPUnit\Framework\TestCase
{
  /**
   * @var \SQLSnippetBuilder
   */
  protected $object;

  /**
   * Sets up the fixture, for example, opens a network connection.
   * This method is called before a test is executed.
   */
  protected function setUp(): void
  {
    $dboperation = new DBOperationMySQLi(array(), 1);
    $this->object = new SQLSnippetBuilder($dboperation);
  }

  protected function setUpMSSQL()
  {
    $dboperation = new DBOperationMssqlSqlsrv(array(), 1);
    $this->object = new SQLSnippetBuilder($dboperation);
  }

  protected function setUpOracle()
  {
    $dboperation = new DBOperationOracle(array(), 1);
    $this->object = new SQLSnippetBuilder($dboperation);
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_NoStringAdded_ReturnEmptyString()
  {
    $this->assertEquals('', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_OneStringAdded_ReturnString()
  {
    $this->object->add('test 1 2 3');
    $this->assertEquals('test 1 2 3 ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ManyStringsAdded_ReturnString()
  {
    $this->object->add('test 1 2 3');
    $this->object->add('4 5 6');
    $this->assertEquals('test 1 2 3 4 5 6 ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ParamNotFound_ReturnString()
  {
    $this->object->add('Select :param:');
    $this->object->paramInt('notFound', 0);
    $this->assertEquals('Select :param: ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ParamFoundOnce_ReplaceParam()
  {
    $this->object->add('SELECT :param:');
    $this->object->paramInt('param', 0);
    $this->assertEquals('SELECT 0 ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ParamFoundMultipleTimes_ReplaceParams()
  {
    $this->object->add('SELECT :param: FROM :param: WHERE :param:');
    $this->object->paramInt('param', 0);
    $this->assertEquals('SELECT 0 FROM 0 WHERE 0 ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_MultipleParams_ReplaceParams()
  {
    $this->object->add('SELECT :select: FROM :from: WHERE :where:');
    $this->object->paramInt('select', 0);
    $this->object->paramInt('from', 1);
    $this->object->paramInt('where', 2);
    $this->assertEquals('SELECT 0 FROM 1 WHERE 2 ', $this->object->getSQL());
  }

  /**
   * Funktioniert nur im MySQL Betrieb!
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ParamsString_EscapeParamAndPutInQuotes()
  {
    $this->object->add('SELECT :select: FROM :from: WHERE :where:');
    $this->object->paramString('select', 'test123');
    $this->object->paramString('from', "te'st");
    $this->object->paramString('where', "te\\'st"); // \\' enspricht \'
    $this->assertEquals("SELECT 'test123' FROM 'te\\'st' WHERE 'te\\\\\\'st' ", $this->object->getSQL());
    // \\' entspricht \' und \\\\\\' entspricht \\\'
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_MultipleParamsDate_ReplaceParams()
  {
    $this->object->add('WHERE :date_from: = :date_to:');
    $this->object->paramDate('date_from', '19.04.1952');
    $this->object->paramDate('date_to', '01.11.2099');
    $this->assertEquals('WHERE \'1952-04-19\' = \'2099-11-01\' ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ArrayInts_ReplaceParams()
  {
    $this->object->add('WHERE xyz IN (:inlist:)');
    $this->object->paramIntArray('inlist', array(1, 2, 3, 4, 5));
    $this->assertEquals('WHERE xyz IN (1, 2, 3, 4, 5) ', $this->object->getSql());
  }

  /**
   * Funktioniert nur im MySQL Betrieb!
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ArrayStrings_ReplaceParams()
  {
    $this->object->add('WHERE xyz IN (:inlist:)');
    $this->object->paramStringArray('inlist', array('1', '2', '3', '4', '5'));
    $this->assertEquals("WHERE xyz IN ('1', '2', '3', '4', '5') ", $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_PrefixParam_ReplaceParams()
  {
    $this->object->add('WHERE :prefix:xyz = :prefix:test');
    $this->object->paramPrefix('prefix', 'pre');
    $this->assertEquals('WHERE pre.xyz = pre.test ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_PrefixParamWihtDot_AddPrefixWithOneDot()
  {
    $this->object->add('WHERE :prefix:xyz = :prefix:test');
    $this->object->paramPrefix('prefix', 'pre.');
    $this->assertEquals('WHERE pre.xyz = pre.test ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_IntParamWithString_ThrowException()
  {
    $this->expectException(Exception::class);
    $this->object->add('SELECT id FROM :tablename:');
    $this->object->paramInt('tablename', 'Test');
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_IntParamWithNumberString_ReturnString()
  {
    $this->object->add('SELECT :id: FROM Test');
    $this->object->paramInt('id', '123');
    $this->assertEquals('SELECT 123 FROM Test ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ifnullVariable_MysqlString()
  {
    $this->object->add('SELECT :ifnull(\'test\', 123):');
    $this->assertEquals('SELECT ifnull(\'test\', 123) ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ifnullVariable_MssqlString()
  {
    $this->setUpMSSQL();
    $this->object->add('SELECT :ifnull(\'test\', 123):');
    $this->assertEquals('SELECT isnull(\'test\', 123) ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_ifnullVariable_OracleString()
  {
    $this->setUpOracle();
    $this->object->add('SELECT :ifnull(\'test\', 123):');
    $this->assertEquals('SELECT nvl(\'test\', 123) ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_defaultdateVariable_MysqlString()
  {
    $this->object->add('SELECT :defaultdate:');
    $this->assertEquals('SELECT \'1899-12-30\' ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_defaultdateVariable_MssqlString()
  {
    $this->setUpMSSQL();
    $this->object->add('SELECT :defaultdate:');
    $this->assertEquals('SELECT \'30.12.1899\' ', $this->object->getSQL());
  }

  /**
   * @covers SqlBuilder::getSql
   * @test
   */
  public function getSql_defaultdateVariable_OracleString()
  {
    $this->setUpOracle();
    $this->object->add('SELECT :defaultdate:');
    $this->assertEquals('SELECT TO_DATE(\'30.12.1899\',\'dd.mm.yyyy\')  ', $this->object->getSQL());
  }
}
