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