function write_system_roles($roles, $system_id)
{
global $c, $session;
$users = "";
$role_update = "";
$system_id = intval($system_id);
foreach ($roles as $user_no => $role_code) {
if ($role_code != "") {
$user_no = intval($user_no);
$users .= ("{$users}" == "" ? "" : ",") . $user_no;
$role_update .= "SELECT set_system_role({$user_no},{$system_id}," . qpg($role_code) . ");";
}
}
if ($users == "") {
$sql = "DELETE FROM system_usr WHERE system_id = {$system_id};";
} else {
$sql = "BEGIN; DELETE FROM system_usr WHERE system_id = {$system_id} AND user_no NOT IN ( {$users} ); {$role_update} COMMIT;";
}
$q = new PgQuery($sql);
if ($q->Exec("SystemUsers::Write")) {
$c->messages[] = "System Roles updated.";
} else {
$c->messages[] = "There was a system problem writing to the database and no changes were made.";
}
}
function Process_Brief_editable_Requests()
{
global $session, $debuggroups, $client_messages, $active_flag, $request_status;
$sql = "BEGIN; ";
foreach ($request_status as $request_id => $new_status) {
if (isset($active_flag)) {
$request_active = $active_flag[$request_id] == 'on' ? 'TRUE' : 'FALSE';
$session->Log("DBG: request_id=%d, new_status=%s, active=%s, submitted_active=%s", $request_id, $new_status, $request_active, $active_flag[$request_id]);
$sql .= "SELECT set_request_status(" . qpg($request_id) . "," . qpg($session->user_no) . "," . qpg($new_status) . ", {$request_active}); ";
} else {
// Or if we are just changing the status, and the active/inactive choice is not available to this user
$sql .= "SELECT set_request_status(" . qpg($request_id) . "," . qpg($session->user_no) . "," . qpg($new_status) . "); ";
}
}
$q = new PgQuery($sql . " COMMIT;");
$q->Exec('WRSrch::ProcBriefEditable');
}
function send_footers()
{
global $settings, $c, $session, $theme, $dbconn, $total_query_time, $debuglevel;
global $REQUEST_URI, $HTTP_USER_AGENT, $HTTP_REFERER, $PHP_SELF;
$theme->EndContentArea();
if ($theme->panel_right) {
$theme->RightPanel();
}
$theme->EndPanels();
if ($theme->panel_bottom) {
$theme->PageFooter();
}
echo <<<CLOSEHTML
<div id="overDiv" style="position:absolute; visibility:hidden; z-index:1000;"></div>
<script language="JavaScript" src="js/overlib.js"></script>
</body>
</html>
CLOSEHTML;
if (is_object($settings) && $settings->is_modified()) {
if (!is_numeric($settings->get('counter'))) {
$settings->set('counter', 0);
} else {
$settings->set('counter', $settings->get('counter') + 1);
}
$config_data_string = qpg($settings->to_save());
$query = "UPDATE session SET session_config={$config_data_string} ";
$query .= "WHERE session_id={$session->session_id} ";
$query .= "AND session_config != {$config_data_string}; ";
if ($session->user_no > 0) {
$query .= "UPDATE usr SET config_data={$config_data_string} WHERE user_no={$session->user_no} ";
$query .= "AND config_data != {$config_data_string}; ";
}
$result = awm_pgexec($dbconn, $query);
}
error_reporting(7);
if ($debuglevel > 0) {
$total_query_time = sprintf("%3.06lf", $total_query_time);
error_log($c->sysabbr . " total_query_ TQ: {$total_query_time} URI: {$REQUEST_URI}", 0);
$total_time = sprintf("%3.06lf", duration($c->started, microtime()));
error_log($c->sysabbr . " process_time TT: {$total_time} Agent: {$HTTP_USER_AGENT} Referrer: {$HTTP_REFERER} ", 0);
error_log("=============================================== Endof {$PHP_SELF}");
}
}
$sql .= "|| '&requested_by={$requested_by}' ";
}
$sql .= "|| '&incstat['||last_status||']=1' AS url_link ";
// And the rest of the SQL is 'reasonably' straightforward...
$sql .= "FROM request JOIN request_status ON request.request_id=request_status.request_id AND status_code=last_status ";
$sql .= "JOIN lookup_code status_lookup ON status_lookup.source_table='request' AND status_lookup.source_field='status_code' AND status_lookup.lookup_code=last_status ";
if (isset($allocated_to)) {
$sql .= "JOIN request_allocated ON request.request_id=request_allocated.request_id AND allocated_to_id = {$allocated_to} ";
}
if (isset($interested_in)) {
$sql .= "JOIN request_interested ON request.request_id=request_interested.request_id AND request_interested.user_no = {$interested_in} ";
}
if (isset($org_code)) {
$sql .= "JOIN usr ON request.requester_id=usr.user_no AND usr.org_code = {$org_code} ";
}
$sql .= "WHERE ((last_status IN ('F', 'C') AND status_on BETWEEN " . qpg($from_date) . " AND " . qpg($to_date) . ") ";
$sql .= "OR last_status NOT IN ('F','C')) ";
if (isset($system_id)) {
$sql .= "AND request.system_id={$system_id} ";
}
if (isset($request_type)) {
$sql .= "AND request.request_type={$request_type} ";
}
if (isset($requested_by)) {
$sql .= "AND requester_id = {$requested_by} ";
}
$sql .= "GROUP BY status_lookup.lookup_desc, request.last_status ";
$sql .= "ORDER BY 2 DESC;";
// echo "$sql";
$debuggroups['PieChart'] = 1;
$pie = new PieChart($sql);
}
/**
* We can't do any more with this since there was no identifiable request_id
*/
exit(0);
}
$qry = new PgQuery("BEGIN;");
$qry->Exec("PUT");
if ($delete_dav_event) {
$qry = new PgQuery("DELETE FROM caldav_data WHERE user_no=? AND dav_name=?", $session->user_no, $put_path);
$qry->Exec("PUT");
}
$action = "Created";
// By default
if (isset($ev->tz_locn) && $ev->tz_locn != '') {
$tzset = "SET TIMEZONE TO " . qpg($ev->tz_locn) . ";";
}
if (isset($etag_match) && $etag_match != '*' && $etag_match != '') {
$action = "Replaced";
$sql = <<<EOSQL
{$tzset}
UPDATE request_timesheet
SET request_id=?, work_on=?::timestamp, work_duration=(?::timestamp - ?::timestamp),
work_by_id={$session->user_no}, work_description=?, work_units='hours'
WHERE timesheet_id={$ts_id};
UPDATE request_timesheet
SET work_quantity = (extract( 'hours' from work_duration)::numeric + extract( 'minutes' from work_duration )::numeric / 60::numeric),
dav_etag = md5(timesheet_id||request_id||work_on||work_duration||work_by_id||COALESCE(charged_details,'')||work_description)
WHERE timesheet_id={$ts_id};
EOSQL;
$qry = new PgQuery($sql, $request_id, $ev->Get('dtstart'), $ev->Get('dtend'), $ev->Get('dtstart'), $ev->Get('summary'));
require_once "classBrowser.php";
$c->local_styles[] = "css/browse.css";
if (isset($submit)) {
$session->Dbg("SavedSearches", "Seem to be submitting a saved search");
$query_is_public = $GLOBALS['query_is_public'];
$show_in_menu = $GLOBALS['show_in_menu'];
$sql = "";
foreach ($query_is_public as $k => $v) {
$k = intval($k);
if (is_array($v) && ($k == $session->user_no || $session->AllowedTo("Admin") || $session->AllowedTo("Support"))) {
foreach ($v as $k2 => $v2) {
$public = $v2 == "on" ? "TRUE" : "FALSE";
$in_menu = $show_in_menu[$k][$k2] == "on" ? "TRUE" : "FALSE";
$k2dec = urldecode($k2);
$session->Dbg("SavedSearches", "Submitted query_is_public[{$k}][{$k2dec}] is >>{$v2}<< and show_in_menu[{$k}][{$k2dec}] is >>%s<<", $show_in_menu[$k][$k2]);
$sql .= "UPDATE saved_queries SET public = {$public} , in_menu = {$in_menu} WHERE user_no = {$k} AND query_name = " . qpg($k2dec) . ";";
}
}
}
$qry = new PgQuery($sql);
$qry->Exec("SavedSearches");
}
$debuggroups["querystring"] = 1;
$browser = new Browser("Your Saved Searches");
if ($session->AllowedTo("Admin") || $session->AllowedTo("Support")) {
$browser->AddColumn('user_no', 'User#', 'center', '<a href="/user.php?user_no=##user_no##">%d</a>');
} else {
$browser->AddHidden('user_no');
}
$browser->AddColumn('query_name', 'Query Name', 'left', '<a href="/wrsearch.php?style=plain&saved_query=##URL:query_name##">%s</a>');
$browser->AddColumn('query_type', 'Type', 'center', '%s');
to_char(work_duration,{$ical_duration_format}) AS duration,
work_description AS summary,
'WR#'||request_id::text AS location,
'WR#'||request_id::text || ' - ' || brief AS description,
'Invoice '||charged_details::text || ', Charged \$'|| to_char(charged_amount,'FM999,999,990.00') ||' by ' || chgby.username || ' on ' || to_char(work_charged,'d/mm/YY') AS invoiced
FROM request_timesheet JOIN request USING (request_id) JOIN usr ON (work_by_id=usr.user_no)
LEFT OUTER JOIN usr chgby ON (charged_by_id = chgby.user_no)
WHERE work_by_id = ? AND work_duration IS NOT NULL
EOSQL;
$where = "";
if (isset($report[$i]['start'])) {
$where = "AND ((work_on + work_duration) >= " . qpg($report[$i]['start']) . "::timestamp with time zone) ";
}
if (isset($report[$i]['end'])) {
$where .= "AND work_on <= " . qpg($report[$i]['end']) . "::timestamp with time zone ";
}
$sql .= $where;
$sql .= " ORDER BY work_on ASC";
$responses = array();
$qry = new PgQuery($sql, $report_user_no);
// echo $qry->querystring;
if ($qry->Exec() && $qry->rows > 0) {
while ($ts = $qry->Fetch()) {
if ($ts->invoiced != "") {
$ts->description .= "\n" . $ts->invoiced;
}
$response = new XMLElement("response");
$prop = new XMLElement("prop");
$ev = new vEvent(array('uid' => $ts->timesheet_id . "@" . $_SERVER['SERVER_NAME'], 'dtstart' => $ts->dtstart, 'duration' => $ts->duration, 'summary' => $ts->summary, 'location' => $ts->location, 'description' => $ts->description));
if (isset($report[$i]['include_href']) && $report[$i]['include_href'] > 0) {
if (isset($interested_in)) {
$interested_in = intval($interested_in);
$sql .= "JOIN request_interested ON request.request_id=request_interested.request_id AND request_interested.user_no = {$interested_in} ";
}
if (isset($org_code)) {
$org_code = intval($org_code);
$sql .= "JOIN usr ON request.requester_id=usr.user_no AND usr.org_code = {$org_code} ";
}
$sql .= "WHERE ((last_status IN ('F', 'C') AND status_on BETWEEN " . qpg($from_date) . " AND " . qpg($to_date) . ") ";
$sql .= "OR last_status NOT IN ('F','C')) ";
if (isset($system_id)) {
$system_id = qpg($system_id);
$sql .= "AND request.system_id={$system_id} ";
}
if (isset($request_type)) {
$request_type = qpg($request_type);
$sql .= "AND request.request_type={$request_type} ";
}
if (isset($requested_by)) {
$requested_by = intval($requested_by);
$sql .= "AND requester_id = {$requested_by} ";
}
$sql .= "GROUP BY status_lookup.lookup_desc ";
$sql .= "ORDER BY 2 DESC;";
// echo "$sql";
$pie = new BarChart($sql);
/*
include("page-header.php");
*/
$pie->Render();
/*
/**
* Get XML response for items in the collection
* If '/' is requested, a list of visible users is given, otherwise
* a list of calendars for the user which are parented by this path.
*/
function get_collection_contents($depth, $user_no, $collection)
{
global $session, $request;
dbg_error_log("PROPFIND", "Getting collection contents: Depth %d, User: %d, Path: %s", $depth, $user_no, $collection->dav_name);
$responses = array();
/**
* freebusy permission is not allowed to see the items in a collection. Must have at least read permission.
*/
if ($request->AllowedTo('read')) {
dbg_error_log("PROPFIND", "Getting collection items: Depth %d, User: %d, Path: %s", $depth, $user_no, $collection->dav_name);
$sql = "SELECT caldav_data.dav_name, caldav_data, caldav_data.dav_etag ";
$sql .= "FROM caldav_data WHERE dav_name ~ " . qpg('^' . $collection->dav_name . '[^/]+$');
$sql .= "ORDER BY caldav_data.dav_name ";
$qry = new PgQuery($sql, PgQuery::Plain(iCalendar::HttpDateFormat()), PgQuery::Plain(iCalendar::HttpDateFormat()));
if ($qry->Exec("PROPFIND", __LINE__, __FILE__) && $qry->rows > 0) {
while ($item = $qry->Fetch()) {
$responses[] = item_to_xml($item);
}
}
}
return $responses;
}
}
} else {
$search_query .= $default_search_statuses;
}
$search_query .= "]') ";
if (eregi("save", "{$submit}") && "{$savelist}" != "") {
$saved_sort = "";
$saved_seq = "";
if (isset($save_query_order) && intval($save_query_order) > "0") {
$saved_sort = $rlsort;
$saved_seq = $rlseq;
}
$qparams = qpg(serialize($_POST));
$savelist = qpg($savelist);
$qquery = qpg($search_query);
$save_rlsort = qpg($saved_rlsort);
$save_rlseq = qpg($saved_rlseq);
$save_public = qpg(intval($save_public) > 0);
$save_in_menu = qpg(intval($save_hotlist) > 0);
$search_query = "DELETE FROM saved_queries WHERE user_no = {$session->user_no} AND LOWER(query_name) = LOWER({$savelist});\nINSERT INTO saved_queries (user_no, query_name, query_sql, maxresults, rlsort, rlseq, public, updated, in_menu, query_params)\n VALUES( {$session->user_no}, {$savelist}, {$qquery}, " . intval($maxresults) . ",\n {$save_rlsort}, {$save_rlseq}, {$save_public}, current_timestamp, {$save_in_menu}, {$qparams});\n{$search_query}";
}
}
if ($rlsort != 'request_tags' || isset($flipped_columns['request_tags'])) {
// We can only sort by request_tags if it is present in the target list!
$search_query .= " ORDER BY {$rlsort} {$rlseq} ";
}
if (!isset($maxresults) || intval($maxresults) == 0) {
$maxresults = 200;
}
$search_query .= " LIMIT {$maxresults} ";
// echo "<p>$search_query</p>";
/**
* Quote the given string so it can be safely used within string delimiters
* in a query.
*
* @see qpg()
* which is where this is really done.
*
* @param mixed $str Data to be converted to a string suitable for including as a value in SQL.
* @return string NULL, TRUE, FALSE, a plain number, or the original string quoted and with ' and \ characters escaped
*/
function quote($str = null)
{
return qpg($str);
}
if ("{$search_for}{$org_code}{$system_id}{$active} " != "") {
$sql = "SELECT *, to_char( last_update, 'dd/mm/yyyy' ) AS last_update, to_char( last_used, 'dd/mm/yyyy' ) AS last_used ";
$sql .= "FROM usr JOIN organisation USING ( org_code ) ";
if (isset($system_id) && $system_id > 0) {
$sql .= "JOIN system_usr USING ( user_no ) ";
$sql .= "JOIN lookup_code ON source_table='system_usr' AND source_field='role' AND lookup_code=role ";
}
$sql .= "WHERE TRUE ";
if (!isset($org_code) || $org_code == 0) {
$sql .= "AND organisation.active ";
}
if (!isset($active) || $active != "f") {
$sql .= "AND usr.active ";
}
if ("{$search_for}" != "") {
$search_for = qpg($search_for);
$sql .= " AND (fullname ~* {$search_for} ";
$sql .= " OR username ~* {$search_for} ";
$sql .= " OR email ~* {$search_for} )";
}
if (is_member_of('Manage') && !is_member_of('Admin', 'Support')) {
$sql .= " AND usr.org_code='{$session->org_code}' ";
} else {
if (isset($org_code) && $org_code > 0) {
$sql .= " AND usr.org_code={$org_code}";
}
}
if (isset($system_id) && $system_id > 0) {
$sql .= " AND system_usr.system_id={$system_id}";
}
$sql .= " ORDER BY LOWER(fullname);";