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