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);
}
