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 this could be usefull for PifaField too.
 424:      *
 425:      * @return bool
 426:      */
 427:     public function store() {
 428:         if (is_null($this->modifiedValues)) {
 429:             return true;
 430:         } else {
 431:             return parent::store();
 432:         }
 433:     }
 434: 
 435:     /**
 436:      * Stores values of each field of this form in defined data table.
 437:      * For fields of type INPUT_FILE the uploaded file is stored in the
 438:      * FileSystem (in $cfg['path']['contenido_cache'] . 'form_assistant/').
 439:      *
 440:      * @throws PifaDatabaseException if values could not be stored
 441:      */
 442:     public function storeData() {
 443:         $cfg = cRegistry::getConfig();
 444: 
 445:         // get values for all defined fields
 446:         $values = $this->getValues();
 447: 
 448:         // make arrays of values storable
 449:         foreach ($values as $column => $value) {
 450:             if (is_array($value)) {
 451:                 $values[$column] = implode(',', $value);
 452:             }
 453:         }
 454: 
 455:         // get DB
 456:         $db = cRegistry::getDb();
 457: 
 458:         // build insert statement
 459:         $sql = $db->buildInsert($this->get('data_table'), $values);
 460: 
 461:         if (NULL === $db->connect()) {
 462:             $msg = Pifa::i18n('DATABASE_CONNECT_ERROR');
 463:             throw new PifaDatabaseException($msg);
 464:         }
 465:         if (0 === strlen(trim($sql))) {
 466:             $msg = Pifa::i18n('SQL_BUILD_ERROR');
 467:             throw new PifaDatabaseException($msg);
 468:         }
 469: 
 470:         // insert new row
 471:         if (false === $db->query($sql)) {
 472:             $msg = Pifa::i18n('VALUE_STORE_ERROR');
 473:             throw new PifaDatabaseException($msg);
 474:         }
 475: 
 476:         // get last insert id
 477:         $lastInsertedId = $db->getLastInsertedId($this->get('data_table'));
 478: 
 479:         $this->setLastInsertedId($lastInsertedId);
 480: 
 481:         // store files
 482:         $files = $this->getFiles();
 483:         foreach ($this->getFiles() as $column => $file) {
 484:             if (!is_array($file)) {
 485:                 continue;
 486:             }
 487:             // if no file was submitted tmp_name is an empty string
 488:             if (0 === strlen($file['tmp_name'])) {
 489:                 continue;
 490:             }
 491:             $tmpName = $file['tmp_name'];
 492:             $destPath = $cfg['path']['contenido_cache'] . 'form_assistant/';
 493:             $destName = $this->get('data_table') . '_' . $lastInsertedId . '_' . $column;
 494:             $destName = preg_replace('/[^a-z0-9_]+/i', '_', $destName);
 495:             if (false === move_uploaded_file($tmpName, $destPath . $destName)) {
 496:                 $msg = Pifa::i18n('FILE_STORE_ERROR');
 497:                 throw new PifaException($msg);
 498:             }
 499:         }
 500:     }
 501: 
 502:     /**
 503:      *
 504:      * @param array $opt
 505:      */
 506:     public function toMailRecipient(array $opt) {
 507:         if (0 == strlen(trim($opt['from']))) {
 508:             $msg = Pifa::i18n('MISSING_SENDER_ADDRESS');
 509:             throw new PifaMailException($msg);
 510:         }
 511:         if (0 == strlen(trim($opt['fromName']))) {
 512:             $msg = Pifa::i18n('MISSING_SENDER_NAME');
 513:             throw new PifaMailException($msg);
 514:         }
 515:         if (0 == strlen(trim($opt['to']))) {
 516:             $msg = Pifa::i18n('MISSING_RECIPIENT_ADDRESS');
 517:             throw new PifaMailException($msg);
 518:         }
 519:         if (0 == strlen(trim($opt['subject']))) {
 520:             $msg = Pifa::i18n('MISSING_SUBJECT');
 521:             throw new PifaMailException($msg);
 522:         }
 523:         if (0 == strlen(trim($opt['body']))) {
 524:             $msg = Pifa::i18n('MISSING_EMAIL_BODY');
 525:             throw new PifaMailException($msg);
 526:         }
 527: 
 528:         // cMailer
 529: 
 530:         $mailer = new cMailer();
 531:         $message = Swift_Message::newInstance($opt['subject'], $opt['body'], 'text/plain', $opt['charSet']);
 532: 
 533:         // add attachments by names
 534:         if (array_key_exists('attachmentNames', $opt)) {
 535:             if (is_array($opt['attachmentNames'])) {
 536:                 $values = $this->getValues();
 537:                 foreach ($opt['attachmentNames'] as $column => $path) {
 538:                     if (!file_exists($path)) {
 539:                         continue;
 540:                     }
 541:                     $attachment = Swift_Attachment::fromPath($path);
 542:                     $filename = $values[$column];
 543:                     $attachment->setFilename($filename);
 544:                     $message->attach($attachment);
 545:                 }
 546:             }
 547:         }
 548: 
 549:         // add attachments by string
 550:         if (array_key_exists('attachmentStrings', $opt)) {
 551:             if (is_array($opt['attachmentStrings'])) {
 552:                 foreach ($opt['attachmentStrings'] as $filename => $string) {
 553:                     // TODO mime type should be configurale
 554:                     $attachment = Swift_Attachment::newInstance($string, $filename, 'text/csv');
 555:                     $message->attach($attachment);
 556:                 }
 557:             }
 558:         }
 559: 
 560:         // add sender
 561:         $message->addFrom($opt['from'], $opt['fromName']);
 562: 
 563:         // add recipient
 564:         $to = explode(',', $opt['to']);
 565:         $message->setTo(array_combine($to, $to));
 566: 
 567:         // send mail
 568:         if (!$mailer->send($message)) {
 569:             $msg = Pifa::i18n('EMAIL_SEND_ERROR');
 570:             throw new PifaMailException($msg);
 571:         }
 572:     }
 573: 
 574:     /**
 575:      *
 576:      * @throws PifaException if form is not loaded
 577:      * @throws PifaException if table does not exist
 578:      * @return array
 579:      */
 580:     public function getData() {
 581:         if (!$this->isLoaded()) {
 582:             $msg = Pifa::i18n('FORM_LOAD_ERROR');
 583:             throw new PifaException($msg);
 584:         }
 585: 
 586:         $db = cRegistry::getDb();
 587: 
 588:         // get table name and check if it exists
 589:         $tableName = $this->get('data_table');
 590:         if (!$this->existsTable($tableName, false)) {
 591:             $msg = Pifa::i18n('MISSING_TABLE_ERROR');
 592:             throw new PifaException($msg);
 593:         }
 594: 
 595:         // build SQL
 596:         $sql = "-- PifaForm->getData()
 597:             SELECT
 598:                 *
 599:             FROM
 600:                 `$tableName`
 601:             ;";
 602: 
 603:         if (false === $db->query($sql)) {
 604:             return array();
 605:         }
 606: 
 607:         if (0 === $db->num_rows()) {
 608:             return array();
 609:         }
 610: 
 611:         $data = array();
 612:         while ($db->nextRecord()) {
 613:             $data[] = $db->toArray();
 614:         }
 615: 
 616:         return $data;
 617:     }
 618: 
 619:     /**
 620:      * Echoes a CSV file containing all of this forms stored data.
 621:      * Thatfor proper headers are sent, that add the created file as attachment
 622:      * for easier download.
 623:      *
 624:      * @param string $optionally
 625:      * @throws PifaException if form is not loaded
 626:      * @throws PifaException if table does not exist
 627:      */
 628:     public function getDataAsCsv($optionally = 'OPTIONALLY') {
 629:         $cfg = cRegistry::getConfig();
 630: 
 631:         if (in_array($cfg['db']['connection']['host'], array(
 632:             '127.0.0.1',
 633:             'localhost'
 634:         ))) {
 635:             // This solution is cool, but won't work, due to the fact that in
 636:             // our database server is not the web server.
 637:             // $out = $this->_getCsvFromLocalDatabaseServer();
 638: 
 639:             // there seems to be a problem using _getCsvFromLocalDatabaseServer
 640:             // so _getCsvFromRemoteDatabaseServer is used in every case
 641:             $out = $this->_getCsvFromRemoteDatabaseServer();
 642:         } else {
 643:             $out = $this->_getCsvFromRemoteDatabaseServer();
 644:         }
 645: 
 646:         // return payload
 647:         return $out;
 648:     }
 649: 
 650:     /**
 651:      *
 652:      * @param string $optionally
 653:      * @throws PifaException if form is not loaded
 654:      * @throws PifaException if table does not exist
 655:      */
 656:     private function _getCsvFromLocalDatabaseServer($optionally = 'OPTIONALLY') {
 657: 
 658:         // assert form is loaded
 659:         if (!$this->isLoaded()) {
 660:             $msg = Pifa::i18n('FORM_LOAD_ERROR');
 661:             throw new PifaException($msg);
 662:         }
 663: 
 664:         // get table name and check if it exists
 665:         $tableName = $this->get('data_table');
 666:         if (!$this->existsTable($tableName, false)) {
 667:             $msg = Pifa::i18n('MISSING_TABLE_ERROR');
 668:             throw new PifaException($msg);
 669:         }
 670: 
 671:         // assert $optionally to be either 'OPTIONALLY' or ''
 672:         if ('OPTIONALLY' !== $optionally) {
 673:             $optionally = '';
 674:         }
 675: 
 676:         // create temp file
 677:         $cfg = cRegistry::getConfig();
 678:         $filename = tempnam($cfg['path']['contenido_cache'], 'PIFA_');
 679:         unlink($filename);
 680: 
 681:         // build SQL
 682:         $sql = "-- PifaForm->_getCsvFromLocalDatabaseServer()
 683:             SELECT
 684:                 *
 685:             INTO OUTFILE
 686:                 '$filename'
 687:             FIELDS TERMINATED BY
 688:                 ','
 689:             $optionally ENCLOSED BY
 690:                 '\"'
 691:             ESCAPED BY
 692:                 '\\\\'
 693:             LINES TERMINATED BY
 694:                 '\\n'
 695:             FROM
 696:                 `$tableName`
 697:             ;";
 698: 
 699:         // execute SQL
 700:         cRegistry::getDb()->query($sql);
 701: 
 702:         // get content
 703:         $out = cFileHandler::read($filename);
 704: 
 705:         // delete temp file
 706:         unlink($filename);
 707: 
 708:         return $out;
 709:     }
 710: 
 711:     /**
 712:      * TODO use fputcsv()
 713:      *
 714:      * @throws PifaException if form is not loaded
 715:      * @throws PifaException if table does not exist
 716:      */
 717:     private function _getCsvFromRemoteDatabaseServer() {
 718: 
 719:         // get column names in correct order
 720:         $columns = array();
 721:         // always append the records ID
 722:         array_push($columns, 'id');
 723:         // append the records timestamp if defined for form
 724:         if (true === (bool) $this->get('with_timestamp')) {
 725:             array_push($columns, 'pifa_timestamp');
 726:         }
 727:         foreach ($this->getFields() as $index => $pifaField) {
 728:             $columns[] = $pifaField->get('column_name');
 729:         }
 730: 
 731:         $out = '';
 732: 
 733:         // add header row
 734:         foreach ($columns as $index => $columnName) {
 735:             if (0 < $index) {
 736:                 $out .= ';';
 737:             }
 738:             $out .= $columnName;
 739:         }
 740: 
 741:         function pifa_form_get_literal_line_endings($value) {
 742:             $value = str_replace("\n", '\n', $value);
 743:             $value = str_replace("\r", '\r', $value);
 744:             $value = "\"$value\"";
 745:             return $value;
 746:         }
 747: 
 748:         // add data rows
 749:         foreach ($this->getData() as $row) {
 750:             // replace \n & \r by it's literal representation
 751:             $row = array_map('pifa_form_get_literal_line_endings', $row);
 752:             // append value
 753:             foreach ($columns as $index => $columnName) {
 754:                 $out .= 0 === $index? "\n" : ';';
 755:                 $out .= $row[$columnName];
 756:             }
 757:         }
 758: 
 759:         return $out;
 760:     }
 761: 
 762:     /**
 763:      * This method returns the current data as CSV file.
 764:      * This file usually contains two rows, one header and one value line.
 765:      * If $oneRowPerField is set to true the CSV-file is mirrored so that each
 766:      * line contains the fields header and then its value.
 767:      * An assoc array of $additionalFields can be given which will be appended
 768:      * th the current values of this form.
 769:      *
 770:      * @param bool $oneRowPerField
 771:      */
 772:     public function getCsv($oneRowPerField = false, array $additionalFields = NULL) {
 773: 
 774:         // get values to be converted into CSV
 775:         $data = $this->getValues();
 776: 
 777:         // add additional fields if given
 778:         if (NULL !== $additionalFields) {
 779:             array_merge($data, $additionalFields);
 780:         }
 781: 
 782:         $out = '';
 783:         if (true === $oneRowPerField) {
 784: 
 785:             // one line for each field containing its header and value
 786:             foreach ($data as $key => $value) {
 787:                 if (0 < strlen($out)) {
 788:                     $out .= "\n";
 789:                 }
 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: