PHP process_sql函数代码示例

function drop_old_sla_table()
{
    echo "Deleting the table 'tincident_sla_graph'...\n";
    $sql = "DROP TABLE IF EXISTS tincident_sla_graph";
    process_sql($sql);
    echo "Deleting the table 'tincident_sla_graph'... DONE\n";
}

function workorders_insert_note($id, $user, $note, $date)
{
    $sql = sprintf('INSERT INTO ttodo_notes (`id_todo`,`written_by`,`description`, `creation`)
                                        VALUES (%d, "%s", "%s", "%s")', $id, $user, $note, $date);
    $res = process_sql($sql, 'insert_id');
    mail_workorder($id, 4, $res);
    return $res;
}

function mysql_session_gc($maxlifetime = 300)
{
    global $config;
    if (isset($config['session_timeout'])) {
        $maxlifetime = $config['session_timeout'];
    }
    $CutoffTime = time() - $maxlifetime;
    $retval = process_sql("DELETE FROM tsessions_php \n\t\t\tWHERE last_active < {$CutoffTime}");
    return $retval;
}

		SET timestamp = "%s", duration = %.2f, description = "%s",
		have_cost = %d, id_profile = %d, public = %d, id_user = "%s",
		work_home = %d 
		WHERE id = %d',
		$timestamp, $duration, $description, $have_cost,
		$id_profile, $public, $wu_user, $work_home, $id_workunit);
	$result = process_sql ($sql);

	if ($id_task !=0) {
	    // Old old association
	    process_sql ("DELETE FROM tworkunit_task WHERE id_workunit = $id_workunit");
	    // Create new one
            $sql = sprintf ('INSERT INTO tworkunit_task
                            (id_task, id_workunit) VALUES (%d, %d)',
                                        $id_task, $id_workunit);
            $result = process_sql ($sql, 'insert_id');
	}
	$result_output = ui_print_success_message (__('Workunit updated'), '', true, 'h3', true);
	audit_db ($config["id_user"], $config["REMOTE_ADDR"], "PWU", "Updated PWU. $description");
	
	if ($result !== false) {
		set_task_completion ($id_task);
	}
}

$multiple_wu_report = array();

if ($operation == 'multiple_wu_insert') {
	
	//Walk post array looking for 
	$i = 1;

    if ($id_parent == 0) {
        $idParentValue = 'NULL';
    } else {
        $idParentValue = sprintf('%d', $id_parent);
    }
    // DONT use MySQL NOW() or UNIXTIME_NOW() because
    // Integria can override localtime zone by a user-specified timezone.
    $timestamp = print_mysql_timestamp();
    $sql = sprintf('INSERT INTO tincidencia
			(inicio, actualizacion, titulo, descripcion,
			id_usuario, estado, prioridad,
			id_grupo, id_creator, notify_email, id_task,
			resolution, id_incident_type, sla_disabled, email_copy, epilog)
			VALUES ("%s", "%s", "%s", "%s", "%s", %d, %d, %d, "%s",
			%d, %d, %d, %d, %d, "%s", "%s")', $timestamp, $timestamp, $title, $description, $id_user_responsible, $estado, $priority, $group_id, $id_creator, $email_notify, $id_task, $resolution, $id_incident_type, $sla_disabled, $email_copy, $epilog);
    $id = process_sql($sql, 'insert_id');
    if ($id !== false) {
        /* Update inventory objects in incident */
        update_incident_inventories($id, array($id_inventory));
        $result_msg = ui_print_success_message(__('Successfully created') . ' (id #' . $id . ')', '', true);
        $result_msg .= '<h4><a href="index.php?sec=incidents&sec2=operation/incidents_simple/incident&id=' . $id . '">' . __('Please click here to continue working with ticket #') . $id . "</a></h4>";
        audit_db($config["id_user"], $config["REMOTE_ADDR"], "Ticket created", "User " . $config['id_user'] . " created ticket #" . $id);
        incident_tracking($id, INCIDENT_CREATED);
        //Add traces and statistic information
        incidents_set_tracking($id, 'create', $priority, $estado, $resolution, $id_user_responsible, $group_id);
        // Email notify to all people involved in this incident
        if ($email_notify) {
            mail_incident($id, $id_user_responsible, "", 0, 1);
        }
        //insert data to incident type fields
        if ($id_incident_type > 0) {

function calendar_get_users_holidays_date_range($start, $end, $id_user)
{
    $mysql_start = date('Y-m-d', $start);
    $mysql_end = date('Y-m-d', $end);
    $user_clause = "";
    if (is_array($id_user)) {
        $aux_ids = array();
        foreach ($id_user as $iu) {
            array_push($aux_ids, "'" . $iu . "'");
        }
        $user_clause = "id_user IN (" . join(",", $aux_ids) . ")";
    } else {
        $user_clause = "id_user = '{$id_user}'";
    }
    $sql = sprintf("SELECT tworkunit.timestamp AS date, tworkunit.id_user as user FROM tworkunit, tworkunit_task WHERE \n\t\t\t\t\t{$user_clause} AND tworkunit_task.id_workunit = tworkunit.id AND tworkunit_task.id_task =-1 AND \n\t\t\t\t\ttimestamp >= '{$mysql_start}' AND timestamp <= '{$mysql_end}' ORDER BY date ASC");
    $res = process_sql($sql);
    if (!$res) {
        $res = array();
    }
    $holidays = array();
    $holidays_counters = array();
    $holidays_last_day = array();
    foreach ($res as $r) {
        if (!isset($holidays[$r["user"]])) {
            $holidays[$r["user"]] = array();
            $holidays_counters[$r["user"]] = -1;
            $holidays_last_day[$r["user"]] = 0;
        }
        //Calculate start and end for the holiday value
        if (isset($holidays_last_day[$r["user"]])) {
            //Last day minus current day is 1 we increase dates for stored holidays
            $last_time = strtotime($holidays_last_day[$r["user"]]);
            $current_time = strtotime($r["date"]);
            $day_in_seconds = 24 * 3600;
            if ($current_time - $last_time <= $day_in_seconds) {
                $pos = $holidays_counters[$r["user"]];
                $holidays[$r["user"]][$pos]["end"] = strtotime($r["date"]);
                $holidays_last_day[$r["user"]] = strtotime($r["date"]);
            } else {
                array_push($holidays[$r["user"]], array("start" => strtotime($r["date"]), "end" => strtotime($r["date"])));
                $holidays_last_day[$r["user"]] = $r["date"];
                $holidays_counters[$r["user"]]++;
            }
        }
        //Update last day
        $holidays_last_day[$r["user"]] = $r["date"];
    }
    $full_holidays = array();
    $colors = array("#aa3333", "#33aa33", "#3a3a3a", "#3333aa", "#045FB4", "#DF7401", "#01DF3A", "#BE81F7", "#8181F7", "#81BEF7", "#F781F3", "#F7D358", "#F78181", "#FA8258", "#BCF5A9", "#A9A9F5", "#D0A9F5", "#E2A9F3", "#A9F5D0", "#A9F5E1", "#BDBDBD", "#E6E6E6", "#F6CECE", "#6AA4B2", "#6AB277", "#B28E6A", "#B26A97", "#776AB2", "#A58EB4", "#8EB490", "#8E96B4", "#B48E98");
    $i = 0;
    $colors_size = count($colors);
    foreach ($holidays as $key => $values) {
        $i = $i % $colors_size;
        $name = get_db_value("nombre_real", "tusuario", "id_usuario", $key);
        $url = "index.php?sec=users&sec2=operation/user_report/holidays_calendar&custom_dates=1&id_user=" . $key;
        array_push($full_holidays, array("name" => safe_output($name), "dates" => $values, "bgColor" => $colors[$i], "link" => $url));
        $i++;
    }
    return $full_holidays;
}

    } else {
        foreach ($all_people as $person) {
            $result = explode('/', $person);
            $id_user = $result[0];
            $id_role = $result[1];
            $filter['id_role'] = $id_role;
            $filter['id_user'] = $id_user;
            $filter['id_task'] = $id_task;
            $role_name = get_db_value('name', 'trole', 'id', $id_role);
            $result_sql = get_db_value_filter('id_user', 'trole_people_task', $filter);
            if ($result_sql !== false) {
                echo "<h3 class='error'>" . __('Not created. Role already exists: ') . $id_user . ' / ' . $role_name . "</h3>";
            } else {
                $sql = "INSERT INTO trole_people_task\n\t\t\t\t\t(id_task, id_user, id_role) VALUES\n\t\t\t\t\t({$id_task}, '{$id_user}', '{$id_role}')";
                task_tracking($id_task, TASK_MEMBER_ADDED);
                $id_task_inserted = process_sql($sql, 'insert_id');
                if ($id_task_inserted !== false) {
                    $result_output .= "<h3 class='suc'>" . __('Successfully created: ') . $id_user . ' / ' . $role_name . "</h3>";
                    audit_db($config["id_user"], $config["REMOTE_ADDR"], "User/Role added to task", "User {$id_user} added to task " . get_db_value("name", "ttask", "id", $id_task));
                } else {
                    $update_mode = 0;
                    $create_mode = 1;
                    $result_output .= "<h3 class='error'>" . __('Not created. Error inserting data: ') . $id_user . ' / ' . $role_name . "</h3>";
                }
            }
        }
    }
}
// ---------------------
// Edition / View mode
// ---------------------

function um_component_is_blacklisted($component, $name)
{
    global $config;
    switch ($config["dbtype"]) {
        case "mysql":
            $result = process_sql('SELECT COUNT(*) AS blacklisted FROM ' . DB_PREFIX . 'tupdate_component_blacklist WHERE component = "' . $component->name . '" AND name = "' . $name . '"');
            break;
        case "postgresql":
        case "oracle":
            $result = process_sql('SELECT COUNT(*) AS blacklisted
				FROM ' . DB_PREFIX . 'tupdate_component_blacklist
				WHERE component = \'' . $component->name . '\' AND name = \'' . $name . '\'');
            break;
    }
    if ($result === false) {
        echo '<strong>Error getting blacklist item</strong> <br />';
        return false;
    }
    $retval = um_std_from_result($result);
    return $retval->blacklisted ? true : false;
}

 public function deleteWorkOrder($id_workorder)
 {
     $result = process_sql("DELETE FROM ttodo WHERE id = {$id_workorder}");
     if ($result) {
         return true;
     }
     return false;
 }

function sql_insert($table, $values)
{
    //Empty rows or values not processed
    if (empty($values)) {
        return false;
    }
    $values = (array) $values;
    $query = sprintf("INSERT INTO `%s` ", $table);
    $fields = array();
    $values_str = '';
    $i = 1;
    $max = count($values);
    foreach ($values as $field => $value) {
        //Add the correct escaping to values
        if ($field[0] != "`") {
            $field = "`" . $field . "`";
        }
        array_push($fields, $field);
        if (is_null($value)) {
            $values_str .= "NULL";
        } elseif (is_int($value) || is_bool($value)) {
            $values_str .= sprintf("%d", $value);
        } else {
            if (is_float($value) || is_double($value)) {
                $values_str .= sprintf("%f", $value);
            } else {
                $values_str .= sprintf("'%s'", $value);
            }
        }
        if ($i < $max) {
            $values_str .= ",";
        }
        $i++;
    }
    $query .= '(' . implode(', ', $fields) . ')';
    $query .= ' VALUES (' . $values_str . ')';
    return process_sql($query, 'insert_id');
}

}

// ---------------
// Set progress
// ---------------

if ($set_progress > -1 ) {
	$todo = get_db_row ("ttodo", "id", get_parameter ("id"));

	if (! get_workorder_acl($todo["id"])) {
		no_permission();
	}
	
	$datetime = date ("Y-m-d H:i:s");
	$sql_update = "UPDATE ttodo SET progress = $set_progress, last_update = '$datetime' WHERE id = ".$todo["id"];
	$result = process_sql($sql_update);
}

// ---------------
// CREATE new todo (form)
// ---------------

if ($operation == "create" || $operation == "update" || $operation == "view")  {
	if ($operation == "create") {
		echo "<h2>".__('New Work order')."</h2><h4>".__('Add work')."</h4>";
		
		$progress = 0;
		$priority = 2;
		$name = '';
		$description = '';
		$creator = $config["id_user"];

function user_search_result($filter, $ajax, $size_page, $offset, $clickin, $search_text, $disabled_user, $level, $group, $from_tickets = false)
{
    global $config;
    if ($filter != 0) {
        $offset = $filter['offset'];
        $search_text = $filter['search_text'];
        $disabled_user = $filter['disabled_user'];
        $level = $filter['level'];
        $group = $filter['group'];
    }
    $search = "WHERE 1=1 ";
    if ($search_text != "") {
        $search .= " AND (id_usuario LIKE '%{$search_text}%' OR comentarios LIKE '%{$search_text}%' OR nombre_real LIKE '%{$search_text}%' OR direccion LIKE '%{$search_text}%')";
    }
    if ($disabled_user > -1) {
        $search .= " AND disabled = {$disabled_user}";
    }
    if ($level > -10) {
        $search .= " AND nivel = {$level}";
    }
    if ($group == -1) {
        $search .= " AND tusuario.id_usuario NOT IN (select id_usuario from tusuario_perfil)";
    } else {
        if ($group > 0) {
            $search .= " AND tusuario.id_usuario = ANY (SELECT id_usuario FROM tusuario_perfil WHERE id_grupo = {$group})";
        }
    }
    $query1 = "SELECT * FROM tusuario {$search} ORDER BY id_usuario";
    if ($from_tickets) {
        $query1 = users_get_allowed_users_query($config['id_user'], $filter);
    }
    $count = get_db_sql("SELECT COUNT(id_usuario) FROM tusuario {$search} ");
    $sql1 = "{$query1} LIMIT {$offset}, " . $size_page;
    echo "<div class='divresult'>";
    pagination($count, "index.php?sec=users&sec2=godmode/usuarios/lista_usuarios&search_text=" . $search_text . "&disabled_user=" . $disabled_user . "&level=" . $level . "&group=" . $group, $offset, true);
    $resq1 = process_sql($sql1);
    if (!$resq1) {
        echo ui_print_error_message(__("No users"), '', true, 'h3', true);
    } else {
        echo '<table width="100%" class="listing">';
        if ($filter == 0) {
            echo '<th>' . print_checkbox('all_user_checkbox', 1, false, true);
            echo '<th title="' . __('Enabled/Disabled') . '">' . __('E/D');
            echo '<th title="' . __('Enabled login') . '">' . __('Enabled login');
        }
        echo '<th>' . __('User ID');
        echo '<th>' . __('Name');
        echo '<th>' . __('Company');
        echo '<th>' . __('Last contact');
        echo '<th>' . __('Profile');
        if ($filter == 0) {
            echo '<th>' . __('Delete');
        }
        // Init vars
        $nombre = "";
        $nivel = "";
        $comentarios = "";
        $fecha_registro = "";
        if ($resq1) {
            foreach ($resq1 as $rowdup) {
                $nombre = $rowdup["id_usuario"];
                $nivel = $rowdup["nivel"];
                $realname = $rowdup["nombre_real"];
                $fecha_registro = $rowdup["fecha_registro"];
                $avatar = $rowdup["avatar"];
                if ($rowdup["nivel"] == 0) {
                    $nivel = "<img src='images/group.png' title='" . __("Grouped user") . "'>";
                } elseif ($rowdup["nivel"] == 1) {
                    $nivel = "<img src='images/integria_mini_logo.png' title='" . __("Administrator") . "'>";
                } else {
                    $nivel = "<img src='images/user_gray.png' title='" . __("Standalone user") . "'>";
                }
                $disabled = $rowdup["disabled"];
                $id_company = $rowdup["id_company"];
                $enabled_login = $rowdup["enable_login"];
                echo "<tr>";
                if ($filter == 0) {
                    echo "<td>";
                    echo print_checkbox_extended("user-" . $rowdup["id_usuario"], $rowdup["id_usuario"], false, false, "", "class='user_checkbox'", true);
                    echo "<td>";
                    if ($disabled == 1) {
                        echo "<img src='images/lightbulb_off.png' title='" . __("Disabled") . "'> ";
                    }
                    echo "<td>";
                    if ($enabled_login == 1) {
                        echo "<img src='images/accept.png' title='" . __("Enabled login") . "'> ";
                    } else {
                        echo "<img src='images/fail.png' title='" . __("Disabled login") . "'> ";
                    }
                }
                echo "<td>";
                if ($filter == 0) {
                    echo "<a href='index.php?sec=users&sec2=godmode/usuarios/configurar_usuarios&update_user=" . $nombre . "'>" . ucfirst($nombre) . "</a>";
                } else {
                    $url = "javascript:loadContactUser(\"" . $nombre . "\",\"" . $clickin . "\");";
                    echo "<a href='" . $url . "'>" . ucfirst($nombre) . "</a>";
                }
                echo "<td style=''>" . $realname;
                $company_name = (string) get_db_value('name', 'tcompany', 'id', $id_company);
                echo "<td>" . $company_name . "</td>";
//.........这里部分代码省略.........

		// Todo: Delete the invoice files from disk
		if ($invoice["id_attachment"] != ""){
			process_sql ("DELETE FROM tattachment WHERE id_attachment = ". $invoice["id_attachment"]);
		}
		$res = process_sql ("DELETE FROM tinvoice WHERE id = $id_invoice");
		if ($res > 0) {
			$company_name = get_db_value('name', 'tcompany', 'id', $invoice['id_company']);
			audit_db ($config["id_user"], $config["REMOTE_ADDR"], "Invoice deleted", "Invoice Bill ID: ".$invoice['bill_id'].", Company: $company_name");
			
			//update last activity
			$datetime =  date ("Y-m-d H:i:s");
			$comments = __("Invoice deleted by ".$config['id_user']);
			$sql_add = sprintf ('INSERT INTO tcompany_activity (id_company, written_by, date, description) VALUES (%d, "%s", "%s", "%s")', $invoice['id_company'], $config["id_user"], $datetime, $comments);
			process_sql ($sql_add);
			$sql_activity = sprintf ('UPDATE tcompany SET last_update = "%s" WHERE id = %d', $datetime, $invoice['id_company']);
			$result_activity = process_sql ($sql_activity);
		}
	}
}

// Lock/Unlock INVOICE
// ----------------
if ($lock_invoice == 1 && $id_invoice) {
	
	$locked = crm_is_invoice_locked ($id_invoice);
	$res = crm_change_invoice_lock ($config["id_user"], $id_invoice);
	
	if ($res === -1) { // -1 equals to false permission to lock or unlock the invoice
		audit_db ($config["id_user"], $config["REMOTE_ADDR"], "ACL Violation", "Trying to lock/unlock an invoice");
		include ("general/noaccess.php");
		exit;

/**
 * This function returns an array with the descendants ids of the
 * company id passed as an argument.
 */
function get_company_descendants($id_company)
{
    global $config;
    $text_id_companies = "";
    $id_companies = array();
    if (is_array($id_company)) {
        for ($i = 0; $i < count($id_company); $i++) {
            $text_id_companies .= $id_company[$i];
            if ($i < count($id_company) - 1) {
                $text_id_companies .= ", ";
            }
        }
    } else {
        $text_id_companies .= $id_company;
    }
    $sql = "SELECT id FROM tcompany WHERE id_parent IN (" . $text_id_companies . ")";
    $result = process_sql($sql);
    foreach ($result as $row) {
        $id_companies[] = $row['id'];
    }
    if (count($id_companies) >= 1) {
        $id_companies = array_merge($id_companies, get_company_descendants($id_companies));
    }
    return $id_companies;
}

/**
 * This function deletes tevent workflow data with more than X days.
 */
function delete_old_workflow_event_data()
{
    global $config;
    $DELETE_DAYS = (int) $config["max_days_workflow_events"];
    if ($DELETE_DAYS > 0) {
        $limit = date("Y/m/d H:i:s", strtotime("now") - $DELETE_DAYS * 86400);
        $sql = "DELETE FROM tevent WHERE timestamp < '{$limit}'\n\t\t\tAND `type` LIKE '%WORKFLOW%'";
        process_sql($sql);
    }
}