function test() { $cu = new CustomQuery(array('namespace' => 'custom-post', 'meta' => array('image', 'settings'))); $cu->define_taxonomy('category', __('Category'), array('is_hierarchical' => true)); // var_dump( $cu->set_taxonomy( 'category' , __( 'Custom Category 4' ) , __( 'Custom Content 4' ) , 15 ) ); /*$set = $cu->set( 'Hello World' , 'Contenu' , array( 'image' => '#image', 'settings' => 'Reglages' ) , array( 'category' => array( 1 ) ) , $mode = 'set' , $identifier = 0 , $filter = 'as_id' ); var_dump( $set );*/ /**$set = $cu->set( 'Titre' , 'Contenu' , array( 'image' => '#image', 'settings' => 'Reglages' ) ); var_dump( $set ); **/ /*$get = $cu->get( array( 'where' => array( 'taxonomy_id' => 1 ) ) ); var_dump( $get );*/ // var_dump( $cu->delete_taxonomies( 2 ) ); /*$tax = $cu->get_taxonomies( 'category' , $get[0][ 'ID' ] , 'as_query_id' ); var_dump( $tax );*/ }
public function getQuery() { $query = new CustomQuery($this->getPhql()); $query->setDI($this->getDI()); return $query; }
public function install(array $options = array()) { $toret = false; $this->error_msg = false; if ($this->checkConnection()) { $drop_table = array_key_exists('drop_table', $options) ? $options['drop_table'] : false; $query = $this->getInstallSQL(); if ($query) { if ($drop_table) { $table = $this->getSource(); $drop_query = new CustomQuery('DROP TABLE IF EXISTS ' . Query::getTable($this) . '', array('connection' => $this->db)); $drop_query->execute(); Backend::addNotice('Dropping table ' . $table); if (!empty($drop_query->error_msg)) { $this->error_msg = $drop_query->error_msg; } } $query = new CustomQuery($query, array('connection' => $this->db)); $toret = $query->execute(); if (!empty($query->error_msg)) { $this->error_msg = $query->error_msg; } } else { if (class_exists('BackendError', false)) { BackendError::add(get_class($this) . ': No Install SQL', 'install'); } $this->error_msg = 'No Install SQL for ' . class_name($this); } } else { if (class_exists('BackendError', false)) { BackendError::add(get_class($this) . ': DB Connection Error', 'install'); } $this->error_msg = 'DB Connection error'; } return $toret; }
public static function getActive($refresh = false) { if (!defined('BACKEND_WITH_DATABASE') || !BACKEND_WITH_DATABASE) { //Return the core components return self::getCoreComponents(); } $result = $refresh ? false : Backend::get('Component::active', false); if ($result) { return $result; } $component = new ComponentObj(); list($query, $params) = $component->getSelectSQL(array('conditions' => '`active` = 1')); if ($query) { $query = new CustomQuery($query); $result = $query->fetchAll($params); Backend::add('Component::active', $result); } return $result; }
require_once 'modules/DataSets/DataSet_Layout.php'; //Create new Custom Queries $query_object1 = new CustomQuery(); $query_object1->name = $mod_strings['LBL_Q'] . "1"; $query_object1->description = $mod_strings['LBL_Q1_DESC']; $query_object1->query_locked = "off"; $query_object1->team_id = 1; if ($query_object1->db->dbType == 'oci8') { } elseif ($query_object1->db->dbType == 'mssql') { $query_object1->custom_query = "SELECT 'New Business' 'Opportunity Type',\ncase MONTH(opportunities.date_closed) when MONTH(GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}0',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,1,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}1',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,2,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}2',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,3,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}3',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,4,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}4',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,5,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}5',\nSUM(opportunities.amount_usdollar) AS 'Total Revenue'\nFROM opportunities\nLEFT JOIN accounts_opportunities ON opportunities.id=accounts_opportunities.opportunity_id \nLEFT JOIN accounts ON accounts_opportunities.account_id=accounts.id\nWHERE opportunities.date_closed <= DATEADD(mm,5,GETDATE()) AND opportunities.date_closed >= GETDATE() AND opportunities.opportunity_type = 'New Business'\ngroup by opportunities.date_closed\n UNION \nSELECT 'Existing Business' as 'Opportunity Type',\ncase MONTH(opportunities.date_closed) when MONTH(GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}0',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,1,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}1',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,2,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}2',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,3,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}3',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,4,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}4',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,5,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}5',\nSUM(opportunities.amount_usdollar) AS 'Total Revenue'\nFROM opportunities\nLEFT JOIN accounts_opportunities ON opportunities.id=accounts_opportunities.opportunity_id \nLEFT JOIN accounts ON accounts_opportunities.account_id=accounts.id\nWHERE opportunities.date_closed <= DATEADD(mm,5,GETDATE()) AND opportunities.date_closed >= GETDATE() AND opportunities.opportunity_type = 'New Business'\ngroup by opportunities.date_closed\n UNION \nSELECT 'Total Revenue' as 'Opportunity Type',\ncase MONTH(opportunities.date_closed) when MONTH(GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}0',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,1,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}1',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,2,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}2',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,3,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}3',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,4,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}4',\ncase MONTH(opportunities.date_closed) when DATEADD(mm,5,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}5',\nSUM(opportunities.amount_usdollar) AS 'Total Revenue'\nFROM opportunities\nLEFT JOIN accounts_opportunities ON opportunities.id=accounts_opportunities.opportunity_id \nLEFT JOIN accounts ON accounts_opportunities.account_id=accounts.id\nWHERE opportunities.date_closed <= DATEADD(mm,5,GETDATE()) AND opportunities.date_closed >= GETDATE()\ngroup by opportunities.date_closed\n"; } elseif ($query_object1->db->dbType == 'mysql') { $query_object1->custom_query = "(\nSELECT \n 'New Business ' as 'Opportunity Type'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(CURDATE()), opportunities.amount_usdollar,0)) as '{sc}0{sc}0'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}1'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 2 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}2'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 3 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}3'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 4 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}4'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 5 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}5'\n,SUM(opportunities.amount_usdollar) AS 'Total Revenue'\n \nFROM opportunities\n LEFT JOIN accounts_opportunities \nON opportunities.id=accounts_opportunities.opportunity_id \nLEFT JOIN accounts \nON accounts_opportunities.account_id=accounts.id\nWHERE opportunities.date_closed <= DATE_ADD(CURDATE(),INTERVAL 5 MONTH) AND opportunities.date_closed >= CURDATE() AND opportunities.opportunity_type = 'New Business'\n) UNION (\nSELECT \n 'Existing Business' as 'Opportunity Type'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(CURDATE()), opportunities.amount_usdollar,0)) as '{sc}0{sc}0'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}1'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 2 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}2'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 3 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}3'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 4 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}4'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 5 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}5'\n,SUM(opportunities.amount_usdollar) AS 'Total Revenue'\n \nFROM opportunities\n LEFT JOIN accounts_opportunities \nON opportunities.id=accounts_opportunities.opportunity_id \nLEFT JOIN accounts \nON accounts_opportunities.account_id=accounts.id\nWHERE opportunities.date_closed <= DATE_ADD(CURDATE(),INTERVAL 5 MONTH) AND opportunities.date_closed >= CURDATE() AND opportunities.opportunity_type = 'Existing Business'\n) UNION (\nSELECT \n 'Total Revenue' as 'Opportunity Type'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(CURDATE()), opportunities.amount_usdollar,0)) as '{sc}0{sc}0'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}1'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 2 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}2'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 3 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}3'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 4 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}4'\n,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 5 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}5'\n,SUM(opportunities.amount_usdollar) AS 'Total Revenue'\n \nFROM opportunities\n LEFT JOIN accounts_opportunities \nON opportunities.id=accounts_opportunities.opportunity_id \nLEFT JOIN accounts \nON accounts_opportunities.account_id=accounts.id\nWHERE opportunities.date_closed <= DATE_ADD(CURDATE(),INTERVAL 5 MONTH) AND opportunities.date_closed >= CURDATE() \n)"; //end if else mysql or oracle } $query_object1->save(); $query_object2 = new CustomQuery(); $query_object2->name = $mod_strings['LBL_Q'] . "2"; $query_object2->description = $mod_strings['LBL_Q2_DESC']; $query_object2->query_locked = "off"; $query_object2->team_id = 1; if ($query_object2->db->dbType == 'oci8') { } elseif ($query_object1->db->dbType == 'mssql') { $query_object2->custom_query = "SELECT accounts.name AS 'Account Name',\n\tcase MONTH(opportunities.date_closed) when MONTH(GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}0',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,1,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}1',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,2,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}2',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,3,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}3',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,4,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}4',\n\tcase MONTH(opportunities.date_closed) when DATEADD(mm,5,GETDATE()) then SUM(opportunities.amount_usdollar) else SUM(0) end '{sc}0{sc}5',\n\tSUM(opportunities.amount_usdollar) AS 'Total Revenue'\n \n\tFROM opportunities\n\tLEFT JOIN accounts_opportunities ON opportunities.id=accounts_opportunities.opportunity_id \n\tLEFT JOIN accounts ON accounts_opportunities.account_id=accounts.id\n\tWHERE opportunities.date_closed <= DATEADD(mm,5,GETDATE()) AND opportunities.date_closed >= GETDATE()\n\tGROUP BY opportunities.date_closed, accounts.id, accounts.name order by accounts.name\n\t"; } elseif ($query_object1->db->dbType == 'mysql') { $query_object2->custom_query = "SELECT accounts.name AS 'Account Name'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(CURDATE()), opportunities.amount_usdollar,0)) as '{sc}0{sc}0'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}1'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 2 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}2'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 3 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}3'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 4 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}4'\n\t,SUM(IF(MONTH(opportunities.date_closed) = MONTH(DATE_ADD(CURDATE(),INTERVAL 5 MONTH)), opportunities.amount_usdollar,0)) as '{sc}0{sc}5'\n\t,SUM(opportunities.amount_usdollar) AS 'Total Revenue'\n\t \n\tFROM opportunities\n\t LEFT JOIN accounts_opportunities \n\tON opportunities.id=accounts_opportunities.opportunity_id \n\tLEFT JOIN accounts \n\tON accounts_opportunities.account_id=accounts.id\n\tWHERE opportunities.date_closed <= DATE_ADD(CURDATE(),INTERVAL 5 MONTH)\n\tAND opportunities.date_closed >= CURDATE()\n\tGROUP BY accounts.id ORDER BY accounts.name"; //end if else mysql or oracle } $query_object2->save(); $query_id1 = $query_object1->id; $query_id2 = $query_object2->id; //Create new Report
public static function model() { $year = date('Y'); extract(self::$variables); if (empty($company)) { $copyright_owner = $author; } else { $copyright_owner = $company; $company = ' (' . $company . ')'; } $file = <<<END <?php /** * The class file for {$class_name}Obj * * @copyright Copyright (c) {$year} {$copyright_owner}. * @author {$author}{$company} - initial implementation * @package ModelFiles * Contributors: * @author {$author}{$company} - initial implementation */ /** * This is the model definition for `{$db_name}`.`{$table_name}` * * @package Models */ class {$class_name}Obj extends DBObject { \tfunction __construct(\$meta = array(), array \$options = array()) { \t\tif (!is_array(\$meta)) { \t\t\t\$meta = is_numeric(\$meta) ? array('id' => \$meta) : array(); \t\t} \t\t\$meta['database'] = '{$db_name}'; \t\t\$meta['table'] = '{$table_name}'; \t\t\$meta['name'] = '{$class_name}'; \t\t\$meta['fields'] = array( END; $query = new CustomQuery("SHOW COLUMNS FROM `{$db_name}`.`{$table_name}`"); $name_length = 0; $fields = array(); while ($row = $query->fetchAssoc()) { $definition = array_change_key_case($row); $definition['null'] = strtolower($row['Null']) == 'yes'; $definition['type'] = 'string'; $name_length = max($name_length, strlen($definition['field'])); unset($definition['key'], $definition['extra']); switch (true) { case strtolower($row['Key']) == 'pri': $definition['type'] = 'primarykey'; break; case strtolower(substr($row['Type'], 0, 7)) == 'varchar': $length = sscanf($row['Type'], 'varchar(%d)'); if ($length && count($length)) { $length = reset($length); $definition['string_size'] = $length; } break; case strtolower(substr($row['Type'], 0, 4)) == 'char': $length = sscanf($row['Type'], 'char(%d)'); if ($length && count($length)) { $length = reset($length); $definition['string_size'] = $length; } break; case strtolower(substr($row['Type'], -4)) == 'text': $definition['type'] = 'text'; break; case strtolower($row['Type']) == 'tinyint(1)': $definition['type'] = 'boolean'; break; case strtolower(substr($row['Type'], 0, 3)) == 'int': case strtolower(substr($row['Type'], 0, 7)) == 'tinyint': case strtolower(substr($row['Type'], 0, 8)) == 'smallint': case strtolower(substr($row['Type'], 0, 6)) == 'bigint': case strtolower(substr($row['Type'], 0, 5)) == 'float': case strtolower(substr($row['Type'], 0, 7)) == 'decimal': $definition['type'] = 'number'; break; case strtolower($row['Type']) == 'timestamp' && strtolower($row['Default']) == 'current_timestamp': $definition['type'] = 'lastmodified'; unset($definition['default']); break; case strtolower($row['Type']) == 'datetime' && in_array(strtolower($row['Field']), array('added', 'dateadded', 'datetimeadded')): $definition['type'] = 'dateadded'; break; case strtolower($row['Type']) == 'date': case strtolower($row['Type']) == 'datetime': case strtolower($row['Type']) == 'time': $definition['type'] = strtolower($row['Type']); break; case strtolower($row['Type']) == 'tinyblob': case strtolower($row['Type']) == 'mediumblob': case strtolower($row['Type']) == 'blob': case strtolower($row['Type']) == 'longblob': $pre = substr($row['Type'], 0, strlen($row['Type']) - 4); if ($pre == '') { $definition['type'] = 'blob'; } else { $definition['type'] = $pre . '_blob'; } break; default: var_dump($row); die; break; } if (!$definition['null']) { if (!in_array($definition['type'], array('primarykey', 'dateadded', 'lastmodified'))) { $definition['required'] = true; } if (array_key_exists('default', $definition) && is_null($definition['default'])) { unset($definition['default']); } } $fields[$row['Field']] = $definition; } if (count($fields) == 0) { Backend::addError('Could not find table'); return false; } foreach ($fields as $name => $definition) { $tmp = array(); foreach ($definition as $key => $value) { $tmp[] = "'{$key}' => " . var_export($value, true); } $definition = 'array(' . implode(', ', $tmp) . ')'; $padding = str_repeat(' ', $name_length - strlen($name)); $file .= "\t\t\t'{$name}'{$padding} => {$definition}," . PHP_EOL; } $file .= <<<END \t\t); \t\t\$meta['keys'] = array( END; //The field already picks up the primary key $query = new CustomQuery("SHOW KEYS FROM `{$db_name}`.`{$table_name}` WHERE `Key_name` != 'PRIMARY'"); $keys = array(); while ($row = $query->fetchAssoc()) { if (!array_key_exists($row['Key_name'], $keys)) { $keys[$row['Key_name']] = array('fields' => array(), 'type' => ''); } $type = empty($row['Non_unique']) ? 'unique' : 'index'; switch (true) { default: $keys[$row['Key_name']]['fields'][] = $row['Column_name']; $keys[$row['Key_name']]['type'] = $type; break; } } foreach ($keys as $name => $key) { $file .= "\t\t\t'{$name}' => array(" . PHP_EOL; $file .= "\t\t\t\t'type' => '{$key['type']}'," . PHP_EOL; $file .= "\t\t\t\t'fields' => array('" . implode("', '", $key['fields']) . "')," . PHP_EOL; $file .= "\t\t\t)," . PHP_EOL; } $file .= <<<END \t\t); \t\treturn parent::__construct(\$meta, \$options); \t} \tfunction validate(\$data, \$action, \$options = array()) { \t\t\$result = true; \t\t\$data = parent::validate(\$data, \$action, \$options); \t\treturn \$result ? \$data : false; \t} } END; return file_put_contents(self::$destination . '/models/' . $class_name . 'Obj.obj.php', $file); }