Example #1
0
 /**
  *Todo: get all records of base table that meet the $this->filter, $this->join, $this->datefilter, sort by $this->sort
  */
 function getRecords()
 {
     $join = '';
     $filter = '';
     $sort = '';
     if ($this->filter) {
         $i = 0;
         //$this->filter should be an array, such as array("MemberID"=>array("1", "2), "OrderType"=>"normal")
         foreach ($this->filter as $k => $v) {
             if ($v !== "All" && $v !== 'all') {
                 $join .= $i == 0 ? "" : " ";
                 //if $v is array, then treat it as an "OR" statement in where claues,
                 //eg. $this->filter = array("MemberID"=>array("1", "2), "OrderType"=>"normal"), then MemberID need to be either 1 or 2.
                 if (is_array($v)) {
                     $j = 0;
                     foreach ($v as $orvalue) {
                         $orclause .= $j == 0 ? "({$k} = '{$orvalue}'" : " OR {$k} = '{$orvalue}'";
                         $j++;
                     }
                     $orclause .= ")";
                     $filter .= $i == 0 ? "{$orclause}" : " AND {$orclause}";
                     $i++;
                 } else {
                     if (is_numeric($k)) {
                         // accept a simple string...
                         $filter .= $v;
                         $filter .= $i == 0 ? "{$orclause}" : " AND {$orclause}";
                     } else {
                         //$v is not an array, concat it to where clause as an "AND" statement.
                         //eg. $this->filter = array("MemberID"=>array("1", "2), "OrderType"=>"normal"), then concat "OrderType"="normal".
                         $filter .= $i == 0 ? "{$k} = '{$v}'" : " AND {$k} = '{$v}'";
                     }
                 }
                 $i++;
             }
         }
     }
     // Concat DateRange to Where clause using "Between ... And ..." on Created field.
     if ($this->dateFilter) {
         $from = preg_replace('/^([0-9]{1,2})\\/([0-9]{1,2})\\/([0-90-9]{2,4})/', '\\3-\\2-\\1', $this->dateFilter['From']);
         $to = preg_replace('/^([0-9]{1,2})\\/([0-9]{1,2})\\/([0-90-9]{2,4})/', '\\3-\\2-\\1', $this->dateFilter['To']);
         $filter .= $filter ? "AND " : "";
         $filter .= "`{$this->baseClass}`.Created BETWEEN '{$from}' AND ('{$to}' + INTERVAL 1 DAY)";
     }
     // Work out Ordered By clause.
     if ($this->sort) {
         $i = 0;
         foreach ($this->sort as $k => $v) {
             $sort .= $i == 0 ? "{$k} {$v}" : ", {$k} {$v}";
             $i++;
         }
     }
     // Work out Join Clause.
     if ($this->join) {
         $i = 0;
         //$k is the key of base table, $v is an array with joined table and join key, such as
         // $v = array("ID"=>array("table"=>"Payment", "field"=>"OrderID", "joinclass" => "Order")).
         // otherwise it treats it as a SQL-string ("LEFT JOIN x ON  x=y")
         foreach ($this->join as $k => $v) {
             $join .= $i == 0 ? "" : " ";
             if (is_string($v)) {
                 $join .= $v;
             } else {
                 // This means we join multiple tables on the same column name
                 if (!$k || !is_string($k)) {
                     $k = $v['joinColumn'];
                 }
                 if (!$v['howtojoin']) {
                     $v['howtojoin'] = "LEFT JOIN";
                 }
                 $this->joinedTables[] = $v['table'];
                 // FIX Stupid arbitrary array-structure makes this neccessary
                 $joinClass = $v['joinclass'] ? $v['joinclass'] : $this->baseClass;
                 $join .= "{$v['howtojoin']} `{$v['table']}` on `{$joinClass}`.`{$k}` = `{$v['table']}`.`{$v['field']}`";
             }
             $i++;
         }
     }
     $instance = singleton($this->baseClass);
     $query = $instance->buildSQL($filter, $sort, null, $join);
     $selected = array();
     $this->buildSelected($this->dataFields, $selected);
     $query->select = $selected;
     $query->select[] = "`{$this->baseClass}`.ClassName";
     $query->select[] = "`{$this->baseClass}`.ClassName AS RecordClassName";
     $records = $query->execute();
     $dataobject = new DataObject();
     $ret = $dataobject->buildDataObjectSet($records, 'DataObjectSet');
     return $ret;
 }