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


class DBWorkVisitenkartenWeb extends \DBWork
{
  public function __construct($pDBTyp = 1, $pNewInstance = false)
  {
    parent::__construct($pDBTyp, $pNewInstance);
    $this->select = 'wv.id, wv.tnr, wv.anrede, wv.name, wv.vorname, wv.firma, wv.firma2, wv.firma_url, wv.strasse, wv.plz, wv.ort, wv.nation,'
            . 'wv.bundesland, wv.telefon, wv.email, wv.aktiv';
  }

  
  public function getNewObject()
  {
    return new \VisitenkartenWeb();
  }

  public function getTableName(): string
  {
    return 'web_visitenkarten';
  }

  
  public function getCurrentRow($pRefreshCache = false)
  {
    $tmp = $this->getNewObject();
    $tmp->id = $this->getDatamodulValue('id');
    $tmp->tnr = $this->getDatamodulValue('tnr');
    $tmp->anrede = $this->getDatamodulValue('anrede');
    $tmp->name = $this->getDatamodulValue('name');
    $tmp->vorname = $this->getDatamodulValue('vorname');
    $tmp->firma = $this->getDatamodulValue('firma');
    $tmp->firma2 = $this->getDatamodulValue('firma2');
    $tmp->firma_url = $this->getDatamodulValue('firma_url');
    $tmp->strasse = $this->getDatamodulValue('strasse');
    $tmp->plz = $this->getDatamodulValue('plz');
    $tmp->ort = $this->getDatamodulValue('ort');
    $tmp->nation = $this->getDatamodulValue('nation');
    $tmp->bundesland = $this->getDatamodulValue('bundesland');
    $tmp->telefon = $this->getDatamodulValue('telefon');
    $tmp->email = $this->getDatamodulValue('email');
    $tmp->aktiv = $this->getDatamodulValue('aktiv');
    $dbwFachgVk = new \DBWorkFachgebieteVisitenkartenWeb($this->dbtyp, true);
    $tmp->fachgebiete = $dbwFachgVk->getFachgebieteByVkId($tmp->id);

    return $tmp;
  }

  public function canupdate($varname)
  {
    switch ($varname) {
      default: return parent::canupdate($varname);
      case 'fachgebiete':
      case 'alle':
      case 'namevorname':
        return false;
    }
  }

  
  public function getVisitenkarteById($pId)
  {
    $id = $this->dboperation->getInt4Sql($pId);
    $sql = 'SELECT ' . $this->select . ' FROM web_visitenkarten wv ' . 'WHERE wv.id = ' . $id;
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  
  public function getVisitenkartenByIds($pIds)
  {
    if (empty($pIds)) {
      return array();
    }
    $ids = array_map(function($id) { return $this->dboperation->getInt4Sql($id); }, $pIds);
    $sql = 'SELECT ' . $this->select . ' FROM web_visitenkarten wv '
            . 'WHERE wv.id IN (' . implode(',', $ids) . ')';
    $sql .= $this->getCompleteOrderByDefault('id');
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  
  public function getVisitenkarteByIdAndTnr($pId, $pTnr)
  {
    $id = $this->dboperation->getInt4Sql($pId);
    $tnr = $this->dboperation->getInt4Sql($pTnr);
    $sql = 'SELECT ' . $this->select . ' FROM web_visitenkarten wv ' . 'WHERE wv.id = ' . $id . ' AND wv.tnr = ' . $tnr;
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getFirstRow();
  }

  
  public function getVisitenkartenByTnr($pTnr, $pNurAktive = true)
  {
    $tnr = $this->dboperation->getInt4Sql($pTnr);
    $dateNow = \DateTimeOperations::getDateNow();
    $dateParts = explode('.', $dateNow);

    $sql = '';
    if ($pNurAktive) {
      $sql = 'SELECT ' . $this->select . ' FROM web_visitenkarten wv ' . 'WHERE tnr = ' . $tnr . ' '
              . 'AND wv.aktiv = \'W\' AND EXISTS (SELECT teilnehm.nr FROM teilnehm WHERE teilnehm.nr=wv.tnr AND teilnehm.mitgl_bis >= '
              . $this->datetimeop->dtopserver->getFormatDate4Server($dateParts[0], $dateParts[1], $dateParts[2]) . ')';
    } elseif (!$pNurAktive) {
      $sql = 'SELECT ' . $this->select . ' FROM web_visitenkarten wv ' . 'WHERE tnr = ' . $tnr . ' ';
    }
    $sql .= $this->getCompleteOrderByDefault('id');
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRows();
  }

  public function getNations()
  {
    $sql = 'SELECT DISTINCT wv.nation FROM web_visitenkarten wv ';
    $sql .= $this->getCompleteOrderByDefault('wv.nation');
    $this->dboperation->clear();
    $this->dboperation->query($sql);

    return $this->getAllRowsSingleColumn('nation');
  }

  
  public function getVisitenKarten4UebersichtLimit($pStartVkId, $pLimit, $pForward = true)
  {
    $dateNow = \DateTimeOperations::getDateNow();
    $dateParts = explode('.', $dateNow);

    
    
    $sql = 'SELECT DISTINCT wv.id, wv.plz, wv.name '
            . 'FROM web_visitenkarten wv '
            . 'INNER JOIN teilnehm t ON wv.tnr = t.nr '
            . 'LEFT JOIN web_fachg_visitenkart wf ON wv.id = wf.web_visitenkarte_id '
            . 'LEFT JOIN hlsachgebiete hls ON wf.sachg = hls.sachg '
            . 'WHERE wv.aktiv = \'W\' AND t.mitgl_bis >= '
            . $this->datetimeop->dtopserver->getFormatDate4Server($dateParts[0], $dateParts[1], $dateParts[2]);

    $sql .= $this->getSearchSQLVisitenkarten('wv.', 'hls.');
    $sql .= $this->getCompleteOrderByDefault('id');

    $this->dboperation->clear();
    $this->dboperation->query($sql);

    $this->_buildSeitenIndexAry($this->dboperation->rowcount(), $pLimit);

    if (isset($pStartVkId) && $pStartVkId != '') {
      $tmp = $this->getLimitRows($pLimit, $pForward, 'id', $pStartVkId);
    } else {
      $tmp = $this->getLimitRows($pLimit, $pForward);
    }
    if (count($tmp) === 0) {
      return array();
    }

    $ids = array();
    foreach ($tmp as $visitenkarte) {
      $ids[] = $visitenkarte->id;
    }

    $dbwVk = new \DBWorkVisitenkartenWeb(1, true);
    $dbwVk->orderby = $this->orderby;
    $dbwVk->orderby2 = $this->orderby2;

    return $dbwVk->getVisitenkartenByIds($ids);
  }

  
  public function insertVisitenkarte($pVisitenkarte, $pDontProtocol)
  {
    $pVisitenkarte->id = $this->insertRecord('web_visitenkarten', 'id', 'name');
    $this->saveChanging('web_visitenkarten', '', '', $pVisitenkarte->id, 'id', 'I', true);
    $orgVisitenkarte = $this->getVisitenkarteById($pVisitenkarte->id);
    $this->updateObject($pVisitenkarte, $orgVisitenkarte, 'web_visitenkarten', 'id', 'A', $pDontProtocol, true);
    $this->setActionIDs();

    return $pVisitenkarte->id;
  }

  
  public function updateVisitenkarte($pVisitenkarte, $pDontProtocol = false)
  {
    $orgVisitenkarte = $this->getVisitenkarteById($pVisitenkarte->id);
    $this->updateObject($pVisitenkarte, $orgVisitenkarte, 'web_visitenkarten', 'id', 'U', $pDontProtocol, true);
    $this->setActionIDs();
  }

  
  public function deleteVisitenkarte($pId, $pDontProtocol = false)
  {
    $vkrtId = $this->dboperation->getInt4Sql($pId);
    $this->dboperation->clear();
    $this->dboperation->execute('DELETE FROM web_visitenkarten WHERE id=' . $vkrtId);
    if (!$pDontProtocol) {
      $this->saveChanging('web_visitenkarten', '', '', "'" . $vkrtId . "'", '', 'D');
    }

    $this->setActionIDs();
  }

  
  public function getSearchSQLVisitenkarten($pPraefix, $pPraefixHLSachgebiete)
  {
    $praefix = $this->dboperation->getString4Sql($pPraefix);
    $praefixHLSachgebiete = $this->dboperation->getString4Sql($pPraefixHLSachgebiete);
    $sql = '';
    if (isset($this->fromobject)) {
      
      if (trim($this->fromobject->namevorname) != '') {
        $sql .= ' AND (' . $this->_getSearchSQLNameVorname($praefix, $this->fromobject->namevorname) . ')';
      }
      
      if (trim($this->fromobject->ort) != '') {
        $sql .= ' AND ' . $this->_getSearchSQLPart($praefix, 'ort', $this->fromobject->ort) . " \n";
      }
      
      if (trim($this->fromobject->plz) != '') {
        $sql .= ' AND ' . $this->_getSearchSQLPart($praefix, 'plz', $this->fromobject->plz, false) . " \n";
      }
      
      if (trim($this->fromobject->nation) != '') {
        $sql .= ' AND ' . $this->_getSearchSQLPart($praefix, 'nation', $this->fromobject->nation) . " \n";
      }
      
      if (!empty($this->fromobject->fachgebiete) && $this->fromobject->fachgebiete[0] !== '') {
        $sql .= ' AND (0=1 ';
        foreach ($this->fromobject->fachgebiete as $fachgebiet) {
          $sql .= ' OR ' . $pPraefixHLSachgebiete . 'sachg = ' . $fachgebiet;
        }
        $sql .= ')';
      }
      
      if (trim($this->fromobject->alle) != '') {
        $vars = array_keys(get_object_vars($this->fromobject));
        $restrictedVars = array('id', 'tnr', 'anrede', 'nation', 'aktiv', 'alle');
        $sql .= ' AND (0=1 ';
        foreach ($vars as $var) {
          if (in_array($var, $restrictedVars)) {
            continue;
          }
          if ($var === 'fachgebiete') {
            $sql .= ' OR ' . $this->_getSearchSQLFachgebiet($praefixHLSachgebiete, $this->fromobject->alle);
          } elseif ($var === 'namevorname') {
            $sql .= ' OR ' . $this->_getSearchSQLNameVorname($praefix, $this->fromobject->alle);
          } else {
            $sql .= ' OR ' . $this->_getSearchSQLPart($praefix, $var, $this->fromobject->alle) . " \n";
          }
        }
        $sql .= ')';
      }
    }

    return $sql;
  }

  
  private function _getSearchSQLPart($pPraefix, $pColumnName, $pSearchedValue, $pWildCardBefore = true, $pWildCardAfter = true, $pIsArray = false)
  {
    if ($pIsArray) {
      $result = ' (';
    } else {
      $result = $this->dboperation->dbparser->caseInsensitiveLike($pPraefix, $pColumnName, ($pWildCardBefore ? '%' : '') . $pSearchedValue . ($pWildCardAfter ? '%' : ''));
    }

    return $result;
  }

  
  private function _getSearchSQLNameVorname($pPraefix, $pSearchedValue)
  {
    $queryNameVorname = str_replace(' ', '%', $pSearchedValue);

    return $this->dboperation->dbparser->concat(array("'%'", 'LOWER(' . $pPraefix . 'vorname)', "'%'", 'LOWER(' . $pPraefix . 'name)', "'%'")) . " LIKE LOWER('%" . $queryNameVorname . "%')"
            . ' OR ' . $this->dboperation->dbparser->concat(array("'%'", 'LOWER(' . $pPraefix . 'name)', "'%'", 'LOWER(' . $pPraefix . 'vorname)', "'%'")) . " LIKE LOWER('%" . $queryNameVorname . "%')";
  }

  
  private function _getSearchSQLFachgebiet($pPraefix, $pSearchedValue)
  {
    return $this->_getSearchSQLPart($pPraefix, 'text', $pSearchedValue) . " \n";
  }
}