function getByUserIdAndFolder($user_id, $folder, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($user_id == '') { return FALSE; } $strict = TRUE; if ($order == NULL) { $strict = FALSE; $order = array('a.status_id' => '= 10 desc', 'a.created_date' => 'desc'); } //Folder is: INBOX, SENT $key = Option::getByValue($folder, $this->getOptions('folder')); if ($key !== FALSE) { $folder = $key; } $rf = new RequestFactory(); $uf = new UserFactory(); $udf = new UserDateFactory(); $pptsvf = new PayPeriodTimeSheetVerifyFactory(); $ph = array('user_id' => $user_id); $folder_sent_query = NULL; $folder_inbox_query = NULL; $folder_inbox_query_a = NULL; $folder_inbox_query_ab = NULL; $folder_inbox_query_b = NULL; $folder_inbox_query_c = NULL; if ($folder == 10) { $ph['id'] = $user_id; $ph['created_by1'] = $user_id; $ph['created_by2'] = $user_id; $ph['created_by3'] = $user_id; $ph['created_by4'] = $user_id; $folder_inbox_query = ' AND a.created_by != ?'; $folder_inbox_query_a = ' OR d.id = ?'; $folder_inbox_query_ab = ' OR e.user_id = ?'; //$folder_inbox_query_b = ' OR a.parent_id in ( select parent_id FROM '. $this->getTable() .' WHERE created_by = '. $user_id .' ) '; $folder_inbox_query_b = ' OR a.parent_id in ( select parent_id FROM ' . $this->getTable() . ' WHERE created_by = ? AND parent_id != 0 ) '; $folder_inbox_query_c = ' OR a.parent_id in ( select id FROM ' . $this->getTable() . ' WHERE created_by = ? AND parent_id = 0 ) '; } elseif ($folder == 20) { $ph['created_by4'] = $user_id; $folder_sent_query = ' OR a.created_by = ?'; } //Need to include all threads that user has posted to. $query = ' SELECT a.*, CASE WHEN a.object_type_id = 5 THEN d.id WHEN a.object_type_id = 50 THEN c.user_id WHEN a.object_type_id = 90 THEN e.user_id END as sent_to_user_id FROM ' . $this->getTable() . ' as a LEFT JOIN ' . $uf->getTable() . ' as d ON a.object_type_id = 5 AND a.object_id = d.id LEFT JOIN ' . $uf->getTable() . ' as f ON a.created_by = f.id LEFT JOIN ' . $rf->getTable() . ' as b ON a.object_type_id = 50 AND a.object_id = b.id LEFT JOIN ' . $udf->getTable() . ' as c ON b.user_date_id = c.id LEFT JOIN ' . $pptsvf->getTable() . ' as e ON a.object_type_id = 90 AND a.object_id = e.id WHERE a.object_type_id in (5,50,90) AND ( ( ( c.user_id = ? ' . $folder_sent_query . ' ' . $folder_inbox_query_a . ' ' . $folder_inbox_query_ab . ' ' . $folder_inbox_query_b . ' ' . $folder_inbox_query_c . ' ) ' . $folder_inbox_query . ' ) ) AND ( a.deleted = 0 AND f.deleted = 0 AND ( b.id IS NULL OR ( b.id IS NOT NULL AND b.deleted = 0 ) ) AND ( c.id IS NULL OR ( c.id IS NOT NULL AND c.deleted = 0 ) ) AND ( d.id IS NULL OR ( d.id IS NOT NULL AND d.deleted = 0 ) ) AND ( e.id IS NULL OR ( e.id IS NOT NULL AND e.deleted = 0 ) ) AND NOT ( b.id IS NULL AND c.id IS NULL AND d.id IS NULL AND e.id IS NULL ) ) '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, array('sent_to_user_id')); //Debug::text('Query: '. $query , __FILE__, __LINE__, __METHOD__,9); if ($limit == NULL) { //Run query without limit $this->rs = $this->db->Execute($query, $ph); } else { $this->rs = $this->db->PageExecute($query, $limit, $page, $ph); } return $this; }
function getFlaggedExceptionsByUserIdAndPayPeriodStatus($user_id, $pay_period_status) { if ($user_id == '') { return FALSE; } if ($pay_period_status == '') { return FALSE; } $udf = new UserDateFactory(); $epf = new ExceptionPolicyFactory(); $ppf = new PayPeriodFactory(); $rf = new RequestFactory(); $ph = array('user_id' => $user_id, 'date_stamp1' => $this->db->BindDate(TTDate::getBeginDayEpoch(TTDate::getTime() - 32 * 86400)), 'date_stamp2' => $this->db->BindDate(TTDate::getBeginDayEpoch(TTDate::getTime())), 'status_id' => $pay_period_status); $query = ' select d.severity_id as severity_id, count(*) as total from ' . $this->getTable() . ' as a LEFT JOIN ' . $udf->getTable() . ' as b ON a.user_date_id = b.id LEFT JOIN ' . $epf->getTable() . ' as d ON a.exception_policy_id = d.id LEFT JOIN ' . $ppf->getTable() . ' as e ON b.pay_period_id = e.id where b.user_id = ? AND a.type_id = 50 AND ( b.date_stamp >= ? AND b.date_stamp <= ? ) AND e.status_id = ? AND NOT EXISTS ( select z.id from ' . $rf->getTable() . ' as z where z.user_date_id = a.user_date_id AND z.status_id = 30 ) AND ( a.deleted = 0 AND b.deleted = 0 AND e.deleted=0) GROUP BY d.severity_id ORDER BY d.severity_id desc '; $this->ExecuteSQL($query, $ph); return $this; }
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } $additional_order_fields = array(); $sort_column_aliases = array(); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('created_date' => 'desc'); $strict = FALSE; } else { //Always try to order by status first so INACTIVE employees go to the bottom. if (!isset($order['created_date'])) { $order = Misc::prependArray(array('created_date' => 'desc'), $order); } $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); //Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10); $uf = new UserFactory(); $rf = new RequestFactory(); $udf = new UserDateFactory(); $pptsvf = new PayPeriodTimeSheetVerifyListFactory(); if (getTTProductEdition() >= TT_PRODUCT_ENTERPRISE) { $uef = new UserExpenseFactory(); } $ph = array('company_id' => $company_id); $query = ' select a.*, CASE WHEN a.object_type_id = 90 THEN pptsvf.user_id ELSE ud.user_id END as user_id, y.first_name as created_by_first_name, y.middle_name as created_by_middle_name, y.last_name as created_by_last_name, z.first_name as updated_by_first_name, z.middle_name as updated_by_middle_name, z.last_name as updated_by_last_name from ' . $this->getTable() . ' as a LEFT JOIN ' . $rf->getTable() . ' as rf ON ( a.object_type_id in (1010,1020,1030,1040,1100) AND a.object_id = rf.id ) LEFT JOIN ' . $udf->getTable() . ' as ud ON ( rf.user_date_id = ud.id ) LEFT JOIN ' . $pptsvf->getTable() . ' as pptsvf ON ( a.object_type_id = 90 AND a.object_id = pptsvf.id ) '; if (getTTProductEdition() >= TT_PRODUCT_ENTERPRISE) { $query .= ' LEFT JOIN ' . $uef->getTable() . ' as uef ON ( a.object_type_id = 200 AND a.object_id = uef.id ) '; } $query .= ' LEFT JOIN ' . $uf->getTable() . ' as y ON ( a.created_by = y.id AND y.deleted = 0 ) LEFT JOIN ' . $uf->getTable() . ' as z ON ( a.updated_by = z.id AND z.deleted = 0 ) where y.company_id = ?'; $user_id_column = 'a.created_by'; if (isset($filter_data['object_type_id']) and in_array($filter_data['object_type_id'], array(1010, 1020, 1030, 1040, 1100))) { //Requests $user_id_column = 'ud.user_id'; } elseif (isset($filter_data['object_type_id']) and in_array($filter_data['object_type_id'], array(90))) { //TimeSheet $user_id_column = 'pptsvf.user_id'; } elseif (isset($filter_data['object_type_id']) and in_array($filter_data['object_type_id'], array(200))) { //Expense $user_id_column = 'uef.user_id'; } if (isset($filter_data['permission_children_ids']) and isset($filter_data['permission_children_ids'][0]) and !in_array(-1, (array) $filter_data['permission_children_ids'])) { $query .= ' AND ' . $user_id_column . ' in (' . $this->getListSQL($filter_data['permission_children_ids'], $ph) . ') '; } if (isset($filter_data['id']) and isset($filter_data['id'][0]) and !in_array(-1, (array) $filter_data['id'])) { $query .= ' AND a.id in (' . $this->getListSQL($filter_data['id'], $ph) . ') '; } if (isset($filter_data['exclude_id']) and isset($filter_data['exclude_id'][0]) and !in_array(-1, (array) $filter_data['exclude_id'])) { $query .= ' AND a.id not in (' . $this->getListSQL($filter_data['exclude_id'], $ph) . ') '; } if (isset($filter_data['object_type_id']) and isset($filter_data['object_type_id'][0]) and !in_array(-1, (array) $filter_data['object_type_id'])) { $query .= ' AND a.object_type_id in (' . $this->getListSQL($filter_data['object_type_id'], $ph) . ') '; } if (isset($filter_data['object_id']) and isset($filter_data['object_id'][0]) and !in_array(-1, (array) $filter_data['object_id'])) { $query .= ' AND a.object_id in (' . $this->getListSQL($filter_data['object_id'], $ph) . ') '; } $query .= isset($filter_data['created_by']) ? $this->getWhereClauseSQL(array('a.created_by', 'y.first_name', 'y.last_name'), $filter_data['created_by'], 'user_id_or_name', $ph) : NULL; $query .= isset($filter_data['updated_by']) ? $this->getWhereClauseSQL(array('a.updated_by', 'z.first_name', 'z.last_name'), $filter_data['updated_by'], 'user_id_or_name', $ph) : NULL; $query .= ' AND a.deleted = 0 '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if (!is_array($order)) { //Use Filter Data ordering if its set. if (isset($filter_data['sort_column']) and $filter_data['sort_order']) { $order = array(Misc::trimSortPrefix($filter_data['sort_column']) => $filter_data['sort_order']); } } $additional_order_fields = array('status_id'); $sort_column_aliases = array('status' => 'a.status_id', 'created_date' => 'c.created_date', 'created_by' => 'c.created_by'); $order = $this->getColumnsFromAliases($order, $sort_column_aliases); if ($order == NULL) { $order = array('a.status_id' => '= 10 desc', 'a.created_date' => 'desc'); $strict = FALSE; } else { $strict = TRUE; } //Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10); Debug::Arr($filter_data, 'Filter Data:', __FILE__, __LINE__, __METHOD__, 10); if (!isset($filter_data['folder_id'])) { $filter_data['folder_id'] = 10; //Inbox. } $mrf = new MessageRecipientFactory(); $msf = new MessageSenderFactory(); $rf = new RequestFactory(); $uf = new UserFactory(); $udf = new UserDateFactory(); $pptsvf = new PayPeriodTimeSheetVerifyFactory(); $ph = array('user_id' => $filter_data['current_user_id'], 'company_id' => $company_id); if ($filter_data['folder_id'] == 10) { //Inbox $additional_order_fields = array('from_last_name'); //Need to include all threads that user has posted to. $query = ' SELECT c.*, a.*, c.created_date as created_date, c.created_by as created_by, b.id as id, a.user_id as to_user_id, aa.first_name as to_first_name, aa.middle_name as to_middle_name, aa.last_name as to_last_name, b.user_id as from_user_id, bb.first_name as from_first_name, bb.middle_name as from_middle_name, bb.last_name as from_last_name FROM ' . $mrf->getTable() . ' as a LEFT JOIN ' . $uf->getTable() . ' as aa ON a.user_id = aa.id LEFT JOIN ' . $msf->getTable() . ' as b ON a.message_sender_id = b.id LEFT JOIN ' . $uf->getTable() . ' as bb ON b.user_id = bb.id LEFT JOIN ' . $this->getTable() . ' as c ON b.message_control_id = c.id LEFT JOIN ' . $uf->getTable() . ' as d ON c.object_type_id = 5 AND c.object_id = d.id LEFT JOIN ' . $rf->getTable() . ' as f ON c.object_type_id = 50 AND c.object_id = f.id LEFT JOIN ' . $pptsvf->getTable() . ' as h ON c.object_type_id = 90 AND c.object_id = h.id WHERE a.user_id = ? AND bb.company_id = ? AND c.object_type_id in (5,50,90)'; if (isset($filter_data['id']) and isset($filter_data['id'][0]) and !in_array(-1, (array) $filter_data['id'])) { $query .= ' AND b.id in (' . $this->getListSQL($filter_data['id'], $ph) . ') '; } if (isset($filter_data['object_type_id']) and isset($filter_data['object_type_id'][0]) and !in_array(-1, (array) $filter_data['object_type_id'])) { $query .= ' AND c.object_type_id in (' . $this->getListSQL($filter_data['object_type_id'], $ph) . ') '; } if (isset($filter_data['status_id']) and isset($filter_data['status_id'][0]) and !in_array(-1, (array) $filter_data['status_id'])) { $query .= ' AND a.status_id in (' . $this->getListSQL($filter_data['status_id'], $ph) . ') '; } if (isset($filter_data['user_id']) and isset($filter_data['user_id'][0]) and !in_array(-1, (array) $filter_data['user_id'])) { $query .= ' AND b.user_id in (' . $this->getListSQL($filter_data['user_id'], $ph) . ') '; } if (isset($filter_data['subject']) and trim($filter_data['subject']) != '') { $ph[] = strtolower(trim($filter_data['subject'])); $query .= ' AND lower(c.subject) LIKE ?'; } if (isset($filter_data['body']) and trim($filter_data['body']) != '') { $ph[] = strtolower(trim($filter_data['body'])); $query .= ' AND lower(c.body) LIKE ?'; } $query .= ' AND ( a.deleted = 0 AND c.deleted = 0 AND ( d.id IS NULL OR ( d.id IS NOT NULL AND d.deleted = 0 ) ) AND ( f.id IS NULL OR ( f.id IS NOT NULL AND f.deleted = 0 ) ) AND ( h.id IS NULL OR ( h.id IS NOT NULL AND h.deleted = 0 ) ) ) '; } else { //Sent //Need to include all threads that user has posted to. $additional_order_fields = array('to_last_name'); $query = ' SELECT c.*, a.*, b.id as id, a.user_id as to_user_id, aa.first_name as to_first_name, aa.middle_name as to_middle_name, aa.last_name as to_last_name, b.user_id as from_user_id, bb.first_name as from_first_name, bb.middle_name as from_middle_name, bb.last_name as from_last_name FROM ' . $mrf->getTable() . ' as a LEFT JOIN ' . $uf->getTable() . ' as aa ON a.user_id = aa.id LEFT JOIN ' . $msf->getTable() . ' as b ON a.message_sender_id = b.id LEFT JOIN ' . $uf->getTable() . ' as bb ON b.user_id = bb.id LEFT JOIN ' . $this->getTable() . ' as c ON b.message_control_id = c.id LEFT JOIN ' . $uf->getTable() . ' as d ON c.object_type_id = 5 AND c.object_id = d.id LEFT JOIN ' . $rf->getTable() . ' as f ON c.object_type_id = 50 AND c.object_id = f.id LEFT JOIN ' . $pptsvf->getTable() . ' as h ON c.object_type_id = 90 AND c.object_id = h.id WHERE b.user_id = ? AND bb.company_id = ? AND c.object_type_id in (5,50,90)'; if (isset($filter_data['id']) and isset($filter_data['id'][0]) and !in_array(-1, (array) $filter_data['id'])) { $query .= ' AND b.id in (' . $this->getListSQL($filter_data['id'], $ph) . ') '; } if (isset($filter_data['object_type_id']) and isset($filter_data['object_type_id'][0]) and !in_array(-1, (array) $filter_data['object_type_id'])) { $query .= ' AND c.object_type_id in (' . $this->getListSQL($filter_data['object_type_id'], $ph) . ') '; } if (isset($filter_data['status_id']) and isset($filter_data['status_id'][0]) and !in_array(-1, (array) $filter_data['status_id'])) { $query .= ' AND a.status_id in (' . $this->getListSQL($filter_data['status_id'], $ph) . ') '; } if (isset($filter_data['user_id']) and isset($filter_data['user_id'][0]) and !in_array(-1, (array) $filter_data['user_id'])) { $query .= ' AND a.user_id in (' . $this->getListSQL($filter_data['user_id'], $ph) . ') '; } if (isset($filter_data['subject']) and trim($filter_data['subject']) != '') { $ph[] = strtolower(trim($filter_data['subject'])); $query .= ' AND lower(c.subject) LIKE ?'; } if (isset($filter_data['body']) and trim($filter_data['body']) != '') { $ph[] = strtolower(trim($filter_data['body'])); $query .= ' AND lower(c.body) LIKE ?'; } $query .= ' AND ( b.deleted = 0 AND c.deleted = 0 AND ( d.id IS NULL OR ( d.id IS NOT NULL AND d.deleted = 0 ) ) AND ( f.id IS NULL OR ( f.id IS NOT NULL AND f.deleted = 0 ) ) AND ( h.id IS NULL OR ( h.id IS NOT NULL AND h.deleted = 0 ) ) ) '; } //Debug::Arr($ph, ' Query: '. $query, __FILE__, __LINE__, __METHOD__,10); $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }