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