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;
}
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;
}
//Redirect
}
$smarty->assign('title', TTi18n::gettext($title = 'View TimeSheet Verification'));
// See index.php
/*
* Get FORM variables
*/
extract(FormVariables::GetVariables(array('action', 'id', 'timesheet_id', 'timesheet_queue_ids', 'selected_level')));
if (isset($timesheet_queue_ids)) {
$timesheet_queue_ids = unserialize(base64_decode(urldecode($timesheet_queue_ids)));
Debug::Arr($timesheet_queue_ids, ' Input TimeSheet Queue IDs ' . $action, __FILE__, __LINE__, __METHOD__, 10);
}
if (isset($data)) {
$data['date_stamp'] = TTDate::parseDateTime($data['date_stamp']);
}
$pptsvf = new PayPeriodTimeSheetVerifyFactory();
$action = Misc::findSubmitButton();
switch ($action) {
case 'pass':
if (count($timesheet_queue_ids) > 1) {
//Remove the authorized/declined timesheet from the stack.
array_shift($timesheet_queue_ids);
Redirect::Page(URLBuilder::getURL(array('id' => $timesheet_queue_ids[0], 'selected_level' => $selected_level, 'timesheet_queue_ids' => base64_encode(serialize($timesheet_queue_ids))), 'ViewTimeSheetVerification.php'));
} else {
Redirect::Page(URLBuilder::getURL(array('refresh' => TRUE), '../CloseWindow.php'));
}
case 'decline':
case 'authorize':
//Debug::setVerbosity(11);
Debug::text(' Authorizing TimeSheet: Action: ' . $action, __FILE__, __LINE__, __METHOD__, 10);
if (!empty($timesheet_id)) {
