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;
}
