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