PHP w2p_Database_Query::loadResult方法代碼示例

 public function add($field_name, $field_description, $field_htmltype, $field_datatype, $field_extratags, $field_order, $field_published, &$error_msg)
 {
     global $db;
     $q = new w2p_Database_Query();
     $q->addTable('custom_fields_struct');
     $q->addQuery('MAX(field_id)');
     $max_id = $q->loadResult();
     $next_id = $max_id ? $max_id + 1 : 1;
     $field_order = $field_order ? $field_order : 1;
     $field_published = $field_published ? 1 : 0;
     $field_a = 'addedit';
     // TODO - module pages other than addedit
     // TODO - validation that field_name doesnt already exist
     $q = new w2p_Database_Query();
     $q->addTable('custom_fields_struct');
     $q->addInsert('field_id', $next_id);
     $q->addInsert('field_module', $this->m);
     $q->addInsert('field_page', $field_a);
     $q->addInsert('field_htmltype', $field_htmltype);
     $q->addInsert('field_datatype', $field_datatype);
     $q->addInsert('field_order', $field_order);
     $q->addInsert('field_name', $field_name);
     $q->addInsert('field_description', $field_description);
     $q->addInsert('field_extratags', $field_extratags);
     $q->addInsert('field_order', $field_order);
     $q->addInsert('field_published', $field_published);
     if (!$q->exec()) {
         $error_msg = $db->ErrorMsg();
         $q->clear();
         return 0;
     } else {
         $q->clear();
         return $next_id;
     }
 }

 public function write($id, $data)
 {
     global $AppUI;
     $q = new w2p_Database_Query();
     $q->addQuery('count(session_id) as row_count');
     $q->addTable('sessions');
     $q->addWhere('session_id = \'' . $id . '\'');
     $row_count = (int) $q->loadResult();
     $q->clear();
     if ($row_count) {
         $q->addTable('sessions');
         $q->addWhere('session_id = \'' . $id . '\'');
         $q->addUpdate('session_data', $data);
         if (isset($AppUI)) {
             $q->addUpdate('session_user', (int) $AppUI->last_insert_id);
         }
     } else {
         $q->addTable('sessions');
         $q->addInsert('session_id', $id);
         $q->addInsert('session_data', $data);
         $q->addInsert('session_created', $q->dbfnNowWithTZ());
     }
     $q->exec();
     $q->clear();
     return true;
 }

 public function _fetchPreviousData()
 {
     $q = new w2p_Database_Query();
     $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;
 }

 public function store()
 {
     if (!is_array($this->options)) {
         $this->options = array();
     }
     $newoptions = $this->options;
     //insert the new option
     foreach ($newoptions as $opt) {
         $q = new w2p_Database_Query();
         $q->addTable('custom_fields_lists');
         $q->addQuery('MAX(list_option_id)');
         $max_id = $q->loadResult();
         $optid = $max_id ? $max_id + 1 : 1;
         $q = new w2p_Database_Query();
         $q->addTable('custom_fields_lists');
         $q->addInsert('field_id', $this->field_id);
         $q->addInsert('list_option_id', $optid);
         $q->addInsert('list_value', $opt);
         $q->exec();
     }
 }

<?php

/* $Id$ $URL$ */
if (!defined('W2P_BASE_DIR')) {
    die('You should not access this file directly.');
}
// Output the PDF
// make the PDF file
if ($project_id != 0) {
    $q = new w2p_Database_Query();
    $q->addTable('projects');
    $q->addQuery('project_name');
    $q->addWhere('project_id=' . $project_id);
    $pname = 'Project: ' . $q->loadResult();
} else {
    $pname = $AppUI->_('All Projects');
}
if ($err = db_error()) {
    $AppUI->setMsg($err, UI_MSG_ERROR);
    $AppUI->redirect();
}
$font_dir = W2P_BASE_DIR . '/lib/ezpdf/fonts';
require $AppUI->getLibraryClass('ezpdf/class.ezpdf');
$pdf = new Cezpdf($paper = 'A4', $orientation = 'landscape');
$pdf->ezSetCmMargins(1, 2, 1.5, 1.5);
$pdf->selectFont($font_dir . '/Helvetica.afm');
$pdf->ezText(utf8_decode(w2PgetConfig('company_name')), 12);
$date = new w2p_Utilities_Date();
$pdf->ezText("\n" . $date->format($df), 8);
$next_week = new w2p_Utilities_Date($date);
$next_week->addSpan(new Date_Span(array(7, 0, 0, 0)));

 public static function getHolidayTitle($date = 0, $userid = 0)
 {
     global $AppUI;
     self::loadHolidaysSettings();
     if (!$date) {
         $date = new w2p_Utilities_Date();
     }
     if (self::$holiday_manual) {
         $q = new w2p_Database_Query();
         // Check if we have a whitelist item for this date
         $q->addTable("holiday");
         $q->addQuery("holiday_description");
         $where = "( date(holiday_start_date) <= '";
         $where .= $date->format('%Y-%m-%d');
         $where .= "' AND date(holiday_end_date) >= '";
         $where .= $date->format('%Y-%m-%d');
         if ($userid > 0) {
             $where .= "' AND (";
             $where .= "(holiday_user=0 AND holiday_type=" . HOLIDAY_TYPE_COMPANY_HOLIDAY . ")";
             $where .= " OR ";
             $where .= "(holiday_user=" . $userid . " AND holiday_type=" . HOLIDAY_TYPE_USER_HOLIDAY . ")";
             $where .= ")";
         } else {
             $where .= "' AND holiday_type=" . HOLIDAY_TYPE_COMPANY_HOLIDAY;
         }
         $where .= " ) OR ( ";
         $where .= " DATE_FORMAT(holiday_start_date, '%m-%d') <= '";
         $where .= $date->format('%m-%d');
         $where .= "' AND DATE_FORMAT(holiday_end_date, '%m-%d') >= '";
         $where .= $date->format('%m-%d');
         $where .= "' AND holiday_annual=1";
         $where .= " AND holiday_type=" . HOLIDAY_TYPE_COMPANY_HOLIDAY . " )";
         $q->addWhere($where);
         $holiday_description = $q->loadResult();
         if ($holiday_description !== false) {
             return $holiday_description;
         }
     }
     if (self::$holiday_auto && self::$holiday_driver >= 0) {
         // Still here? Ok, lets poll the automatic system
         if (self::$holiday_driver_instance->getYear() != $date->getYear()) {
             self::$holiday_driver_instance->setYear($date->getYear());
             self::$holiday_driver_instance->setLocale($AppUI->user_locale);
         }
         if (!Date_Holidays::isError(self::$holiday_driver_instance)) {
             $holidays = self::$holiday_driver_instance->getHolidayForDate($date, null, true);
             if (!is_null($holidays)) {
                 $titles = array();
                 foreach ($holidays as $holiday) {
                     if (is_null(self::$holiday_filter_instance) || self::$holiday_filter_instance->accept($holiday->getInternalName())) {
                         $title = $holiday->getTitle();
                         if (!in_array($title, $titles)) {
                             $titles[] = gettype($title) == 'object' ? $title->getMessage() : $title;
                         }
                     }
                 }
                 return implode("/", $titles);
             }
         }
     }
     return "";
 }

function getHelpdeskFolder()
{
    $q = new w2p_Database_Query();
    $q->addTable('file_folders', 'ff');
    $q->addQuery('file_folder_id');
    $q->addWhere('ff.file_folder_name = \'Helpdesk\'');
    $ffid = $q->loadResult();
    $q->clear();
    return intval($ffid);
}

 public static function updateHoursWorked($taskId, $totalHours)
 {
     $q = new w2p_Database_Query();
     $q->addTable('tasks');
     $q->addUpdate('task_hours_worked', $totalHours + 0);
     $q->addWhere('task_id = ' . $taskId);
     $q->exec();
     $q->clear();
     $q->addTable('tasks');
     $q->addQuery('task_project');
     $q->addWhere('task_id = ' . $taskId);
     $project_id = $q->loadResult();
     CProject::updateHoursWorked($project_id);
 }

 foreach ($user_list as $user_id => $user) {
     $q->addTable('user_tasks', 'ut');
     $q->addQuery('task_id');
     $q->addWhere('user_id = ' . (int) $user_id);
     $tasks_id = $q->loadColumn();
     $q->clear();
     $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
             $q->addTable('task_log');
             $q->addQuery('SUM(task_log_hours)');
             $q->addWhere('task_log_task =' . (int) $task_id);
             $q->addWhere('task_log_creator =' . (int) $user_id);
             $hours_worked = round($q->loadResult(), 2);
             $q->clear();
             $q->addTable('tasks');
             $q->addQuery('task_percent_complete');
             $q->addWhere('task_id =' . (int) $task_id);
             $percent = $q->loadColumn();
             $q->clear();
             $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;
         }
     }

function get_actual_end_date_pd($task_id, $task)
{
    global $AppUI;
    $q = new w2p_Database_Query();
    $mods = $AppUI->getActiveModules();
    if (!empty($mods['history']) && canView('history')) {
        $q->addQuery('MAX(history_date) as actual_end_date');
        $q->addTable('history');
        $q->addWhere('history_table=\'tasks\' AND history_item=' . $task_id);
    } else {
        $q->addQuery('MAX(task_log_date) AS actual_end_date');
        $q->addTable('task_log');
        $q->addWhere('task_log_task = ' . (int) $task_id);
    }
    $task_log_end_date = $q->loadResult();
    $edate = $task_log_end_date;
    $edate = $edate > $task->task_end_date || $task->task_percent_complete == 100 ? $edate : $task->task_end_date;
    return $edate;
}

function showcompany($company, $restricted = false)
{
    global $AppUI, $allpdfdata, $log_start_date, $log_end_date, $log_all;
    $q = new w2p_Database_Query();
    $q->addTable('projects');
    $q->addQuery('project_id, project_name');
    $q->addWhere('project_company = ' . (int) $company);
    $projects = $q->loadHashList();
    $q->clear();
    $q->addTable('companies');
    $q->addQuery('company_name');
    $q->addWhere('company_id = ' . (int) $company);
    $company_name = $q->loadResult();
    $q->clear();
    $table = '<h2>Company: ' . $company_name . '</h2>
    	<table cellspacing="1" cellpadding="4" border="0" class="tbl">';
    $project_row = '
        <tr>
                <th>' . $AppUI->_('Project') . '</th>';
    $pdfth[] = $AppUI->_('Project');
    $project_row .= '<th>' . $AppUI->_('Total') . '</th></tr>';
    $pdfth[] = $AppUI->_('Total');
    $pdfdata[] = $pdfth;
    $hours = 0.0;
    $table .= $project_row;
    foreach ($projects as $project => $name) {
        $pdfproject = array();
        $pdfproject[] = $name;
        $project_hours = 0;
        $project_row = '<tr><td>' . $name . '</td>';
        $q->addTable('projects');
        $q->addTable('tasks');
        $q->addTable('task_log');
        $q->addQuery('task_log_costcode, SUM(task_log_hours) as hours');
        $q->addWhere('project_id = ' . (int) $project);
        $q->addWhere('project_active = 1');
        if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
            $q->addWhere('project_status <> ' . (int) $template_status);
        }
        if ($log_start_date != 0 && !$log_all) {
            $q->addWhere('task_log_date >=' . $log_start_date);
        }
        if ($log_end_date != 0 && !$log_all) {
            $q->addWhere('task_log_date <=' . $log_end_date);
        }
        if ($restricted) {
            $q->addWhere('task_log_creator = ' . (int) $AppUI->user_id);
        }
        $q->addWhere('project_id = task_project');
        $q->addWhere('task_id = task_log_task');
        $q->addGroup('project_id');
        $task_logs = $q->loadHashList();
        $q->clear();
        foreach ($task_logs as $task_log) {
            $project_hours += $task_log;
        }
        $project_row .= '<td style="text-align:right;">' . sprintf('%.2f', round($project_hours, 2)) . '</td></tr>';
        $pdfproject[] = round($project_hours, 2);
        $hours += $project_hours;
        if ($project_hours > 0) {
            $table .= $project_row;
            $pdfdata[] = $pdfproject;
        }
    }
    if ($hours > 0) {
        $pdfdata[] = array($AppUI->_('Total'), round($hours, 2));
        $allpdfdata[$company_name] = $pdfdata;
        echo $table;
        echo '<tr><td>' . $AppUI->_('Total') . '</td><td style="text-align:right;">' . sprintf('%.2f', round($hours, 2)) . '</td></tr></table>';
    }
    return $hours;
}

function sendNewPass()
{
    global $AppUI;
    $_live_site = w2PgetConfig('base_url');
    $_sitename = w2PgetConfig('company_name');
    // ensure no malicous sql gets past
    $checkusername = trim(w2PgetParam($_POST, 'checkusername', ''));
    $checkusername = db_escape($checkusername);
    $confirmEmail = trim(w2PgetParam($_POST, 'checkemail', ''));
    $confirmEmail = strtolower(db_escape($confirmEmail));
    $q = new w2p_Database_Query();
    $q->addTable('users');
    $q->addJoin('contacts', 'con', 'user_contact = contact_id', 'inner');
    $q->addQuery('user_id');
    $q->addWhere('user_username = \'' . $checkusername . '\'');
    /* Begin Hack */
    /*
     * This is a particularly annoying hack but I don't know of a better
     *   way to resolve #457. In v2.0, there was a refactoring to allow for
     *   muliple contact methods which resulted in the contact_email being
     *   removed from the contacts table. If the user is upgrading from
     *   v1.x and they try to log in before applying the database, crash.
     *   Info: http://bugs.web2project.net/view.php?id=457
     */
    $qTest = new w2p_Database_Query();
    $qTest->addTable('w2pversion');
    $qTest->addQuery('max(db_version)');
    $dbVersion = $qTest->loadResult();
    if ($dbVersion >= 21 && $dbVersion < 26) {
        $q->leftJoin('contacts_methods', 'cm', 'cm.contact_id = con.contact_id');
        $q->addWhere("cm.method_value = '{$confirmEmail}'");
    } else {
        $q->addWhere("LOWER(contact_email) = '{$confirmEmail}'");
    }
    /* End Hack */
    if (!($user_id = $q->loadResult()) || !$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 w2p_Utilities_Mail();
    // create the mail
    $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);
    $q->addTable('users');
    $q->addUpdate('user_password', $newpass);
    $q->addWhere('user_id=' . $user_id);
    $cur = $q->exec();
    if (!$cur) {
        die('SQL error' . $database->stderr(true));
    } else {
        $AppUI->setMsg('New User Password created and emailed to you');
        $AppUI->redirect();
    }
}

     $q->clear();
     $q->updateArray($sql_table, $pairs, 'contact_id');
     $q->clear();
     $s .= '<td><span style="color:#880000;">There is a duplicate record for ' . $pairs['contact_first_name'] . ' ' . $pairs['contact_last_name'] . ', the record has been updated.</span></td>';
 } else {
     //If the contact has no name, go to the next
     if (!mb_trim($pairs['contact_first_name'] . ' ' . $pairs['contact_last_name'])) {
         continue;
     }
     $s .= '<td>Adding ' . $pairs['contact_first_name'] . ' ' . $pairs['contact_last_name'] . '.</td>';
     //Try to find a matching company name in the system, if not them set contact_company to 0
     $q = new w2p_Database_Query();
     $q->addQuery('company_id');
     $q->addTable('companies');
     $q->addWhere('company_name LIKE \'' . mb_trim($pairs['contact_company']) . '\'');
     $company_id = $q->loadResult();
     $pairs['contact_company'] = $company_id ? $company_id : 0;
     $q->clear();
     //Try to find a matching department name in the system, if not them set contact_department to 0
     $q->addQuery('dept_id');
     $q->addTable('departments');
     $q->addWhere('dept_name LIKE \'' . mb_trim($pairs['contact_department']) . '\'');
     $dept_id = $q->loadResult();
     $pairs['contact_department'] = $dept_id ? $dept_id : 0;
     $q->clear();
     $contact_id = $q->insertArray($sql_table, $pairs);
     $q->clear();
     foreach ($contact_array as $name => $value) {
         $q->addTable('contacts_methods');
         $q->addInsert('contact_id', $contact_id);
         $q->addInsert('method_name', $name);

// setup the title block
$titleBlock = new CTitleBlock('Checkout', 'folder5.png', $m, "{$m}.{$a}");
$titleBlock->addCrumb('?m=files', 'files list');
$titleBlock->show();
if ($obj->file_project) {
    $file_project = $obj->file_project;
}
if ($obj->file_task) {
    $file_task = $obj->file_task;
    $task_name = $obj->getTaskName();
} elseif ($file_task) {
    $q = new w2p_Database_Query();
    $q->addTable('tasks');
    $q->addQuery('task_name');
    $q->addWhere('task_id=' . (int) $file_task);
    $task_name = $q->loadResult();
    $q->clear();
} else {
    $task_name = '';
}
$extra = array('where' => 'project_active<>0');
$project = new CProject();
$projects = $project->getAllowedRecords($AppUI->user_id, 'projects.project_id,project_name', 'project_name', null, $extra, 'projects');
$projects = arrayMerge(array('0' => $AppUI->_('All')), $projects);
?>

<script language="javascript" type="text/javascript">
function popFile( params ) {
    fileloader = window.open("fileviewer.php?"+params,"mywindow","location=1,status=1,scrollbars=0,width=80,height=80");
    fileloader.moveTo(0,0);
}

/* $Id$ $URL$ */
if (!defined('W2P_BASE_DIR')) {
    die('You should not access this file directly.');
}
$perms =& $AppUI->acl();
if (!canEdit('system')) {
    $AppUI->redirect('m=public&a=access_denied');
}
$obj = new CConfig();
// set all checkboxes to false
// overwrite the true/enabled/checked checkboxes later
$q = new w2p_Database_Query();
$q->addTable('config');
$q->addUpdate('config_value', 'false');
$q->addWhere("config_type = 'checkbox'");
$rs = $q->loadResult();
$q->clear();
foreach ($_POST['w2Pcfg'] as $name => $value) {
    $obj->config_name = $name;
    $obj->config_value = $value;
    // grab the appropriate id for the object in order to ensure
    // that the db is updated well (config_name must be unique)
    $obj->config_id = $_POST['w2PcfgId'][$name];
    $update = false;
    // This is really kludgy, but it works.. suggestions?
    if (strpos($name, '_pass') !== false) {
        if (1 == $_POST[$name . '_mod']) {
            $update = true;
        }
    } else {
        $update = true;