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