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