1: <?php
   2: 
   3: /**
   4:  *
   5:  * @package Plugin
   6:  * @subpackage FormAssistant
   7:  * @author Marcus Gnaß <marcus.gnass@4fb.de>
   8:  * @copyright four for business AG
   9:  * @link http://www.4fb.de
  10:  */
  11: 
  12: // assert CONTENIDO framework
  13: defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');
  14: 
  15: /**
  16:  * PIFA form item collection class.
  17:  * It's a kind of a model.
  18:  *
  19:  * @author Marcus Gnaß <marcus.gnass@4fb.de>
  20:  */
  21: class PifaFormCollection extends ItemCollection {
  22: 
  23:     /**
  24:      * Create an instance.
  25:      *
  26:      * @param mixed $where clause to be used to load items or false
  27:      */
  28:     public function __construct($where = false) {
  29:         $cfg = cRegistry::getConfig();
  30:         parent::__construct(cRegistry::getDbTableName('pifa_form'), 'idform');
  31:         $this->_setItemClass('PifaForm');
  32:         if (false !== $where) {
  33:             $this->select($where);
  34:         }
  35:     }
  36: 
  37:     /**
  38:      * Get forms according to given params.
  39:      *
  40:      * @param int $client
  41:      * @param int $lang
  42:      * @throws PifaException if forms could not be read
  43:      * @return PifaFormCollection
  44:      */
  45:     private static function _getBy($client, $lang) {
  46: 
  47:         // conditions to be used for reading items
  48:         $conditions = array();
  49: 
  50:         // consider $client
  51:         $client = cSecurity::toInteger($client);
  52:         if (0 < $client) {
  53:             $conditions[] = 'idclient=' . $client;
  54:         }
  55: 
  56:         // consider $lang
  57:         $lang = cSecurity::toInteger($lang);
  58:         if (0 < $lang) {
  59:             $conditions[] = 'idlang=' . $lang;
  60:         }
  61: 
  62:         // get items
  63:         $forms = new PifaFormCollection();
  64:         $succ = $forms->select(implode(' AND ', $conditions));
  65: 
  66:         // throw exception if forms coud not be read
  67:         // Its not a good idea to throw an exception in this case,
  68:         // cause this would lead to an error message if no forms
  69:         // were created yet.
  70:         // if (false === $succ) {
  71:         // throw new PifaException('forms could not be read');
  72:         // }
  73:         // better return false in this case
  74:         if (false === $succ) {
  75:             return false;
  76:         }
  77: 
  78:         return $forms;
  79:     }
  80: 
  81:     /**
  82:      * Get forms of given client in any language.
  83:      *
  84:      * @param int $client
  85:      * @throws PifaException if $client is not greater 0
  86:      * @throws PifaException if forms could not be read
  87:      * @return PifaFormCollection
  88:      */
  89:     public static function getByClient($client) {
  90:         if (0 >= cSecurity::toInteger($client)) {
  91:             $msg = Pifa::i18n('MISSING_CLIENT');
  92:             throw new PifaException($msg);
  93:         }
  94: 
  95:         return self::_getBy($client, 0);
  96:     }
  97: 
  98:     /**
  99:      * Get forms of any client in given language.
 100:      *
 101:      * @param int $lang
 102:      * @throws PifaException if $lang is not greater 0
 103:      * @throws PifaException if forms could not be read
 104:      * @return PifaFormCollection
 105:      */
 106:     public static function getByLang($lang) {
 107:         if (0 >= cSecurity::toInteger($lang)) {
 108:             $msg = Pifa::i18n('MISSING_LANG');
 109:             throw new PifaException($msg);
 110:         }
 111: 
 112:         return self::_getBy(0, $lang);
 113:     }
 114: 
 115:     /**
 116:      * Get forms of given client in given language.
 117:      *
 118:      * @param int $client
 119:      * @param int $lang
 120:      * @throws PifaException if $client is not greater 0
 121:      * @throws PifaException if $lang is not greater 0
 122:      * @throws PifaException if forms could not be read
 123:      * @return PifaFormCollection
 124:      */
 125:     public static function getByClientAndLang($client, $lang) {
 126:         if (0 >= cSecurity::toInteger($client)) {
 127:             $msg = Pifa::i18n('MISSING_CLIENT');
 128:             throw new PifaException($msg);
 129:         }
 130: 
 131:         if (0 >= cSecurity::toInteger($lang)) {
 132:             $msg = Pifa::i18n('MISSING_LANG');
 133:             throw new PifaException($msg);
 134:         }
 135: 
 136:         return self::_getBy($client, $lang);
 137:     }
 138: 
 139: }
 140: 
 141: /**
 142:  * PIFA form item class.
 143:  * It's a kind of a model.
 144:  *
 145:  * @author Marcus Gnaß <marcus.gnass@4fb.de>
 146:  */
 147: class PifaForm extends Item {
 148: 
 149:     /**
 150:      * aggregated collection of this form fields
 151:      *
 152:      * @var array
 153:      */
 154:     private $_fields = NULL;
 155: 
 156:     /**
 157:      * array of errors with field names as keys and error messages as values
 158:      *
 159:      * @var array
 160:      */
 161:     private $_errors = array();
 162: 
 163:     /**
 164:      *
 165:      * @var int lastInsertedId
 166:      */
 167:     private $_lastInsertedId = NULL;
 168: 
 169:     /**
 170:      * Create an instance.
 171:      *
 172:      * @param mixed $id ID of item to be loaded or false
 173:      */
 174:     public function __construct($id = false) {
 175:         $cfg = cRegistry::getConfig();
 176:         parent::__construct(cRegistry::getDbTableName('pifa_form'), 'idform');
 177:         $this->setFilters(array(), array());
 178:         if (false !== $id) {
 179:             $this->loadByPrimaryKey($id);
 180:         }
 181:     }
 182: 
 183:     /**
 184:      *
 185:      * @return array
 186:      */
 187:     public function getErrors() {
 188:         return $this->_errors;
 189:     }
 190: 
 191:     /**
 192:      *
 193:      * @param array $_errors
 194:      */
 195:     public function setErrors($_errors) {
 196:         $this->_errors = $_errors;
 197:     }
 198: 
 199:     /**
 200:      * Read this forms fields from database and aggregate them.
 201:      */
 202:     public function loadFields() {
 203:         $col = new PifaFieldCollection();
 204:         $col->setWhere('PifaFieldCollection.idform', $this->get('idform'));
 205:         $col->setOrder('PifaFieldCollection.field_rank');
 206:         $col->query();
 207:         $this->_fields = array();
 208:         while (false !== $pifaField = $col->next()) {
 209:             $this->columnNames[] = $pifaField->get('column_name');
 210:             $this->_fields[] = clone $pifaField;
 211:         }
 212:     }
 213: 
 214:     /**
 215:      * Returns aggregated list of PIFA fields.
 216:      * If no fields are aggregated, this forms fields are read from database and
 217:      * aggregated.
 218:      *
 219:      * @return array:PifaField
 220:      */
 221:     public function getFields() {
 222:         if (NULL === $this->_fields) {
 223:             $this->loadFields();
 224:         }
 225: 
 226:         return $this->_fields;
 227:     }
 228: 
 229:     /**
 230:      *
 231:      * @return int
 232:      */
 233:     public function getLastInsertedId() {
 234:         return $this->_lastInsertedId;
 235:     }
 236: 
 237:     /**
 238:      *
 239:      * @param int $_lastInsertedId
 240:      */
 241:     public function setLastInsertedId($_lastInsertedId) {
 242:         $this->_lastInsertedId = $_lastInsertedId;
 243:     }
 244: 
 245:     /**
 246:      * Returns an array containing current values of all fields of this form
 247:      * where the fields column name is used as key.
 248:      *
 249:      * @return array
 250:      */
 251:     public function getValues() {
 252:         $values = array();
 253:         foreach ($this->getFields() as $pifaField) {
 254:             // ommit fields which are not stored in database
 255:             try {
 256:                 $isStored = NULL !== $pifaField->getDbDataType();
 257:             } catch (PifaException $e) {
 258:                 $isStored = false;
 259:             }
 260:             if (false === $isStored) {
 261:                 continue;
 262:             }
 263:             $values[$pifaField->get('column_name')] = $pifaField->getValue();
 264:         }
 265: 
 266:         return $values;
 267:     }
 268: 
 269:     /**
 270:      * Sets values for this form fields.
 271:      *
 272:      * The given data array is searched for keys corresponding to this form
 273:      * field names. Other values are omitted. This method is meant to be called
 274:      * with the $_GET or $_POST superglobal variables. Validation is performed
 275:      * according to the specifications defined for aech form field.
 276:      *
 277:      * @param array $data
 278:      * @param bool $clear if missing values should be interpreted as NULL
 279:      */
 280:     public function setValues(array $values = NULL, $clear = false) {
 281:         if (NULL === $values) {
 282:             return;
 283:         }
 284: 
 285:         foreach ($this->getFields() as $pifaField) {
 286:             $columnName = $pifaField->get('column_name');
 287:             if (array_key_exists($columnName, $values)) {
 288:                 $value = $values[$columnName];
 289:                 $pifaField->setValue($value);
 290:             } else if (true === $clear) {
 291:                 $pifaField->setValue(NULL);
 292:             }
 293:         }
 294:     }
 295: 
 296:     /**
 297:      * Returns an array containing uploaded files of all fields of this form
 298:      * where the fields column name is used as key.
 299:      *
 300:      * @return array:mixed
 301:      */
 302:     public function getFiles() {
 303:         $files = array();
 304:         foreach ($this->getFields() as $pifaField) {
 305:             // ommit fields that are not an INPUTFILE
 306:             if (PifaField::INPUTFILE !== cSecurity::toInteger($pifaField->get('field_type'))) {
 307:                 continue;
 308:             }
 309:             $files[$pifaField->get('column_name')] = $pifaField->getFile();
 310:         }
 311: 
 312:         return $files;
 313:     }
 314: 
 315:     /**
 316:      * Sets uploaded file(s) for appropriate form fields.
 317:      *
 318:      * @param array $files super global files array
 319:      */
 320:     public function setFiles(array $files = NULL) {
 321:         if (NULL === $files) {
 322:             return;
 323:         }
 324: 
 325:         foreach ($this->getFields() as $pifaField) {
 326:             // ommit fields that are not an INPUTFILE
 327:             if (PifaField::INPUTFILE !== cSecurity::toInteger($pifaField->get('field_type'))) {
 328:                 continue;
 329:             }
 330:             $columnName = $pifaField->get('column_name');
 331:             if (array_key_exists($columnName, $files)) {
 332:                 $file = $files[$columnName];
 333:                 $pifaField->setFile($file);
 334:                 // store original name of uploaded file as value!
 335:                 $pifaField->setValue($file['name']);
 336:             }
 337:         }
 338:     }
 339: 
 340:     /**
 341:      * Getter for protected prop.
 342:      */
 343:     public function getLastError() {
 344:         return $this->lasterror;
 345:     }
 346: 
 347:     /**
 348:      */
 349:     public function fromForm() {
 350: 
 351:         // get data from source depending on method
 352:         switch (cString::toUpperCase($this->get('method'))) {
 353:             case 'GET':
 354:                 $this->setValues($_GET);
 355:                 break;
 356:             case 'POST':
 357:                 $this->setValues($_POST);
 358:                 if (isset($_FILES)) {
 359:                     $this->setFiles($_FILES);
 360:                 }
 361:                 break;
 362:         }
 363:     }
 364: 
 365:     /**
 366:      * Returns HTML for this form that should be displayed in frontend.
 367:      *
 368:      * @param array $opt to determine form attributes
 369:      * @return string
 370:      */
 371:     public function toHtml(array $opt = NULL) {
 372: 
 373:         // get form attribute values
 374:         $opt = array_merge(array(
 375:             // or whatever
 376:             'name' => 'pifa-form',
 377:             'action' => 'main.php',
 378:             'method' => $this->get('method'),
 379:             'class' => 'pifa-form jqtransform'
 380:         ), $opt);
 381:         $idform = $this->get('idform');
 382:         $headline = '';
 383:         if(isset($opt['headline']) && cString::getStringLength($opt['headline']) > 0) {
 384:             $headline = '<h1 class="pifa-headline">' . $opt['headline'] . '</h1>';
 385:         }
 386: 
 387:         // build form
 388:         $htmlForm = new cHTMLForm($opt['name'], $opt['action'], $opt['method'], $opt['class']);
 389: 
 390:         // set ID (workaround: remove ID first!)
 391:         $htmlForm->removeAttribute('id')->setID('pifa-form-' . $idform);
 392: 
 393:         // add hidden input field with idform in order to be able to distinguish
 394:         // several forms on a single page when one of them is submitted
 395:         $htmlForm->appendContent("<input type=\"hidden\" name=\"idform\" value=\"$idform\">");
 396: 
 397:         // add fields
 398:         foreach ($this->getFields() as $pifaField) {
 399:             // enable file upload
 400:             if (PifaField::INPUTFILE === cSecurity::toInteger($pifaField->get('field_type'))) {
 401:                 $htmlForm->setAttribute('enctype', 'multipart/form-data');
 402:             }
 403:             $errors = $this->getErrors();
 404:             $htmlField = $pifaField->toHtml($errors);
 405:             if (NULL !== $htmlField) {
 406:                 $htmlForm->appendContent($htmlField);
 407:             }
 408:         }
 409:         $htmlForm->appendContent("\n");
 410: 
 411:         return $headline . $htmlForm->render();
 412:     }
 413: 
 414:     /**
 415:      * Loops all fields and checks their value for being obligatory
 416:      * and conforming to the fields rule.
 417:      *
 418:      * @throws PifaValidationException if at least one field was invalid
 419:      */
 420:     public function validate() {
 421: 
 422:         // validate all fields
 423:         $errors = array();
 424:         foreach ($this->getFields() as $pifaField) {
 425:             try {
 426:                 $pifaField->validate();
 427:             } catch (PifaValidationException $e) {
 428:                 // $errors = array_merge($errors, $e->getErrors());
 429:                 foreach ($e->getErrors() as $idfield => $error) {
 430:                     $errors[$idfield] = $error;
 431:                 }
 432:             }
 433:         }
 434: 
 435:         // if some fields were invalid
 436:         if (0 < count($errors)) {
 437:             // throw a single PifaValidationException with infos for all invalid
 438:             // fields
 439:             throw new PifaValidationException($errors);
 440:         }
 441:     }
 442: 
 443:     /**
 444:      * Stores the loaded and modified item to the database.
 445:      *
 446:      * In contrast to its parent method this store() method returns true even if
 447:      * there were no modiifed values and thus no statement was executed. This
 448:      * helps in handling database errors.
 449:      *
 450:      * @todo Check if method store() should be implemented for PifaField too.
 451:      * @return bool
 452:      */
 453:     public function store() {
 454:         if (is_null($this->modifiedValues)) {
 455:             return true;
 456:         } else {
 457:             return parent::store();
 458:         }
 459:     }
 460: 
 461:     /**
 462:      * Stores values of each field of this form in defined data table.
 463:      * For fields of type INPUT_FILE the uploaded file is stored in the
 464:      * FileSystem (in $cfg['path']['contenido_cache'] . 'form_assistant/').
 465:      *
 466:      * @throws PifaDatabaseException if values could not be stored
 467:      */
 468:     public function storeData() {
 469:         $cfg = cRegistry::getConfig();
 470: 
 471:         // get values for all defined fields
 472:         $values = $this->getValues();
 473: 
 474:         // make arrays of values storable
 475:         foreach ($values as $column => $value) {
 476:             if (is_array($value)) {
 477:                 $values[$column] = implode(',', $value);
 478:             }
 479:         }
 480: 
 481:         // get DB
 482:         $db = cRegistry::getDb();
 483: 
 484:         // build insert statement
 485:         $sql = $db->buildInsert($this->get('data_table'), $values);
 486: 
 487:         if (NULL === $db->connect()) {
 488:             $msg = Pifa::i18n('DATABASE_CONNECT_ERROR');
 489:             throw new PifaDatabaseException($msg);
 490:         }
 491:         if (0 === cString::getStringLength(trim($sql))) {
 492:             $msg = Pifa::i18n('SQL_BUILD_ERROR');
 493:             throw new PifaDatabaseException($msg);
 494:         }
 495: 
 496:         // insert new row
 497:         if (false === $db->query($sql)) {
 498:             $msg = Pifa::i18n('VALUE_STORE_ERROR');
 499:             throw new PifaDatabaseException($msg);
 500:         }
 501: 
 502:         // get last insert id
 503:         $lastInsertedId = $db->getLastInsertedId();
 504: 
 505:         $this->setLastInsertedId($lastInsertedId);
 506: 
 507:         // store files
 508:         $files = $this->getFiles();
 509:         foreach ($this->getFiles() as $column => $file) {
 510:             if (!is_array($file)) {
 511:                 continue;
 512:             }
 513:             $tmpName = $file['tmp_name'];
 514:             // if no file was submitted tmp_name is an empty string
 515:             if (0 === cString::getStringLength($tmpName)) {
 516:                 continue;
 517:             }
 518:             $destPath = $cfg['path']['contenido_cache'] . 'form_assistant/';
 519:             // CON-1566 create folder (create() checks if it exists!)
 520:             if (!cDirHandler::create($destPath)) {
 521:                 $msg = Pifa::i18n('FOLDER_CREATE_ERROR');
 522:                 throw new PifaException($msg);
 523:             }
 524:             $destName = $this->get('data_table') . '_' . $lastInsertedId . '_' . $column;
 525:             $destName = preg_replace('/[^a-z0-9_]+/i', '_', $destName);
 526:             if (false === move_uploaded_file($tmpName, $destPath . $destName)) {
 527:                 $msg = Pifa::i18n('FILE_STORE_ERROR');
 528:                 throw new PifaException($msg);
 529:             }
 530:         }
 531:     }
 532: 
 533:     /**
 534:      *
 535:      * @param array $opt
 536:      */
 537:     public function toMailRecipient(array $opt) {
 538:         if (0 == cString::getStringLength(trim($opt['from']))) {
 539:             $msg = Pifa::i18n('MISSING_SENDER_ADDRESS');
 540:             throw new PifaMailException($msg);
 541:         }
 542:         if (0 == cString::getStringLength(trim($opt['fromName']))) {
 543:             $msg = Pifa::i18n('MISSING_SENDER_NAME');
 544:             throw new PifaMailException($msg);
 545:         }
 546:         if (0 == cString::getStringLength(trim($opt['to']))) {
 547:             $msg = Pifa::i18n('MISSING_RECIPIENT_ADDRESS');
 548:             throw new PifaMailException($msg);
 549:         }
 550:         if (0 == cString::getStringLength(trim($opt['subject']))) {
 551:             $msg = Pifa::i18n('MISSING_SUBJECT');
 552:             throw new PifaMailException($msg);
 553:         }
 554:         if (0 == cString::getStringLength(trim($opt['body']))) {
 555:             $msg = Pifa::i18n('MISSING_EMAIL_BODY');
 556:             throw new PifaMailException($msg);
 557:         }
 558: 
 559:         // cMailer
 560: 
 561:         try {
 562:             $mailer = new cMailer();
 563:             $message = Swift_Message::newInstance($opt['subject'], $opt['body'], 'text/plain', $opt['charSet']);
 564: 
 565:             // add attachments by names
 566:             if (array_key_exists('attachmentNames', $opt)) {
 567:                 if (is_array($opt['attachmentNames'])) {
 568:                     $values = $this->getValues();
 569:                     foreach ($opt['attachmentNames'] as $column => $path) {
 570:                         if (!file_exists($path)) {
 571:                             continue;
 572:                         }
 573:                         $attachment = Swift_Attachment::fromPath($path);
 574:                         $filename = $values[$column];
 575:                         $attachment->setFilename($filename);
 576:                         $message->attach($attachment);
 577:                     }
 578:                 }
 579:             }
 580: 
 581:             // add attachments by string
 582:             if (array_key_exists('attachmentStrings', $opt)) {
 583:                 if (is_array($opt['attachmentStrings'])) {
 584:                     foreach ($opt['attachmentStrings'] as $filename => $string) {
 585:                         // TODO mime type should be configurale
 586:                         $attachment = Swift_Attachment::newInstance($string, $filename, 'text/csv');
 587:                         $message->attach($attachment);
 588:                     }
 589:                 }
 590:             }
 591: 
 592:             // add sender
 593:             $message->addFrom($opt['from'], $opt['fromName']);
 594: 
 595:             // add recipient
 596:             $to = explode(',', $opt['to']);
 597:             $message->setTo(array_combine($to, $to));
 598:         } catch (Exception $e) {
 599:             throw new PifaException($e->getMessage());
 600:         }
 601:         // send mail
 602:         if (!$mailer->send($message)) {
 603:             $msg = mi18n("PIFA_MAIL_ERROR_SUFFIX");
 604:             throw new PifaMailException($msg);
 605:         }
 606:     }
 607: 
 608:     /**
 609:      * Returns an array containing this forms stored data.
 610:      *
 611:      * @throws PifaException if form is not loaded
 612:      * @throws PifaException if table does not exist
 613:      * @return array
 614:      */
 615:     public function getData() {
 616:         if (!$this->isLoaded()) {
 617:             $msg = Pifa::i18n('FORM_LOAD_ERROR');
 618:             throw new PifaException($msg);
 619:         }
 620: 
 621:         $db = cRegistry::getDb();
 622: 
 623:         // get table name and check if it exists
 624:         $tableName = $this->get('data_table');
 625:         if (!$this->existsTable($tableName, false)) {
 626:             $msg = Pifa::i18n('MISSING_TABLE_ERROR');
 627:             throw new PifaException($msg);
 628:         }
 629: 
 630:         // build SQL
 631:         $sql = "-- PifaForm->getData()
 632:             SELECT
 633:                 *
 634:             FROM
 635:                 `$tableName`
 636:             ;";
 637: 
 638:         if (false === $db->query($sql)) {
 639:             return array();
 640:         }
 641: 
 642:         if (0 === $db->numRows()) {
 643:             return array();
 644:         }
 645: 
 646:         $data = array();
 647:         while ($db->nextRecord()) {
 648:             $data[] = $db->toArray();
 649:         }
 650: 
 651:         return $data;
 652:     }
 653: 
 654:     /**
 655:      * Echoes a CSV file containing all of this forms stored data.
 656:      * Thatfor proper headers are sent, that add the created file as attachment
 657:      * for easier download.
 658:      *
 659:      * @param string $optionally
 660:      * @throws PifaException if form is not loaded
 661:      * @throws PifaException if table does not exist
 662:      */
 663:     public function getDataAsCsv($optionally = 'OPTIONALLY') {
 664:         $cfg = cRegistry::getConfig();
 665: 
 666:         if (in_array($cfg['db']['connection']['host'], array(
 667:             '127.0.0.1',
 668:             'localhost'
 669:         ))) {
 670:             // This solution is cool, but won't work, due to the fact that in
 671:             // our database server is not the web server.
 672:             // $out = $this->_getCsvFromLocalDatabaseServer();
 673: 
 674:             // there seems to be a problem using _getCsvFromLocalDatabaseServer
 675:             // so _getCsvFromRemoteDatabaseServer is used in every case
 676:             $out = $this->_getCsvFromRemoteDatabaseServer();
 677:         } else {
 678:             $out = $this->_getCsvFromRemoteDatabaseServer();
 679:         }
 680: 
 681:         // return payload
 682:         return $out;
 683:     }
 684: 
 685:     /**
 686:      *
 687:      * @param string $optionally
 688:      * @throws PifaException if form is not loaded
 689:      * @throws PifaException if table does not exist
 690:      */
 691:     private function _getCsvFromLocalDatabaseServer($optionally = 'OPTIONALLY') {
 692: 
 693:         // assert form is loaded
 694:         if (!$this->isLoaded()) {
 695:             $msg = Pifa::i18n('FORM_LOAD_ERROR');
 696:             throw new PifaException($msg);
 697:         }
 698: 
 699:         // get table name and check if it exists
 700:         $tableName = $this->get('data_table');
 701:         if (!$this->existsTable($tableName, false)) {
 702:             $msg = Pifa::i18n('MISSING_TABLE_ERROR');
 703:             throw new PifaException($msg);
 704:         }
 705: 
 706:         // assert $optionally to be either 'OPTIONALLY' or ''
 707:         if ('OPTIONALLY' !== $optionally) {
 708:             $optionally = '';
 709:         }
 710: 
 711:         // create temp file
 712:         $cfg = cRegistry::getConfig();
 713:         $filename = tempnam($cfg['path']['contenido_cache'], 'PIFA_');
 714:         unlink($filename);
 715: 
 716:         // build SQL
 717:         $sql = "-- PifaForm->_getCsvFromLocalDatabaseServer()
 718:             SELECT
 719:                 *
 720:             INTO OUTFILE
 721:                 '$filename'
 722:             FIELDS TERMINATED BY
 723:                 ','
 724:             $optionally ENCLOSED BY
 725:                 '\"'
 726:             ESCAPED BY
 727:                 '\\\\'
 728:             LINES TERMINATED BY
 729:                 '\\n'
 730:             FROM
 731:                 `$tableName`
 732:             ;";
 733: 
 734:         // execute SQL
 735:         cRegistry::getDb()->query($sql);
 736: 
 737:         // get content
 738:         $out = cFileHandler::read($filename);
 739: 
 740:         // delete temp file
 741:         unlink($filename);
 742: 
 743:         return $out;
 744:     }
 745: 
 746:     /**
 747:      * TODO use fputcsv()
 748:      *
 749:      * @throws PifaException if form is not loaded
 750:      * @throws PifaException if table does not exist
 751:      */
 752:     private function _getCsvFromRemoteDatabaseServer() {
 753: 
 754:         // get column names in correct order
 755:         $columns = array();
 756:         // always append the records ID
 757:         array_push($columns, 'id');
 758:         // append the records timestamp if defined for form
 759:         if (true === (bool) $this->get('with_timestamp')) {
 760:             array_push($columns, 'pifa_timestamp');
 761:         }
 762:         foreach ($this->getFields() as $index => $pifaField) {
 763:             // CON-2169 filter empty values
 764:             if (cString::getStringLength(trim($pifaField->get('column_name'))) > 0) {
 765:                 $columns[] = $pifaField->get('column_name');
 766:             }
 767:         }
 768: 
 769:         $out = '';
 770: 
 771:         // add header row
 772:         foreach ($columns as $index => $columnName) {
 773:             if (0 < $index) {
 774:                 $out .= ';';
 775:             }
 776:             $out .= $columnName;
 777:         }
 778: 
 779:         function pifa_form_get_literal_line_endings($value) {
 780:             $value = str_replace("\n", '\n', $value);
 781:             $value = str_replace("\r", '\r', $value);
 782:             $value = "\"$value\"";
 783:             return $value;
 784:         }
 785: 
 786:         // add data rows
 787:         foreach ($this->getData() as $row) {
 788:             // replace \n & \r by it's literal representation
 789:             $row = array_map('pifa_form_get_literal_line_endings', $row);
 790:             // append value
 791:             foreach ($columns as $index => $columnName) {
 792:                 $out .= 0 === $index? "\n" : ';';
 793:                 $out .= $row[$columnName];
 794:             }
 795:         }
 796: 
 797:         return $out;
 798:     }
 799: 
 800:     /**
 801:      * This method returns the current data as CSV file.
 802:      * This file usually contains two rows, one header and one value line.
 803:      * If $oneRowPerField is set to true the CSV-file is mirrored so that each
 804:      * line contains the fields header and then its value.
 805:      * An assoc array of $additionalFields can be given which will be appended
 806:      * to the current values of this form.
 807:      * (CON-1648)The CSV is created using a temporary file in the systems (not
 808:      * CONTENIDOs) TEMP folder.
 809:      *
 810:      * @param bool $oneRowPerField
 811:      * @param array $additionalFields
 812:      * @return string
 813:      */
 814:     public function getCsv($oneRowPerField = false, array $additionalFields = NULL) {
 815: 
 816:         // get values to be converted into CSV
 817:         $data = $this->getValues();
 818: 
 819:         // add additional fields if given
 820:         if (NULL !== $additionalFields) {
 821:             $data = array_merge($data, $additionalFields);
 822:         }
 823: 
 824:         // initializing toCsv variable (CON-2051)
 825:         $toCsv = '';
 826: 
 827:         // convert array values to CSV values
 828:         $data = array_map(function($in) {
 829:             return implode(',', $in);;
 830:         }, $data);
 831: 
 832:         // optionally rearrange/mirror array
 833:         if (!$oneRowPerField) {
 834:             $data = array(
 835:                 array_keys($data),
 836:                 array_values($data)
 837:             );
 838:         }
 839: 
 840:         // == create CSV (CON-1648)
 841:         $csv = '';
 842:         // write all lines of data as CSV into tmp file
 843:         $total = 0;
 844:         if (false !== $tmpfile = tmpfile()) {
 845:             foreach ($data as $line) {
 846:                 $length = fputcsv($tmpfile, $data, ';', '"');
 847:                 if (false !== $length) {
 848:                     $total += $length;
 849:                 }
 850:             }
 851:         }
 852:         // read CSV from tmp file and delete it
 853:         if (0 < $total) {
 854:             $csv = fread($tmpfile, $length);
 855:             fclose($tmpfile);
 856:         }
 857: 
 858:         return $csv;
 859:     }
 860: 
 861:     /**
 862:      *
 863:      * @throws PifaException if existance of table could not be determined
 864:      * @see http://www.electrictoolbox.com/check-if-mysql-table-exists/
 865:      */
 866:     public function existsTable($tableName, $bySchema = false) {
 867:         $cfg = cRegistry::getConfig();
 868: 
 869:         // prepare statement
 870:         if (true === $bySchema) {
 871:             // using the information schema
 872:             $sql = "-- PifaForm->existsTable()
 873:                 SELECT
 874:                     *
 875:                 FROM
 876:                     `information_schema.tables`
 877:                 WHERE
 878:                     table_schema = '" . $cfg['db']['connection']['database'] . "'
 879:                     AND table_name = '$tableName'
 880:                 ;";
 881:         } else {
 882:             // using show tables
 883:             $sql = "-- PifaForm->existsTable()
 884:                 SHOW TABLES
 885:                 LIKE
 886:                     '$tableName';
 887:                 ;";
 888:         }
 889: 
 890:         // check table
 891:         $db = cRegistry::getDb();
 892:         if (false === $db->query($sql)) {
 893:             $msg = Pifa::i18n('TABLE_CHECK_ERROR');
 894:             $msg = sprintf($msg, $db->getErrorMessage());
 895:             throw new PifaException($msg);
 896:         }
 897: 
 898:         return (bool) (0 !== $db->numRows());
 899:     }
 900: 
 901:     /**
 902:      * Create data table for form if it does not already exist.
 903:      * If there are any fields defined for this form, their columns will be
 904:      * created too! N.b. these fields don't have to be aggregated yet. They will
 905:      * be read from database if this form does not aggregate them yet.
 906:      *
 907:      * @param bool $withTimestamp if table should include column for timestamp
 908:      * @throws PifaException if form is not loaded
 909:      * @throws PifaException if existance of table could not be determined
 910:      * @throws PifaException if table already exists
 911:      * @throws PifaException if table could not be created
 912:      */
 913:     public function createTable($withTimestamp) {
 914:         if (!$this->isLoaded()) {
 915:             $msg = Pifa::i18n('FORM_LOAD_ERROR');
 916:             throw new PifaException($msg);
 917:         }
 918: 
 919:         // get & check table name
 920:         $tableName = $this->get('data_table');
 921:         if ($this->existsTable($tableName)) {
 922:             $msg = Pifa::i18n('TABLE_EXISTS_ERROR');
 923:             $msg = sprintf($msg, $tableName);
 924:             throw new PifaException($msg);
 925:         }
 926: 
 927:         // prepare column definitions
 928:         $createDefinitions = array();
 929:         array_push($createDefinitions, "id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'primary key'");
 930:         if ($withTimestamp) {
 931:             array_push($createDefinitions, "pifa_timestamp TIMESTAMP NOT NULL COMMENT 'automatic PIFA timestamp'");
 932:         }
 933:         // read fields from DB if none are found!
 934:         if (NULL === $this->_fields) {
 935:             $this->loadFields();
 936:         }
 937:         foreach ($this->_fields as $pifaField) {
 938:             $columnName = $pifaField->get('column_name');
 939:             // skip fields w/o column
 940:             if (0 === cString::getStringLength(trim($columnName))) {
 941:                 continue;
 942:             }
 943:             $dataType = $pifaField->getDbDataType();
 944:             array_push($createDefinitions, "`$columnName` $dataType");
 945:         }
 946:         $createDefinitions = join(',', $createDefinitions);
 947: 
 948:         // prepare statement
 949:         $sql = "-- PifaForm->createTable()
 950:             CREATE TABLE
 951:                 -- IF NOT EXISTS
 952:                 `$tableName`
 953:             ($createDefinitions)
 954:             ENGINE=MyISAM
 955:             DEFAULT CHARSET=utf8
 956:             ;";
 957: 
 958:         // create table
 959:         $db = cRegistry::getDb();
 960:         if (false === $db->query($sql)) {
 961:             $msg = Pifa::i18n('TABLE_CREATE_ERROR');
 962:             throw new PifaException($msg);
 963:         }
 964:     }
 965: 
 966:     /**
 967:      * Alter data table.
 968:      * Renames data table if name has changed and adds or drops column for
 969:      * timestamp if setting has changed.
 970:      *
 971:      * HINT: passing the old values is correct!
 972:      * The new values have already been stored inside the pifaForm object!
 973:      *
 974:      * @param string $oldTableName
 975:      * @param bool $oldWithTimestamp
 976:      * @throws PifaException if form is not loaded
 977:      */
 978:     public function alterTable($oldTableName, $oldWithTimestamp) {
 979:         if (!$this->isLoaded()) {
 980:             $msg = Pifa::i18n('FORM_LOAD_ERROR');
 981:             throw new PifaException($msg);
 982:         }
 983: 
 984:         // get & check table name
 985:         $tableName = $this->get('data_table');
 986: 
 987:         // rename data table if name has changed
 988:         if ($oldTableName !== $tableName) {
 989:             if ($this->existsTable($tableName)) {
 990:                 $this->set('data_table', $oldTableName);
 991:             } else {
 992:                 $sql = "-- PifaForm->alterTable()
 993:                     RENAME TABLE
 994:                         `$oldTableName`
 995:                     TO
 996:                         `$tableName`
 997:                     ;";
 998:                 cRegistry::getDb()->query($sql);
 999:             }
1000:         }
1001: 
1002:         // adds or drop column for timestamp if setting has changed.
1003:         $withTimestamp = $this->get('with_timestamp');
1004:         if ($oldWithTimestamp != $withTimestamp) {
1005:             if ($withTimestamp) {
1006:                 $sql = "-- PifaForm->alterTable()
1007:                     ALTER TABLE
1008:                         `$tableName`
1009:                     ADD
1010:                         `pifa_timestamp`
1011:                     TIMESTAMP
1012:                     NOT NULL
1013:                     COMMENT
1014:                         'automatic PIFA timestamp'
1015:                     AFTER id
1016:                     ;";
1017:             } else {
1018:                 $sql = "-- PifaForm->alterTable()
1019:                     ALTER TABLE
1020:                         `$tableName`
1021:                     DROP
1022:                         `pifa_timestamp`
1023:                     ;";
1024:             }
1025:             cRegistry::getDb()->query($sql);
1026:         }
1027:     }
1028: 
1029:     /**
1030:      *
1031:      * @param PifaField $pifaField
1032:      * @param string $oldColumnName
1033:      * @throws PifaException if form is not loaded
1034:      * @throws PifaException if field is not loaded
1035:      */
1036:     public function storeColumn(PifaField $pifaField, $oldColumnName) {
1037:         if (!$this->isLoaded()) {
1038:             $msg = Pifa::i18n('FORM_LOAD_ERROR');
1039:             throw new PifaException($msg);
1040:         }
1041:         if (!$pifaField->isLoaded()) {
1042:             $msg = Pifa::i18n('FIELD_LOAD_ERROR');
1043:             throw new PifaException($msg);
1044:         }
1045: 
1046:         $columnName = $pifaField->get('column_name');
1047:         $dataType = $pifaField->getDbDataType();
1048: 
1049:         if (0 === cString::getStringLength(trim($oldColumnName))) {
1050:             if (0 === cString::getStringLength(trim($columnName))) {
1051:                 // PASS
1052:             } else {
1053:                 $this->addColumn($columnName, $dataType);
1054:             }
1055:         } else {
1056:             if (0 === cString::getStringLength(trim($columnName))) {
1057:                 $this->dropColumn($oldColumnName);
1058:             } else {
1059:                 $this->changeColumn($columnName, $dataType, $oldColumnName);
1060:             }
1061:         }
1062:     }
1063: 
1064:     /**
1065:      * rename column if name has changed
1066:      *
1067:      * @param string $columnName
1068:      * @param string $dataType
1069:      * @param string $oldColumnName
1070:      * @throws PifaException if column already exists
1071:      * @throws PifaException if column could not be changed
1072:      */
1073:     public function changeColumn($columnName, $dataType, $oldColumnName) {
1074:         $tableName = $this->get('data_table');
1075: 
1076:         if ($oldColumnName === $columnName) {
1077:             return;
1078:         }
1079:         if (true === $this->_existsColumn($columnName)) {
1080:             $msg = Pifa::i18n('COLUMN_EXISTS_ERROR');
1081:             $msg = sprintf($msg, $columnName);
1082:             throw new PifaException($msg);
1083:         }
1084:         if (NULL === $dataType) {
1085:             return;
1086:         }
1087: 
1088:         $sql = "-- PifaForm->changeColumn()
1089:             ALTER TABLE
1090:                 `$tableName`
1091:             CHANGE
1092:                 `$oldColumnName`
1093:                 `$columnName` $dataType
1094:             ;";
1095: 
1096:         $db = cRegistry::getDb();
1097:         if (false === $db->query($sql)) {
1098:             $msg = Pifa::i18n('COLUMN_ALTER_ERROR');
1099:             throw new PifaException($msg);
1100:         }
1101:     }
1102: 
1103:     /**
1104:      * Adds a column for the current field to the table of the current form.
1105:      *
1106:      * @param string $columnName
1107:      * @throws PifaException if column already exists
1108:      */
1109:     public function dropColumn($columnName) {
1110:         $tableName = $this->get('data_table');
1111:         if (false === $this->_existsColumn($columnName)) {
1112:             $msg = Pifa::i18n('COLUMN_EXISTS_ERROR');
1113:             $msg = sprintf($msg, $columnName);
1114:             throw new PifaException($msg);
1115:         }
1116: 
1117:         $sql = "-- PifaForm->dropColumn()
1118:             ALTER TABLE
1119:                 `$tableName`
1120:             DROP
1121:                 `$columnName`
1122:             ;";
1123: 
1124:         $db = cRegistry::getDb();
1125:         if (false === $db->query($sql)) {
1126:             $msg = Pifa::i18n('COLUMN_DROP_ERROR');
1127:             throw new PifaException($msg);
1128:         }
1129:     }
1130: 
1131:     /**
1132:      * Adds a column for the current field to the table of the current form.
1133:      *
1134:      * @param string $columnName
1135:      * @param string $dataType
1136:      * @throws PifaException if field is not loaded
1137:      */
1138:     public function addColumn($columnName, $dataType) {
1139:         $tableName = $this->get('data_table');
1140:         if (true === $this->_existsColumn($columnName)) {
1141:             $msg = Pifa::i18n('COLUMN_EXISTS_ERROR');
1142:             $msg = sprintf($msg, $columnName);
1143:             throw new PifaException($msg);
1144:         }
1145:         if (NULL === $dataType) {
1146:             return;
1147:         }
1148: 
1149:         $sql = "-- PifaForm->addColumn()
1150:                ALTER TABLE
1151:                    `$tableName`
1152:                ADD
1153:                    `$columnName` $dataType
1154:             ;";
1155: 
1156:         $db = cRegistry::getDb();
1157:         if (false === $db->query($sql)) {
1158:             $msg = Pifa::i18n('COLUMN_ADD_ERROR');
1159:             throw new PifaException($msg);
1160:         }
1161:     }
1162: 
1163:     /**
1164:      *
1165:      * @param string $columnName
1166:      * @throws PifaException if columns could not be read
1167:      * @return boolean
1168:      */
1169:     protected function _existsColumn($columnName) {
1170:         $tableName = $this->get('data_table');
1171:         $sql = "-- PifaForm->_existsColumn()
1172:             SHOW FIELDS FROM
1173:                 `$tableName`
1174:             ;";
1175: 
1176:         $db = cRegistry::getDb();
1177:         if (false === $db->query($sql)) {
1178:             $msg = Pifa::i18n('COLUMNS_LOAD_ERROR');
1179:             throw new PifaException($msg);
1180:         }
1181: 
1182:         // Field, Type, Null, Key, Default, Extra
1183:         while (false !== $db->nextRecord()) {
1184:             $field = $db->toArray();
1185:             if (cString::toLowerCase($field['Field']) == cString::toLowerCase($columnName)) {
1186:                 return true;
1187:             }
1188:         }
1189: 
1190:         return false;
1191:     }
1192: 
1193:     /**
1194:      * Deletes this form with all its fields and stored data.
1195:      * The forms data table is also dropped.
1196:      */
1197:     public function delete() {
1198:         $cfg = cRegistry::getConfig();
1199:         $db = cRegistry::getDb();
1200: 
1201:         if (!$this->isLoaded()) {
1202:             $msg = Pifa::i18n('FORM_LOAD_ERROR');
1203:             throw new PifaException($msg);
1204:         }
1205: 
1206:         // delete form
1207:         $sql = "-- PifaForm->delete()
1208:             DELETE FROM
1209:                 `" . cRegistry::getDbTableName('pifa_form') . "`
1210:             WHERE
1211:                 idform = " . cSecurity::toInteger($this->get('idform')) . "
1212:             ;";
1213:         if (false === $db->query($sql)) {
1214:             $msg = Pifa::i18n('FORM_DELETE_ERROR');
1215:             throw new PifaException($msg);
1216:         }
1217: 
1218:         // delete fields
1219:         $sql = "-- PifaForm->delete()
1220:             DELETE FROM
1221:                 `" . cRegistry::getDbTableName('pifa_field') . "`
1222:             WHERE
1223:                 idform = " . cSecurity::toInteger($this->get('idform')) . "
1224:             ;";
1225:         if (false === $db->query($sql)) {
1226:             $msg = Pifa::i18n('FIELDS_DELETE_ERROR');
1227:             throw new PifaException($msg);
1228:         }
1229: 
1230:         // drop data
1231:         if (0 < cString::getStringLength(trim($this->get('data_table')))) {
1232:             $sql = "-- PifaForm->delete()
1233:                 DROP TABLE IF EXISTS
1234:                     `" . cSecurity::toString($this->get('data_table')) . "`
1235:                 ;";
1236:             if (false === $db->query($sql)) {
1237:                 $msg = Pifa::i18n('TABLE_DROP_ERROR');
1238:                 throw new PifaException($msg);
1239:             }
1240:         }
1241:     }
1242: 
1243:     /**
1244:      *
1245:      * @deprecated use $this->get('data_table') instead
1246:      */
1247:     public function getTableName() {
1248:         return $this->get('data_table');
1249:     }
1250: 
1251: }
1252: