PHP db_loadResult函数代码示例

function checkCompanyId($company_id)
{
    $q = new DBQuery();
    $q->addTable('companies');
    $q->addQuery('count(*)');
    $q->addWhere("company_id = '{$company_id}'");
    return db_loadResult($q->prepare());
}

function isHoliday($date = 0)
{
    // Query database for settings
    $holiday_manual = db_loadResult('SELECT holiday_manual FROM holiday_settings');
    $holiday_auto = db_loadResult('SELECT holiday_auto FROM holiday_settings');
    $holiday_driver = db_loadResult('SELECT holiday_driver FROM holiday_settings');
    if (!$date) {
        $date = new CDate();
    }
    if ($holiday_manual) {
        // Check whether the date is blacklisted
        $sql = "SELECT * FROM holiday ";
        $sql .= "WHERE ( date(holiday_start_date) <= '";
        $sql .= $date->format('%Y-%m-%d');
        $sql .= "' AND date(holiday_end_date) >= '";
        $sql .= $date->format('%Y-%m-%d');
        $sql .= "' AND holiday_white=0 ) ";
        $sql .= "OR ( ";
        $sql .= " DATE_FORMAT(holiday_start_date, '%m-%d') <= '";
        $sql .= $date->format('%m-%d');
        $sql .= "' AND DATE_FORMAT(holiday_end_date, '%m-%d') >= '";
        $sql .= $date->format('%m-%d');
        $sql .= "' AND holiday_annual=1";
        $sql .= " AND holiday_white=0 ) ";
        if (db_loadResult($sql)) {
            return 0;
        }
        // Check if we have a whitelist item for this date
        $sql = "SELECT * FROM holiday ";
        $sql .= "WHERE ( date(holiday_start_date) <= '";
        $sql .= $date->format('%Y-%m-%d');
        $sql .= "' AND date(holiday_end_date) >= '";
        $sql .= $date->format('%Y-%m-%d');
        $sql .= "' AND holiday_white=1 ) ";
        $sql .= "OR ( ";
        $sql .= " DATE_FORMAT(holiday_start_date, '%m-%d') <= '";
        $sql .= $date->format('%m-%d');
        $sql .= "' AND DATE_FORMAT(holiday_end_date, '%m-%d') >= '";
        $sql .= $date->format('%m-%d');
        $sql .= "' AND holiday_annual=1";
        $sql .= " AND holiday_white=1 ) ";
        if (db_loadResult($sql)) {
            return 1;
        }
    }
    if ($holiday_auto) {
        // Still here? Ok, lets poll the automatic system
        $drivers_alloc = Date_Holidays::getInstalledDrivers();
        $driver_object = Date_Holidays::factory($drivers_alloc[$holiday_driver]['title'], $date->getYear(), 'en_EN');
        if (!Date_Holidays::isError($driver_object)) {
            if ($driver_object->getHolidayForDate($date)) {
                return 1;
            }
        }
    }
    // No hits, must be a working day
    return 0;
}

function sendNewPass()
{
    global $AppUI;
    $_live_site = dPgetConfig('base_url');
    $_sitename = dPgetConfig('company_name');
    // ensure no malicous sql gets past
    $checkusername = trim(dPgetParam($_POST, 'checkusername', ''));
    $checkusername = db_escape($checkusername);
    $confirmEmail = trim(dPgetParam($_POST, 'checkemail', ''));
    $confirmEmail = mb_strtolower(db_escape($confirmEmail));
    $query = 'SELECT user_id FROM users LEFT JOIN contacts ON user_contact = contact_id' . " WHERE user_username='{$checkusername}' AND LOWER(contact_email)='{$confirmEmail}'";
    if (!($user_id = db_loadResult($query)) || !$checkusername || !$confirmEmail) {
        $AppUI->setMsg('Invalid username or email.', UI_MSG_ERROR);
        $AppUI->redirect();
    }
    $newpass = makePass();
    $message = $AppUI->_('sendpass0', UI_OUTPUT_RAW) . ' ' . $checkusername . ' ' . $AppUI->_('sendpass1', UI_OUTPUT_RAW) . ' ' . $_live_site . ' ' . $AppUI->_('sendpass2', UI_OUTPUT_RAW) . ' ' . $newpass . ' ' . $AppUI->_('sendpass3', UI_OUTPUT_RAW);
    $subject = "{$_sitename} :: " . $AppUI->_('sendpass4', UI_OUTPUT_RAW) . " - {$checkusername}";
    $m = new Mail();
    // create the mail
    $m->From("dotProject@" . dPgetConfig('site_domain'));
    $m->To($confirmEmail);
    $m->Subject($subject);
    $m->Body($message, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : "");
    // set the body
    $m->Send();
    // send the mail
    $newpass = md5($newpass);
    $sql = "UPDATE users SET user_password='{$newpass}' WHERE user_id='{$user_id}'";
    $cur = db_exec($sql);
    if (!$cur) {
        die('SQL error' . $database->stderr(true));
    } else {
        $AppUI->setMsg('New User Password created and emailed to you');
        $AppUI->redirect();
    }
}

<?php 
    if (count($user_list)) {
        $percentage_sum = $hours_allocated_sum = $hours_worked_sum = 0;
        $sum_total_hours_allocated = $sum_total_hours_worked = 0;
        $sum_hours_allocated_complete = $sum_hours_worked_complete = 0;
        //TODO: Split times for which more than one users were working...
        foreach ($user_list as $user_id => $user) {
            $sql = "SELECT task_id\n\t\t\t        FROM user_tasks\n\t\t\t        where user_id = {$user_id}";
            $tasks_id = db_loadColumn($sql);
            $total_hours_allocated = $total_hours_worked = 0;
            $hours_allocated_complete = $hours_worked_complete = 0;
            foreach ($tasks_id as $task_id) {
                if (isset($task_list[$task_id])) {
                    // Now let's figure out how many time did the user spent in this task
                    $sql = "SELECT sum(task_log_hours)\n\t\t        \t\t\tFROM task_log\n\t\t        \t\t\tWHERE task_log_task        = {$task_id}\n\t\t\t\t\t              AND task_log_creator = {$user_id}";
                    $hours_worked = round(db_loadResult($sql), 2);
                    $sql = "SELECT task_percent_complete\n                                                FROM tasks\n                                                WHERE task_id = {$task_id}";
                    //                 echo $sql;
                    $percent = db_loadColumn($sql);
                    $complete = $percent[0] == 100;
                    if ($complete) {
                        $hours_allocated_complete += $task_list[$task_id]["hours_allocated"];
                        $hours_worked_complete += $hours_worked;
                    }
                    $total_hours_allocated += $task_list[$task_id]["hours_allocated"];
                    $total_hours_worked += $hours_worked;
                }
            }
            $sum_total_hours_allocated += $total_hours_allocated;
            $sum_total_hours_worked += $total_hours_worked;
            $sum_hours_allocated_complete += $hours_allocated_complete;

 function getCompanyName()
 {
     $sql = "select company_name from companies where company_id = '" . $this->contact_company . "'";
     $q = new DBQuery();
     $q->addTable('companies');
     $q->addQuery('company_name');
     $q->addWhere('company_id = ' . $this->contact_company);
     $sql = $q->prepare();
     $q->clear();
     $company_name = db_loadResult($sql);
     return $company_name;
 }

    $titleBlock->addCrumb("?m=companies", "companies list");
    if ($company_id) {
        $titleBlock->addCrumb("?m=companies&a=view&company_id={$company_id}", "view this company");
    }
    $titleBlock->show();
} else {
    ##echo $sql.db_error();##
    $company_id = $dept_id ? $drow['dept_company'] : $company_id;
    // check if valid company
    $q = new DBQuery();
    $q->addTable('companies', 'com');
    $q->addQuery('company_name');
    $q->addWhere('com.company_id = ' . $company_id);
    $sql = $q->prepare();
    $q->clear();
    $company_name = db_loadResult($sql);
    if (!$dept_id && $company_name === null) {
        $AppUI->setMsg('badCompany', UI_MSG_ERROR);
        $AppUI->redirect();
    }
    // collect all the departments in the company
    $depts = array(0 => '');
    if ($company_id) {
        $q = new DBQuery();
        $q->addTable('departments', 'dep');
        $q->addQuery('dept_id, dept_name, dept_parent');
        $q->addWhere('dep.dept_company = ' . $company_id);
        $q->addWhere('dep.dept_id != ' . $dept_id);
        $depts = $q->loadArrayList();
        $depts['0'] = array(0, '- ' . $AppUI->_('Select Unit') . ' -', -1);
    }

$sql = "SELECT COUNT(item_id)\n        FROM helpdesk_items\n        WHERE {$item_perms}";
$numtotal = db_loadResult($sql);
/*
 * Unassigned = 0
 * Open = 1
 * Closed = 2
 * On hold = 3
 * Delete = 4
 * Testing = 5
 */
$sql = "SELECT COUNT(DISTINCT(item_id))\n        FROM \n        \thelpdesk_items\n        WHERE \n        \titem_assigned_to=" . $AppUI->user_id . "\n        \tAND (item_status != 2)";
$nummine = db_loadResult($sql);
$sql = "SELECT COUNT(DISTINCT(item_id))\n        FROM \n        \thelpdesk_items\n        \tLEFT JOIN helpdesk_item_status on helpdesk_items.item_id = helpdesk_item_status.status_item_id\n        WHERE \n        \tstatus_code = 0\n            AND item_status <> 2\n            AND (TO_DAYS(NOW()) - TO_DAYS(status_date) = 0)\n        \tAND {$item_perms}";
$numopened = db_loadResult($sql);
$sql = "SELECT COUNT(DISTINCT(item_id))\n        FROM \n        \thelpdesk_items\n        \tLEFT JOIN helpdesk_item_status on helpdesk_items.item_id = helpdesk_item_status.status_item_id\n        WHERE \n        \titem_status=2\n        \tAND status_code=11\n        \tAND (TO_DAYS(NOW()) - TO_DAYS(status_date) = 0)\n          AND {$item_perms}";
$numclosed = db_loadResult($sql);
?>
<table cellspacing="0" cellpadding="2" border="0" width="100%">
<tr>
	<td width="80%" valign="top">
  <?php 
// Tabbed information boxes
$tabBox = new CTabBox("?m=helpdesk", "{$dPconfig['root_dir']}/modules/helpdesk/", $tab);
$tabBox->add('vw_idx_stats', $AppUI->_('Help Desk Items') . " ({$numtotal})");
$tabBox->add('vw_idx_my', $AppUI->_('My Open') . " ({$nummine})");
$tabBox->add('vw_idx_new', $AppUI->_('Opened Today') . " ({$numopened})");
$tabBox->add('vw_idx_closed', $AppUI->_('Closed Today') . " ({$numclosed})");
$tabBox->add('vw_idx_watched', "Watched Tickets");
$tabBox->show();
?>
	</td>

        $AppUI->setMsg("noCompanies", UI_MSG_ERROR, true);
        $AppUI->redirect();
    }
}
if ($project_id == 0 && $company_id > 0) {
    $row->project_company = $company_id;
}
// add in the existing company if for some reason it is dis-allowed
if ($project_id && !array_key_exists($row->project_company, $companies)) {
    $q = new DBQuery();
    $q->addTable('companies');
    $q->addQuery('company_name');
    $q->addWhere('companies.company_id = ' . $row->project_company);
    $sql = $q->prepare();
    $q->clear();
    $companies[$row->project_company] = db_loadResult($sql);
}
// get critical tasks (criteria: task_end_date)
$criticalTasks = $project_id > 0 ? $row->getCriticalTasks() : NULL;
// get ProjectPriority from sysvals
$projectPriority = dPgetSysVal('ProjectPriority');
// format dates
$df = $AppUI->getPref('SHDATEFORMAT');
$start_date = new CDate($row->project_start_date);
$end_date = intval($row->project_end_date) ? new CDate($row->project_end_date) : null;
$actual_end_date = intval($criticalTasks[0]['task_end_date']) ? new CDate($criticalTasks[0]['task_end_date']) : null;
$style = $actual_end_date > $end_date && !empty($end_date) ? 'style="color:red; font-weight:bold"' : '';
// setup the title block
$ttl = $project_id > 0 ? "Edit Project" : "New Project";
$titleBlock = new CTitleBlock($ttl, 'applet3-48.png', $m, "{$m}.{$a}");
$titleBlock->addCrumb("?m=projects", "projects list");

</td>		
	</tr>
	</table>
<?php 
    if ($log_pdf) {
        // make the PDF file
        if ($project_id) {
            $sql = "SELECT project_name FROM projects WHERE project_id={$project_id}";
            $pname = 'Project: ' . db_loadResult($sql);
        } else {
            $pname = "All Companies and All Projects";
        }
        echo db_error();
        if ($company_id) {
            $sql = "SELECT company_name FROM companies WHERE company_id={$company_id}";
            $cname = 'Company: ' . db_loadResult($sql);
        } else {
            $cname = "All Companies and All Projects";
        }
        echo db_error();
        $font_dir = dPgetConfig('root_dir') . "/lib/ezpdf/fonts";
        $temp_dir = dPgetConfig('root_dir') . "/files/temp";
        $base_url = dPgetConfig('base_url');
        require $AppUI->getLibraryClass('ezpdf/class.ezpdf');
        $pdf =& new Cezpdf();
        $pdf->ezSetCmMargins(1, 2, 1.5, 1.5);
        $pdf->selectFont("{$font_dir}/Helvetica.afm");
        $pdf->ezText(dPgetConfig('company_name'), 12);
        // $pdf->ezText( dPgetConfig( 'company_name' ).' :: '.dPgetConfig( 'page_title' ), 12 );
        $date = new CDate();
        $pdf->ezText("\n" . $date->format($df), 8);

 function upgrade($old_version)
 {
     $success = 1;
     switch ($old_version) {
         case "0.1":
             // Drop unused columns, add some new columns
             $bulk_sql[] = "\n          ALTER TABLE `helpdesk_items`\n          ADD `item_requestor_phone` varchar(30) NOT NULL default '' AFTER `item_requestor_email`,\n          ADD `item_company_id` int(11) NOT NULL default '0' AFTER `item_project_id`,\n          ADD `item_requestor_type` tinyint NOT NULL default '0' AFTER `item_requestor_phone`,\n          ADD `item_notify` int(1) DEFAULT '1' NOT NULL AFTER `item_assigned_to`,\n          ADD `item_created_by` int(11) NOT NULL default '0',\n\t\t  ADD `item_updated` datetime default NULL,\n          DROP `item_receipt_target`,\n          DROP `item_receipt_custom`,\n          DROP `item_receipted`,\n          DROP `item_resolve_target`,\n          DROP `item_resolve_custom`,\n          DROP `item_resolved`,\n          DROP `item_assetno`\n        ";
             // Add help desk item id to task log table
             $bulk_sql[] = "\n          ALTER TABLE `task_log`\n          ADD `task_log_help_desk_id` int(11) NOT NULL default '0' AFTER `task_log_task`\n        ";
             // Add help desk item status log table
             $bulk_sql[] = "\n          CREATE TABLE `helpdesk_item_status` (\n            `status_id` INT NOT NULL AUTO_INCREMENT,\n            `status_item_id` INT NOT NULL,\n            `status_code` TINYINT NOT NULL,\n            `status_date` TIMESTAMP NOT NULL,\n            `status_modified_by` INT NOT NULL,\n            `status_comment` TEXT DEFAULT '',\n            PRIMARY KEY (`status_id`)\n          )\n        ";
             // Execute the above SQL
             foreach ($bulk_sql as $s) {
                 db_exec($s);
                 if (db_error()) {
                     $success = 0;
                 }
             }
             // Add audit trail to system values
             $sql = "SELECT syskey_id\n                FROM syskeys\n                WHERE syskey_name = 'HelpDeskList'";
             $syskey_id = db_loadResult($sql);
             $sv = new CSysVal($syskey_id, 'HelpDeskAuditTrail', "0|Created\n1|Title\n2|Requestor Name\n3|Requestor E-mail\n4|Requestor Phone\n5|Assigned To\n6|Notify by e-mail\n7|Company\n8|Project\n9|Call Type\n10|Call Source\n11|Status\n12|Priority\n13|Severity\n14|Operating System\n15|Application\n16|Summary\n17|Deleted");
             $sv->store();
             // Update help desk status values
             $sql = "UPDATE sysvals\n                SET sysval_value='0|Unassigned\n1|Open\n2|Closed\n3|On Hold\n4|Testing'\n                WHERE sysval_title='HelpDeskStatus'\n                LIMIT 1";
             db_exec($sql);
             /* Get data for conversion update */
             $sql = "SELECT item_id,item_requestor_id,item_created,item_project_id\n                FROM helpdesk_items";
             $items = db_loadList($sql);
             /* Populate the status log table with the item's creation date */
             foreach ($items as $item) {
                 $timestamp = date('Ymdhis', db_dateTime2unix($item['item_created']));
                 $sql = "INSERT INTO helpdesk_item_status\n                    (status_item_id,status_code,status_date,status_modified_by)\n                  VALUES ({$item['item_id']},0,'{$timestamp}',\n                          {$item['item_requestor_id']})";
                 db_exec($sql);
             }
             /* Figure out the company for each item based on project id or based
                on requestor id */
             foreach ($items as $item) {
                 if ($item['item_project_id']) {
                     $sql = "SELECT project_company\n                    FROM projects\n                    WHERE project_id='{$item['item_project_id']}'";
                     $company_id = db_loadResult($sql);
                 } else {
                     if ($item['item_requestor_id']) {
                         $sql = "SELECT user_company\n                    FROM users\n                    WHERE user_id='{$item['item_requestor_id']}'";
                         $company_id = db_loadResult($sql);
                     }
                 }
                 if ($company_id) {
                     $sql = "UPDATE helpdesk_items\n                    SET item_company_id='{$company_id}'\n                    WHERE item_id='{$item['item_id']}'";
                     db_exec($sql);
                 }
             }
             // If our status was 5 (Testing), now it is 4 (Testing)
             $sql = "UPDATE helpdesk_items\n                SET item_status='4'\n                WHERE item_status='5'";
             db_exec($sql);
             break;
         case 0.2:
             // Version 0.3 features new permissions
             $success = 1;
             break;
         case 0.3:
             // Version 0.31 includes new watchers functionality
             $sql = "\n\t\tCREATE TABLE helpdesk_item_watchers (\n\t\t  `item_id` int(11) NOT NULL default '0',\n\t\t  `user_id` int(11) NOT NULL default '0',\n\t\t  `notify` char(1) NOT NULL default ''\n\t\t) TYPE=MyISAM";
             db_exec($sql);
         case 0.31:
             $sql = "\n          ALTER TABLE `helpdesk_items`\n\t\t  ADD `item_updated` datetime default NULL\n        ";
             db_exec($sql);
             $sql = "SELECT `item_id` FROM helpdesk_items";
             $rows = db_loadList($sql);
             $sql = '';
             foreach ($rows as $row) {
                 $sql = "SELECT MAX(status_date) status_date FROM helpdesk_item_status WHERE status_item_id =" . $row['item_id'];
                 $sdrow = db_loadList($sql);
                 $sql = '';
                 $sql = "UPDATE `helpdesk_items`\n    \t  \tSET `item_updated`='" . $sdrow[0]['status_date'] . "' \n    \t  \tWHERE `item_id`=" . $row['item_id'];
                 db_exec($sql);
             }
             if (db_error()) {
                 $success = 0;
             } else {
                 $success = 1;
             }
             break;
         default:
             $success = 0;
     }
     // NOTE: Need to return true, not null, if all is good
     return $success;
 }

function checkCompanyId($company_id)
{
    return db_loadResult("SELECT count(*) FROM companies WHERE company_id = '{$company_id}'");
}

/**
* Document::db_updateObject()
*
* { Description }
*
* @param [type] $updateNulls
*/
function db_updateObject($table, &$object, $keyName, $updateNulls = true, $descriptionField = NULL)
{
    global $AppUI;
    $perms =& $AppUI->acl();
    $fmtsql = "UPDATE `{$table}` SET %s WHERE %s";
    $obj_vars_arr = get_object_vars($object);
    foreach ($obj_vars_arr as $k => $v) {
        if (is_array($v) || is_object($v) || $k[0] == '_') {
            // internal or NA field
            continue;
        }
        if ($k == $keyName) {
            // PK not to be updated
            $where = "{$keyName}='" . db_escape($v) . "'";
            continue;
        }
        if ($v === NULL && !$updateNulls) {
            continue;
        }
        $val = $v === '' ? "''" : "'" . db_escape($v) . "'";
        $tmp[] = "{$k}={$val}";
    }
    if (count($tmp)) {
        $sql = sprintf($fmtsql, implode(",", $tmp), $where);
        $retval = db_exec($sql);
        if ($retval) {
            $perm_item_id = $perms->get_object_id($table, $obj_vars_arr[$keyName], 'axo');
            if ($perm_item_id) {
                if ($descriptionField) {
                    $keyDesc = $descriptionField;
                } else {
                    //try to get a valid label field from module table by default
                    $keyDesc = db_loadResult('SELECT permissions_item_label FROM modules' . " WHERE permissions_item_table = '" . $table . "'");
                }
                if ($keyDesc) {
                    $perms->edit_object($perm_item_id, $table, $obj_vars_arr[$keyDesc], $obj_vars_arr[$keyName], 0, 0, 'axo');
                }
            }
        }
    } else {
        $retval = true;
    }
    return $retval;
}

function getPermission($mod, $perm, $item_id = 0)
{
    // First check if the module is readable, i.e. has view permission.
    $perms =& $GLOBALS['AppUI']->acl();
    $result = $perms->checkModule($mod, $perm);
    // If we have access then we need to ensure we are not denied access to the particular
    // item.
    if ($result && $item_id) {
        if ($perms->checkModuleItemDenied($mod, $perm, $item_id)) {
            $result = false;
        }
    }
    // If denied we need to check if we are allowed the task.  This can be done
    // a lot better in PHPGACL, but is here for compatibility.
    if ($mod == 'tasks' && !$result && $item_id > 0) {
        $sql = "SELECT task_project FROM tasks WHERE task_id = {$item_id}";
        $project_id = db_loadResult($sql);
        $result = getPermission('projects', $perm, $project_id);
    }
    return $result;
}

    if (!$res) {
        $AppUI->setMsg('File could not be moved', UI_MSG_ERROR);
        $AppUI->redirect();
    }
}
if (!$file_id) {
    $obj->file_owner = $AppUI->user_id;
    if (!$obj->file_version_id) {
        $q = new DBQuery();
        $q->addTable('files');
        $q->addQuery('file_version_id');
        $q->addOrder('file_version_id DESC');
        $q->setLimit(1);
        $sql = $q->prepare();
        $q->clear();
        $latest_file_version = db_loadResult($sql);
        $obj->file_version_id = $latest_file_version + 1;
    } else {
        $q = new DBQuery();
        $q->addTable('files');
        $q->addUpdate('file_checkout', '');
        $q->addWhere("file_version_id = {$obj->file_version_id}");
        $q->exec();
        $q->clear();
    }
}
if ($msg = $obj->store()) {
    $AppUI->setMsg($msg, UI_MSG_ERROR);
} else {
    $obj->load($obj->file_id);
    if ($not == '1') {

function getPermission($mod, $perm, $item_id = 0)
{
    global $AppUI;
    $perms =& $AppUI->acl();
    // First check if the module is readable, i.e. has view permission.
    $result = $perms->checkModuleItem($mod, $perm, $item_id);
    // We need to check if we are allowed to view in the parent module item.
    // This can be done a lot better in PHPGACL, but is here for compatibility.
    if ($item_id && $perm == 'view') {
        if ($mod == 'task_log') {
            $sql = 'SELECT task_log_task FROM task_log WHERE task_log_id =' . $item_id;
            $task_id = db_loadResult($sql);
            $result = $result && getPermission('tasks', $perm, $task_id);
        } else {
            if ($mod == 'tasks') {
                $sql = 'SELECT task_project FROM tasks WHERE task_id =' . $item_id;
                $project_id = db_loadResult($sql);
                $result = $result && getPermission('projects', $perm, $project_id);
            } else {
                if ($mod == 'projects') {
                    $sql = 'SELECT project_company FROM projects WHERE project_id =' . $item_id;
                    $company_id = db_loadResult($sql);
                    $result = $result && getPermission('companies', $perm, $company_id);
                }
            }
        }
    }
    return $result;
}