function getCompanyDepartment($company_id)
{
global $AppUI;
$q = new DBQuery();
$q->addTable('companies');
$q->addQuery('company_name');
$q->addWhere('company_id = ' . $company_id);
$company_name = $q->loadResult();
if (!$company_name) {
$AppUI->setMsg('Company not found', UI_MSG_ERROR);
echo $AppUI->getMsg();
return;
}
$q->addTable('departments');
$q->addQuery('dept_id,dept_parent,dept_name');
$q->addWhere('dept_company = ' . $company_id);
$q->addOrder('dept_parent, dept_name');
$depts = $q->loadHashList('dept_id');
if (!$depts) {
$AppUI->setMsg('Company [ ' . $company_name . ' ] has no department', UI_MSG_WARNING);
echo $AppUI->getMsg();
return;
}
include 'modules/public/resources.info.php';
}
/**
* This creates all the tables as defined in self::$required_tables
*/
public function createTables()
{
foreach (self::$required_tables as $statement) {
$query = new DBQuery($statement);
$query->execute();
}
}
function setComplete($id)
{
global $AppUI;
$task = new CTask();
if ($task->load($id)) {
$q = new DBQuery();
$q->addTable('user_tasks');
$q->addQuery('user_id');
$q->addWhere('task_id = ' . $id);
$q->addWhere('user_id = ' . $AppUI->user_id);
$r = $q->loadResult();
if ($r != $AppUI->user_id) {
$p = new CProject($task->task_project);
if (!$p->project_id || $p->getManager() != $AppUI->user_id) {
return 'Error';
}
}
$q->addTable('tasks');
$q->addUpdate('task_percent_complete', '100');
$q->addWhere('task_id = ' . $id);
$q->exec();
return 'OK';
}
return 'Error';
}
function delete()
{
$q = new DBQuery();
$q->addTable('departments', 'dep');
$q->addQuery('dep.*');
$q->addWhere('dep.dept_parent = ' . $this->dept_id);
$res = $q->exec();
if (db_num_rows($res)) {
$q->clear();
return "deptWithSub";
}
$q->clear();
$q->addTable('projects', 'p');
$q->addQuery('p.*');
$q->addWhere('p.project_department = ' . $this->dept_id);
$res = $q->exec();
if (db_num_rows($res)) {
$q->clear();
return "deptWithProject";
}
// $sql = "DELETE FROM departments WHERE dept_id = $this->dept_id";
$q->clear();
$q->addQuery('*');
$q->setDelete('departments');
$q->addWhere('dept_id = ' . $this->dept_id);
if (!$q->exec()) {
$result = db_error();
} else {
$result = NULL;
}
$q->clear();
return $result;
}
function remove()
{
$q = new DBQuery();
$q->dropTable('projects_statistics');
$q->exec();
$q->clear();
return null;
}
public function remove()
{
$q = new DBQuery();
$q->setDelete('modules');
$q->addWhere("mod_directory = 'importers'");
$q->exec();
return true;
}
function insertCompany($company_name)
{
$q = new DBQuery();
$q->addTable("companies");
$q->addInsert('company_name', $company_name);
db_exec($q->prepareInsert());
return db_insert_id();
}
/**
* postsave functions are only called after a succesful save. They are
* used to perform database operations after the event.
*/
function resource_postsave()
{
global $other_resources;
global $obj;
$task_id = $obj->task_id;
dprint(__FILE__, __LINE__, 5, "saving resources, {$other_resources}");
if (isset($other_resources)) {
$value = array();
$reslist = explode(';', $other_resources);
foreach ($reslist as $res) {
if ($res) {
list($resource, $perc) = explode('=', $res);
$value[] = array($task_id, $resource, $perc);
}
}
// first delete any elements already there, then replace with this
// list.
$q = new DBQuery();
$q->setDelete('resource_tasks');
$q->addWhere('task_id = ' . $obj->task_id);
$q->exec();
$q->clear();
if (count($value)) {
foreach ($value as $v) {
$q->addTable('resource_tasks');
$q->addInsert('task_id,resource_id,percent_allocated', $v, true);
$q->exec();
$q->clear();
}
}
}
}
public function bind($hash)
{
if (!is_array($hash)) {
return get_class($this) . "::bind failed";
} else {
$q = new DBQuery();
$q->bindHashToObject($hash, $this);
$q->clear();
return null;
}
}
function delete()
{
global $dPconfig;
$this->_message = "deleted";
// delete the main table reference
$q = new DBQuery();
$q->setDelete('links');
$q->addWhere('link_id = ' . $this->link_id);
if (!$q->exec()) {
return db_error();
}
return NULL;
}
function listCompaniesByType($type)
{
global $AppUI;
$q = new DBQuery();
$q->addQuery('company_id, company_name');
$q->addTable('companies');
foreach ($type as $t) {
$q->addWhere('company_type =' . $t);
}
$this->setAllowedSQL($AppUI->user_id, $q);
$q->addOrder('company_name');
return $q->loadHashList();
}
function _buildQuery()
{
$q = new DBQuery();
$q->addTable($this->table);
$q->addQuery('*');
$sql = '';
foreach ($this->search_fields as $field) {
$sql .= " {$field} LIKE '%{$this->keyword}%' or ";
}
$sql = substr($sql, 0, -4);
$q->addWhere($sql);
return $q->prepare(true);
}
function getFolderSelectList()
{
global $AppUI;
$folders = array(0 => '');
$q = new DBQuery();
$q->addTable('file_folders');
$q->addQuery('file_folder_id, file_folder_name, file_folder_parent');
$q->addOrder('file_folder_name');
$sql = $q->prepare();
// $sql = "SELECT file_folder_id, file_folder_name, file_folder_parent FROM file_folders";
$vfolders = arrayMerge(array('0' => array(0, $AppUI->_('Root'), -1)), db_loadHashList($sql, 'file_folder_id'));
$folders = array_filter($vfolders, "check_perm");
return $folders;
}
function remove()
{
$q = new DBQuery();
$q->dropTable('activity');
$q->exec();
$q->clear();
$q->dropTable('timesheet');
$q->exec();
$q->clear();
$q->dropTable('task_timesheet');
$q->exec();
$q->clear();
return db_error();
}
function install()
{
$q = new DBQuery();
$q->createTable('links');
$q->createDefinition("(\n`link_id` int(11) NOT NULL AUTO_INCREMENT ,\n`link_url` varchar(255) NOT NULL default '',\n`link_project` int(11) NOT NULL default '0',\n`link_task` int(11) NOT NULL default '0',\n`link_name` varchar(255) NOT NULL default '',\n`link_parent` int(11) default '0',\n`link_description` text,\n`link_owner` int(11) default '0',\n`link_date` datetime default NULL ,\n`link_icon` varchar(20) default 'obj/',\n`link_category` int(11) NOT NULL default '0',\nPRIMARY KEY (`link_id`) ,\nKEY `idx_link_task` (`link_task`) ,\nKEY `idx_link_project` (`link_project`) ,\nKEY `idx_link_parent` (`link_parent`) \n) DEFAULT CHARSET utf8");
$q->exec($sql);
$q->clear();
$q->addTable('sysvals');
$q->addInsert('sysval_key_id', 1);
$q->addInsert('sysval_title', 'LinkType');
$q->addInsert('sysval_value', "0|Unknown\n1|Document\n2|Application");
$q->exec();
return NULL;
}
public function executeQuery(DBQuery $query)
{
$this->last_query = $query;
if (!$this->connected) {
$this->last_error = "Keine Verbindung zur Datenbank.";
return NULL;
}
$result = mysql_query($query->getString());
if (!$result) {
$this->last_error = mysql_error();
return NULL;
}
return new ResultSet($result, $this->connection_link);
}
function _fetchPreviousData()
{
$q = new DBQuery();
$q->addTable($this->table_name);
$q->addQuery($this->field_name);
$q->addWhere("{$this->id_field_name} = {$this->row_id}");
$previous_data = $q->loadResult();
if ($previous_data != "") {
$previous_data = unserialize($previous_data);
$previous_data = !is_array($previous_data) ? array() : $previous_data;
} else {
$previous_data = array();
}
$this->previous_data = $previous_data;
}
function delete($oid = NULL)
{
$id = $this->user_id;
$result = parent::delete($oid);
if (!$result) {
$acl =& $GLOBALS['AppUI']->acl();
$acl->deleteLogin($id);
$q = new DBQuery();
$q->setDelete('user_preferences');
$q->addWhere('pref_user = ' . $this->user_id);
$q->exec();
$q->clear();
}
return $result;
}
function breakUpAndGetIDs($field, $dbfieldid, $dbfieldname, $dbtable)
{
$sval = explode(",", $field);
$where = array();
foreach ($sval as $val) {
$where[] = "lower( " . $dbfieldname . " ) LIKE lower( '" . trim($val) . "' )";
}
$ssql = "SELECT " . $dbfieldid . " FROM " . $dbtable . " WHERE " . implode(" OR ", $where);
$qry = new DBQuery();
$qry->execute($ssql);
$sid = array();
while ($srow = $qry->getRow()) {
$sid[] = $srow[$dbfieldid];
}
return $sid;
}
function canDelete(&$msg, $oid = null, $joins = null)
{
global $AppUI;
if ($oid) {
//Check to see if there is a user
$q = new DBQuery();
$q->addTable('users');
$q->addQuery('count(*) as user_count');
$q->addWhere('user_contact = ' . (int) $oid);
$user_count = $q->loadResult();
if ($user_count > 0) {
$msg = $AppUI->_('contactsDeleteUserError');
return false;
}
}
return parent::canDelete($msg, $oid, $joins);
}
public function afterConstruct()
{
foreach ($this->Language->findAll() as $Language) {
$modelName = 'Text' . ucFirst($Language->id);
$this->bind($modelName, 'hasOne', array('class' => 'MediaText', 'foreignKey' => 'media_file_id', 'dependent' => true, 'conditions' => array($modelName . '.language_id' => DBQuery::quote($Language->id))));
$this->{$modelName}->language_id = $Language->id;
}
return parent::afterConstruct();
}
function upgrade($old_version)
{
switch ($old_version) {
case "1.0":
$q = new DBQuery();
$q->addTable('resources');
$q->addField('resource_key', "varchar(64) not null default ''");
$q->exec();
if (db_error()) {
return false;
}
// FALLTHROUGH
// FALLTHROUGH
case "1.0.1":
break;
}
return true;
}
function testUpdateBD()
{
$q = new DBQuery();
$q->addTable('eap');
$q->addQuery("id,nome,linha,coluna");
$q->addUpdate(nome, 'Dot Project');
$q->addWhere("id = 1");
$q->prepareUpdate();
$this->assertEqual($q->exec(), true);
$q->clear();
}
/**
* Tests if the user is unique
* @return boolean
*/
public function isUnique()
{
$params = array('conditions' => array('User.email' => DBQuery::quote($this->email)));
if ($this->exists()) {
$params['conditions']['User.id <>'] = (int) $this->id;
}
if ($doubleUser = $this->find($params)) {
return false;
}
return true;
}
function _buildQuery()
{
$q = new DBQuery();
$q->addTable($this->table);
$q->addTable('files');
$q->addQuery('*');
$q->addWhere("files.file_id = {$this->table}.file_id");
$sql = '';
foreach ($this->search_fields as $field) {
$sql .= " {$field} LIKE '%{$this->keyword}%' or ";
}
$sql = substr($sql, 0, -4);
$q->addWhere("({$sql})");
$q->addGroup('files.file_id');
return $q->prepare(true);
}
public function similarEntries()
{
if (!$this->exists() || $this->Tags->count() == 0) {
return false;
}
$queryFile = dirname(__FILE__) . '/../../console/sql/similarBlogPosts.sql';
if (!file_exists($queryFile)) {
return false;
}
$tmp = new IndexedArray();
foreach ($this->Tags as $Tag) {
$tmp[] = 'Tag.name = ' . DBQuery::quote($Tag->get('name'));
}
$tagConditions = $tmp->implode(' OR ');
$query = sprintf(file_get_contents($queryFile), $tagConditions, $this->id);
return $this->query($query);
}
function __construct($query)
{
if (is_null(self::$conn)) {
self::$conn = mysql_connect();
if (self::$conn === false) {
throw new Exception("Couldn't connect to database");
}
}
$this->result = mysql_query($query, self::$conn);
if ($this->result === false) {
throw new DBQueryException("Bad query: {$query}");
}
$this->fieldCnt = mysql_num_fields($this->result);
$this->rowCnt = mysql_num_rows($this->result);
$this->rowIter = 0;
// point to latest row in result
}
function _buildQuery()
{
$q = new DBQuery();
$q->addTable($this->table);
$q->addQuery('company_id');
$q->addQuery('company_name');
$sql = array();
foreach ($this->search_fields as $field) {
$sql[] = "{$field} LIKE '%{$this->keyword}%'";
}
if (count($sql)) {
$q->addWhere(implode(' OR ', $sql));
}
$result = $q->prepare();
$q->clear();
return $result;
}
function getAllUsersGroupByDept()
{
$q = new DBQuery();
$q->addTable('users');
$q->addQuery('user_id, contact_department, concat_ws(", ", contact_last_name, contact_first_name) as contact_name');
$q->addJoin('contacts', 'con', 'contact_id = user_contact');
$q->addOrder('contact_last_name');
$res = $q->exec();
$userlist = array();
while ($row = $q->fetchRow()) {
if ($row['contact_department'] == null) {
$row['contact_department'] = 0;
}
if (!isset($userlist[$row['contact_department']])) {
$userlist[$row['contact_department']] = array();
}
$userlist[$row['contact_department']][$row['user_id']] = $row['contact_name'];
}
$q->clear();
return $userlist;
}
function store()
{
$msg = $this->check();
if ($msg) {
return get_class($this) . "::store-check failed";
}
$q = new DBQuery();
if ($this->user_id) {
// save the old password
$perm_func = "updateLogin";
$q->addTable('users');
$q->addQuery('user_password');
$q->addWhere("user_id = {$this->user_id}");
$pwd = $q->loadResult();
if ($pwd != $this->user_password) {
$this->user_password = md5($this->user_password);
} else {
$this->user_password = null;
}
$ret = db_updateObject('users', $this, 'user_id', false);
} else {
$perm_func = "addLogin";
$this->user_password = md5($this->user_password);
$ret = db_insertObject('users', $this, 'user_id');
}
if (!$ret) {
return get_class($this) . "::store failed <br />" . db_error();
} else {
$acl =& $GLOBALS['AppUI']->acl();
$acl->{$perm_func}($this->user_id, $this->user_username);
return NULL;
}
}
