public function __construct() { $cal_id = phpgw::get_var('cal_id', 'int', 'POST'); if ($cal_id) { $this->cal_id = $cal_id; } $this->bo = CreateObject('calendar.bocalendar', 1); $this->so = CreateObject('calendar.socalendar', 1); $this->tz_offset = phpgwapi_datetime::user_timezone(); if ($this->debug) { echo "BO Owner : " . $this->bo->owner . "<br />\n"; } if ($this->bo->use_session) { $this->save_sessiondata(); } }
function list_repeated_events($syear, $smonth, $sday, $eyear, $emonth, $eday, $owner_id = 0) { if (!isset($GLOBALS['phpgw_info']['server']['calendar_type']) || isset($GLOBALS['phpgw_info']['server']['calendar_type']) && $GLOBALS['phpgw_info']['server']['calendar_type'] != 'sql') { return array(); } $user_timezone = phpgwapi_datetime::user_timezone(); $starttime = mktime(0, 0, 0, $smonth, $sday, $syear) - $user_timezone; $endtime = mktime(23, 59, 59, $emonth, $eday, $eyear) - $user_timezone; // $starttime = mktime(0,0,0,$smonth,$sday,$syear); // $endtime = mktime(23,59,59,$emonth,$eday,$eyear); $sql = "AND (phpgw_cal.cal_type='M') " . 'AND (phpgw_cal_user.cal_login IN ('; if ($owner_id) { if (is_array($owner_id)) { $ids = $owner_id; } else { $ids[] = $owner_id; } } else { $ids = !$this->is_group ? array($this->owner) : $this->g_owner; } $sql .= is_array($ids) && count($ids) ? implode(',', $ids) : 0; // $member_groups = $GLOBALS['phpgw']->accounts->membership($this->user); // @reset($member_groups); // while(list($key,$group_info) = each($member_groups)) // { // $member[] = $group_info['account_id']; // } // @reset($member); // $sql .= ','.implode(',',$member).') '; // $sql .= 'AND (phpgw_cal.datetime <= '.$starttime.') '; // $sql .= 'AND (((phpgw_cal_repeats.recur_enddate >= '.$starttime.') AND (phpgw_cal_repeats.recur_enddate <= '.$endtime.')) OR (phpgw_cal_repeats.recur_enddate=0))) ' $sql .= ') AND ((phpgw_cal_repeats.recur_enddate >= ' . $starttime . ') OR (phpgw_cal_repeats.recur_enddate=0))) ' . (strpos($this->filter, 'private') ? 'AND phpgw_cal.is_public=0 ' : '') . ($this->cat_id ? "AND phpgw_cal.category like '%" . $this->cat_id . "%' " : '') . 'ORDER BY phpgw_cal.datetime ASC, phpgw_cal.edatetime ASC, phpgw_cal.priority ASC'; if ($this->debug) { echo '<!-- SO list_repeated_events : SQL : ' . $sql . ' -->' . "\n"; } return $this->get_event_ids(True, $sql); }
/** * Show current date * * @param integer $t Time, defaults to user preferences * @param string $format Date format, defaults to user preferences * @return string Formated date */ public function show_date($t = '', $format = '') { if (!$t || substr(php_uname(), 0, 7) == "Windows" && intval($t) <= 0) { return ''; // return nothing if not valid input // $t = phpgwapi_datetime::gmtnow(); } // + (date('I') == 1?3600:0) $t += phpgwapi_datetime::user_timezone(); if (!$format) { $format = $GLOBALS['phpgw_info']['user']['preferences']['common']['dateformat'] . ' - '; if ($GLOBALS['phpgw_info']['user']['preferences']['common']['timeformat'] == '12') { $format .= 'h:i a'; } else { $format .= 'H:i'; } } return date($format, $t); }
function read($data) { $start = isset($data['start']) && $data['start'] ? $data['start'] : 0; $filter = $data['filter'] ? (int) $data['filter'] : 0; $query = isset($data['query']) ? $data['query'] : ''; $sort = isset($data['sort']) ? $data['sort'] : 'DESC'; $order = isset($data['order']) ? $data['order'] : ''; $cat_id = isset($data['cat_id']) && $data['cat_id'] ? $data['cat_id'] : 0; $status_id = isset($data['status_id']) && $data['status_id'] ? $data['status_id'] : 'open'; $start_date = isset($data['start_date']) && $data['start_date'] ? (int) $data['start_date'] : 0; $end_date = isset($data['end_date']) && $data['end_date'] ? (int) $data['end_date'] : 0; $overdue = isset($data['overdue']) && $data['overdue'] ? (int) $data['overdue'] : 0; $allrows = isset($data['allrows']) ? $data['allrows'] : ''; $wo_hour_cat_id = isset($data['wo_hour_cat_id']) ? $data['wo_hour_cat_id'] : ''; $district_id = isset($data['district_id']) ? $data['district_id'] : ''; $dry_run = isset($data['dry_run']) ? $data['dry_run'] : ''; $criteria = isset($data['criteria']) && $data['criteria'] ? $data['criteria'] : array(); $project_type_id = $data['project_type_id'] ? (int) $data['project_type_id'] : 0; $filter_year = isset($data['filter_year']) ? $data['filter_year'] : ''; $sql = $this->bocommon->fm_cache('sql_project_' . !!$wo_hour_cat_id); if (!$sql) { $entity_table = 'fm_project'; $cols = $entity_table . '.location_code'; $cols_return[] = 'location_code'; $cols .= ",project_type_id"; $cols_return[] = 'project_type_id'; /* $uicols['input_type'][] = 'hidden'; $uicols['name'][] = 'project_type_id'; $uicols['descr'][] = ''; $uicols['statustext'][] = ''; $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; */ $cols .= ",{$entity_table}.id as project_id"; $cols_return[] = 'project_id'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'project_id'; $uicols['descr'][] = lang('Project'); $uicols['statustext'][] = lang('Project ID'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'linktToProject'; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $cols .= ", project_group"; $cols_return[] = 'project_group'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'project_group'; $uicols['descr'][] = lang('group'); $uicols['statustext'][] = lang('Project group'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = true; $cols .= ", fm_project_status.descr as status"; $cols_return[] = 'status'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'status'; $uicols['descr'][] = lang('status'); $uicols['statustext'][] = lang('status'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $cols .= ",{$entity_table}.entry_date"; $cols_return[] = 'entry_date'; $cols .= ",{$entity_table}.start_date"; $cols_return[] = 'start_date'; $cols .= ",{$entity_table}.end_date"; $cols_return[] = 'end_date'; $cols .= ",{$entity_table}.ecodimb"; $cols_return[] = 'ecodimb'; /* $uicols['input_type'][] = 'text'; $uicols['name'][] = 'entry_date'; $uicols['descr'][] = lang('entry date'); $uicols['statustext'][] = lang('Project entry date'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; */ $cols .= ",{$entity_table}.name as name"; $cols_return[] = 'name'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'name'; $uicols['descr'][] = lang('name'); $uicols['statustext'][] = lang('Project name'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; $cols .= ",loc1_name"; // $cols_return[] = 'loc1_name'; /* $uicols['input_type'][] = 'hidden'; $uicols['name'][] = 'loc1_name'; $uicols['descr'][] = lang('loc1_name'); $uicols['statustext'][] = lang('loc1_name'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; */ $cols .= ",account_lid as coordinator"; $cols_return[] = 'coordinator'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'coordinator'; $uicols['descr'][] = lang('Coordinator'); $uicols['statustext'][] = lang('Project coordinator'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; // $cols.= ",(fm_project.budget + fm_project.reserve) as budget"; $cols_return[] = 'budget'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'budget'; $uicols['descr'][] = lang('budget'); $uicols['statustext'][] = lang('Project budget'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'myFormatCount2'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = ''; // $cols .= ',sum(fm_workorder.combined_cost) as combined_cost'; // $cols_return[] = 'combined_cost'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'obligation'; $uicols['descr'][] = lang('obligation'); $uicols['statustext'][] = lang('obligation'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'myFormatCount2'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = ''; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'actual_cost'; $uicols['descr'][] = lang('Actual cost'); $uicols['statustext'][] = lang('Actual cost - paid so far'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'myFormatCount2'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = ''; // $cols .= ',planned_cost'; // $cols_return[] = 'planned_cost'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'diff'; $uicols['descr'][] = lang('difference'); $uicols['statustext'][] = lang('difference'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'myFormatCount2'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = ''; $cols .= ",{$entity_table}.user_id"; // $cols .= ',sum(fm_workorder.billable_hours) as billable_hours'; // $cols_return[] = 'billable_hours'; $joinmethod = " {$this->join} phpgw_accounts ON ({$entity_table}.coordinator = phpgw_accounts.account_id))"; $paranthesis = '('; $joinmethod .= " {$this->join} fm_project_status ON ({$entity_table}.status = fm_project_status.id))"; $paranthesis .= '('; $joinmethod .= " {$this->left_join} fm_project_budget ON ({$entity_table}.id = fm_project_budget.project_id))"; $paranthesis .= '('; $joinmethod .= " {$this->left_join} fm_project_buffer_budget ON ({$entity_table}.id = fm_project_buffer_budget.buffer_project_id))"; $paranthesis .= '('; /* $joinmethod .= " {$this->left_join} fm_workorder ON ($entity_table.id = fm_workorder.project_id))"; $paranthesis .='('; */ //----- wo_hour_status if ($wo_hour_cat_id) { $joinmethod .= " {$this->join} fm_workorder ON ({$entity_table}.id = fm_workorder.project_id))"; $paranthesis .= '('; $joinmethod .= " {$this->join} fm_wo_hours ON (fm_workorder.id = fm_wo_hours.workorder_id))"; $paranthesis .= '('; $joinmethod .= " {$this->join} fm_wo_hours_category ON (fm_wo_hours.category = fm_wo_hours_category.id))"; $paranthesis .= '('; } //----- wo_hour_status $sql = $this->bocommon->generate_sql(array('entity_table' => $entity_table, 'cols' => $cols, 'cols_return' => $cols_return, 'uicols' => $uicols, 'joinmethod' => $joinmethod, 'paranthesis' => $paranthesis, 'force_location' => true, 'location_level' => 0)); $this->bocommon->fm_cache('sql_project_' . !!$wo_hour_cat_id, $sql); $uicols = $this->bocommon->uicols; $cols_return = $this->bocommon->cols_return; $type_id = $this->bocommon->type_id; $this->cols_extra = $this->bocommon->cols_extra; $this->bocommon->fm_cache('uicols_project_' . !!$wo_hour_cat_id, $uicols); $this->bocommon->fm_cache('cols_return_project_' . !!$wo_hour_cat_id, $cols_return); $this->bocommon->fm_cache('type_id_project_' . !!$wo_hour_cat_id, $type_id); $this->bocommon->fm_cache('cols_extra_project_' . !!$wo_hour_cat_id, $this->cols_extra); } else { $uicols = $this->bocommon->fm_cache('uicols_project_' . !!$wo_hour_cat_id); $cols_return = $this->bocommon->fm_cache('cols_return_project_' . !!$wo_hour_cat_id); $type_id = $this->bocommon->fm_cache('type_id_project_' . !!$wo_hour_cat_id); $this->cols_extra = $this->bocommon->fm_cache('cols_extra_project_' . !!$wo_hour_cat_id); } $user_columns = isset($GLOBALS['phpgw_info']['user']['preferences']['property']['project_columns']) && $GLOBALS['phpgw_info']['user']['preferences']['property']['project_columns'] ? $GLOBALS['phpgw_info']['user']['preferences']['property']['project_columns'] : array(); $_user_columns = array(); foreach ($user_columns as $user_column_id) { if (ctype_digit($user_column_id)) { $_user_columns[] = $user_column_id; } } $user_column_filter = ''; $location_id = $GLOBALS['phpgw']->locations->get_id('property', '.project'); $attribute_filter = " location_id = {$location_id}"; if ($_user_columns) { $user_column_filter = " OR ({$attribute_filter} AND id IN (" . implode(',', $_user_columns) . '))'; } $attribute_table = 'phpgw_cust_attribute'; $this->db->query("SELECT * FROM {$attribute_table} WHERE list=1 AND {$attribute_filter} {$user_column_filter} ORDER BY group_id, attrib_sort ASC"); $_custom_cols = ''; $_attrib = array(); while ($this->db->next_record()) { $_column_name = $this->db->f('column_name'); $_attrib[$_column_name] = $this->db->f('id'); $_custom_cols .= ", fm_project.{$_column_name}"; $cols_return[] = $_column_name; $uicols['input_type'][] = 'text'; $uicols['name'][] = $_column_name; $uicols['descr'][] = $this->db->f('input_text'); $uicols['statustext'][] = $this->db->f('statustext'); $uicols['datatype'][] = $this->db->f('datatype'); $uicols['sortable'][] = true; $uicols['exchange'][] = false; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; } $this->uicols = $uicols; $order_field = ''; if ($order) { $ordermethod = "ORDER BY {$order} {$sort}"; switch ($order) { case 'project_id': $ordermethod = "ORDER BY fm_project.id {$sort}"; break; case 'combined_cost': $order_field = ',sum(fm_workorder.combined_cost) as combined_cost'; break; case 'address': $order_field = ", fm_project.address"; $group_field = $order_field; break; case 'status': $order_field = ", fm_project_status.descr as status"; $group_field = ', fm_project_status.descr'; $ordermethod = "ORDER BY fm_project_status.descr {$sort}"; break; case 'entry_date': $order_field = ", fm_project.entry_date"; $group_field = $order_field; break; case 'start_date': $order_field = ", fm_project.start_date"; $group_field = $order_field; break; case 'end_date': $order_field = ", fm_project.end_date"; $group_field = $order_field; break; case 'ecodimb': $order_field = ", fm_project.ecodimb"; $group_field = $order_field; break; case 'location_code': $order_field = ", fm_project.location_code"; $group_field = $order_field; break; default: $order_field = ", {$order}"; $group_field = $order_field; } } else { $ordermethod = ' ORDER BY fm_project.id DESC'; } $where = 'WHERE'; $filtermethod = ''; $GLOBALS['phpgw']->config->read(); if (isset($GLOBALS['phpgw']->config->config_data['acl_at_location']) && $GLOBALS['phpgw']->config->config_data['acl_at_location']) { $access_location = $this->bocommon->get_location_list(PHPGW_ACL_READ); $filtermethod = " WHERE fm_project.loc1 in ('" . implode("','", $access_location) . "')"; $where = 'AND'; } if ($cat_id > 0) { $cats = CreateObject('phpgwapi.categories', -1, 'property', '.project'); $cats->supress_info = true; $cat_list_project = $cats->return_sorted_array(0, false, '', '', '', false, $cat_id, false); $cat_filter = array($cat_id); foreach ($cat_list_project as $_category) { $cat_filter[] = $_category['id']; } $filtermethod .= " {$where} fm_project.category IN (" . implode(',', $cat_filter) . ')'; $where = 'AND'; } if ($status_id && $status_id != 'all') { if ($status_id == 'open') { $_status_filter = array(); $this->db->query("SELECT * FROM fm_project_status WHERE closed IS NULL"); while ($this->db->next_record()) { $_status_filter[] = $this->db->f('id'); } if ($_status_filter) { $filtermethod .= " {$where} fm_project.status IN ('" . implode("','", $_status_filter) . "')"; } } else { $filtermethod .= " {$where} fm_project.status='{$status_id}' "; } $where = 'AND'; } if ($project_type_id) { $filtermethod .= " {$where} fm_project.project_type_id={$project_type_id}"; $where = 'AND'; } if ($wo_hour_cat_id) { $filtermethod .= " {$where} fm_wo_hours_category.id={$wo_hour_cat_id}"; $where = 'AND'; } if ($district_id) { $filtermethod .= " {$where} fm_part_of_town.district_id = {$district_id}"; $where = 'AND'; } /* $group_method = ' GROUP BY fm_project_status.descr,loc1_name,fm_project.location_code,fm_project.id,fm_project.entry_date,fm_project.start_date,fm_project.end_date,' . 'fm_project.name,fm_project.ecodimb,phpgw_accounts.account_lid,fm_project.user_id,fm_project.address,' . 'fm_project.budget,fm_project.reserve,planned_cost,project_group'; */ if (is_array($this->grants)) { $grants = $this->grants; while (list($user) = each($grants)) { $public_user_list[] = $user; } reset($public_user_list); $filtermethod .= " {$where} (fm_project.user_id IN(" . implode(',', $public_user_list) . ")"; $where = 'AND'; } if ($filter) { $filtermethod .= " {$where} fm_project.coordinator={$filter}"; $where = 'AND'; } if ($start_date) { $end_date = $end_date + 3600 * 16 + phpgwapi_datetime::user_timezone(); $start_date = $start_date - 3600 * 8 + phpgwapi_datetime::user_timezone(); $filtermethod .= " {$where} fm_project.start_date >= {$start_date} AND fm_project.start_date <= {$end_date} "; $where = 'AND'; } if ($overdue) { $end_date = $overdue + 3600 * 16 + phpgwapi_datetime::user_timezone(); $filtermethod .= " {$where} fm_project.end_date <= {$end_date} AND fm_project.start_date <= {$end_date} "; $_status_filter = array(); $this->db->query("SELECT * FROM fm_project_status WHERE closed IS NULL"); while ($this->db->next_record()) { $_status_filter[] = $this->db->f('id'); } if ($_status_filter) { $filtermethod .= " AND fm_project.status IN ('" . implode("','", $_status_filter) . "')"; } $where = 'AND'; } if ($filter_year && $filter_year != 'all') { $filter_year = (int) $filter_year; $filtermethod .= " {$where} (fm_project_budget.year={$filter_year} OR fm_project_buffer_budget.year={$filter_year})"; $where = 'AND'; } $querymethod = ''; if ($query) { $query = $this->db->db_addslashes($query); $query = str_replace(",", '.', $query); if (isset($criteria[0]['field']) && $criteria[0]['field'] == 'fm_project.p_num') { $query = explode(".", $query); $querymethod = " {$where} (fm_project.p_entity_id='" . (int) $query[1] . "' AND fm_project.p_cat_id='" . (int) $query[2] . "' AND fm_project.p_num='" . (int) $query[3] . "')"; } else { if (stristr($query, '.')) { $query = explode(".", $query); $querymethod = " {$where} (fm_project.loc1='" . $query[0] . "' AND fm_project.loc" . $type_id . "='" . $query[1] . "')"; } else { $matchtypes = array('exact' => '=', 'like' => $this->like); if (count($criteria) > 1) { $_querymethod = array(); foreach ($criteria as $field_info) { if ($field_info['type'] == int) { $_query = (int) $query; } else { $_query = $query; } $_querymethod[] = "{$field_info['field']} {$matchtypes[$field_info['matchtype']]} {$field_info['front']}{$_query}{$field_info['back']}"; } $querymethod = $where . ' (' . implode(' OR ', $_querymethod) . ')'; unset($_querymethod); } else { if ($criteria[0]['type'] == int) { $_query = (int) $query; } else { $_query = $query; } $querymethod = "{$where} {$criteria[0]['field']} {$matchtypes[$criteria[0]['matchtype']]} {$criteria[0]['front']}{$_query}{$criteria[0]['back']}"; } } } } $querymethod .= ')'; $sql = str_replace('FROM', "{$_custom_cols} FROM", $sql); // $sql .= " $filtermethod $querymethod"; $sql_full = "{$sql} {$filtermethod} {$querymethod}"; //echo substr($sql,strripos($sql,'from')); if ($GLOBALS['phpgw_info']['server']['db_type'] == 'postgres') { $sql_minimized = 'SELECT DISTINCT fm_project.id ' . substr($sql_full, strripos($sql_full, 'FROM')); $sql_count = "SELECT count(id) as cnt FROM ({$sql_minimized}) as t"; $this->db->query($sql_count, __LINE__, __FILE__); $this->db->next_record(); $this->total_records = $this->db->f('cnt'); } else { $sql_count = 'SELECT DISTINCT fm_project.id ' . substr($sql_full, strripos($sql_full, 'FROM')); $this->db->query($sql_count, __LINE__, __FILE__); $this->total_records = $this->db->num_rows(); } $sql_end = str_replace('SELECT DISTINCT fm_project.id', "SELECT DISTINCT fm_project.id {$order_field}", $sql_minimized) . " GROUP BY fm_project.id {$group_field} {$ordermethod}"; $project_list = array(); if (!$dry_run) { if (!$allrows) { $this->db->limit_query($sql_end, $start, __LINE__, __FILE__); } else { $_fetch_single = false; /* if($this->total_records > 200) { $_fetch_single = true; } else { $_fetch_single = false; } */ $this->db->query($sql_end, __LINE__, __FILE__, false, $_fetch_single); unset($_fetch_single); } $project_list = array(); //_debug_array($cols_return); $count_cols_return = count($cols_return); while ($this->db->next_record()) { $project_list[] = array('project_id' => $this->db->f('id')); } $this->db->set_fetch_single(false); //$test=array(); foreach ($project_list as &$project) { $this->db->query("{$sql} WHERE fm_project.id = '{$project['project_id']}' {$group_method}"); $this->db->next_record(); //_debug_array("{$sql} WHERE fm_project.id = '{$project['project_id']}' {$group_method}"); for ($i = 0; $i < $count_cols_return; $i++) { $project[$cols_return[$i]] = $this->db->f($cols_return[$i]); } $project['grants'] = (int) $this->grants[$this->db->f('user_id')]; $location_code = $this->db->f('location_code'); $location = explode('-', $location_code); $count_location = count($location); for ($m = 0; $m < $count_location; $m++) { $project['loc' . ($m + 1)] = $location[$m]; $project['query_location']['loc' . ($m + 1)] = implode("-", array_slice($location, 0, $m + 1)); } $project['combined_cost'] = 0; $project['actual_cost'] = 0; $project['billable_hours'] = 0; } //_debug_array($project_list); unset($project); $_datatype = array(); foreach ($this->uicols['name'] as $key => $_name) { $_datatype[$_name] = $this->uicols['datatype'][$key]; } $dataset = array(); $j = 0; foreach ($project_list as $project) { foreach ($project as $field => $value) { $dataset[$j][$field] = array('value' => $value, 'datatype' => isset($_datatype[$field]) && $_datatype[$field] ? $_datatype[$field] : false, 'attrib_id' => isset($_attrib[$field]) && $_attrib[$field] ? $_attrib[$field] : false); } $j++; } $values = $this->custom->translate_value($dataset, $location_id); foreach ($values as &$project) { $project['combined_cost'] = 0; $project['budget'] = 0; $project['obligation'] = 0; $project['actual_cost'] = 0; if ($project['project_type_id'] == 3) { $buffer_budget = $this->get_buffer_budget($project['project_id']); foreach ($buffer_budget as $entry) { $project['budget'] += $entry['amount_in']; $project['budget'] -= $entry['amount_out']; } unset($entry); } else { $year = (int) $filter_year; $project_budget = $this->get_budget($project['project_id']); $project['vendor_list'] = $this->vendor_list; foreach ($project_budget as $entry) { if ($year && $entry['year'] == $year) { if ($entry['active']) { $project['combined_cost'] += $entry['sum_orders']; $project['budget'] += $entry['budget']; if (!$entry['closed']) { $project['obligation'] += $entry['sum_oblications']; } } $project['actual_cost'] += $entry['actual_cost']; } else { if (!$year) { if ($entry['active']) { $project['combined_cost'] += $entry['sum_orders']; $project['budget'] += $entry['budget']; if (!$entry['closed']) { $project['obligation'] += $entry['sum_oblications']; } } $project['actual_cost'] += $entry['actual_cost']; } } } /* $workorder_data = $this->project_workorder_data(array('project_id' => $project['project_id'], 'year' => (int)$filter_year)); foreach($workorder_data as $entry) { $project['actual_cost'] += $entry['actual_cost']; $project['combined_cost'] += $entry['combined_cost']; $project['budget'] += $entry['budget']; $project['obligation'] += $entry['obligation']; } unset($entry); */ $_diff_start = abs($project['budget']) > 0 ? $project['budget'] : $project['combined_cost']; if (abs($_diff_start) > 0) { $project['diff'] = $_diff_start - $project['obligation'] - $project['actual_cost']; } else { $project['diff'] = 0; } } } //_debug_array($values); //_debug_array($test); return $values; } return array(); }
/** * Prepare an attribute value so it can be saved in the database * * @param array $values_attribute an attribute structure * * @return array the structure with the value prepared * * @internal the name of this method is misleading */ public function convert_attribute_save($values_attribute = null) { if (!is_array($values_attribute)) { return ''; } foreach ($values_attribute as &$attrib) { if (!$attrib['value']) { continue; } switch ($attrib['datatype']) { case 'CH': $attrib['value'] = ',' . implode(',', $attrib['value']) . ','; break; case 'R': $attrib['value'] = $attrib['value'][0]; break; case 'N': $attrib['value'] = str_replace(',', '.', $attrib['value']); break; case 'D': $ts = phpgwapi_datetime::date_to_timestamp($attrib['value']) - phpgwapi_datetime::user_timezone(); $attrib['value'] = date($this->_dateformat, $ts); break; case 'DT': if ($attrib['value']['date']) { $date_array = phpgwapi_datetime::date_array($attrib['value']['date']); $ts = mktime((int) $attrib['value']['hour'], (int) $attrib['value']['min'], 0, $date_array['month'], $date_array['day'], $date_array['year']) - phpgwapi_datetime::user_timezone(); $attrib['value'] = date($this->_datetimeformat, $ts); } else { $attrib['value'] = ''; } break; } } return $values_attribute; }
function read($data) { $start = isset($data['start']) && $data['start'] ? $data['start'] : 0; $filter = $data['filter'] ? (int) $data['filter'] : 0; $query = isset($data['query']) ? $data['query'] : ''; $sort = isset($data['sort']) && $data['sort'] ? $data['sort'] : 'DESC'; $order = isset($data['order']) ? $data['order'] : ''; $cat_id = isset($data['cat_id']) && $data['cat_id'] ? $data['cat_id'] : 0; $status_id = isset($data['status_id']) && $data['status_id'] ? $data['status_id'] : 'open'; $start_date = isset($data['start_date']) && $data['start_date'] ? (int) $data['start_date'] : 0; $end_date = isset($data['end_date']) && $data['end_date'] ? (int) $data['end_date'] : 0; $allrows = isset($data['allrows']) ? $data['allrows'] : ''; $obligation = isset($data['obligation']) ? $data['obligation'] : ''; $wo_hour_cat_id = isset($data['wo_hour_cat_id']) ? $data['wo_hour_cat_id'] : ''; $b_group = isset($data['b_group']) ? $data['b_group'] : ''; $ecodimb = isset($data['ecodimb']) ? $data['ecodimb'] : ''; $paid = isset($data['paid']) ? $data['paid'] : ''; $b_account = isset($data['b_account']) ? $data['b_account'] : ''; $district_id = isset($data['district_id']) ? $data['district_id'] : ''; $dry_run = isset($data['dry_run']) ? $data['dry_run'] : ''; $criteria = isset($data['criteria']) && $data['criteria'] ? $data['criteria'] : array(); $filter_year = isset($data['filter_year']) ? $data['filter_year'] : ''; $tender_deadline = isset($data['tender_deadline']) && $data['tender_deadline'] ? (int) $data['tender_deadline'] : 0; $inspection_on_completion = isset($data['inspection_on_completion']) && $data['inspection_on_completion'] ? (int) $data['inspection_on_completion'] : 0; $GLOBALS['phpgw']->config->read(); $sql = $this->bocommon->fm_cache('sql_workorder' . !!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group); //echo $sql; if (!$sql) { $cols = "fm_project.id as project_id"; $cols_return[] = 'project_id'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'project_id'; $uicols['descr'][] = lang('Project'); $uicols['statustext'][] = lang('Project ID'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'linktToProject'; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $cols .= ",fm_workorder.id as workorder_id"; $cols_return[] = 'workorder_id'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'workorder_id'; $uicols['descr'][] = lang('Workorder'); $uicols['statustext'][] = lang('Workorder ID'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'linktToOrder'; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $cols .= ",fm_workorder.title as title"; $cols_return[] = 'title'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'title'; $uicols['descr'][] = lang('Title'); $uicols['statustext'][] = lang('Workorder title'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; $cols .= ",fm_workorder_status.descr as status"; $cols_return[] = 'status'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'status'; $uicols['descr'][] = lang('Status'); $uicols['statustext'][] = lang('Workorder status'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; $cols .= ",fm_workorder.entry_date as entry_date"; $cols_return[] = 'entry_date'; $cols .= ",fm_workorder.start_date as start_date"; $cols_return[] = 'start_date'; $cols .= ",fm_workorder.end_date as end_date"; $cols_return[] = 'end_date'; $cols .= ",fm_workorder.tender_deadline as tender_deadline"; $cols_return[] = 'tender_deadline'; $cols .= ",fm_workorder.tender_received as tender_received"; $cols_return[] = 'tender_received'; $cols .= ",fm_workorder.inspection_on_completion as inspection_on_completion"; $cols_return[] = 'inspection_on_completion'; $cols .= ",fm_project.ecodimb"; $cols_return[] = 'ecodimb'; $cols .= ",fm_workorder.contract_sum"; $cols_return[] = 'contract_sum'; $cols .= ",fm_workorder.approved"; $cols_return[] = 'approved'; $cols .= ",fm_workorder.account_id as b_account_id"; $cols_return[] = 'b_account_id'; /* $uicols['input_type'][] = 'text'; $uicols['name'][] = 'entry_date'; $uicols['descr'][] = lang('Entry date'); $uicols['statustext'][] = lang('Workorder entry date'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; */ $cols .= ",phpgw_accounts.account_lid as user_lid"; $cols_return[] = 'user_lid'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'user_lid'; $uicols['descr'][] = lang('User'); $uicols['statustext'][] = lang('Workorder User'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; $cols .= ',fm_workorder.vendor_id'; $cols_return[] = 'vendor_id'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'vendor_id'; $uicols['descr'][] = lang('Vendor ID'); $uicols['statustext'][] = lang('Vendor ID'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; $cols .= ",loc1_name"; // $cols_return[] = 'loc1_name'; /* $uicols['input_type'][] = 'hidden'; $uicols['name'][] = 'loc1_name'; $uicols['descr'][] = ''; $uicols['statustext'][] = ''; $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; */ $cols .= ",fm_project.user_id as project_owner"; $joinmethod .= "{$this->join} fm_workorder ON (fm_project.id = fm_workorder.project_id) {$this->join} phpgw_accounts ON (fm_workorder.user_id = phpgw_accounts.account_id))"; $paranthesis .= '('; $joinmethod .= " {$this->join} fm_workorder_status ON (fm_workorder.status = fm_workorder_status.id))"; $paranthesis .= '('; $cols .= ',fm_workorder_status.closed'; $cols .= ',fm_vendor.org_name'; $cols_return[] = 'org_name'; $uicols['input_type'][] = 'hidden'; $uicols['name'][] = 'org_name'; $uicols['descr'][] = lang('Vendor name'); $uicols['statustext'][] = lang('Vendor name'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = ''; $cols .= ',fm_workorder.budget'; $cols_return[] = 'budget'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'budget'; $uicols['descr'][] = lang('budget'); $uicols['statustext'][] = lang('budget'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'myFormatCount2'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = false; // $cols .= ',fm_workorder.combined_cost'; // $cols_return[] = 'combined_cost'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'obligation'; $uicols['descr'][] = lang('sum orders'); $uicols['statustext'][] = lang('Cost - either budget or calculation'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'myFormatCount2'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = false; $cols .= ',fm_workorder.actual_cost'; $cols_return[] = 'actual_cost'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'actual_cost'; $uicols['descr'][] = lang('Actual cost'); $uicols['statustext'][] = lang('Actual cost - paid so far'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'myFormatCount2'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = true; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'diff'; $uicols['descr'][] = lang('difference'); $uicols['statustext'][] = lang('difference'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'myFormatCount2'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = ''; $joinmethod .= " {$this->left_join} fm_vendor ON (fm_workorder.vendor_id = fm_vendor.id))"; $paranthesis .= '('; $joinmethod .= " {$this->left_join} fm_workorder_budget ON (fm_workorder.id = fm_workorder_budget.order_id))"; $paranthesis .= '('; //----- wo_hour_status if ($wo_hour_cat_id) { $joinmethod .= " {$this->join} fm_wo_hours ON (fm_workorder.id = fm_wo_hours.workorder_id))"; $paranthesis .= '('; $joinmethod .= " {$this->join} fm_wo_hours_category ON (fm_wo_hours.category = fm_wo_hours_category.id))"; $paranthesis .= '('; } $joinmethod .= " {$this->join} fm_b_account ON (fm_workorder.account_id =fm_b_account.id))"; $paranthesis .= '('; //----- b_group $cols_return[] = 'location_code'; $cols_return[] = 'billable_hours'; $cols_return[] = 'continuous'; $cols .= ',fm_workorder.billable_hours'; $cols .= ',fm_workorder.continuous'; $no_address = false; if (isset($GLOBALS['phpgw']->config->config_data['location_at_workorder']) && $GLOBALS['phpgw']->config->config_data['location_at_workorder']) { $no_address = true; $cols .= ',fm_workorder.location_code'; $cols .= ',fm_workorder.address'; $cols_return[] = 'address'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'address'; $uicols['descr'][] = lang('address'); $uicols['statustext'][] = lang('address'); $uicols['exchange'][] = false; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $joinmethod .= "{$this->join} fm_locations ON (fm_workorder.location_code = fm_locations.location_code))"; $paranthesis .= '('; $location_table = 'fm_locations'; } else { $cols .= ",fm_project.location_code"; $location_table = 'fm_project'; } $entity_table = 'fm_project'; $sql = $this->bocommon->generate_sql(array('entity_table' => $entity_table, 'location_table' => $location_table, 'cols' => $cols, 'cols_return' => $cols_return, 'uicols' => $uicols, 'joinmethod' => $joinmethod, 'paranthesis' => $paranthesis, 'force_location' => true, 'no_address' => $no_address, 'location_level' => 0)); $this->bocommon->fm_cache('sql_workorder' . !!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group, $sql); $this->uicols = $this->bocommon->uicols; $cols_return = $this->bocommon->cols_return; $type_id = $this->bocommon->type_id; // $this->cols_extra = $this->bocommon->cols_extra; $this->bocommon->fm_cache('uicols_workorder' . !!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group, $this->uicols); $this->bocommon->fm_cache('cols_return_workorder' . !!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group, $cols_return); $this->bocommon->fm_cache('type_id_workorder' . !!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group, $type_id); // $this->bocommon->fm_cache('cols_extra_workorder'.!!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group,$this->cols_extra); } else { $this->uicols = $this->bocommon->fm_cache('uicols_workorder' . !!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group); $cols_return = $this->bocommon->fm_cache('cols_return_workorder' . !!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group); $type_id = $this->bocommon->fm_cache('type_id_workorder' . !!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group); // $this->cols_extra = $this->bocommon->fm_cache('cols_extra_workorder'.!!$search_vendor . '_' . !!$wo_hour_cat_id . '_' . !!$b_group); } $location_table = 'fm_project'; if (isset($GLOBALS['phpgw']->config->config_data['location_at_workorder']) && $GLOBALS['phpgw']->config->config_data['location_at_workorder']) { $location_table = 'fm_workorder'; } $order_field = ''; if ($order) { $ordermethod = " ORDER BY {$order} {$sort}"; switch ($order) { case 'workorder_id': $ordermethod = " ORDER BY fm_workorder.id {$sort}"; break; case 'actual_cost': $order_field = ',fm_workorder.actual_cost'; break; case 'address': if (isset($GLOBALS['phpgw']->config->config_data['location_at_workorder']) && $GLOBALS['phpgw']->config->config_data['location_at_workorder']) { $order_field = ", fm_workorder.address"; } else { $order_field = ", fm_project.address"; } break; case 'entry_date': $order_field = ", fm_workorder.entry_date"; $ordermethod = " ORDER BY fm_workorder.entry_date {$sort}, fm_workorder.id"; break; case 'start_date': $order_field = ", fm_workorder.start_date"; $ordermethod = " ORDER BY fm_workorder.start_date {$sort}, fm_workorder.id"; break; case 'end_date': $order_field = ", fm_workorder.end_date"; $ordermethod = " ORDER BY fm_workorder.end_date {$sort}, fm_workorder.id"; break; case 'tender_deadline': $order_field = ", fm_workorder.tender_deadline"; $ordermethod = " ORDER BY fm_workorder.tender_deadline {$sort}, fm_workorder.id"; break; case 'tender_received': $order_field = ", fm_workorder.tender_received"; $ordermethod = " ORDER BY fm_workorder.tender_received {$sort}, fm_workorder.id"; break; case 'inspection_on_completion': $order_field = ", fm_workorder.inspection_on_completion"; $ordermethod = " ORDER BY fm_workorder.inspection_on_completion {$sort}, fm_workorder.id"; break; case 'ecodimb': $order_field = ", fm_project.ecodimb"; $ordermethod = " ORDER BY fm_workorder.ecodimb {$sort}, fm_workorder.id"; break; case 'budget': $order_field = ", fm_workorder.budget"; $ordermethod = " ORDER BY fm_workorder.budget {$sort}, fm_workorder.id"; break; case 'approved': $order_field = ", fm_workorder.approved"; $ordermethod = " ORDER BY fm_workorder.approved {$sort}, fm_workorder.id"; break; case 'b_account_id': $order_field = ", fm_workorder.account_id"; $ordermethod = " ORDER BY fm_workorder.account_id {$sort}, fm_workorder.id"; break; default: $order_field = ", {$order}"; } } else { // $ordermethod = ' ORDER BY fm_workorder.project_id DESC,fm_workorder.id DESC'; $ordermethod = ' ORDER BY fm_workorder.id DESC'; } //_debug_array($order_field);die; $where = 'WHERE'; $filtermethod = ''; if (isset($GLOBALS['phpgw']->config->config_data['acl_at_location']) && $GLOBALS['phpgw']->config->config_data['acl_at_location']) { $access_location = $this->bocommon->get_location_list(PHPGW_ACL_READ); $filtermethod = " WHERE fm_project.loc1 in ('" . implode("','", $access_location) . "')"; $where = 'AND'; } if ($cat_id > 0) { $cats = CreateObject('phpgwapi.categories', -1, 'property', '.project'); $cats->supress_info = true; $cat_list_project = $cats->return_sorted_array(0, false, '', '', '', false, $cat_id, false); $cat_filter = array($cat_id); foreach ($cat_list_project as $_category) { $cat_filter[] = $_category['id']; } $filtermethod .= " {$where} fm_workorder.category IN (" . implode(',', $cat_filter) . ')'; $where = 'AND'; } if ($status_id && $status_id != 'all') { if ($status_id == 'open') { $filtermethod .= " {$where} fm_workorder_status.closed IS NULL"; /* $_status_filter = array(); $this->db->query("SELECT * FROM fm_workorder_status WHERE closed IS NULL"); $this->db->query("SELECT * FROM fm_workorder_status WHERE delivered IS NULL AND closed IS NULL"); while($this->db->next_record()) { $_status_filter[] = $this->db->f('id'); } $filtermethod .= " $where fm_workorder.status IN ('" . implode("','", $_status_filter) . "')"; */ } else { $filtermethod .= " {$where} fm_workorder.status='{$status_id}' "; } $where = 'AND'; } $group_method = ''; if ($wo_hour_cat_id) { $filtermethod .= " {$where} fm_wo_hours_category.id={$wo_hour_cat_id} "; $where = 'AND'; $group_method = " group by fm_project.id,{$location_table}.location_code,fm_workorder.id,workorder_id,title,fm_workorder.status,fm_workorder.entry_date,user_lid,fm_workorder.vendor_id,project_owner,{$location_table}.address,fm_vendor.org_name,fm_workorder.combined_cost,fm_workorder.actual_cost,fm_workorder.act_vendor_cost"; } if ($b_group) { $filtermethod .= " {$where} fm_b_account.category='{$b_group}' "; $where = 'AND'; } if ($paid) { /* 0 => cancelled, 1 => obligation , 2 => paid */ $filtermethod .= " {$where} fm_workorder.paid = {$paid} AND vendor_id > 0"; $where = 'AND'; } if ($ecodimb) { $filtermethod .= " {$where} fm_project.ecodimb =" . (int) $ecodimb; $where = 'AND'; } if ($b_account) { $filtermethod .= " {$where} fm_workorder.account_id = '{$b_account}'"; $where = 'AND'; } if ($district_id) { $filtermethod .= " {$where} district_id = {$district_id}"; $where = 'AND'; } if (is_array($this->grants)) { $grants = $this->grants; while (list($user) = each($grants)) { $public_user_list[] = $user; } reset($public_user_list); $filtermethod .= " {$where} (fm_project.access='public' AND fm_project.user_id IN(" . implode(',', $public_user_list) . ")"; $where = 'AND'; } if ($filter) { $filtermethod .= " {$where} fm_workorder.user_id={$filter}"; $where = 'AND'; } if ($start_date) { $end_date = $end_date + 3600 * 16 + phpgwapi_datetime::user_timezone(); $start_date = $start_date - 3600 * 8 + phpgwapi_datetime::user_timezone(); $filtermethod .= " {$where} (fm_workorder.start_date >= {$start_date} AND fm_workorder.start_date <= {$end_date}"; if ($obligation) { $filtermethod .= " OR fm_workorder_status.closed IS NULL)"; } else { $filtermethod .= ')'; } $where = 'AND'; } if ($tender_deadline) { $tender_deadline = $tender_deadline - 3600 * 8 + phpgwapi_datetime::user_timezone(); $filtermethod .= " {$where} (fm_workorder.tender_deadline IS NOT NULL AND fm_workorder.tender_deadline > 0" . " AND fm_workorder.tender_deadline <= {$tender_deadline} AND" . " (fm_workorder.tender_received = 0 OR fm_workorder.tender_received IS NULL))"; $where = 'AND'; } if ($inspection_on_completion) { $inspection_on_completion = $inspection_on_completion - 3600 * 8 + phpgwapi_datetime::user_timezone(); $filtermethod .= " {$where} (fm_workorder.tender_deadline IS NOT NULL AND fm_workorder.tender_deadline > 0" . " AND fm_workorder.end_date IS NOT NULL AND fm_workorder.end_date > 0" . " AND fm_workorder.end_date <= {$inspection_on_completion} AND" . " (fm_workorder.inspection_on_completion = 0 OR fm_workorder.inspection_on_completion IS NULL))"; $where = 'AND'; } if ($filter_year && $filter_year != 'all') { $filter_year = (int) $filter_year; $filtermethod .= " {$where} (fm_workorder_budget.year={$filter_year} OR fm_workorder_status.closed IS NULL)"; $where = 'AND'; } $querymethod = ''; if ($query) { $query = $this->db->db_addslashes($query); $query = str_replace(",", '.', $query); if (stristr($query, '.')) { $query = explode(".", $query); $querymethod = " {$where} ({$location_table}.location_code {$this->like} '{$query[0]}%' AND {$location_table}.location_code {$this->like} '%{$query[1]}')"; } else { $matchtypes = array('exact' => '=', 'like' => $this->like); if (count($criteria) > 1) { $_querymethod = array(); foreach ($criteria as $field_info) { if ($field_info['type'] == int) { $_query = (int) $query; } else { if ($field_info['type'] == 'bigint' && !ctype_digit($query)) { $_query = 0; } else { $_query = $query; } } $_querymethod[] = "{$field_info['field']} {$matchtypes[$field_info['matchtype']]} {$field_info['front']}{$_query}{$field_info['back']}"; } $querymethod = $where . ' (' . implode(' OR ', $_querymethod) . ')'; unset($_querymethod); //_debug_array($querymethod); } else { if ($criteria[0]['type'] == 'int') { $_query = (int) $query; } else { if ($criteria[0]['type'] == 'bigint' && !ctype_digit($query)) { $_query = 0; } else { $_query = $query; } } $querymethod = "{$where} {$criteria[0]['field']} {$matchtypes[$criteria[0]['matchtype']]} {$criteria[0]['front']}{$_query}{$criteria[0]['back']}"; } } $where = 'AND'; } $querymethod .= ')'; $sql_full = "{$sql} {$filtermethod} {$querymethod}"; $sql_base = substr($sql_full, strripos($sql_full, 'FROM')); if ($GLOBALS['phpgw_info']['server']['db_type'] == 'postgres') { $sql_minimized = "SELECT DISTINCT fm_workorder.id {$sql_base}"; $sql_count = "SELECT count(id) as cnt FROM ({$sql_minimized}) as t"; $this->db->query($sql_count, __LINE__, __FILE__); $this->db->next_record(); $this->total_records = $this->db->f('cnt'); } else { $sql_count = 'SELECT DISTINCT fm_workorder.id ' . substr($sql_full, strripos($sql_full, 'FROM')); $this->db->query($sql_count, __LINE__, __FILE__); $this->total_records = $this->db->num_rows(); } $workorder_list = array(); if ($dry_run) { return $workorder_list; } $sql_end = str_replace('SELECT DISTINCT fm_workorder.id', "SELECT DISTINCT fm_workorder.id {$order_field}", $sql_minimized) . $ordermethod; // _debug_array($sql_end); if (!$allrows) { $this->db->limit_query($sql_end, $start, __LINE__, __FILE__); } else { $_fetch_single = false; //FIXME: something wrong here... /* if($this->total_records > 200) { $_fetch_single = true; } else { $_fetch_single = false; } */ $this->db->query($sql_end, __LINE__, __FILE__, false, $_fetch_single); unset($_fetch_single); } $count_cols_return = count($cols_return); $_order_list = array(); while ($this->db->next_record()) { $workorder_list[] = array('workorder_id' => $this->db->f('id')); $_order_list[] = $this->db->f('id'); } $this->db->set_fetch_single(false); $_actual_cost_arr = array(); $this->db->query('SELECT id, percent FROM fm_ecomva', __LINE__, __FILE__); $_taxcode = array(0 => 0); while ($this->db->next_record()) { $_taxcode[$this->db->f('id')] = $this->db->f('percent'); } foreach ($workorder_list as &$workorder) { $this->db->query("{$sql} WHERE fm_workorder.id = '{$workorder['workorder_id']}'"); $this->db->next_record(); for ($i = 0; $i < $count_cols_return; $i++) { $workorder[$cols_return[$i]] = $this->db->f($cols_return[$i]); } $workorder['actual_cost'] = 0; $workorder['obligation'] = 0; $workorder['combined_cost'] = 0; $workorder['budget'] = 0; //--------- $workorder['grants'] = (int) $this->grants[$this->db->f('project_owner')]; $location_code = $this->db->f('location_code'); $location = explode('-', $location_code); $count_location = count($location); for ($m = 0; $m < $count_location; $m++) { $workorder['loc' . ($m + 1)] = $location[$m]; $workorder['query_location']['loc' . ($m + 1)] = implode("-", array_slice($location, 0, $m + 1)); } } reset($workorder_list); foreach ($workorder_list as &$workorder) { $order_budget = $this->get_budget($workorder['workorder_id']); foreach ($order_budget as $entry) { if ($entry['active'] == 2) { continue; } if ($filter_year && $filter_year != 'all') { if ($entry['year'] == $filter_year) { $workorder['actual_cost'] += $entry['actual_cost']; $workorder['combined_cost'] += $entry['sum_orders']; $workorder['budget'] += $entry['budget']; $workorder['obligation'] += $entry['sum_oblications']; } } else { $workorder['actual_cost'] += $entry['actual_cost']; if ($entry['active']) { $workorder['combined_cost'] += $entry['sum_orders']; $workorder['budget'] += $entry['budget']; $workorder['obligation'] += $entry['sum_oblications']; } } } $_diff_start = abs($workorder['budget']) > 0 ? $workorder['budget'] : $workorder['combined_cost']; $workorder['diff'] = $_diff_start - $workorder['obligation'] - $workorder['actual_cost']; } return $workorder_list; }
function list_repeated_events($data = array()) { $syear = $data['syear']; $smonth = $data['smonth']; $sday = $data['sday']; $eyear = $data['eyear']; $emonth = $data['emonth']; $eday = $data['eday']; $owner_id = $data['owner_id'] ? $data['owner_id'] : 0; $location_id = (int) $data['location_id']; $location_item_id = $data['location_item_id']; if (!$syear || !$smonth || !$sday || !$eyear || !$emonth || !$eday || !$location_id || !$location_item_id) { throw new Exception("property_soevent::list_repeated_events - Missing date info"); } $user_timezone = phpgwapi_datetime::user_timezone(); $starttime = mktime(0, 0, 0, $smonth, $sday, $syear) - $user_timezone; $endtime = mktime(23, 59, 59, $emonth, $eday, $eyear) - $user_timezone; $sql = "(fm_event.location_id = {$location_id} AND fm_event.location_item_id = {$location_item_id})" . ' AND ((fm_event.end_date >= ' . $starttime . ') OR (fm_event.end_date=0))' . ' ORDER BY fm_event.start_date ASC, fm_event.end_date ASC'; return $this->get_event_ids(true, $sql); }
function prepare_read_holidays($year = 0, $owner = 0) { $this->year = isset($year) && $year > 0 ? $year : $GLOBALS['phpgw']->common->show_date(time() - phpgwapi_datetime::user_timezone(), 'Y'); $this->owner = $owner ? $owner : $GLOBALS['phpgw_info']['user']['account_id']; if ($this->debug) { echo 'Setting Year to : ' . $this->year . '<br />' . "\n"; } if (@$GLOBALS['phpgw_info']['user']['preferences']['common']['country']) { $this->locales[] = $GLOBALS['phpgw_info']['user']['preferences']['common']['country']; } elseif (@$GLOBALS['phpgw_info']['user']['preferences']['calendar']['locale']) { $this->locales[] = $GLOBALS['phpgw_info']['user']['preferences']['calendar']['locale']; } else { $this->locales[] = 'US'; } if ($this->owner != $GLOBALS['phpgw_info']['user']['account_id']) { $owner_pref = CreateObject('phpgwapi.preferences', $owner); $owner_prefs = $owner_pref->read(); if (@$owner_prefs['common']['country']) { $this->locales[] = $owner_prefs['common']['country']; } elseif (@$owner_prefs['calendar']['locale']) { $this->locales[] = $owner_prefs['calendar']['locale']; } unset($owner_pref); } if (isset($GLOBALS['phpgw_info']['server']['auto_load_holidays']) && $GLOBALS['phpgw_info']['server']['auto_load_holidays'] == True && is_array($this->locales)) { foreach ($this->locales as $locale) { $this->auto_load_holidays($locale); } } }
/** * Prepare custom attributes for ui * * @param array $values values and definitions of custom attributes * @param ???? $appname ???? * @param ???? $location ???? * @param ???? $view_only ???? * * @return array values and definitions of custom attributes prepared for ui */ public function prepare($values, $appname, $location, $view_only = '') { $cache_custom_lookup = array(); $vendor = CreateObject('property.sogeneric'); $vendor->get_location_info('vendor', false); $location_id = $GLOBALS['phpgw']->locations->get_id($appname, $location); // _debug_array($location_id); // _debug_array($values);die(); $dateformat = $GLOBALS['phpgw_info']['user']['preferences']['common']['dateformat']; $input_type_array = array('R' => 'radio', 'CH' => 'checkbox', 'LB' => 'listbox'); $m = 0; $i = 0; foreach ($values['attributes'] as &$attributes) { $attributes['datatype_text'] = $this->translate_datatype($attributes['datatype']); $attributes['help_url'] = $attributes['helpmsg'] ? $GLOBALS['phpgw']->link('/index.php', array('menuaction' => 'manual.uimanual.attrib_help', 'appname' => $appname, 'location' => $location, 'id' => $attributes['id'])) : ''; if (!$view_only && $attributes['history']) { $GLOBALS['phpgw']->jqcal->add_listener("values_attribute_{$i}_date"); } if ($attributes['datatype'] == 'D' || $attributes['datatype'] == 'DT' || $attributes['datatype'] == 'date' || $attributes['datatype'] == 'timestamp') { if (!$view_only) { $GLOBALS['phpgw']->jqcal->add_listener('values_attribute_' . $i); $attributes['lang_datetitle'] = lang('Select date'); } if ($attributes['datatype'] == 'D') { $clear_functions[$m]['name'] = "clear_{$attributes['name']}()"; $confirm_msg = lang('delete') . '?'; $clear_functions[$m]['action'] = <<<JS \t\t\t\t\t\t\tif(confirm("{$confirm_msg}")) \t\t\t\t\t\t\t{ \t\t\t\t\t\t\t\tvar attribute_{$i}_date = document.getElementById('values_attribute_{$i}'); \t\t\t\t\t\t\t\tattribute_{$i}_date.value = ''; \t\t\t\t\t\t\t} JS; $m++; } else { if ($attributes['datatype'] == 'DT') { $clear_functions[$m]['name'] = "clear_{$attributes['name']}()"; $confirm_msg = lang('delete') . '?'; $clear_functions[$m]['action'] = <<<JS \t\t\t\t\t\t\tif(confirm("{$confirm_msg}")) \t\t\t\t\t\t\t{ \t\t\t\t\t\t\t\tvar attribute_{$i}_date = document.getElementById('values_attribute_{$i}'); \t\t\t\t\t\t\t\tvar attribute_{$i}_hour = document.getElementById('values_attribute_{$i}_hour'); \t\t\t\t\t\t\t\tvar attribute_{$i}_min = document.getElementById('values_attribute_{$i}_min'); \t\t\t\t\t\t\t\tattribute_{$i}_date.value = ''; \t\t\t\t\t\t\t\tattribute_{$i}_hour.value = ''; \t\t\t\t\t\t\t\tattribute_{$i}_min.value = ''; \t\t\t\t\t\t\t} JS; $m++; } } if (isset($attributes['value']) && $attributes['value']) { if ($attributes['datatype'] == 'DT') { $timestamp = strtotime($attributes['value']); $attributes['value'] = array(); $attributes['value']['date'] = $GLOBALS['phpgw']->common->show_date($timestamp, $dateformat); $attributes['value']['hour'] = date('H', $timestamp + phpgwapi_datetime::user_timezone()); $attributes['value']['min'] = date('i', $timestamp + phpgwapi_datetime::user_timezone()); } else { $timestamp_date = mktime(0, 0, 0, date('m', strtotime($attributes['value'])), date('d', strtotime($attributes['value'])), date('y', strtotime($attributes['value']))); $attributes['value'] = $GLOBALS['phpgw']->common->show_date($timestamp_date, $dateformat); } } } else { if ($attributes['datatype'] == 'AB') { if ($attributes['value']) { $contact_data = $this->contacts->read_single_entry($attributes['value'], array('fn', 'tel_work', 'email')); $attributes['contact_name'] = $contact_data[0]['fn']; $attributes['contact_email'] = $contact_data[0]['email']; $attributes['contact_tel'] = $contact_data[0]['tel_work']; } $insert_record_values[] = $attributes['name']; $lookup_link = $GLOBALS['phpgw']->link('/index.php', array('menuaction' => 'property.uilookup.addressbook', 'column' => $attributes['name'])); $lookup_functions[$m]['name'] = 'lookup_' . $attributes['name'] . '()'; $lookup_functions[$m]['action'] = 'Window1=window.open(' . "'" . $lookup_link . "'" . ',"Search","left=50,top=100,width=800,height=700,toolbar=no,scrollbars=yes,resizable=yes");'; $clear_functions[$m]['name'] = "clear_{$attributes['name']}()"; $confirm_msg = lang('delete') . '?'; $clear_functions[$m]['action'] = <<<JS \t\t\t\t\tif(confirm("{$confirm_msg}")) \t\t\t\t\t{ \t\t\t\t\t\tdocument.getElementsByName('{$attributes['name']}')[0].value = ''; \t\t\t\t\t\tdocument.getElementsByName('{$attributes['name']}_name')[0].value = ''; \t\t\t\t\t} JS; $m++; } else { if ($attributes['datatype'] == 'ABO') { if ($attributes['value']) { $contact_data = $this->contacts->get_principal_organizations_data($attributes['value']); $attributes['org_name'] = $contact_data[0]['org_name']; $comms = $this->contacts->get_comm_contact_data($attributes['value'], $fields_comms = '', $simple = false); $comm_data = array(); if (is_array($comms)) { foreach ($comms as $key => $value) { $comm_data[$value['comm_contact_id']][$value['comm_description']] = $value['comm_data']; } } if (count($comm_data)) { $attributes['org_email'] = isset($comm_data[$attributes['value']]['work email']) ? $comm_data[$attributes['value']]['work email'] : ''; $attributes['org_tel'] = isset($comm_data[$attributes['value']]['work phone']) ? $comm_data[$attributes['value']]['work phone'] : ''; } } $insert_record_values[] = $attributes['name']; $lookup_link = $GLOBALS['phpgw']->link('/index.php', array('menuaction' => 'property.uilookup.organisation', 'column' => $attributes['name'])); $lookup_functions[$m]['name'] = 'lookup_' . $attributes['name'] . '()'; $lookup_functions[$m]['action'] = 'Window1=window.open(' . "'" . $lookup_link . "'" . ',"Search","left=50,top=100,width=800,height=700,toolbar=no,scrollbars=yes,resizable=yes");'; $m++; } else { if ($attributes['datatype'] == 'VENDOR') { if ($attributes['value']) { $vendor_data = $vendor->read_single(array('id' => $attributes['value']), array('attributes' => array(0 => array('column_name' => 'org_name')))); for ($n = 0; $n < count($vendor_data['attributes']); $n++) { if ($vendor_data['attributes'][$n]['column_name'] == 'org_name') { $attributes['vendor_name'] = $vendor_data['attributes'][$n]['value']; $n = count($vendor_data['attributes']); } } } $insert_record_values[] = $attributes['name']; $lookup_link = $GLOBALS['phpgw']->link('/index.php', array('menuaction' => 'property.uilookup.vendor', 'column' => $attributes['name'])); $lookup_functions[$m]['name'] = 'lookup_' . $attributes['name'] . '()'; $lookup_functions[$m]['action'] = 'Window1=window.open(' . "'" . $lookup_link . "'" . ',"Search","left=50,top=100,width=800,height=700,toolbar=no,scrollbars=yes,resizable=yes");'; $m++; } else { if ($attributes['datatype'] == 'custom1') { $attributes['choice'] = array(); if ($attributes['get_list_function']) { $_compare_key = $this->_get_compare_key($attributes['get_list_function'], $attributes['get_list_function_input']); if (isset($cache_custom_lookup[$_compare_key])) { $attributes['choice'] = $cache_custom_lookup[$_compare_key]; } else { $attributes['choice'] = execMethod($attributes['get_list_function'], $attributes['get_list_function_input']); $cache_custom_lookup[$_compare_key] = $attributes['choice']; } } foreach ($attributes['choice'] as &$_choice) { $_choice['selected'] = $_choice['id'] == $attributes['value'] ? 1 : 0; } } else { if ($attributes['datatype'] == 'custom2') { if ($attributes['value'] && $attributes['get_single_function']) { if (!$attributes['get_single_function_input']) { $attributes['get_single_function_input'] = $attributes['value']; } $attributes['custom_name'] = execMethod($attributes['get_single_function'], $attributes['get_single_function_input']); } $insert_record_values[] = $attributes['name']; $lookup_link = $GLOBALS['phpgw']->link('/index.php', array('menuaction' => 'property.uilookup.custom', 'column' => $attributes['name'], 'get_list_function' => $attributes['get_list_function'], 'get_list_function_input' => urlencode(serialize($attributes['get_list_function_input'])))); $lookup_functions[$m]['name'] = 'lookup_' . $attributes['name'] . '()'; $lookup_functions[$m]['action'] = 'Window1=window.open(' . "'" . $lookup_link . "'" . ',"Search","left=50,top=100,width=800,height=700,toolbar=no,scrollbars=yes,resizable=yes");'; $m++; } else { if ($attributes['datatype'] == 'custom3') { if ($attributes['value'] && $attributes['get_single_function']) { if (!$attributes['get_single_function_input']) { $attributes['get_single_function_input'] = $attributes['value']; } $attributes['custom_name'] = execMethod($attributes['get_single_function'], $attributes['get_single_function_input']); } $insert_record_values[] = $attributes['name']; $_append_url = ''; if (isset($attributes['get_list_function_input']) && is_array($attributes['get_list_function_input'])) { $_append_url = '&' . http_build_query($attributes['get_list_function_input']); } $_autocomplete = <<<JS \t\t\t\t\tYAHOO.util.Event.addListener(window, "load", function() \t\t\t\t\t{ \t\t\t\t\t\tvar oArgs = {menuaction:'{$attributes['get_list_function']}'}; \t\t\t\t\t\tvar strURL = phpGWLink('index.php', oArgs, true); \t\t\t\t\t\tstrURL += '{$_append_url}'; \t\t\t\t\t YAHOO.portico.autocompleteHelper(strURL, '{$attributes['name']}_name', '{$attributes['name']}_id', '{$attributes['name']}_container'); \t\t\t\t\t}); JS; if (!$view_only) { $GLOBALS['phpgw']->js->add_code('', $_autocomplete); } } else { if ($attributes['datatype'] == 'user') { if ($attributes['value']) { $attributes['user_name'] = $GLOBALS['phpgw']->accounts->id2name($attributes['value']); } $insert_record_values[] = $attributes['name']; $lookup_link = $GLOBALS['phpgw']->link('/index.php', array('menuaction' => $this->_appname . '.uilookup.phpgw_user', 'column' => $attributes['name'])); $lookup_functions[$m]['name'] = 'lookup_' . $attributes['name'] . '()'; $lookup_functions[$m]['action'] = 'Window1=window.open(' . "'" . $lookup_link . "'" . ',"Search","left=50,top=100,width=800,height=700,toolbar=no,scrollbars=yes,resizable=yes");'; $m++; } else { if ($attributes['datatype'] == 'R' || $attributes['datatype'] == 'CH' || $attributes['datatype'] == 'LB') { $input_type = $input_type_array[$attributes['datatype']]; if ($attributes['datatype'] == 'CH') { // $attributes['value'] = unserialize($attributes['value']); $attributes['value'] = explode(',', trim($attributes['value'], ',')); if (isset($attributes['choice']) and is_array($attributes['choice'])) { foreach ($attributes['choice'] as &$choice) { $choice['input_type'] = $input_type; if (isset($attributes['value']) && is_array($attributes['value'])) { foreach ($attributes['value'] as &$selected) { if ($selected == $choice['id']) { $choice['checked'] = 'checked'; } } } } } } else { for ($j = 0; $j < count($attributes['choice']); $j++) { $attributes['choice'][$j]['input_type'] = $input_type; if ($attributes['choice'][$j]['id'] == $attributes['value']) { $attributes['choice'][$j]['checked'] = 'checked'; } } } } else { if ($attributes['datatype'] == 'event') { // If the record is not saved - issue a warning if (isset($values['id']) || $values['id']) { $attributes['item_id'] = $values['id']; } else { if (isset($values['location_code']) || $values['location_code']) { $attributes['item_id'] = execMethod('property.solocation.get_item_id', $values['location_code']); } else { $attributes['warning'] = lang('Warning: the record has to be saved in order to plan an event'); } } if (isset($attributes['value']) && $attributes['value']) { $event = execMethod('property.soevent.read_single', $attributes['value']); $attributes['descr'] = $event['descr']; $attributes['enabled'] = $event['enabled'] ? lang('yes') : lang('no'); $attributes['lang_enabled'] = lang('enabled'); $id = "property{$location}::{$attributes['item_id']}::{$attributes['value']}"; $job = execMethod('phpgwapi.asyncservice.read', $id); $attributes['next'] = $GLOBALS['phpgw']->common->show_date($job[$id]['next'], $dateformat); $attributes['lang_next_run'] = lang('next run'); unset($event); unset($id); unset($job); } $insert_record_values[] = $attributes['name']; $lookup_functions[$m]['name'] = 'lookup_' . $attributes['name'] . '()'; $lookup_functions[$m]['action'] = "var oArgs = {menuaction:'{$this->_appname}.uievent.edit'," . "location:'{$location}'," . "attrib_id:'{$attributes['id']}'"; $lookup_functions[$m]['action'] .= isset($attributes['item_id']) && $attributes['item_id'] ? ",item_id:{$attributes['item_id']}" : ''; $lookup_functions[$m]['action'] .= isset($attributes['value']) && $attributes['value'] ? ",id:{$attributes['value']}" : ''; $lookup_functions[$m]['action'] .= "};\n"; $lookup_functions[$m]['action'] .= "if(document.form.{$attributes['name']}.value)\n"; $lookup_functions[$m]['action'] .= "{\n"; $lookup_functions[$m]['action'] .= "oArgs['id'] = document.form.{$attributes['name']}.value;"; $lookup_functions[$m]['action'] .= "}\n"; $lookup_functions[$m]['action'] .= "var strURL = phpGWLink('index.php', oArgs);\n"; $lookup_functions[$m]['action'] .= 'Window1=window.open(strURL,"Search","left=50,top=100,width=800,height=500,toolbar=no,scrollbars=yes,resizable=yes");'; $m++; } else { if (isset($entity['attributes'][$i]) && $entity['attributes'][$i]['datatype'] != 'I' && $entity['attributes'][$i]['value']) { $entity['attributes'][$i]['value'] = stripslashes($entity['attributes'][$i]['value']); } } } } } } } } } } } $attributes['datatype_text'] = $this->translate_datatype($attributes['datatype']); $attributes['counter'] = $i; $i++; } if (isset($lookup_functions) && is_array($lookup_functions)) { foreach ($lookup_functions as $lookup_function) { $values['lookup_functions'] .= 'function ' . $lookup_function['name'] . "\r\n"; $values['lookup_functions'] .= '{' . "\r\n"; $values['lookup_functions'] .= $lookup_function['action'] . "\r\n"; $values['lookup_functions'] .= '}' . "\r\n"; } } if (isset($clear_functions) && $clear_functions) { foreach ($clear_functions as $clear_function) { $values['lookup_functions'] .= 'function ' . $clear_function['name'] . "\r\n"; $values['lookup_functions'] .= '{' . "\r\n"; $values['lookup_functions'] .= $clear_function['action'] . "\r\n"; $values['lookup_functions'] .= '}' . "\r\n"; } } //_debug_array($values);die(); if (isset($lookup_functions) && $lookup_functions) { $GLOBALS['phpgw']->session->appsession('insert_record_values' . $location, $appname, $insert_record_values); } return $values; }
function period_list($selected = '') { $sogeneric = CreateObject('property.sogeneric', 'period_transition'); $config = $sogeneric->read(array('allrows' => true)); $period_transition = array(); foreach ($config as $entry) { $period_transition[$entry['month']] = $entry; } $current_month = date('n'); if (isset($period_transition[$current_month])) { $_lag_day = (int) $period_transition[$current_month]['day']; $_lag_hour = (int) $period_transition[$current_month]['hour']; $_lag_seconds = $_lag_day * 24 * 3600 + $_lag_hour * 3600; } else { if (isset($period_transition[13])) { $_lag_day = (int) $period_transition[13]['day']; $_lag_hour = (int) $period_transition[13]['hour']; $_lag_seconds = $_lag_day * 24 * 3600 + $_lag_hour * 3600; } else { $_lag = date('n') == 1 ? 11 : 7; //6 days into next month, 10 days into next year $_lag_seconds = $_lag * 24 * 3600; } } $_lag_seconds -= phpgwapi_datetime::user_timezone(); //_debug_array($period_transition); $time = time(); $timestamp_at_start_month = mktime($hour = 0, $minute = 0, $second = 0, $month = date("n"), $day = 0, $year = date("Y")); if ($time - $timestamp_at_start_month < $_lag_seconds) { $time = $time - $_lag_seconds; } $month = date('n', $time); $year = date('Y', $time); $check_year = true; $period_list = array(); for ($i = $month; $i <= 12; $i++) { $period = sprintf("%s%02d", $year, $i); $period_list[] = array('id' => $period, 'name' => $period); if ($check_year = true && $year != date('Y')) { $i = 0; $year++; $check_year = false; } } if (count($period_list) == 1) { $year++; $period = sprintf("%s%02d", $year, 1); $period_list[] = array('id' => $period, 'name' => $period); } foreach ($period_list as &$_period) { $_period['selected'] = $_period['id'] == $selected ? 1 : 0; } return $period_list; }
function read($data) { $start = isset($data['start']) && $data['start'] ? $data['start'] : 0; $status_id = isset($data['status_id']) && $data['status_id'] ? $data['status_id'] : 'O'; //O='Open' $user_id = isset($data['user_id']) && $data['user_id'] ? (int) $data['user_id'] : 0; $owner_id = isset($data['owner_id']) ? $data['owner_id'] : ''; $query = isset($data['query']) ? $data['query'] : ''; $sort = isset($data['sort']) && $data['sort'] ? $data['sort'] : 'DESC'; $order = isset($data['order']) ? $data['order'] : ''; $cat_id = isset($data['cat_id']) && $data['cat_id'] ? $data['cat_id'] : 0; $district_id = isset($data['district_id']) && $data['district_id'] ? $data['district_id'] : 0; $allrows = isset($data['allrows']) ? $data['allrows'] : ''; $start_date = isset($data['start_date']) && $data['start_date'] ? (int) $data['start_date'] : 0; $end_date = isset($data['end_date']) && $data['end_date'] ? (int) $data['end_date'] : 0; $dry_run = isset($data['dry_run']) ? $data['dry_run'] : ''; $new = isset($data['new']) ? $data['new'] : ''; $location_code = isset($data['location_code']) ? $data['location_code'] : ''; $p_num = isset($data['p_num']) ? $data['p_num'] : ''; $this->grants = $GLOBALS['phpgw']->session->appsession('grants_ticket', 'helpdesk'); if (!$this->grants) { $GLOBALS['phpgw']->acl->set_account_id($this->account); $this->grants = $GLOBALS['phpgw']->acl->get_grants('helpdesk', '.ticket'); $GLOBALS['phpgw']->session->appsession('grants_ticket', 'helpdesk', $this->grants); } $result_order_field = ''; if ($order) { if ($order == 'assignedto') { $result_order_field = ',account_lastname'; $order_join = "LEFT OUTER JOIN phpgw_accounts ON phpgw_helpdesk_tickets.assignedto=phpgw_accounts.account_id"; $order = 'account_lastname'; } else { if ($order == 'user') { $result_order_field = ',account_lastname'; $order_join = "LEFT OUTER JOIN phpgw_accounts ON phpgw_helpdesk_tickets.user_id=phpgw_accounts.account_id"; $order = 'account_lastname'; } else { $order_join = ''; } } $ordermethod = " ORDER BY {$order} {$sort}"; } else { $ordermethod = ' ORDER BY phpgw_helpdesk_tickets.id DESC'; } $filtermethod = ''; $categories = $GLOBALS['phpgw']->locations->get_subs('helpdesk', '.ticket.category'); $grant_category = array(); foreach ($categories as $location) { if ($GLOBALS['phpgw']->acl->check($location, PHPGW_ACL_READ, 'helpdesk')) { $category = explode('.', $location); $grant_category[] = $category[3]; } } $grant_category[] = -1; //If no one found - not breaking the query $where = 'WHERE'; $GLOBALS['phpgw']->config->read(); if (isset($GLOBALS['phpgw']->config->config_data['acl_at_tts_category']) && $GLOBALS['phpgw']->config->config_data['acl_at_tts_category']) { $filtermethod = " WHERE phpgw_helpdesk_tickets.cat_id IN (" . implode(",", $grant_category) . ")"; $where = 'AND'; } if (isset($GLOBALS['phpgw']->config->config_data['acl_at_location']) && $GLOBALS['phpgw']->config->config_data['acl_at_location']) { $access_location = execMethod('property.socommon.get_location_list', PHPGW_ACL_READ); if ($access_location) { $filtermethod .= " {$where} phpgw_helpdesk_tickets.loc1 in ('" . implode("','", $access_location) . "')"; $where = 'AND'; } } if (is_array($this->grants)) { $grants =& $this->grants; foreach ($grants as $user => $right) { $public_user_list[] = $user; } reset($public_user_list); $filtermethod .= " {$where} ( phpgw_helpdesk_tickets.user_id IN(" . implode(',', $public_user_list) . "))"; $where = 'AND'; } if ($tenant_id = $GLOBALS['phpgw']->session->appsession('tenant_id', 'helpdesk')) { $filtermethod .= $where . ' phpgw_helpdesk_tickets.tenant_id=' . $tenant_id; $where = 'AND'; } if ($status_id == 'X') { $closed = ''; $this->db->query('SELECT * from phpgw_helpdesk_status', __LINE__, __FILE__); while ($this->db->next_record()) { if ($this->db->f('closed')) { $closed .= " OR phpgw_helpdesk_tickets.status = 'C" . $this->db->f('id') . "'"; } } $filtermethod .= " {$where} ( (phpgw_helpdesk_tickets.status='X'{$closed})"; $where = 'AND'; // $filtermethod .= " $where ( phpgw_helpdesk_tickets.status='X'"; // $where = 'AND'; } else { if ($status_id == 'O2') { $filtermethod .= " {$where} ( phpgw_helpdesk_tickets.status='O'"; $where = 'AND'; } else { if ($status_id == 'O') { $open = ''; $this->db->query('SELECT * from phpgw_helpdesk_status', __LINE__, __FILE__); while ($this->db->next_record()) { if (!$this->db->f('closed')) { $open .= " OR phpgw_helpdesk_tickets.status = 'C" . $this->db->f('id') . "'"; } } $filtermethod .= " {$where} ( (phpgw_helpdesk_tickets.status='O'{$open})"; $where = 'AND'; } else { if ($status_id == 'all') { $filtermethod .= "{$where} (1=1"; //nothing $where = 'AND'; } else { if (is_array($status_id) && count($status_id)) { $or = ''; $filtermethod .= "{$where} (("; foreach ($status_id as $value) { if ($value) { $filtermethod .= "{$or} phpgw_helpdesk_tickets.status = '{$value}'"; $or = ' OR'; } } $filtermethod .= ')'; $where = 'AND'; } else { $filtermethod .= " {$where} (phpgw_helpdesk_tickets.status='{$status_id}'"; $where = 'AND'; } } } } } if ($new) { $filtermethod .= " OR phpgw_helpdesk_views.id IS NULL )"; } else { $filtermethod .= ')'; } if ($cat_id > 0) { $filtermethod .= " {$where} cat_id=" . (int) $cat_id; $where = 'AND'; } if ($user_id > 0) { $filtermethod .= " {$where} (assignedto={$user_id}"; $where = 'AND'; $membership = $GLOBALS['phpgw']->accounts->membership($user_id); $filtermethod .= ' OR (assignedto IS NULL AND group_id IN (' . implode(',', array_keys($membership)) . ')))'; } if ($owner_id > 0) { $filtermethod .= " {$where} phpgw_helpdesk_tickets.user_id=" . (int) $owner_id; $where = 'AND'; } if ($district_id > 0) { $filtermethod .= " {$where} district_id=" . (int) $district_id; $where = 'AND'; } if ($start_date) { $end_date = $end_date + 3600 * 16 + phpgwapi_datetime::user_timezone(); $start_date = $start_date - 3600 * 8 + phpgwapi_datetime::user_timezone(); $filtermethod .= " {$where} phpgw_helpdesk_tickets.entry_date >= {$start_date} AND phpgw_helpdesk_tickets.entry_date <= {$end_date} "; $where = 'AND'; } if ($location_code) { $filtermethod .= " {$where} phpgw_helpdesk_tickets.location_code {$this->like} '{$location_code}%'"; $where = 'AND'; } $querymethod = ''; if ($query) { $query = $this->db->db_addslashes($query); $query = str_replace(",", '.', $query); if (stristr($query, '.') && !$p_num) { $query = explode(".", $query); $querymethod = " {$where} (phpgw_helpdesk_tickets.loc1='" . $query[0] . "' AND phpgw_helpdesk_tickets.loc4='" . $query[1] . "')"; } else { if (stristr($query, '.') && $p_num) { $query = explode(".", $query); $querymethod = " {$where} (phpgw_helpdesk_tickets.p_entity_id='" . (int) $query[1] . "' AND phpgw_helpdesk_tickets.p_cat_id='" . (int) $query[2] . "' AND phpgw_helpdesk_tickets.p_num='" . (int) $query[3] . "')"; } else { $querymethod = " {$where} (subject {$this->like} '%{$query}%'" . " OR address {$this->like} '%{$query}%' " . " OR fm_location1.loc1_name {$this->like} '%{$query}%'" . " OR phpgw_helpdesk_tickets.location_code {$this->like} '%{$query}%'" . " OR phpgw_helpdesk_tickets.order_id =" . (int) $query . ')'; } } } $sql = "SELECT DISTINCT phpgw_helpdesk_tickets.* , phpgw_helpdesk_views.id as view {$result_order_field} FROM phpgw_helpdesk_tickets" . " {$order_join}" . " LEFT OUTER JOIN phpgw_helpdesk_views ON (phpgw_helpdesk_tickets.id = phpgw_helpdesk_views.id AND phpgw_helpdesk_views.account_id='{$this->account}')" . " {$filtermethod} {$querymethod}"; $sql2 = "SELECT count(*) as cnt FROM ({$sql}) as t"; $this->db->query($sql2, __LINE__, __FILE__); $this->db->next_record(); $this->total_records = $this->db->f('cnt'); unset($sql2); $tickets = array(); if (!$dry_run) { if (!$allrows) { $this->db->limit_query($sql . $ordermethod, $start, __LINE__, __FILE__); } else { if ($this->total_records > 200) { $_fetch_single = true; } else { $_fetch_single = false; } $this->db->query($sql . $ordermethod, __LINE__, __FILE__, false, $_fetch_single); unset($_fetch_single); } while ($this->db->next_record()) { $tickets[] = array('id' => (int) $this->db->f('id'), 'subject' => $this->db->f('subject', true), 'loc1_name' => $this->db->f('loc1_name', true), 'location_code' => $this->db->f('location_code'), 'district' => $this->db->f('district', true), 'user_id' => $this->db->f('user_id'), 'address' => $this->db->f('address', true), 'assignedto' => $this->db->f('assignedto'), 'status' => $this->db->f('status'), 'priority' => $this->db->f('priority'), 'cat_id' => $this->db->f('cat_id'), 'group_id' => $this->db->f('group_id'), 'entry_date' => $this->db->f('entry_date'), 'finnish_date' => $this->db->f('finnish_date'), 'finnish_date2' => $this->db->f('finnish_date2'), 'order_id' => $this->db->f('order_id'), 'vendor_id' => $this->db->f('vendor_id'), 'actual_cost' => $this->db->f('actual_cost'), 'estimate' => $this->db->f('budget'), 'new_ticket' => $this->db->f('view') ? false : true, 'billable_hours' => $this->db->f('billable_hours')); } /* foreach ($tickets as &$ticket) { $this->db->query("SELECT count(*) as hits FROM phpgw_helpdesk_views where id={$ticket['id']}" . " AND account_id='{$this->account}'",__LINE__,__FILE__); $this->db->next_record(); if(! $this->db->f('hits')) { $ticket['new_ticket'] = true; } } */ } return $tickets; }
function edit_form($param) { if (!is_array($param)) { $this->index(); } $str = ''; if (isset($param['event'])) { $event = $param['event']; } $hourformat = substr($this->bo->users_timeformat, 0, 1); // $sb = CreateObject('phpgwapi.sbox'); $sb = CreateObject('phpgwapi.sbox'); $jscal = CreateObject('phpgwapi.jscalendar'); unset($GLOBALS['phpgw_info']['flags']['noheader']); unset($GLOBALS['phpgw_info']['flags']['nonavbar']); $GLOBALS['phpgw_info']['flags']['noappheader'] = True; $GLOBALS['phpgw_info']['flags']['noappfooter'] = True; $GLOBALS['phpgw_info']['flags']['app_header'] = $event['id'] ? lang('Calendar - Edit') : lang('Calendar - Add'); $this->header(false); $p =& $GLOBALS['phpgw']->template; $p->set_file(array('edit' => 'edit.tpl', 'form_button' => 'form_button_script.tpl')); $p->set_block('edit', 'edit_entry', 'edit_entry'); $p->set_block('edit', 'list', 'list'); $p->set_block('edit', 'hr', 'hr'); // Recurrence Exeptions $recur_exception = ''; if (isset($event['recur_exception']) && $event['recur_exception'] > 0) { $recur_exception = implode(",", $event['recur_exception']); } $cal_id = phpgw::get_var('cal_id', 'int', 'GET'); //FIXME this is ugly and should use templates better $common_hidden = <<<HTML \t\t\t\t<input type="hidden" name="cal[id]" value="{$event['id']}" /> \t\t\t\t<input type="hidden" name="cal[owner]" value="{$event['owner']}" /> \t\t\t\t<input type="hidden" name="recur_exception" value="{$recur_exception}" /> HTML; if ($cal_id && $event['id'] == 0) { $common_hidden .= <<<HTML \t\t\t\t<input type="hidden" name="cal[reference]" value="{$cal_id}"> HTML; } else { if (isset($event['reference'])) { $common_hidden .= <<<HTML \t\t\t\t<input type="hidden" name="cal[reference]" value="{$event['reference']}"> HTML; } } if (isset($GLOBALS['phpgw_info']['server']['deny_user_grants_access']) && $GLOBALS['phpgw_info']['server']['deny_user_grants_access']) { $common_hidden .= <<<HTML \t\t\t\t<input type="hidden" name="participants[]" value="{$this->bo->owner}"> HTML; } $vars = array('action_url' => $GLOBALS['phpgw']->link('/index.php', array('menuaction' => 'calendar.bocalendar.update')), 'popup_url' => $GLOBALS['phpgw']->link('/index.php', array('menuaction' => 'calendar.uicalendar.participants_popup'), true), 'common_hidden' => $common_hidden, 'errormsg' => $param['cd'] ? $GLOBALS['phpgw']->common->check_code($param['cd']) : ''); $p->set_var($vars); // Brief Description $var['title'] = array('tr_color' => $this->theme['th_bg'], 'field' => lang('Title'), 'data' => '<input name="cal[title]" size="45" maxlength="80" value="' . $event['title'] . '">'); // Full Description $var['description'] = array('field' => lang('Full Description'), 'data' => '<textarea name="cal[description]" rows="5" cols="40" wrap="virtual" maxlength="2048">' . $event['description'] . '</textarea>'); // Display Categories if (isset($event['category']) && strpos($event['category'], ',')) { $temp_cats = explode(',', $event['category']); foreach ($temp_cats as $value) { $check_cats[] = (int) $value; } } elseif (isset($event['category']) && $event['category']) { $check_cats[] = (int) $event['category']; } else { $check_cats[] = 0; } $var['category'] = array('field' => lang('Category'), 'data' => '<select name="categories[]" multiple size="5">' . $this->cat->formated_list('select', 'all', $check_cats, True) . '</select>'); // Location $var['location'] = array('field' => lang('Location'), 'data' => '<input name="cal[location]" size="45" maxlength="255" value="' . $event['location'] . '">'); // Date $user_timezone = phpgwapi_datetime::user_timezone(); $start = $this->bo->maketime($event['start']) - $user_timezone; $var['startdate'] = array('field' => lang('Start Date'), 'data' => $jscal->input('start[str]', $start)); // Time if ($this->bo->prefs['common']['timeformat'] == '12') { $str .= '<input type="radio" name="start[ampm]" value="am"' . ($event['start']['hour'] >= 12 ? '' : ' checked') . '>am' . "\n" . '<input type="radio" name="start[ampm]" value="pm"' . ($event['start']['hour'] >= 12 ? ' checked' : '') . '>pm' . "\n"; } $var['starttime'] = array('field' => lang('Start Time'), 'data' => '<input name="start[hour]" size="2" VALUE="' . $GLOBALS['phpgw']->common->show_date($start, $hourformat) . '" maxlength="2">:<input name="start[min]" size="2" value="' . $GLOBALS['phpgw']->common->show_date($start, 'i') . '" maxlength="2">' . "\n" . $str); // End Date $end = $this->bo->maketime($event['end']) - $user_timezone; $var['enddate'] = array('field' => lang('End Date'), 'data' => $jscal->input('end[str]', $end)); // End Time if ($this->bo->prefs['common']['timeformat'] == '12') { $str = '<input type="radio" name="end[ampm]" value="am"' . ($event['end']['hour'] >= 12 ? '' : ' checked') . '>am' . "\n" . '<input type="radio" name="end[ampm]" value="pm"' . ($event['end']['hour'] >= 12 ? ' checked' : '') . '>pm' . "\n"; } $var['endtime'] = array('field' => lang('End Time'), 'data' => '<input name="end[hour]" size="2" VALUE="' . $GLOBALS['phpgw']->common->show_date($end, $hourformat) . '" maxlength="2">:<input name="end[min]" size="2" value="' . $GLOBALS['phpgw']->common->show_date($end, 'i') . '" maxlength="2">' . "\n" . $str); // Priority $var['priority'] = array('field' => lang('Priority'), 'data' => $sb->getPriority('cal[priority]', $event['priority'])); // Access $var['access'] = array('field' => lang('Private'), 'data' => '<input type="checkbox" name="cal[private]" value="private"' . (!$event['public'] ? ' checked' : '') . '>'); // Participants if (!is_array($event['participants'])) { $event['participants'] = array(); } $str = ''; foreach ($event['participants'] as $id => $status) { if ($id != intval($event['owner'])) { $str .= '<option value="' . $id . $status . '" selected="selected">' . $this->bo->contacts->get_name_of_person_id($id) . '</option>' . "\n"; } } $var['participants'] = array('field' => '<input type="button" value="' . lang('Participants') . '" onClick="accounts_popup();">', 'data' => "\n" . ' <select id="event_participants" name="participants[]" multiple="multiple" size="7">' . "\n{$str}</select>"); // I Participate if ($event['id'] > 0 && isset($event['participants'][$event['owner']]) || !$event['id']) { $checked = ' checked="checked"'; } else { $checked = ''; } $var['owner'] = array('field' => lang('%1 participates', $this->bo->contacts->get_name_of_person_id($event['owner'])), 'data' => '<input type="checkbox" name="participants[]" value="' . $event['owner'] . 'A"' . $checked . '>'); // Reminder // The user must use "Alarm Management" to change/modify an alarm // so only display the email reminder fields if this is a new event // i.e. not editing an existing event if ($event['id'] == 0) { // get defaults $days = isset($this->bo->prefs['calendar']['default_email_days']) ? $this->bo->prefs['calendar']['default_email_days'] : ''; $hours = isset($this->bo->prefs['calendar']['default_email_hours']) ? $this->bo->prefs['calendar']['default_email_hours'] : ''; $min = isset($this->bo->prefs['calendar']['default_email_min']) ? $this->bo->prefs['calendar']['default_email_min'] : ''; if (isset($event['alarm']) && count($event['alarm']) > 1) { // this should not happen because when creating a new event // only 1 alarm is displayed on the screen // if the user wants more than 1 alarm they should // use "Alarm Management" echo '<!-- how did this happen, too many alarms -->' . "\n"; } // if there was an error pick up what the user entered if (@isset($event['alarm'])) { @reset($event['alarm']); // just get the first one see above!!! list($key, $alarm) = @each($event['alarm']); $diff = $start - $alarm['time']; $days = intval($diff / (24 * 3600)); $hours = intval(($diff - $days * 24 * 3600) / 3600); $min = intval(($diff - $days * 24 * 3600 - $hours * 3600) / 60); } // days $dout = '<select name="cal[alarmdays]">' . "\n"; for ($i = 0; $i < 32; $i++) { $dout .= '<option value="' . $i . '"' . ($i == $days ? ' selected' : '') . '>' . $i . '</option>' . "\n"; } $dout .= '</select>' . "\n" . ' ' . lang('days') . ' '; // hours $hout = '<select name="cal[alarmhours]">' . "\n"; for ($i = 0; $i < 25; $i++) { $hout .= '<option value="' . $i . '"' . ($i == $hours ? ' selected' : '') . '>' . $i . '</option>' . "\n"; } $hout .= '</select>' . "\n" . ' ' . lang('hours') . ' '; // minutes $mout = '<select name="cal[alarmminutes]">' . "\n"; for ($i = 0; $i < 61; $i++) { $mout .= '<option value="' . $i . '"' . ($i == $min ? ' selected' : '') . '>' . $i . '</option>' . "\n"; } $mout .= '</select>' . "\n" . ' ' . lang('minutes') . ' '; $var['alarm'] = array('field' => lang('Alarm'), 'data' => $dout . $hout . $mout . lang('before the event')); } // Repeat Type $str = ''; foreach ($this->bo->rpt_type as $type => $label) { $str .= '<option value="' . $type . '"' . ($event['recur_type'] == $type ? ' selected' : '') . '>' . lang($label) . '</option>'; } $var['recure_type'] = array('field' => lang('Repeat Type'), 'data' => '<select name="cal[recur_type]">' . "\n" . $str . '</select>' . "\n"); if (isset($event['recur_enddate']['year']) && $event['recur_enddate']['year'] != 0 && (isset($event['recur_enddate']['month']) && $event['recur_enddate']['month'] != 0) && (isset($event['recur_enddate']['mday']) && $event['recur_enddate']['mday'] != 0)) { $checked = ' checked'; $recur_end = $this->bo->maketime($event['recur_enddate']) - $user_timezone; } else { $checked = ''; $recur_end = $this->bo->maketime($event['start']) + phpgwapi_datetime::SECONDS_IN_DAY - $user_timezone; } $var['recure_enddate'] = array('field' => lang('Repeat End Date'), 'data' => '<input type="checkbox" name="cal[rpt_use_end]" value="y"' . $checked . '>' . lang('Use End Date') . ' ' . $jscal->input('recur_enddate[str]', $recur_end)); $i = 0; $boxes = ''; foreach ($this->bo->rpt_day as $mask => $name) { $boxes .= '<input type="checkbox" name="cal[rpt_day][]" value="' . $mask . '"' . (isset($event['recur_data']) && $event['recur_data'] & $mask ? ' checked' : '') . '> ' . lang($name) . "\n"; if (++$i == 5) { $boxes .= '<br />'; } } $var['recure_day'] = array('field' => lang('Repeat Day') . '<br />' . lang('(for weekly)'), 'data' => $boxes); $var['recure_interval'] = array('field' => lang('Interval'), 'data' => '<input name="cal[recur_interval]" size="4" maxlength="4" value="' . (isset($event['recur_interval']) ? $event['recur_interval'] : '') . '">'); if (!isset($this->fields)) { $this->custom_fields = CreateObject('calendar.bocustom_fields'); $this->fields =& $this->custom_fields->fields; $this->stock_fields =& $this->custom_fields->stock_fields; } $preserved = False; foreach ($this->fields as $field => $data) { if (!isset($data['disabled']) || !$data['disabled']) { if (isset($var[$field])) { switch ($field) { case 'startdate': $this->output_template_array($p, 'row', 'list', $var['startdate']); $this->output_template_array($p, 'row', 'list', $var['starttime']); break; case 'enddate': $this->output_template_array($p, 'row', 'list', $var['enddate']); $this->output_template_array($p, 'row', 'list', $var['endtime']); break; case 'recure_type': $p->set_var('tr_color', $this->theme['th_bg']); $p->set_var('hr_text', '<center><b>' . lang('Repeating Event Information') . '</b></center>'); $p->parse('row', 'hr', True); $this->output_template_array($p, 'row', 'list', $var['recure_type']); $this->output_template_array($p, 'row', 'list', $var['recure_enddate']); $this->output_template_array($p, 'row', 'list', $var['recure_day']); $this->output_template_array($p, 'row', 'list', $var['recure_interval']); break; default: $this->output_template_array($p, 'row', 'list', $var[$field]); } } elseif (!isset($this->stock_fields[$field])) { $lang = lang($name = substr($field, 1)); $size = 'SIZE=' . ($data['shown'] ? $data['shown'] : ($data['length'] ? $data['length'] : 30)) . ' MAXLENGTH=' . ($data['length'] ? $data['length'] : 255); $v = array('field' => $lang == $name . '*' ? $name : $lang, 'data' => '<input name="cal[' . htmlspecialchars($field) . ']" ' . $size . ' value="' . $event['#' . $name] . '">'); if ($data['title']) { $v['tr_color'] = $this->theme['th_bg']; } if (!$data['length'] && $data['title']) { $p->set_var('tr_color', $this->theme['th_bg']); $p->set_var('hr_text', '<center><b>' . $v['field'] . '</b></center>'); $p->parse('row', 'hr', True); } else { $this->output_template_array($p, 'row', 'list', $v); } } } else { switch ($field) { case 'owner': $preserved[$field] = $event['id'] ? $event['participants'][$event['owner']] : 'A'; break; case 'recure_type': foreach (array('recur_type', 'recur_enddate', 'recur_data', 'recur_interval') as $field) { $preserved[$field] = $event[$field]; } break; case 'startdate': case 'enddate': $field = substr($field, 0, -4); default: $preserved[$field] = $event[$field]; } } } unset($var); if (is_array($preserved)) { //echo "preserving<pre>"; print_r($preserved); echo "</pre>\n"; $p->set_var('common_hidden', $p->get_var('common_hidden') . '<input type="hidden" name="preserved" value="' . htmlspecialchars(serialize($preserved)) . '">' . "\n"); } $p->set_var('submit_button', lang('Save')); $delete_button = $cancel_button = ''; if ($event['id'] > 0) { $var = array('action_url_button' => $this->page('delete', array('cal_id' => $event['id'])), 'action_text_button' => lang('Delete'), 'action_confirm_button' => "onClick=\"return confirm('" . lang("Are you sure\\nyou want to\\ndelete this entry ?\\n\\nThis will delete\\nthis entry for all users.") . "')\"", 'action_extra_field' => ''); $p->set_var($var); $delete_button = $p->fp('out', 'form_button'); } $p->set_var('delete_button', $delete_button); if ($this->bo->return_to) { $var = array('action_url_button' => $GLOBALS['phpgw']->link('/index.php', $this->bo->return_to), 'action_text_button' => lang('Cancel'), 'action_confirm_button' => '', 'action_extra_field' => ''); $p->set_var($var); $cancel_button = $p->fp('out', 'form_button'); } $p->set_var('cancel_button', $cancel_button); $p->pparse('out', 'edit_entry'); }
function read($data) { $start = isset($data['start']) && $data['start'] ? (int) $data['start'] : 0; $filter = isset($data['filter']) ? $data['filter'] : ''; $query = isset($data['query']) ? $data['query'] : ''; $sort = isset($data['sort']) && $data['sort'] ? $data['sort'] : 'DESC'; $order = isset($data['order']) ? $data['order'] : ''; $cat_id = isset($data['cat_id']) ? $data['cat_id'] : 0; $property_cat_id = isset($data['property_cat_id']) ? $data['property_cat_id'] : 0; $status_id = isset($data['status_id']) && $data['status_id'] ? $data['status_id'] : 'open'; $district_id = isset($data['district_id']) && $data['district_id'] ? $data['district_id'] : 0; $project_id = isset($data['project_id']) ? $data['project_id'] : ''; $allrows = isset($data['allrows']) ? $data['allrows'] : ''; $list_descr = isset($data['list_descr']) ? $data['list_descr'] : ''; $dry_run = isset($data['dry_run']) ? $data['dry_run'] : ''; $p_num = isset($data['p_num']) ? $data['p_num'] : ''; $start_date = isset($data['start_date']) && $data['start_date'] ? phpgwapi_datetime::date_to_timestamp($data['start_date']) : 0; $end_date = isset($data['end_date']) && $data['end_date'] ? phpgwapi_datetime::date_to_timestamp($data['end_date']) : 0; $building_part = isset($data['building_part']) && $data['building_part'] ? (int) $data['building_part'] : 0; $degree_id = $data['degree_id']; $attrib_filter = $data['attrib_filter'] ? $data['attrib_filter'] : array(); $condition_survey_id = $data['condition_survey_id'] ? (int) $data['condition_survey_id'] : 0; $responsible_unit = (int) $data['responsible_unit']; $recommended_year = (int) $data['recommended_year']; $location_id = $GLOBALS['phpgw']->locations->get_id('property', '.project.request'); $attribute_table = 'phpgw_cust_attribute'; $attribute_filter = " location_id = {$location_id}"; $entity_table = 'fm_request'; $GLOBALS['phpgw']->config->read(); $uicols = array(); $cols .= "{$entity_table}.location_code"; $cols_return[] = 'location_code'; $cols_group[] = "{$entity_table}.location_code"; $cols_group[] = 'fm_location1.loc1_name'; $cols .= ",{$entity_table}.id as request_id"; $cols_return[] = 'request_id'; $cols .= ",fm_request_status.descr as status"; $cols_return[] = 'status'; $cols_group[] = 'fm_request_status.descr'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'status'; $uicols['descr'][] = lang('status'); $uicols['statustext'][] = lang('status'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = false; $cols .= ",{$entity_table}.building_part"; $cols_return[] = 'building_part'; $cols_group[] = 'building_part'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'building_part'; $uicols['descr'][] = lang('building part'); $uicols['statustext'][] = lang('building part'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $cols .= ",{$entity_table}.start_date,{$entity_table}.entry_date,{$entity_table}.closed_date,{$entity_table}.in_progress_date,{$entity_table}.delivered_date"; $cols_return[] = "start_date"; $cols_return[] = "entry_date"; $cols_return[] = "closed_date"; $cols_return[] = "in_progress_date"; $cols_return[] = "delivered_date"; // $cols_group[] = "{$entity_table}.start_date"; $cols_group[] = "{$entity_table}.entry_date"; $cols_group[] = "{$entity_table}.closed_date"; $cols_group[] = "{$entity_table}.in_progress_date"; $cols_group[] = "{$entity_table}.delivered_date"; /* $uicols['input_type'][] = 'text'; $uicols['name'][] = 'start_date'; $uicols['descr'][] = lang('start date'); $uicols['statustext'][] = lang('Request start date'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = true; */ $cols .= ",{$entity_table}.title as title"; $cols_return[] = 'title'; $cols_group[] = "title"; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'title'; $uicols['descr'][] = lang('request title'); $uicols['statustext'][] = lang('Request title'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = true; if ($list_descr) { $cols .= ",{$entity_table}.descr as descr"; $cols_return[] = 'descr'; $cols_group[] = "{$entity_table}.descr"; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'descr'; $uicols['descr'][] = lang('descr'); $uicols['statustext'][] = lang('Request descr'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = false; } $cols .= ",max(fm_request_condition.degree) as condition_degree"; $cols_return[] = 'condition_degree'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'condition_degree'; $uicols['descr'][] = lang('condition degree'); $uicols['statustext'][] = lang('condition degree'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $cols .= ",({$entity_table}.amount_investment * multiplier) as amount_investment"; $cols_return[] = 'amount_investment'; $cols_group[] = 'amount_investment'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'amount_investment'; $uicols['descr'][] = lang('investment'); $uicols['statustext'][] = lang('cost estimate'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'FormatterRight'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = true; $cols .= ",({$entity_table}.amount_operation * multiplier) as amount_operation"; $cols_return[] = 'amount_operation'; $cols_group[] = 'amount_operation'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'amount_operation'; $uicols['descr'][] = lang('operation'); $uicols['statustext'][] = lang('cost estimate'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'FormatterRight'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = true; $cols .= ",({$entity_table}.amount_potential_grants * multiplier) as amount_potential_grants"; $cols_return[] = 'amount_potential_grants'; $cols_group[] = 'amount_potential_grants'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'amount_potential_grants'; $uicols['descr'][] = lang('potential grants'); $uicols['statustext'][] = lang('potential grants'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'FormatterRight'; $uicols['classname'][] = 'rightClasss'; $uicols['sortable'][] = true; // $cols.= ",sum(amount) as consume"; // $cols_return[] = 'consume'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'consume'; $uicols['descr'][] = lang('consume'); $uicols['statustext'][] = lang('consume'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'FormatterRight'; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $cols .= ",{$entity_table}.score"; $cols_return[] = 'score'; $cols_group[] = 'score'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'score'; $uicols['descr'][] = lang('score'); $uicols['statustext'][] = lang('score'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = 'FormatterRight'; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $cols .= ",recommended_year"; $cols_return[] = 'recommended_year'; $cols_group[] = 'recommended_year'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'recommended_year'; $uicols['descr'][] = lang('recommended year'); $uicols['statustext'][] = lang('recommended year'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $cols .= ",start_date AS planned_year"; $cols_return[] = 'planned_year'; $cols_group[] = 'start_date'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'planned_year'; $uicols['descr'][] = lang('planned year'); $uicols['statustext'][] = lang('planned year'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = true; $this->_db->query("SELECT * FROM {$attribute_table} WHERE list=1 AND {$attribute_filter}"); $_attrib = array(); while ($this->_db->next_record()) { $_column_name = $this->_db->f('column_name'); $cols .= ",{$entity_table}.{$_column_name}"; $cols_return[] = $_column_name; $cols_group[] = $_column_name; $uicols['input_type'][] = 'text'; $uicols['name'][] = $_column_name; $uicols['descr'][] = $this->_db->f('input_text', true); $uicols['statustext'][] = $this->_db->f('statustext', true); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = $this->_db->f('datatype'); $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = false; $_attrib[$_column_name] = $this->_db->f('id'); } $cols .= ",{$entity_table}.coordinator"; $cols_return[] = 'coordinator'; $cols_group[] = 'coordinator'; $uicols['input_type'][] = 'text'; $uicols['name'][] = 'coordinator'; $uicols['descr'][] = isset($GLOBALS['phpgw']->config->config_data['lang_request_coordinator']) && $GLOBALS['phpgw']->config->config_data['lang_request_coordinator'] ? $GLOBALS['phpgw']->config->config_data['lang_request_coordinator'] : lang('Coordinator'); $uicols['statustext'][] = lang('Project coordinator'); $uicols['exchange'][] = ''; $uicols['align'][] = ''; $uicols['datatype'][] = ''; $uicols['formatter'][] = ''; $uicols['classname'][] = ''; $uicols['sortable'][] = false; $paranthesis = '('; $joinmethod = "{$this->_left_join} fm_request_status ON {$entity_table}.status = fm_request_status.id)"; $paranthesis .= '('; $joinmethod .= "{$this->_left_join} fm_request_planning ON {$entity_table}.id = fm_request_planning.request_id)"; $paranthesis .= '('; $joinmethod .= "{$this->_left_join} fm_request_consume ON {$entity_table}.id = fm_request_consume.request_id)"; $paranthesis .= '('; $joinmethod .= "{$this->_left_join} fm_request_condition ON {$entity_table}.id = fm_request_condition.request_id)"; $_location_level = isset($GLOBALS['phpgw']->config->config_data['request_location_level']) && $GLOBALS['phpgw']->config->config_data['request_location_level'] ? $GLOBALS['phpgw']->config->config_data['request_location_level'] : 0; $sql = $this->bocommon->generate_sql(array('entity_table' => $entity_table, 'cols' => $cols, 'cols_return' => $cols_return, 'uicols' => array(), 'joinmethod' => $joinmethod, 'paranthesis' => $paranthesis, 'query' => $query, 'force_location' => true, 'location_level' => $_location_level)); for ($i = 2; $i < $_location_level + 1; $i++) { $cols_group[] = "fm_location{$i}.loc{$i}_name"; } $cols_group[] = "{$entity_table}.multiplier"; $cols_group[] = "{$entity_table}.id"; $cols_group[] = 'fm_request_status.descr'; $cols_group[] = "{$entity_table}.address"; $groupmethod = 'GROUP BY ' . implode(',', $cols_group); if ($order) { switch ($order) { case 'planned_year': $ordermethod = " ORDER BY planned_year {$sort}"; break; default: $ordermethod = " order by {$order} {$sort}"; } } else { $ordermethod = ' order by fm_request.id DESC'; } $where = 'WHERE'; $filtermethod = ''; if (isset($GLOBALS['phpgw']->config->config_data['acl_at_location']) && $GLOBALS['phpgw']->config->config_data['acl_at_location']) { $access_location = $this->bocommon->get_location_list(PHPGW_ACL_READ); $filtermethod = " WHERE fm_request.loc1 in ('" . implode("','", $access_location) . "')"; $where = 'AND'; } if ($property_cat_id > 0) { $filtermethod .= " {$where} fm_location1.category='{$property_cat_id}' "; $where = 'AND'; } if ($cat_id > 0) { $filtermethod .= " {$where} fm_request.category='{$cat_id}'"; $where = 'AND'; } if ($recommended_year > 0) { $filtermethod .= " {$where} fm_request.recommended_year = {$recommended_year}"; $where = 'AND'; } if ($condition_survey_id > 0) { $filtermethod .= " {$where} fm_request.condition_survey_id = '{$condition_survey_id}'"; $where = 'AND'; } if ($status_id && $status_id != 'all') { if ($status_id == 'open') { $_status_filter = array(); $this->_db->query("SELECT * FROM fm_request_status WHERE delivered IS NULL AND closed IS NULL"); while ($this->_db->next_record()) { $_status_filter[] = $this->_db->f('id'); } $filtermethod .= " {$where} fm_request.status IN ('" . implode("','", $_status_filter) . "')"; } else { $filtermethod .= " {$where} fm_request.status='{$status_id}' "; } $where = 'AND'; } if ($degree_id) { $degree_id = (int) $degree_id - 1; $filtermethod .= " {$where} fm_request_condition.degree = {$degree_id}"; $where = 'AND'; } if ($building_part) { $filtermethod .= " {$where} fm_request.building_part {$this->_like} '{$building_part}%'"; $where = 'AND'; } if ($start_date) { $end_date = $end_date + 3600 * 16 + phpgwapi_datetime::user_timezone(); $start_date = $start_date - 3600 * 8 + phpgwapi_datetime::user_timezone(); $filtermethod .= " {$where} fm_request.start_date >= {$start_date} AND fm_request.start_date <= {$end_date} "; $where = 'AND'; } if ($filter) { $filtermethod .= " {$where} fm_request.coordinator='{$filter}' "; $where = 'AND'; } if ($attrib_filter) { $filtermethod .= " {$where} " . implode(' AND ', $attrib_filter); $where = 'AND'; } if ($project_id && !$status_id) { // $filtermethod .= " $where project_id is NULL "; $filtermethod .= " {$where} fm_request_status.closed is NULL "; $where = 'AND'; } if ($district_id) { $filtermethod .= " {$where} fm_part_of_town.district_id = {$district_id}"; $where = 'AND'; } if ($responsible_unit) { $filtermethod .= " {$where} fm_request.responsible_unit='{$responsible_unit}'"; $where = 'AND'; } if ($query) { if (stristr($query, '.') && $p_num) { $query = explode(".", $query); $querymethod = " {$where} (fm_request.p_entity_id='" . (int) $query[1] . "' AND fm_request.p_cat_id='" . (int) $query[2] . "' AND fm_request.p_num='" . (int) $query[3] . "')"; } else { $query = $this->_db->db_addslashes($query); $querymethod = " {$where} (fm_request.title {$this->_like} '%{$query}%' OR fm_request.address {$this->_like} '%{$query}%' OR fm_request.location_code {$this->_like} '%{$query}%' OR fm_request.id =" . (int) $query; for ($i = 1; $i <= $_location_level; $i++) { $querymethod .= " OR fm_location{$i}.loc{$i}_name {$this->_like} '%{$query}%'"; } $querymethod .= ')'; } } $sql .= " {$filtermethod} {$querymethod}"; $sql_arr = explode('FROM', $sql); $sql .= " {$groupmethod}"; //_debug_array($sql); $this->uicols['input_type'] = array_merge($this->bocommon->uicols['input_type'], $uicols['input_type']); $this->uicols['name'] = array_merge($this->bocommon->uicols['name'], $uicols['name']); $this->uicols['descr'] = array_merge($this->bocommon->uicols['descr'], $uicols['descr']); $this->uicols['statustext'] = array_merge($this->bocommon->uicols['statustext'], $uicols['statustext']); $this->uicols['exchange'] = array_merge($this->bocommon->uicols['exchange'], $uicols['exchange']); $this->uicols['align'] = array_merge($this->bocommon->uicols['align'], $uicols['align']); $this->uicols['datatype'] = array_merge($this->bocommon->uicols['datatype'], $uicols['datatype']); $this->uicols['formatter'] = array_merge($this->bocommon->uicols['formatter'], $uicols['formatter']); $this->uicols['classname'] = array_merge($this->bocommon->uicols['classname'], $uicols['classname']); $this->uicols['sortable'] = array_merge($this->bocommon->uicols['sortable'], $uicols['sortable']); array_unshift($this->uicols['input_type'], 'text'); array_unshift($this->uicols['name'], 'request_id'); array_unshift($this->uicols['descr'], 'ID'); array_unshift($this->uicols['statustext'], 'Request ID'); array_unshift($this->uicols['exchange'], ''); array_unshift($this->uicols['align'], ''); array_unshift($this->uicols['datatype'], ''); array_unshift($this->uicols['formatter'], 'linktToRequest'); array_unshift($this->uicols['classname'], ''); array_unshift($this->uicols['sortable'], true); $cols_return = $this->bocommon->cols_return; $this->cols_extra = $this->bocommon->cols_extra; $this->_db->fetchmode = 'ASSOC'; // $sql2 = "SELECT count(*) as cnt, sum(amount_investment) as sum_investment, sum(amount_operation) as sum_operation, sum(amount_potential_grants) as sum_potential_grants FROM ({$sql}) as t"; $sql2 = "SELECT count(*) as cnt, (sum(amount_investment * multiplier)) as sum_investment, (sum(amount_operation * multiplier)) as sum_operation, (sum(amount_potential_grants * multiplier)) as sum_potential_grants FROM {$sql_arr[1]}"; $this->_db->query($sql2, __LINE__, __FILE__); $this->_db->next_record(); $this->_total_records = $this->_db->f('cnt'); $this->sum_investment = $this->_db->f('sum_investment'); $this->sum_operation = $this->_db->f('sum_operation'); $this->sum_potential_grants = $this->_db->f('sum_potential_grants'); $sql3 = "SELECT sum(fm_request_consume.amount) as sum_consume FROM {$sql_arr[1]}"; $this->_db->query($sql3, __LINE__, __FILE__); $this->_db->next_record(); $this->sum_consume = $this->_db->f('sum_consume'); // _debug_array($sql_arr); //cramirez.r@ccfirst.com 23/10/08 avoid retrieve data in first time, only render definition for headers (var myColumnDefs) if ($dry_run) { return array(); } else { if (!$allrows) { $this->_db->limit_query($sql . $ordermethod, $start, __LINE__, __FILE__); } else { $this->_db->query($sql . $ordermethod, __LINE__, __FILE__); } } $_datatype = array(); foreach ($this->uicols['name'] as $key => $_name) { $_datatype[$_name] = $this->uicols['datatype'][$key]; } $dataset = array(); $j = 0; while ($this->_db->next_record()) { foreach ($cols_return as $key => $field) { $dataset[$j][$field] = array('value' => $this->_db->f($field), 'datatype' => $_datatype[$field], 'attrib_id' => $_attrib[$field]); } $j++; } foreach ($dataset as &$entry) { $sql = "SELECT sum(amount) as consume FROM fm_request_consume WHERE request_id={$entry['request_id']['value']}"; $this->_db->query($sql, __LINE__, __FILE__); $this->_db->next_record(); $entry['consume'] = array('value' => $this->_db->f('consume'), 'datatype' => false, 'attrib_id' => false); } $values = $this->custom->translate_value($dataset, $location_id); return $values; }
function save_event($event) { $locks = array('phpgw_cal', 'phpgw_cal_user', 'phpgw_cal_repeats', 'phpgw_cal_extra'); $this->stream->lock($locks); if (!isset($event['id']) || !$event['id']) { $this->stream->query('INSERT INTO phpgw_cal(title, owner, priority, is_public, category) ' . "VALUES('" . $this->stream->db_addslashes($event['title']) . "',{$event['owner']},{$event['priority']},{$event['public']}," . "'{$event['category']}')", __LINE__, __FILE__); $event['id'] = $this->stream->get_last_insert_id('phpgw_cal', 'cal_id'); } $user_timezone = phpgwapi_datetime::user_timezone(); $date = $this->maketime($event['start']) - $user_timezone; $enddate = $this->maketime($event['end']) - $user_timezone; $today = time() - $user_timezone; if ($event['recur_type'] != MCAL_RECUR_NONE) { $type = 'M'; } else { $type = 'E'; } $sql = 'UPDATE phpgw_cal SET ' . 'owner=' . $event['owner'] . ', ' . 'datetime=' . $date . ', ' . 'mdatetime=' . $today . ', ' . 'edatetime=' . $enddate . ', ' . 'priority=' . $event['priority'] . ', ' . "category='" . $event['category'] . "', " . "cal_type='" . $type . "', " . 'is_public=' . $event['public'] . ', ' . "title='" . $this->stream->db_addslashes($event['title']) . "', " . "description='" . $this->stream->db_addslashes($event['description']) . "', " . "location='" . $this->stream->db_addslashes($event['location']) . "', " . (isset($event['groups']) ? "groups='" . (count($event['groups']) > 1 ? implode(',', $event['groups']) : ',' . $event['groups'][0] . ',') . "', " : '') . 'reference=' . $event['reference'] . ' ' . 'WHERE cal_id=' . $event['id']; $this->stream->query($sql, __LINE__, __FILE__); $this->stream->query('DELETE FROM phpgw_cal_user WHERE cal_id=' . $event['id'], __LINE__, __FILE__); @reset($event['participants']); while (list($key, $value) = @each($event['participants'])) { if (intval($key) == $event['owner']) { $value = 'A'; } $this->stream->query('INSERT INTO phpgw_cal_user(cal_id,cal_login,cal_status,cal_type) ' . 'VALUES(' . $event['id'] . ',' . intval($key) . ",'" . $value . "','" . $type . "')", __LINE__, __FILE__); } if ($event['recur_type'] != MCAL_RECUR_NONE) { if ($event['recur_enddate']['month'] != 0 && $event['recur_enddate']['mday'] != 0 && $event['recur_enddate']['year'] != 0) { $end = $this->maketime($event['recur_enddate']) - $user_timezone; } else { $end = 0; } $this->stream->query('SELECT count(cal_id) as cnt FROM phpgw_cal_repeats WHERE cal_id=' . $event['id'], __LINE__, __FILE__); $this->stream->next_record(); $num_rows = $this->stream->f('cnt'); if ($num_rows == 0) { $this->stream->query('INSERT INTO phpgw_cal_repeats(cal_id,recur_type,recur_enddate,recur_data,recur_interval) ' . 'VALUES(' . $event['id'] . ',' . $event['recur_type'] . ',' . $end . ',' . $event['recur_data'] . ',' . $event['recur_interval'] . ')', __LINE__, __FILE__); } else { $this->stream->query('UPDATE phpgw_cal_repeats ' . 'SET recur_type=' . $event['recur_type'] . ', ' . 'recur_enddate=' . $end . ', ' . 'recur_data=' . $event['recur_data'] . ', ' . 'recur_interval=' . $event['recur_interval'] . ', ' . "recur_exception='" . (count($event['recur_exception']) > 1 ? implode(',', $event['recur_exception']) : (count($event['recur_exception']) == 1 ? $event['recur_exception'][0] : '')) . "' " . 'WHERE cal_id=' . $event['id'], __LINE__, __FILE__); } } else { $this->stream->query('DELETE FROM phpgw_cal_repeats WHERE cal_id=' . $event['id'], __LINE__, __FILE__); } // Custom fields $this->stream->query('DELETE FROM phpgw_cal_extra WHERE cal_id=' . $event['id'], __LINE__, __FILE__); foreach ($event as $name => $value) { if ($name[0] == '#' && strlen($value)) { $this->stream->query('INSERT INTO phpgw_cal_extra (cal_id,cal_extra_name,cal_extra_value) ' . 'VALUES(' . $event['id'] . ",'" . addslashes(substr($name, 1)) . "','" . addslashes($value) . "')", __LINE__, __FILE__); } } /* $alarmcount = count($event['alarm']); if ($alarmcount > 1) { // this should never happen, $event['alarm'] should only be set // if creating a new event and uicalendar only sets up 1 alarm // the user must use "Alarm Management" to create/establish multiple // alarms or to edit/change an alarm echo '<!-- how did this happen, too many alarms -->'."\n"; $this->stream->unlock(); return True; } if ($alarmcount == 1) { list($key,$alarm) = @each($event['alarm']); $this->stream->query('INSERT INTO phpgw_cal_alarm(cal_id,cal_owner,cal_time,cal_text,alarm_enabled) VALUES('.$event['id'].','.$event['owner'].','.$alarm['time'].",'".$alarm['text']."',".$alarm['enabled'].')',__LINE__,__FILE__); $this->stream->query('SELECT LAST_INSERT_ID()'); $this->stream->next_record(); $alarm['id'] = $this->stream->f(0); } */ print_debug('Event Saved: ID #', $event['id']); $this->stream->unlock(); if (isset($event['alarm']) && is_array($event['alarm'])) { foreach ($event['alarm'] as $alarm) { $this->save_alarm($event['id'], $alarm); } } $GLOBALS['phpgw_info']['cal_new_event_id'] = $event['id']; $this->event = $event; return True; }
function export($params) { $event_id = phpgw::get_var('cal_id', 'int', 'GET', $params['l_event_id']); if (isset($params['alarms_only'])) { $alarms_only = true; } else { $alarms_only = false; } if (isset($params['chunk_split'])) { $this->chunk_split = $params['chunk_split']; $method = isset($params['method']) && $params['method'] ? $params['method'] : 'publish'; } $method = isset($params['method']) && $params['method'] ? $params['method'] : "publish"; $string_array = array('description' => 'description', 'location' => 'location', 'summary' => 'title', 'uid' => 'uid'); $cats = CreateObject('phpgwapi.categories', 0, 'calendar'); $contacts = createObject('phpgwapi.contacts'); if (!isset($db) || !is_object($db)) { $db =& $GLOBALS['phpgw']->db; } if (!isset($GLOBALS['uicalendar']) || !is_object($GLOBALS['uicalendar'])) { if (!isset($GLOBALS['bocalendar']) || !is_object($GLOBALS['bocalendar'])) { $so_event = createobject('calendar.socalendar', array('owner' => 0, 'filter' => '', 'category' => '')); } else { $so_event =& $GLOBALS['bocalendar']->so; } } else { $so_event =& $GLOBALS['uicalendar']->bo->so; } if (!is_array($event_id)) { $ids[] = $event_id; } else { $ids = $event_id; } $ical = $this->new_ical(); include PHPGW_SERVER_ROOT . '/calendar/setup/setup.inc.php'; $versiona = explode('.', $setup_info['calendar']['version']); unset($versiona[count($versiona) - 1]); //drop the minor from the version (hide patch level from clients) $version = implode('.', $versiona); $this->set_var($ical['prodid'], 'value', "-//phpGroupWare//phpGroupWare {$version} MIMEDIR//" . strtoupper($GLOBALS['phpgw_info']['user']['preferences']['common']['lang'])); $this->set_var($ical['version'], 'value', '2.0'); $this->set_var($ical['method'], 'value', strtoupper($method)); unset($version, $versiona); if (isset($params['minutes'])) { $minutes = (int) $params['minutes']; } foreach ($ids as $key => $value) { $ical_event = array(); // $event seems to get the wrong dates & not get the alarm part filled in $event = $so_event->read_entry($value); if ($alarms_only && !(isset($event['alarm']) && is_array($event['alarm']) && !empty($event['alarm']))) { continue; } $ical_event['priority'] = $event['priority']; $ical_event['class'] = intval($event['public']); $dtstart_mktime = $so_event->maketime($event['start']); $this->parse_value($ical_event, 'dtstart', date('Ymd\\THis\\Z', $dtstart_mktime), 'vevent'); $dtend_mktime = $so_event->maketime($event['end']); $this->parse_value($ical_event, 'dtend', date('Ymd\\THis\\Z', $dtend_mktime), 'vevent'); $mod_mktime = $so_event->maketime($event['modtime']); $this->parse_value($ical_event, 'last_modified', date('Ymd\\THis\\Z', $mod_mktime), 'vevent'); foreach ($string_array as $ical_value => $event_value) { if (isset($event[$event_value]) && $event[$event_value]) { $this->set_var($ical_event[$ical_value], 'value', $event[$event_value]); } } if (isset($event['alarm']) && is_array($event['alarm'])) { foreach ($event['alarm'] as $alarm) { // if alarm is earlier than now(), or later than now()+$minutes, skip it if (isset($minutes)) { if ($alarm['time'] < strtotime("now") || $alarm['time'] > time() + $minutes * 60) { continue; } } $ical_temp = array(); $ical_temp['action']['value'] = 'DISPLAY'; $ical_temp['description']['value'] = isset($alarm['text']) ? $alarm['text'] : ''; $this->set_var($ical_temp['trigger'], 'value', 'VALUE=DATE-TIME:' . date('Ymd\\THis\\Z', $alarm['time']), 'valarm'); $ical_event['alarm'][] = $ical_temp; } } if (isset($event['category']) && strlen($event['category'])) { $category = explode(',', $event['category']); $cat_strings = array(); foreach ($category as $key => $cat) { $cat_data = $cats->return_single($cat); $cat_strings[] = $cat_data[0]['name']; } $this->set_var($ical_event['categories'], 'value', implode(',', $cat_strings)); unset($cat_strings); } if (is_array($event['participants']) && count($event['participants']) > 1) { if ($method != 'reply' || $part == $GLOBALS['phpgw_info']['user']['account_id']) { $this->parse_value($ical_event, 'attendee', $str, 'vevent'); } foreach ($event['participants'] as $part => $status) { if (!intval($part)) { continue; //dud entry! } //trims are used as there is sometimes needles whitespace $name = trim(@$contacts->get_contact_name($part)); if (!strlen($name)) { continue; //no name no point! } $owner_status = $this->switch_partstat(intval($this->switch_phpgw_status($event['participants'][$part]))); $str = "PARTSTAT={$owner_status};CN=\"{$name}\""; $mailto = trim($contacts->get_email($part)); if (strlen($mailto)) { $str .= ":MAILTO:{$mailto}"; } if ($part == $event['owner']) { $str = "ROLE=CHAIR;{$str}"; $this->parse_value($ical_event, 'organizer', $str, 'vevent'); } else { $str = "ROLE=REQ-PARTICIPANT;{$str}"; if ($method != 'reply' || $part == $GLOBALS['phpgw_info']['user']['person_id']) { $this->parse_value($ical_event, 'attendee', $str, 'vevent'); } } $this->parse_value($ical_event, 'organizer', $str, 'vevent'); } } if (isset($event['recur_type'])) { $str = ''; switch ($event['recur_type']) { case MCAL_RECUR_DAILY: $str .= 'FREQ=DAILY'; if ($event['recur_data'] > 0) { $str .= ';BYDAY='; for ($i = 1; $i < MCAL_M_ALLDAYS; $i = $i * 2) { if ($i & $event['recur_data']) { switch ($i) { case MCAL_M_SUNDAY: $day[] = 'SU'; break; case MCAL_M_MONDAY: $day[] = 'MO'; break; case MCAL_M_TUESDAY: $day[] = 'TU'; break; case MCAL_M_WEDNESDAY: $day[] = 'WE'; break; case MCAL_M_THURSDAY: $day[] = 'TH'; break; case MCAL_M_FRIDAY: $day[] = 'FR'; break; case MCAL_M_SATURDAY: $day[] = 'SA'; break; } } } $str .= implode(',', $day); } break; case MCAL_RECUR_WEEKLY: $str .= 'FREQ=WEEKLY'; if ($event['recur_data']) { $str .= ';BYDAY='; for ($i = 1; $i < MCAL_M_ALLDAYS; $i = $i * 2) { if ($i & $event['recur_data']) { switch ($i) { case MCAL_M_SUNDAY: $day[] = 'SU'; break; case MCAL_M_MONDAY: $day[] = 'MO'; break; case MCAL_M_TUESDAY: $day[] = 'TU'; break; case MCAL_M_WEDNESDAY: $day[] = 'WE'; break; case MCAL_M_THURSDAY: $day[] = 'TH'; break; case MCAL_M_FRIDAY: $day[] = 'FR'; break; case MCAL_M_SATURDAY: $day[] = 'SA'; break; } } } $str .= implode(',', $day); } break; case MCAL_RECUR_MONTHLY_MDAY: $str .= 'FREQ=MONTHLY;BYMONTHDAY=' . $event['start']['mday']; break; case MCAL_RECUR_MONTHLY_WDAY: // Calculate which weekday from monthstart $weekdaycount = intval($event['start']['mday'] / 7) + 1; $weekday = strtoupper(substr(date('D', mktime($event['start']['hour'], $event['start']['min'], $event['start']['sec'], $event['start']['month'], $event['start']['mday'], $event['start']['year'])), 0, 2)); if ($event['recur_last_weekday_in_month']) { $str .= 'FREQ=MONTHLY;BYDAY=-1' . $weekday; } else { $str .= 'FREQ=MONTHLY;BYDAY=' . $weekdaycount . $weekday; } break; case MCAL_RECUR_YEARLY: $str .= 'FREQ=YEARLY;BYMONTH=' . $event['start']['month'] . ';BYMONTHDAY=' . $event['start']['mday']; break; case MCAL_RECUR_YEARLY_WDAY: $eventstart_jd = gregoriantojd($event['start']['month'], $event['start']['mday'], $event['start']['year']); $monthstart_jd = gregoriantojd($event['start']['month'], 1, $event['start']['year']); $weekdaycount = intval(($eventstart_jd - $monthstart_jd) / 7) + 1; $weekday = strtoupper(substr(date('D', mktime($event['start']['hour'], $event['start']['min'], $event['start']['sec'], $event['start']['month'], $event['start']['mday'], $event['start']['year'])), 0, 2)); $str .= 'FREQ=YEARLY;BYMONTH=' . $event['start']['month'] . ';BYDAY=' . $weekdaycount . $weekday; break; } if (isset($event['recur_interval']) && $event['recur_interval']) { $str .= ';INTERVAL=' . $event['recur_interval']; } if (isset($event['recur_enddate']['month']) && $event['recur_enddate']['month'] != 0 && (isset($event['recur_enddate']['mday']) && $event['recur_enddate']['mday'] != 0) && (isset($event['recur_enddate']['year']) && $event['recur_enddate']['year'] != 0)) { $recur_mktime = $so_event->maketime($event['recur_enddate']) - phpgwapi_datetime::user_timezone(); $str .= ';UNTIL=' . date('Ymd\\THis\\Z', $recur_mktime); } $this->parse_value($ical_event, 'rrule', $str, 'vevent'); $exceptions =& $event['recur_exception']; if (is_array($exceptions)) { foreach ($exceptions as $key => $except_datetime) { $except_datetime = mktime(date('H', $except_datetime), date('i', $except_datetime) - $offset, 0, date('m', $except_datetime), date('d', $except_datetime), date('Y', $except_datetime)); $ical_event['exdate'][] = $this->switch_date(date('Ymd\\THis\\Z', $except_datetime)); } } $rdate = isset($event['recur_date']) ? $event['recur_date'] : ''; if (is_array($rdate)) { @reset($rdate); while (list($key, $recur_datetime) = each($rdate)) { $recur_datetime = explode("/", $recur_datetime); $recur_datetime['0'] = mktime(date('H', $recur_datetime['0']), date('i', $recur_datetime['0']) - $offset, 0, date('m', $recur_datetime['0']), date('d', $recur_datetime['0']), date('Y', $recur_datetime['0'])); $ical_event['rdate'][] = $this->switch_date(date('Ymd\\THis\\Z', $recur_datetime['0'])); } } } $ical_events[] = $ical_event; } $ical['event'] = $ical_events; return $this->build_ical($ical); }
function read($data) { $start = isset($data['start']) && $data['start'] ? (int) $data['start'] : 0; $status_id = isset($data['status_id']) && $data['status_id'] ? $data['status_id'] : 'O'; //O='Open' $user_id = isset($data['user_id']) && $data['user_id'] ? (int) $data['user_id'] : 0; $reported_by = isset($data['reported_by']) && $data['reported_by'] ? (int) $data['reported_by'] : 0; $query = isset($data['query']) ? $data['query'] : ''; $sort = isset($data['sort']) && $data['sort'] ? $data['sort'] : 'DESC'; $order = isset($data['order']) ? $data['order'] : ''; $cat_id = isset($data['cat_id']) && $data['cat_id'] ? (int) $data['cat_id'] : 0; $district_id = isset($data['district_id']) && $data['district_id'] ? (int) $data['district_id'] : 0; $part_of_town_id = isset($data['part_of_town_id']) && $data['part_of_town_id'] ? (int) $data['part_of_town_id'] : 0; $allrows = isset($data['allrows']) ? $data['allrows'] : ''; $start_date = isset($data['start_date']) && $data['start_date'] ? (int) $data['start_date'] : 0; $end_date = isset($data['end_date']) && $data['end_date'] ? (int) $data['end_date'] : 0; $external = isset($data['external']) ? $data['external'] : ''; $dry_run = isset($data['dry_run']) ? $data['dry_run'] : ''; $new = isset($data['new']) ? $data['new'] : ''; $location_code = isset($data['location_code']) ? $data['location_code'] : ''; $p_num = isset($data['p_num']) ? $data['p_num'] : ''; $vendor_id = isset($data['vendor_id']) && $data['vendor_id'] ? (int) $data['vendor_id'] : 0; $ecodimb = isset($data['ecodimb']) && $data['ecodimb'] ? (int) $data['ecodimb'] : 0; $b_account = isset($data['b_account']) && $data['b_account'] ? $data['b_account'] : ''; $building_part = isset($data['building_part']) && $data['building_part'] ? $data['building_part'] : ''; $branch_id = isset($data['branch_id']) && $data['branch_id'] ? (int) $data['branch_id'] : 0; $order_dim1 = isset($data['order_dim1']) && $data['order_dim1'] ? (int) $data['order_dim1'] : 0; $custom_filtermethod = isset($data['custom_filtermethod']) && $data['custom_filtermethod'] ? (array) $data['custom_filtermethod'] : array(); $result_order_field = ''; if ($order) { if ($order == 'assignedto') { $result_order_field = ',account_lastname'; $order_join = "LEFT OUTER JOIN phpgw_accounts ON fm_tts_tickets.assignedto=phpgw_accounts.account_id"; $order = 'account_lastname'; } else { if ($order == 'user') { $result_order_field = ',account_lastname'; $order_join = "LEFT OUTER JOIN phpgw_accounts ON fm_tts_tickets.user_id=phpgw_accounts.account_id"; $order = 'account_lastname'; } else { $order_join = ''; } } $ordermethod = " ORDER BY {$order} {$sort}"; } else { $ordermethod = ' ORDER BY fm_tts_tickets.id DESC'; } $filtermethod = ''; $where = 'WHERE'; $GLOBALS['phpgw']->config->read(); if (!isset($GLOBALS['phpgw']->config->config_data['bypass_acl_at_tickets']) || !$GLOBALS['phpgw']->config->config_data['bypass_acl_at_tickets']) { $this->grants = $GLOBALS['phpgw']->session->appsession('grants_ticket', 'property'); if (!$this->grants) { $GLOBALS['phpgw']->acl->set_account_id($this->account); $this->grants = $GLOBALS['phpgw']->acl->get_grants('property', '.ticket'); $GLOBALS['phpgw']->session->appsession('grants_ticket', 'property', $this->grants); } $public_user_list = array(); if (isset($GLOBALS['phpgw']->config->config_data['acl_at_tts_category']) && $GLOBALS['phpgw']->config->config_data['acl_at_tts_category']) { $categories = $GLOBALS['phpgw']->locations->get_subs('property', '.ticket.category'); $category_grants = array(); foreach ($categories as $location) { $category_grants = array_merge($category_grants, $GLOBALS['phpgw']->acl->get_grants('property', $location)); } foreach ($category_grants as $user => $right) { $public_user_list[] = $user; } } if (isset($GLOBALS['phpgw']->config->config_data['acl_at_location']) && $GLOBALS['phpgw']->config->config_data['acl_at_location']) { $access_location = execMethod('property.socommon.get_location_list', PHPGW_ACL_READ); if ($access_location) { $filtermethod .= " {$where} fm_tts_tickets.loc1 in ('" . implode("','", $access_location) . "')"; $where = 'AND'; } } if (is_array($this->grants)) { $grants =& $this->grants; foreach ($grants as $user => $right) { $public_user_list[] = $user; } } if ($public_user_list) { $public_user_list = array_unique($public_user_list); reset($public_user_list); $filtermethod .= " {$where} ( fm_tts_tickets.user_id IN(" . implode(',', $public_user_list) . "))"; $where = 'AND'; } } if ($tenant_id = $GLOBALS['phpgw']->session->appsession('tenant_id', 'property')) { $filtermethod .= $where . ' fm_tts_tickets.tenant_id=' . $tenant_id; $where = 'AND'; } if ($status_id == 'X') { $closed = ''; $this->db->query('SELECT * from fm_tts_status', __LINE__, __FILE__); while ($this->db->next_record()) { if ($this->db->f('closed')) { $closed .= " OR fm_tts_tickets.status = 'C" . $this->db->f('id') . "'"; } } $filtermethod .= " {$where} ( (fm_tts_tickets.status='X'{$closed})"; $where = 'AND'; // $filtermethod .= " $where ( fm_tts_tickets.status='X'"; // $where = 'AND'; } else { if ($status_id == 'O2') { $filtermethod .= " {$where} ( fm_tts_tickets.status='O'"; $where = 'AND'; } else { if ($status_id == 'O') { $open = ''; $this->db->query('SELECT * from fm_tts_status', __LINE__, __FILE__); while ($this->db->next_record()) { if (!$this->db->f('closed')) { $open .= " OR fm_tts_tickets.status = 'C" . $this->db->f('id') . "'"; } } $filtermethod .= " {$where} ( (fm_tts_tickets.status='O'{$open})"; $where = 'AND'; } else { if ($status_id == 'all') { $filtermethod .= "{$where} (1=1"; //nothing $where = 'AND'; } else { if (is_array($status_id) && count($status_id)) { $or = ''; $filtermethod .= "{$where} (("; foreach ($status_id as $value) { if ($value) { $filtermethod .= "{$or} fm_tts_tickets.status = '{$value}'"; $or = ' OR'; } } $filtermethod .= ')'; $where = 'AND'; } else { $filtermethod .= " {$where} (fm_tts_tickets.status='{$status_id}'"; $where = 'AND'; } } } } } if ($new) { $filtermethod .= " OR fm_tts_views.id IS NULL )"; } else { $filtermethod .= ')'; } if ($cat_id > 0) { $filtermethod .= " {$where} cat_id=" . (int) $cat_id; $where = 'AND'; } if ($vendor_id > 0) { $filtermethod .= " {$where} vendor_id=" . (int) $vendor_id; $where = 'AND'; } if ($ecodimb > 0) { $filtermethod .= " {$where} ecodimb=" . (int) $ecodimb; $where = 'AND'; } if ($b_account > 0) { $filtermethod .= " {$where} b_account_id='{$b_account}'"; $where = 'AND'; } if ($building_part) { $filtermethod .= " {$where} building_part='{$building_part}'"; $where = 'AND'; } if ($branch_id > 0) { $filtermethod .= " {$where} branch_id=" . (int) $branch_id; $where = 'AND'; } if ($order_dim1 > 0) { $filtermethod .= " {$where} order_dim1=" . (int) $order_dim1; $where = 'AND'; } if ($user_id > 0) { $filtermethod .= " {$where} (assignedto={$user_id}"; $where = 'AND'; if (!($membership = $GLOBALS['phpgw']->accounts->membership($user_id))) { $membership = array(-1 => 0); } $filtermethod .= ' OR (assignedto IS NULL AND group_id IN (' . implode(',', array_keys($membership)) . ')))'; } if ($user_id < 0) { $filtermethod .= " {$where} fm_tts_tickets.user_id=" . (int) abs($user_id); $where = 'AND'; } if ($reported_by > 0) { $filtermethod .= " {$where} fm_tts_tickets.user_id=" . (int) $reported_by; $where = 'AND'; } if ($district_id > 0) { $filtermethod .= " {$where} district_id=" . (int) $district_id; $where = 'AND'; } if ($part_of_town_id > 0) { $filtermethod .= " {$where} fm_part_of_town.part_of_town_id= " . (int) $part_of_town_id; $where = 'AND'; } $actual_cost_field = 'fm_tts_tickets.actual_cost'; $actual_cost_group_field = ',fm_tts_tickets.actual_cost'; $budget_field = 'fm_tts_tickets.budget'; $budget_group_field = ',fm_tts_tickets.budget'; $date_join = ''; $date_budget_join = ''; if ($start_date) { $order_add = $GLOBALS['phpgw']->acl->check('.ticket.order', PHPGW_ACL_ADD, 'property'); $order_edit = $GLOBALS['phpgw']->acl->check('.ticket.order', PHPGW_ACL_EDIT, 'property'); $_end_date = $end_date + 3600 * 16 + phpgwapi_datetime::user_timezone(); $_start_date = $start_date - 3600 * 8 + phpgwapi_datetime::user_timezone(); $filtermethod .= " {$where} (fm_tts_tickets.modified_date >= {$_start_date} AND fm_tts_tickets.modified_date <= {$_end_date} "; if ($order_add || $order_edit) { $end_period = date('Ym', $end_date); $start_period = date('Ym', $start_date); $filtermethod .= " OR (fm_tts_payments.period >= {$start_period} AND fm_tts_payments.period <= {$end_period})"; $date_join = "LEFT JOIN fm_tts_payments ON ( fm_tts_tickets.id=fm_tts_payments.ticket_id AND fm_tts_payments.period >= {$start_period} AND fm_tts_payments.period <= {$end_period} )"; $actual_cost_field = 'SUM(fm_tts_payments.amount) AS actual_cost'; $actual_cost_group_field = ''; $start_budget_period = date('Y', $end_date) . '00'; $end_budget_period = date('Y', $start_date) . '13'; $filtermethod .= " OR (fm_tts_budget.period >= {$start_budget_period} AND fm_tts_budget.period <= {$end_budget_period}))"; $date_budget_join = "LEFT JOIN fm_tts_budget ON ( fm_tts_tickets.id=fm_tts_budget.ticket_id AND fm_tts_budget.period >= {$start_budget_period} AND fm_tts_budget.period <= {$end_budget_period} )"; $budget_field = 'SUM(fm_tts_budget.amount) AS budget'; $budget_group_field = ''; } else { $filtermethod .= ')'; } $where = 'AND'; } if ($location_code) { $filtermethod .= " {$where} fm_tts_tickets.location_code {$this->like} '{$location_code}%'"; $where = 'AND'; } foreach ($custom_filtermethod as $custom_filter_key => $custom_filter_value) { $filtermethod .= " {$where} fm_tts_tickets.{$custom_filter_key} = '{$custom_filter_value}'"; $where = 'AND'; } $location_id = $GLOBALS['phpgw']->locations->get_id('property', '.ticket'); $querymethod = ''; if ($query) { $query = $this->db->db_addslashes($query); $querymethod = " {$where} ((subject {$this->like} '%{$query}%'" . " OR address {$this->like} '%{$query}%' " . " OR fm_location1.loc1_name {$this->like} '%{$query}%'" . " OR fm_tts_tickets.location_code {$this->like} '%{$query}%'"; if (ctype_digit($query)) { $querymethod .= " OR fm_tts_tickets.order_id =" . (int) $query . " OR fm_tts_tickets.id =" . (int) $query . ')'; } else { $querymethod .= ')'; } $query = str_replace(",", '.', $query); if (stristr($query, '.')) { if (!$p_num) { $query = explode(".", $query); $querymethod .= " OR (fm_tts_tickets.loc1='{$query[0]}' AND fm_tts_tickets.loc4='{$query[1]}')"; } else { $query = explode(".", $query); $querymethod = " {$where} (fm_tts_tickets.p_entity_id='" . (int) $query[1] . "' AND fm_tts_tickets.p_cat_id='" . (int) $query[2] . "' AND fm_tts_tickets.p_num='{$query[3]}')"; } } $custom_filter = $this->custom->get_custom_filter($location_id, 'fm_tts_tickets', $criteria_id = '', $query); if ($custom_filter['querymethod']) { $_where = $where == 'AND' ? 'OR' : 'WHERE'; $querymethod .= " {$_where} (" . implode(' OR ', $custom_filter['querymethod']) . ')'; } else { if (isset($custom_filter['joinmethod_datatype']) && $custom_filter['joinmethod_datatype']) { $querymethod = ''; } } if ($querymethod) { $querymethod .= ')'; } } $return_fields = "fm_tts_tickets.id,fm_tts_tickets.assignedto,fm_tts_tickets.status,fm_tts_tickets.user_id," . "fm_tts_tickets.subject,fm_tts_tickets.address,fm_tts_tickets.location_code,fm_tts_tickets.priority,fm_tts_tickets.cat_id,fm_tts_tickets.group_id," . "fm_tts_tickets.entry_date,fm_tts_tickets.modified_date,fm_tts_tickets.finnish_date,fm_tts_tickets.finnish_date2,fm_tts_tickets.order_id,fm_tts_tickets.vendor_id," . "fm_tts_tickets.billable_hours,fm_district.descr as district,fm_tts_views.id as view,fm_location1.loc1_name,fm_tts_tickets.ecodimb,fm_tts_tickets.order_dim1 {$result_order_field}"; $custom_cols = $this->custom->find('property', '.ticket', 0, '', 'ASC', 'attrib_sort', true, true); foreach ($custom_cols as $custom_col) { $return_fields .= ",fm_tts_tickets.{$custom_col['column_name']}"; } $sql = "SELECT DISTINCT {$return_fields},{$budget_field},{$actual_cost_field} FROM fm_tts_tickets" . " {$this->left_join} fm_location1 ON fm_tts_tickets.loc1=fm_location1.loc1" . " {$this->left_join} fm_part_of_town ON fm_location1.part_of_town_id=fm_part_of_town.part_of_town_id" . " {$this->left_join} fm_district ON fm_district.id = fm_part_of_town.district_id" . " {$order_join}{$date_join}{$date_budget_join}" . " LEFT OUTER JOIN fm_tts_views ON (fm_tts_tickets.id = fm_tts_views.id AND fm_tts_views.account_id='{$this->account}')"; if (isset($custom_filter['joinmethod_datatype']) && $custom_filter['joinmethod_datatype']) { foreach ($custom_filter['joinmethod_datatype'] as $_joinmethod) { $sql .= $_joinmethod; } } $group_fields = str_ireplace(array('fm_district.descr as district', 'fm_tts_views.id as view'), array('fm_district.descr', 'fm_tts_views.id'), $return_fields); $sql .= " {$filtermethod} {$querymethod} GROUP BY {$group_fields}{$budget_group_field}{$actual_cost_group_field}"; $sql_cnt = "SELECT DISTINCT {$budget_field},{$actual_cost_field}, fm_tts_tickets.id FROM fm_tts_tickets" . " {$this->left_join} fm_location1 ON fm_tts_tickets.loc1=fm_location1.loc1" . " {$this->left_join} fm_part_of_town ON fm_location1.part_of_town_id=fm_part_of_town.part_of_town_id" . " {$this->left_join} fm_district ON fm_district.id = fm_part_of_town.district_id" . " {$order_join}{$date_join}{$date_budget_join}" . " LEFT OUTER JOIN fm_tts_views ON (fm_tts_tickets.id = fm_tts_views.id AND fm_tts_views.account_id='{$this->account}')"; if (isset($custom_filter['joinmethod_datatype']) && $custom_filter['joinmethod_datatype']) { foreach ($custom_filter['joinmethod_datatype'] as $_joinmethod) { $sql_cnt .= $_joinmethod; } } $sql_cnt .= " {$filtermethod} {$querymethod}"; //_debug_array($sql); $cache_info = phpgwapi_cache::session_get('property', 'tts_listing_metadata'); if (!isset($cache_info['sql_hash']) || $cache_info['sql_hash'] != md5($sql_cnt)) { $cache_info = array(); } if (!$cache_info) { $sql2 = "SELECT count(*) as cnt, sum(budget) as sum_budget, sum(actual_cost) as sum_actual_cost FROM ({$sql_cnt} GROUP BY fm_tts_tickets.id, fm_tts_tickets.budget {$actual_cost_group_field}) as t"; $this->db->query($sql2, __LINE__, __FILE__); $this->db->next_record(); unset($sql2); $cache_info = array('total_records' => $this->db->f('cnt'), 'sum_budget' => $this->db->f('sum_budget'), 'sum_actual_cost' => $this->db->f('sum_actual_cost'), 'sql_hash' => md5($sql_cnt)); $custom_status = $this->get_custom_status(); $closed_status = array('X'); foreach ($custom_status as $custom) { if ($custom['closed']) { $closed_status[] = "C{$custom['id']}"; } } $filter_closed = " AND fm_tts_tickets.status NOT IN ('" . implode("','", $closed_status) . "')"; $sql2 = "SELECT (SUM(budget) - SUM(actual_cost)) as sum_difference FROM ({$sql_cnt} {$filter_closed} GROUP BY fm_tts_tickets.id, fm_tts_tickets.budget {$actual_cost_group_field}) as t"; $this->db->query($sql2, __LINE__, __FILE__); $this->db->next_record(); unset($sql2); $cache_info['sum_difference'] = (double) $this->db->f('sum_difference'); phpgwapi_cache::session_set('property', 'tts_listing_metadata', $cache_info); } $this->total_records = (int) $cache_info['total_records']; $this->sum_budget = (int) $cache_info['sum_budget']; $this->sum_actual_cost = (int) $cache_info['sum_actual_cost']; $this->sum_difference = (double) $cache_info['sum_difference']; $tickets = array(); if (!$dry_run) { if (!$allrows) { $this->db->limit_query($sql . $ordermethod, $start, __LINE__, __FILE__); } else { $_fetch_single = false; /* if($this->total_records > 200) { $_fetch_single = true; } else { $_fetch_single = false; } */ $this->db->query($sql . $ordermethod, __LINE__, __FILE__, false, $_fetch_single); unset($_fetch_single); } $i = 0; while ($this->db->next_record()) { $tickets[] = array('id' => (int) $this->db->f('id'), 'subject' => $this->db->f('subject', true), 'loc1_name' => $this->db->f('loc1_name', true), 'location_code' => $this->db->f('location_code'), 'district' => $this->db->f('district', true), 'user_id' => $this->db->f('user_id'), 'address' => $this->db->f('address', true), 'assignedto' => $this->db->f('assignedto'), 'status' => $this->db->f('status'), 'priority' => $this->db->f('priority'), 'cat_id' => $this->db->f('cat_id'), 'group_id' => $this->db->f('group_id'), 'entry_date' => $this->db->f('entry_date'), 'modified_date' => $this->db->f('modified_date'), 'finnish_date' => $this->db->f('finnish_date'), 'finnish_date2' => $this->db->f('finnish_date2'), 'order_id' => $this->db->f('order_id'), 'vendor_id' => $this->db->f('vendor_id'), 'actual_cost' => $this->db->f('actual_cost'), 'estimate' => $this->db->f('budget'), 'new_ticket' => $this->db->f('view') ? false : true, 'billable_hours' => $this->db->f('billable_hours'), 'ecodimb' => $this->db->f('ecodimb'), 'order_dim1' => $this->db->f('order_dim1')); foreach ($custom_cols as $custom_col) { if ($custom_value = $this->db->f($custom_col['column_name'], true)) { $custom_value = $this->custom->get_translated_value(array('value' => $custom_value, 'attrib_id' => $custom_col['id'], 'datatype' => $custom_col['datatype']), $location_id); } $tickets[$i][$custom_col['column_name']] = $custom_value; } $i++; } } $this->db->set_fetch_single(false); return $tickets; }