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