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