本文整理汇总了PHP中UserFactory::getTable方法的典型用法代码示例。如果您正苦于以下问题：PHP UserFactory::getTable方法的具体用法？PHP UserFactory::getTable怎么用？PHP UserFactory::getTable使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类UserFactory的用法示例。
在下文中一共展示了UserFactory::getTable方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的PHP代码示例。
示例1: getByCompanyId
function getByCompanyId($company_id, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
$uf = new UserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $uf->getTable() . ' as uf ON a.user_id = uf.id
where uf.company_id = ?
AND ( uf.deleted = 0 )';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order);
$this->ExecuteSQL($query, $ph);
return $this;
}示例2: getAPISearchByCompanyIdAndArrayCriteria
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' => 'status_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('status_id' => 'asc', 'name' => 'asc');
$strict = FALSE;
} else {
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['status_id'])) {
$order = Misc::prependArray(array('status_id' => 'asc'), $order);
}
//Always sort by last name,first name after other columns
if (!isset($order['name'])) {
$order['name'] = 'asc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
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 ' . $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 a.company_id = ?
';
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['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['name']) and trim($filter_data['name']) != '') {
$ph[] = strtolower(trim($filter_data['name']));
$query .= ' AND lower(a.name) LIKE ?';
}
if (isset($filter_data['country']) and isset($filter_data['country'][0]) and !in_array(-1, (array) $filter_data['country'])) {
$query .= ' AND a.country in (' . $this->getListSQL($filter_data['country'], $ph) . ') ';
}
if (isset($filter_data['province']) and isset($filter_data['province'][0]) and !in_array(-1, (array) $filter_data['province']) and !in_array('00', (array) $filter_data['province'])) {
$query .= ' AND a.province in (' . $this->getListSQL($filter_data['province'], $ph) . ') ';
}
if (isset($filter_data['manual_id']) and trim($filter_data['manual_id']) != '') {
$ph[] = $this->Validator->stripNonNumeric(trim($filter_data['manual_id']));
$query .= ' AND a.manual_id = ?';
}
if (isset($filter_data['work_phone']) and trim($filter_data['work_phone']) != '') {
$ph[] = strtolower(trim($filter_data['work_phone']));
$query .= ' AND a.work_phone LIKE ?';
}
if (isset($filter_data['fax_phone']) and trim($filter_data['fax_phone']) != '') {
$ph[] = strtolower(trim($filter_data['fax_phone']));
$query .= ' AND a.fax_phone LIKE ?';
}
if (isset($filter_data['address1']) and trim($filter_data['address1']) != '') {
$ph[] = strtolower(trim($filter_data['address1']));
$query .= ' AND lower(a.address1) LIKE ?';
}
if (isset($filter_data['address2']) and trim($filter_data['address2']) != '') {
$ph[] = strtolower(trim($filter_data['address2']));
$query .= ' AND lower(a.address2) LIKE ?';
}
if (isset($filter_data['postal_code']) and trim($filter_data['postal_code']) != '') {
$ph[] = strtolower(trim($filter_data['postal_code']));
$query .= ' AND lower(a.postal_code) LIKE ?';
}
if (isset($filter_data['city']) and trim($filter_data['city']) != '') {
$ph[] = strtolower(trim($filter_data['city']));
$query .= ' AND lower(a.city) LIKE ?';
}
if (isset($filter_data['created_by']) and isset($filter_data['created_by'][0]) and !in_array(-1, (array) $filter_data['created_by'])) {
$query .= ' AND a.created_by in (' . $this->getListSQL($filter_data['created_by'], $ph) . ') ';
}
if (isset($filter_data['updated_by']) and isset($filter_data['updated_by'][0]) and !in_array(-1, (array) $filter_data['updated_by'])) {
$query .= ' AND a.updated_by in (' . $this->getListSQL($filter_data['updated_by'], $ph) . ') ';
}
$query .= '
AND a.deleted = 0
//.........这里部分代码省略.........
示例3: isUniqueValue
function isUniqueValue($user_id, $type_id, $value)
{
$ph = array('user_id' => (int) $user_id, 'type_id' => (int) $type_id, 'value' => (string) $value);
$uf = new UserFactory();
$query = 'select a.id
from ' . $this->getTable() . ' as a,
' . $uf->getTable() . ' as b
where a.user_id = b.id
AND b.company_id = ( select z.company_id from ' . $uf->getTable() . ' as z where z.id = ? and z.deleted = 0 )
AND a.type_id = ?
AND a.value = ?
AND ( a.deleted = 0 AND b.deleted = 0 )';
$id = $this->db->GetOne($query, $ph);
//Debug::Arr($id,'Unique Value: '. $value, __FILE__, __LINE__, __METHOD__,10);
if ($id === FALSE) {
return TRUE;
} else {
if ($id == $this->getId()) {
return TRUE;
}
}
return FALSE;
}示例4: array
function getAPILastWageSearchByCompanyIdAndArrayCriteria($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']);
}
}
if (!isset($filter_data['effective_date'])) {
$filter_data['effective_date'] = TTDate::getTime();
}
if (isset($filter_data['include_user_id'])) {
$filter_data['user_id'] = $filter_data['include_user_id'];
}
if (isset($filter_data['exclude_user_id'])) {
$filter_data['exclude_id'] = $filter_data['exclude_user_id'];
}
$additional_order_fields = array('wage_group');
if ($order == NULL) {
$order = array('effective_date' => 'desc', 'wage_group_id' => 'asc', 'type_id' => 'asc');
$strict = FALSE;
} else {
//Always sort by last name,first name after other columns
if (!isset($order['effective_date'])) {
$order['effective_date'] = 'desc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$cf = new CurrencyFactory();
$wgf = new WageGroupFactory();
$ph = array('effective_date' => $this->db->BindTimeStamp($filter_data['effective_date']), 'company_id' => $company_id);
/*
$query = '
select a.*
from '. $this->getTable() .' as a,
(
select z.user_id, max(effective_date) as effective_date
from '. $this->getTable() .' as z
where
z.effective_date <= ?
AND z.wage_group_id = 0
AND z.user_id in ('. $this->getListSQL( $user_id, $ph ) .')
AND ( z.deleted = 0 )
GROUP BY z.user_id
) as b,
'. $uf->getTable() .' as c
WHERE a.user_id = b.user_id
AND a.effective_date = b.effective_date
AND a.user_id = c.id
AND ( c.deleted = 0 AND a.deleted = 0)
';
*/
$query = '
select a.*,
CASE WHEN a.wage_group_id = 0 THEN \'' . TTi18n::getText('-Default-') . '\' ELSE ab.name END as wage_group,
b.first_name as first_name,
b.last_name as last_name,
b.country as country,
b.province as province,
c.id as default_branch_id,
c.name as default_branch,
d.id as default_department_id,
d.name as default_department,
e.id as group_id,
e.name as user_group,
f.id as title_id,
f.name as title,
g.id as currency_id,
g.iso_code as iso_code,
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 (
select uwf.user_id as user_id, uwf.wage_group_id as wage_group_id, max(effective_date) as effective_date
from ' . $this->getTable() . ' as uwf
where uwf.effective_date <= ? AND uwf.deleted = 0
GROUP BY uwf.wage_group_id, uwf.user_id
) as uwf_b
LEFT JOIN ' . $this->getTable() . ' as a ON ( a.user_id = uwf_b.user_id AND a.wage_group_id = uwf_b.wage_group_id AND a.effective_date = uwf_b.effective_date )
LEFT JOIN ' . $wgf->getTable() . ' as ab ON ( a.wage_group_id = ab.id AND ab.deleted = 0 )
LEFT JOIN ' . $uf->getTable() . ' as b ON ( a.user_id = b.id AND b.deleted = 0 )
LEFT JOIN ' . $bf->getTable() . ' as c ON ( b.default_branch_id = c.id AND c.deleted = 0)
//.........这里部分代码省略.........
示例5: getAPISearchByCompanyIdAndArrayCriteria
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' => 'status_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('status_id' => 'asc', 'name' => 'asc');
$strict = FALSE;
} else {
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['status_id'])) {
$order = Misc::prependArray(array('status_id' => 'asc'), $order);
}
//Always sort by last name,first name after other columns
if (!isset($order['name'])) {
$order['name'] = 'asc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
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 ' . $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 a.company_id = ?';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.created_by', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('a.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL;
if (isset($filter_data['status']) and trim($filter_data['status']) != '' and !isset($filter_data['status_id'])) {
$filter_data['status_id'] = Option::getByFuzzyValue($filter_data['status'], $this->getOptions('status'));
}
$query .= isset($filter_data['status_id']) ? $this->getWhereClauseSQL('a.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text_metaphone', $ph) : NULL;
$query .= isset($filter_data['country']) ? $this->getWhereClauseSQL('a.country', $filter_data['country'], 'upper_text_list', $ph) : NULL;
$query .= isset($filter_data['province']) ? $this->getWhereClauseSQL('a.province', $filter_data['province'], 'upper_text_list', $ph) : NULL;
$query .= isset($filter_data['city']) ? $this->getWhereClauseSQL('a.city', $filter_data['city'], 'text', $ph) : NULL;
$query .= isset($filter_data['manual_id']) ? $this->getWhereClauseSQL('a.manual_id', $this->Validator->stripNon32bitInteger($filter_data['manual_id']), 'numeric', $ph) : NULL;
$query .= isset($filter_data['work_phone']) ? $this->getWhereClauseSQL('a.work_phone', $filter_data['work_phone'], 'phone', $ph) : NULL;
$query .= isset($filter_data['fax_phone']) ? $this->getWhereClauseSQL('a.work_phone', $filter_data['fax_phone'], 'phone', $ph) : NULL;
$query .= isset($filter_data['address1']) ? $this->getWhereClauseSQL('a.address1', $filter_data['address1'], 'text', $ph) : NULL;
$query .= isset($filter_data['address2']) ? $this->getWhereClauseSQL('a.address2', $filter_data['address2'], 'text', $ph) : NULL;
$query .= isset($filter_data['postal_code']) ? $this->getWhereClauseSQL('a.postal_code', $filter_data['postal_code'], 'text', $ph) : NULL;
$query .= isset($filter_data['tag']) ? $this->getWhereClauseSQL('a.id', array('company_id' => $company_id, 'object_type_id' => 110, 'tag' => $filter_data['tag']), 'tag', $ph) : NULL;
$query .= isset($filter_data['created_date']) ? $this->getWhereClauseSQL('a.created_date', $filter_data['created_date'], 'date_range', $ph) : NULL;
$query .= isset($filter_data['updated_date']) ? $this->getWhereClauseSQL('a.updated_date', $filter_data['updated_date'], 'date_range', $ph) : NULL;
/*
$query .= ( isset($filter_data['created_by']) AND is_array($filter_data['created_by']) ) ? $this->getWhereClauseSQL( 'a.created_by', $filter_data['created_by'], 'numeric_list', $ph ) : NULL;
$query .= ( isset($filter_data['updated_by']) AND is_array($filter_data['updated_by']) ) ? $this->getWhereClauseSQL( 'a.updated_by', $filter_data['updated_by'], 'numeric_list', $ph ) : NULL;
if ( isset($filter_data['created_by']) AND !is_array($filter_data['created_by']) AND trim($filter_data['created_by']) != '' ) {
$ph[] = $ph[] = $this->handleSQLSyntax(strtolower(trim($filter_data['created_by'])));
$query .= ' AND (lower(y.first_name) LIKE ? OR lower(y.last_name) LIKE ? ) ';
}
if ( isset($filter_data['updated_by']) AND !is_array($filter_data['updated_by']) AND trim($filter_data['updated_by']) != '' ) {
$ph[] = $ph[] = $this->handleSQLSyntax(strtolower(trim($filter_data['updated_by'])));
$query .= ' AND (lower(z.first_name) LIKE ? OR lower(z.last_name) LIKE ? ) ';
}
*/
$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;
}示例6: getAPISearchByCompanyIdAndArrayCriteria
//.........这里部分代码省略.........
a.actual_total_time as actual_total_time,
c.user_id as user_id,
c.date_stamp as date_stamp,
c.pay_period_id as pay_period_id,
d.first_name as first_name,
d.last_name as last_name,
d.status_id as user_status_id,
d.group_id as group_id,
g.name as "group",
d.title_id as title_id,
h.name as title,
d.default_branch_id as default_branch_id,
e.name as default_branch,
d.default_department_id as default_department_id,
f.name as default_department,
d.created_by as user_created_by,
z.id as user_wage_id,
z.effective_date as user_wage_effective_date ';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ',
x.name as job,
x.name as job_name,
x.status_id as job_status_id,
x.manual_id as job_manual_id,
x.branch_id as job_branch_id,
x.department_id as job_department_id,
x.group_id as job_group_id,
y.name as job_item';
}
$query .= '
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $udf->getTable() . ' as c ON a.user_date_id = c.id
LEFT JOIN ' . $uf->getTable() . ' as d ON c.user_id = d.id
LEFT JOIN ' . $bf->getTable() . ' as e ON ( d.default_branch_id = e.id AND e.deleted = 0)
LEFT JOIN ' . $df->getTable() . ' as f ON ( d.default_department_id = f.id AND f.deleted = 0)
LEFT JOIN ' . $ugf->getTable() . ' as g ON ( d.group_id = g.id AND g.deleted = 0 )
LEFT JOIN ' . $utf->getTable() . ' as h ON ( d.title_id = h.id AND h.deleted = 0 )
LEFT JOIN ' . $bf->getTable() . ' as j ON ( a.branch_id = j.id AND j.deleted = 0)
LEFT JOIN ' . $df->getTable() . ' as k ON ( a.department_id = k.id AND k.deleted = 0)
LEFT JOIN ' . $otpf->getTable() . ' as otpf ON ( a.over_time_policy_id > 0 AND a.over_time_policy_id = otpf.id AND otpf.deleted = 0 )
LEFT JOIN ' . $apf->getTable() . ' as apf ON ( a.absence_policy_id > 0 AND a.absence_policy_id = apf.id AND apf.deleted = 0 )
LEFT JOIN ' . $ppf->getTable() . ' as ppf ON ( a.premium_policy_id > 0 AND a.premium_policy_id = ppf.id AND ppf.deleted = 0 )
LEFT JOIN ' . $mpf->getTable() . ' as mpf ON ( a.meal_policy_id > 0 AND a.meal_policy_id = mpf.id AND mpf.deleted = 0 )
LEFT JOIN ' . $bpf->getTable() . ' as bpf ON ( a.break_policy_id > 0 AND a.break_policy_id = bpf.id AND bpf.deleted = 0 )
LEFT JOIN ' . $uwf->getTable() . ' as z ON z.id = (select z.id
from ' . $uwf->getTable() . ' as z
where z.user_id = c.user_id
and z.effective_date <= c.date_stamp
and z.wage_group_id = 0
and z.deleted = 0
order by z.effective_date desc LiMiT 1)
';
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$query .= ' LEFT JOIN ' . $jf->getTable() . ' as x ON a.job_id = x.id';
$query .= ' LEFT JOIN ' . $jif->getTable() . ' as y ON a.job_item_id = y.id';
}
$query .= ' WHERE d.company_id = ?';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('d.id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL;示例7: getAPISearchByCompanyIdAndArrayCriteria
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
if (isset($filter_data['qualification_group_id'])) {
$filter_data['group_id'] = $filter_data['qualification_group_id'];
}
if (isset($filter_data['qualification_type_id'])) {
$filter_data['type_id'] = $filter_data['qualification_type_id'];
}
if (isset($filter_data['qualification_id'])) {
$filter_data['id'] = $filter_data['qualification_id'];
}
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('type_id', 'group');
$sort_column_aliases = array('type' => 'type_id', 'group' => 'd.name');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('type_id' => 'asc', 'name' => 'asc');
$strict = FALSE;
} else {
$strict = TRUE;
}
Debug::Arr($order, 'Order Data:', __FILE__, __LINE__, __METHOD__, 10);
Debug::Arr($filter_data, 'Filter Data:', __FILE__, __LINE__, __METHOD__, 10);
$uf = new UserFactory();
$qgf = new QualificationGroupFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
d.name as "group",
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 ' . $qgf->getTable() . ' as d ON ( a.group_id = d.id AND d.deleted = 0)
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 a.company_id = ?';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.created_by', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL;
//$query .= ( isset($filter_data['exclude_id']) ) ? $this->getWhereClauseSQL( 'a.id', $filter_data['exclude_id'], 'not_numeric_list', $ph ) : NULL;
if (isset($filter_data['type']) and trim($filter_data['type']) != '' and !isset($filter_data['type_id'])) {
$filter_data['type_id'] = Option::getByFuzzyValue($filter_data['type'], $this->getOptions('type'));
}
$query .= isset($filter_data['type_id']) ? $this->getWhereClauseSQL('a.type_id', $filter_data['type_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text_metaphone', $ph) : NULL;
$query .= isset($filter_data['description']) ? $this->getWhereClauseSQL('a.description', $filter_data['description'], 'text', $ph) : NULL;
$query .= isset($filter_data['group_id']) ? $this->getWhereClauseSQL('a.group_id', $filter_data['group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['group']) ? $this->getWhereClauseSQL('d.name', $filter_data['group'], 'text', $ph) : NULL;
$query .= isset($filter_data['tag']) ? $this->getWhereClauseSQL('a.id', array('company_id' => $company_id, 'object_type_id' => 250, 'tag' => $filter_data['tag']), 'tag', $ph) : NULL;
if (isset($filter_data['created_date']) and trim($filter_data['created_date']) != '') {
$date_filter = $this->getDateRangeSQL($filter_data['created_date'], 'a.created_date');
if ($date_filter != FALSE) {
$query .= ' AND ' . $date_filter;
}
unset($date_filter);
}
if (isset($filter_data['updated_date']) and trim($filter_data['updated_date']) != '') {
$date_filter = $this->getDateRangeSQL($filter_data['updated_date'], 'a.updated_date');
if ($date_filter != FALSE) {
$query .= ' AND ' . $date_filter;
}
unset($date_filter);
}
$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);
//Debug::Arr( $ph, 'Query: '.$query, __FILE__, __LINE__, __METHOD__, 10 );
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}示例8: array
function getDaysWorkedByTimePeriodAndUserIdAndCompanyIdAndStartDateAndEndDate($time_period, $user_ids, $company_id, $start_date, $end_date, $where = NULL, $order = NULL)
{
if ($time_period == '') {
return FALSE;
}
if ($user_ids == '') {
return FALSE;
}
if ($company_id == '') {
return FALSE;
}
if ($start_date == '') {
return FALSE;
}
if ($end_date == '') {
return FALSE;
}
/*
if ( $order == NULL ) {
$order = array( 'date_stamp' => 'asc' );
$strict = FALSE;
} else {
$strict = TRUE;
}
*/
$uf = new UserFactory();
$pcf = new PunchControlFactory();
$ph = array('company_id' => $company_id, 'start_date' => $this->db->BindDate($start_date), 'end_date' => $this->db->BindDate($end_date));
$query = '
select user_id,
avg(total) as avg,
min(total) as min,
max(total) as max
from (
select a.user_id,
(EXTRACT(' . $time_period . ' FROM a.date_stamp) || \'-\' || EXTRACT(year FROM a.date_stamp) ) as date,
count(*) as total
from ' . $this->getTable() . ' as a,
' . $uf->getTable() . ' as b
where a.user_id = b.id
AND b.company_id = ?
AND a.date_stamp >= ?
AND a.date_stamp <= ?
AND a.user_id in (' . $this->getListSQL($user_ids, $ph) . ')
AND exists(
select id
from ' . $pcf->getTable() . ' as z
where z.user_date_id = a.id
AND z.deleted=0
)
AND ( a.deleted = 0 AND b.deleted=0 )
GROUP BY user_id,(EXTRACT(' . $time_period . ' FROM a.date_stamp) || \'-\' || EXTRACT(year FROM a.date_stamp) )
) tmp
GROUP BY user_id
';
/*
$query = '
select user_id,
avg(total) as avg,
min(total) as min,
max(total) as max
from (
select a.user_id,
(date_part(\''.$time_period.'\', a.date_stamp) || \'-\' || date_part(\'year\', a.date_stamp) ) as date,
count(*) as total
from '. $this->getTable() .' as a,
'. $uf->getTable() .' as b
where a.user_id = b.id
AND b.company_id = ?
AND a.date_stamp >= ?
AND a.date_stamp <= ?
AND a.user_id in ('. $this->getListSQL($user_ids, $ph) .')
AND exists(
select id
from '. $pcf->getTable() .' as z
where z.user_date_id = a.id
AND z.deleted=0
)
AND ( a.deleted = 0 AND b.deleted=0 )
GROUP BY user_id,(date_part(\''. $time_period.'\', a.date_stamp) || \'-\' || date_part(\'year\', a.date_stamp) )
) tmp
GROUP BY user_id
';
*/
//$query .= $this->getWhereSQL( $where );
//$query .= $this->getSortSQL( $order );
$this->rs = $this->db->Execute($query, $ph);
return $this;
}示例9: getAPISearchByCompanyIdAndArrayCriteria
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
if (isset($filter_data['roe_id'])) {
$filter_data['id'] = $filter_data['roe_id'];
unset($filter_data['roe_id']);
}
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('uf.first_name', 'uf.last_name', 'code_id', 'pay_period_type_id');
$sort_column_aliases = array('code' => 'code_id', 'pay_period_type' => 'pay_period_type_id');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('last_date' => 'desc');
//Order ROEs by last date for which paid.
$strict = FALSE;
} else {
if (isset($order['first_name'])) {
$order['uf.first_name'] = $order['first_name'];
unset($order['first_name']);
}
if (isset($order['last_name'])) {
$order['uf.last_name'] = $order['last_name'];
unset($order['last_name']);
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
//$ppsf = new PayPeriodScheduleFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
uf.first_name as first_name,
uf.last_name as last_name,
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 ' . $uf->getTable() . ' as uf ON ( a.user_id = uf.id AND uf.deleted = 0 )
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 uf.company_id = ?';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL;
if (isset($filter_data['code']) and trim($filter_data['code']) != '' and !isset($filter_data['code_id'])) {
$filter_data['code_id'] = Option::getByFuzzyValue($filter_data['code'], $this->getOptions('code'));
}
$query .= isset($filter_data['first_name']) ? $this->getWhereClauseSQL('uf.first_name', $filter_data['first_name'], 'text_metaphone', $ph) : NULL;
$query .= isset($filter_data['last_name']) ? $this->getWhereClauseSQL('uf.last_name', $filter_data['last_name'], 'text_metaphone', $ph) : NULL;
$query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('a.user_id', $filter_data['user_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['code_id']) ? $this->getWhereClauseSQL('a.code_id', $filter_data['code_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['pay_period_type_id']) ? $this->getWhereClauseSQL('a.pay_period_type_id', $filter_data['pay_period_type_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['first_date']) ? $this->getWhereClauseSQL('a.first_date', $filter_data['first_date'], 'date_range', $ph) : NULL;
$query .= isset($filter_data['last_date']) ? $this->getWhereClauseSQL('a.last_date', $filter_data['last_date'], 'date_range', $ph) : NULL;
$query .= isset($filter_data['pay_period_end_date']) ? $this->getWhereClauseSQL('a.pay_period_end_date', $filter_data['pay_period_end_date'], 'date_range', $ph) : NULL;
$query .= isset($filter_data['recall_date']) ? $this->getWhereClauseSQL('a.recall_date', $filter_data['recall_date'], 'date_range', $ph) : NULL;
$query .= isset($filter_data['serial']) ? $this->getWhereClauseSQL('a.serial', $filter_data['serial'], 'text', $ph) : NULL;
$query .= isset($filter_data['comments']) ? $this->getWhereClauseSQL('a.comments', $filter_data['comments'], 'text', $ph) : NULL;
if (isset($filter_data['created_by']) and isset($filter_data['created_by'][0]) and !in_array(-1, (array) $filter_data['created_by'])) {
$query .= ' AND a.created_by in (' . $this->getListSQL($filter_data['created_by'], $ph) . ') ';
}
if (isset($filter_data['updated_by']) and isset($filter_data['updated_by'][0]) and !in_array(-1, (array) $filter_data['updated_by'])) {
$query .= ' AND a.updated_by in (' . $this->getListSQL($filter_data['updated_by'], $ph) . ') ';
}
$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;
}示例10: getLastPunchByCompanyIdAndArrayCriteria
function getLastPunchByCompanyIdAndArrayCriteria($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('b.name', 'c.name', 'd.name', 'e.name');
$additional_order_fields = array('b.branch_id', 'c.date_stamp', 'd.last_name', 'a.time_stamp', 'a.status_id', 'b.branch_id', 'b.department_id', 'e.type_id');
if ($order == NULL) {
$order = array('b.branch_id' => 'asc', 'd.last_name' => 'asc', 'a.time_stamp' => 'desc', 'a.punch_control_id' => 'asc', 'a.status_id' => 'asc');
$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['exclude_user_ids'])) {
$filter_data['exclude_id'] = $filter_data['exclude_user_ids'];
}
if (isset($filter_data['include_user_ids'])) {
$filter_data['id'] = $filter_data['include_user_ids'];
}
if (isset($filter_data['user_status_ids'])) {
$filter_data['status_id'] = $filter_data['user_status_ids'];
}
if (isset($filter_data['user_title_ids'])) {
$filter_data['title_id'] = $filter_data['user_title_ids'];
}
if (isset($filter_data['group_ids'])) {
$filter_data['group_id'] = $filter_data['group_ids'];
}
if (isset($filter_data['branch_ids'])) {
$filter_data['default_branch_id'] = $filter_data['branch_ids'];
}
if (isset($filter_data['department_ids'])) {
$filter_data['default_department_id'] = $filter_data['department_ids'];
}
if (isset($filter_data['punch_branch_ids'])) {
$filter_data['punch_branch_id'] = $filter_data['punch_branch_ids'];
}
if (isset($filter_data['punch_department_ids'])) {
$filter_data['punch_department_id'] = $filter_data['punch_department_ids'];
}
if (isset($filter_data['exclude_job_ids'])) {
$filter_data['exclude_id'] = $filter_data['exclude_job_ids'];
}
if (isset($filter_data['include_job_ids'])) {
$filter_data['include_job_id'] = $filter_data['include_job_ids'];
}
if (isset($filter_data['job_group_ids'])) {
$filter_data['job_group_id'] = $filter_data['job_group_ids'];
}
if (isset($filter_data['job_item_ids'])) {
$filter_data['job_item_id'] = $filter_data['job_item_ids'];
}
$uf = new UserFactory();
$udf = new UserDateFactory();
$pcf = new PunchControlFactory();
$sf = new StationFactory();
if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) {
$jf = new JobFactory();
$jif = new JobItemFactory();
}
$ph = array('company_id' => $company_id);
$query = '
select
a.id as punch_id,
a.punch_control_id as punch_control_id,
a.type_id as type_id,
a.status_id as status_id,
a.time_stamp as time_stamp,
a.actual_time_stamp as actual_time_stamp,
b.user_date_id as user_date_id,
c.date_stamp as date_stamp,
b.branch_id as branch_id,
b.department_id as department_id,
b.job_id as job_id,
b.job_item_id as job_item_id,
b.note as note,
c.user_id as user_id,
e.type_id as station_type_id,
e.station_id as station_station_id,
e.source as station_source,
e.description as station_description
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $pcf->getTable() . ' as b ON a.punch_control_id = b.id
LEFT JOIN ' . $udf->getTable() . ' as c ON b.user_date_id = c.id
LEFT JOIN ' . $uf->getTable() . ' as d ON c.user_id = d.id
LEFT JOIN ' . $sf->getTable() . ' as e ON a.station_id = e.id
LEFT JOIN (
select tmp2_d.id, max(tmp2_a.time_stamp) as max_punch_time_stamp
//.........这里部分代码省略.........
示例11: getUniqueCountryByCompanyId
function getUniqueCountryByCompanyId($id, $where = NULL, $order = NULL)
{
if ($id == '') {
return FALSE;
}
$uf = new UserFactory();
$ph = array('id' => $id);
$query = '
select distinct a.country
from ' . $uf->getTable() . ' as a
where a.company_id = ?
AND ( a.deleted = 0 )';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order);
return $this->db->GetCol($query, $ph);
}示例12: getAPISearchByCompanyIdAndArrayCriteria
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();
if ($order == NULL) {
$order = array('name' => 'asc');
$strict = FALSE;
} else {
//Always sort by last name,first name after other columns
if (!isset($order['name'])) {
$order['name'] = 'asc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
Debug::Arr($filter_data, 'Filter Data:', __FILE__, __LINE__, __METHOD__, 10);
$uf = new UserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
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 ' . $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 a.company_id = ?
';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('a.created_by', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL;
//Use this method instead of the method below.
$query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('a.id', $filter_data['id'], 'numeric_list', $ph) : NULL;
//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) .') ';
//}
$query .= isset($filter_data['exclude_id']) ? $this->getWhereClauseSQL('a.id', $filter_data['exclude_id'], 'not_numeric_list', $ph) : NULL;
$query .= isset($filter_data['name']) ? $this->getWhereClauseSQL('a.name', $filter_data['name'], 'text', $ph) : NULL;
if (isset($filter_data['created_date']) and trim($filter_data['created_date']) != '') {
$date_filter = $this->getDateRangeSQL($filter_data['created_date'], 'a.created_date');
if ($date_filter != FALSE) {
$query .= ' AND ' . $date_filter;
}
unset($date_filter);
}
if (isset($filter_data['updated_date']) and trim($filter_data['updated_date']) != '') {
$date_filter = $this->getDateRangeSQL($filter_data['updated_date'], 'a.updated_date');
if ($date_filter != FALSE) {
$query .= ' AND ' . $date_filter;
}
unset($date_filter);
}
$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;
}示例13: getAPISearchByCompanyIdAndArrayCriteria
function getAPISearchByCompanyIdAndArrayCriteria($company_id, $filter_data, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
if (isset($filter_data['include_user_id'])) {
$filter_data['user_id'] = $filter_data['include_user_id'];
}
if (isset($filter_data['exclude_user_id'])) {
$filter_data['exclude_id'] = $filter_data['exclude_user_id'];
}
if (isset($filter_data['qualification_group_id'])) {
$filter_data['group_id'] = $filter_data['qualification_group_id'];
}
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('uf.first_name', 'uf.last_name', 'qf.name', 'qgf.name', 'df.name', 'bf.name', 'ugf.name', 'utf.name');
$sort_column_aliases = array();
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('qf.name' => 'asc', 'qgf.name' => 'asc');
$strict = FALSE;
} else {
if (isset($order['first_name'])) {
$order['uf.first_name'] = $order['first_name'];
unset($order['first_name']);
}
if (isset($order['last_name'])) {
$order['uf.last_name'] = $order['last_name'];
unset($order['last_name']);
}
if (isset($order['qualification'])) {
$order['qf.name'] = $order['qualification'];
unset($order['qualification']);
}
if (isset($order['group'])) {
$order['qgf.name'] = $order['group'];
unset($order['group']);
}
if (isset($order['default_department'])) {
$order['df.name'] = $order['default_department'];
unset($order['default_department']);
}
if (isset($order['default_branch'])) {
$order['bf.name'] = $order['default_branch'];
unset($order['default_branch']);
}
if (isset($order['user_group'])) {
$order['ugf.name'] = $order['user_group'];
unset($order['user_group']);
}
if (isset($order['title'])) {
$order['utf.name'] = $order['title'];
unset($order['title']);
}
$strict = TRUE;
}
Debug::Arr($order, 'Order Data:', __FILE__, __LINE__, __METHOD__, 10);
Debug::Arr($filter_data, 'Filter Data:', __FILE__, __LINE__, __METHOD__, 10);
$uf = new UserFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$qf = new QualificationFactory();
$usf = new UserSkillFactory();
$ulf = new UserLanguageFactory();
$umf = new UserMembershipFactory();
$qgf = new QualificationGroupFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
uf.first_name as first_name,
uf.last_name as last_name,
qf.name as qualification,
qgf.name as "group",
bf.id as default_branch_id,
bf.name as default_branch,
df.id as default_department_id,
df.name as default_department,
ugf.id as user_group_id,
ugf.name as user_group,
utf.id as user_title_id,
utf.name as title,
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 ' . $uf->getTable() . ' as uf ON ( a.user_id = uf.id AND uf.deleted = 0)
LEFT JOIN ' . $usf->getTable() . ' as usf ON ( a.qualification_id = usf.qualification_id AND usf.deleted = 0)
LEFT JOIN ' . $ulf->getTable() . ' as ulf ON ( a.qualification_id = ulf.qualification_id AND ulf.deleted =0 )
//.........这里部分代码省略.........
示例14: getAPISearchByCompanyIdAndArrayCriteria
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('action_id', 'object_id', 'last_name', 'first_name');
$sort_column_aliases = array('action' => 'action_id', 'object' => 'table_name');
$order = $this->getColumnsFromAliases($order, $sort_column_aliases);
if ($order == NULL) {
$order = array('date' => 'desc', 'table_name' => 'asc', 'object_id' => 'asc');
$strict = FALSE;
} else {
//Always try to order by status first so INACTIVE employees go to the bottom.
if (!isset($order['date'])) {
$order['date'] = 'desc';
}
$strict = TRUE;
}
//Debug::Arr($order,'Order Data:', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($filter_data,'Filter Data:', __FILE__, __LINE__, __METHOD__,10);
$uf = new UserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
uf.first_name as first_name,
uf.middle_name as middle_name,
uf.last_name as last_name
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $uf->getTable() . ' as uf ON ( a.user_id = uf.id AND uf.deleted = 0 )
where uf.company_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 a.user_id 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['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['action']) and trim($filter_data['action']) != '' and !isset($filter_data['action_id'])) {
$filter_data['action_id'] = Option::getByFuzzyValue($filter_data['action'], $this->getOptions('action'));
}
if (isset($filter_data['table_name']) and isset($filter_data['table_name'][0]) and !in_array(-1, (array) $filter_data['table_name'])) {
$query .= ' AND a.table_name in (' . $this->getListSQL($filter_data['table_name'], $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['action_id']) ? $this->getWhereClauseSQL('a.action_id', $filter_data['action_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['date']) ? $this->getWhereClauseSQL('a.date', $filter_data['date'], 'date_range', $ph) : NULL;
if (isset($filter_data['first_name']) and trim($filter_data['first_name']) != '') {
$ph[] = $this->handleSQLSyntax(strtolower(trim($filter_data['first_name'])));
$query .= ' AND (lower(uf.first_name) LIKE ? ) ';
}
if (isset($filter_data['last_name']) and trim($filter_data['last_name']) != '') {
$ph[] = $this->handleSQLSyntax(strtolower(trim($filter_data['last_name'])));
$query .= ' AND (lower(uf.last_name) LIKE ? ) ';
}
//Need to support table_name -> object_id pairs for including log entires from different tables/objects.
if (isset($filter_data['table_name_object_id']) and is_array($filter_data['table_name_object_id']) and count($filter_data['table_name_object_id']) > 0) {
foreach ($filter_data['table_name_object_id'] as $table_name => $object_id) {
$ph[] = strtolower(trim($table_name));
$sub_query[] = '(a.table_name = ? AND a.object_id in (' . $this->getListSQL($object_id, $ph) . ') )';
}
if (isset($sub_query)) {
$query .= ' AND ( ' . implode(' OR ', $sub_query) . ' ) ';
}
unset($table_name, $object_id, $sub_query);
}
if (isset($filter_data['description']) and trim($filter_data['description']) != '') {
$ph[] = strtolower(trim($filter_data['description']));
$query .= ' AND lower(a.description) LIKE ?';
}
$query .= '';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}示例15: getSearchByCompanyIdAndArrayCriteria
function getSearchByCompanyIdAndArrayCriteria($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']);
}
}
Debug::Arr($order, 'aOrder Data:', __FILE__, __LINE__, __METHOD__, 10);
$additional_order_fields = array('name', 'description', 'last_name', 'template_id');
if ($order == NULL) {
$order = array('last_name' => 'asc', 'd.id' => 'asc', 'a.start_date' => 'desc');
$strict = FALSE;
} else {
//Always try to order by status first so UNPAID employees go to the bottom.
if (isset($order['last_name'])) {
$order['d.last_name'] = $order['last_name'];
unset($order['last_name']);
}
if (isset($order['first_name'])) {
$order['d.first_name'] = $order['first_name'];
unset($order['first_name']);
}
if (isset($order['template_id'])) {
$order['b.id'] = $order['template_id'];
unset($order['template_id']);
}
/*
if ( isset($order['status']) ) {
$order['status_id'] = $order['status'];
unset($order['status']);
}
if ( isset($order['transaction_date']) ) {
$order['last_name'] = 'asc';
} else {
$order['transaction_date'] = 'desc';
}
*/
$strict = TRUE;
}
Debug::Arr($order, 'bOrder Data:', __FILE__, __LINE__, __METHOD__, 10);
Debug::Arr($filter_data, 'Filter Data:', __FILE__, __LINE__, __METHOD__, 10);
$uf = new UserFactory();
$rsuf = new RecurringScheduleUserFactory();
$rstcf = new RecurringScheduleTemplateControlFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
b.name as name,
b.description as description,
c.user_id as user_id,
d.last_name as last_name
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $rstcf->getTable() . ' as b ON a.recurring_schedule_template_control_id = b.id
LEFT JOIN ' . $rsuf->getTable() . ' as c ON a.id = c.recurring_schedule_control_id
LEFT JOIN ' . $uf->getTable() . ' as d ON c.user_id = d.id
where a.company_id = ?
';
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['permission_children_ids']) and isset($filter_data['permission_children_ids'][0]) and !in_array(-1, (array) $filter_data['permission_children_ids'])) {
$query .= ' AND d.id in (' . $this->getListSQL($filter_data['permission_children_ids'], $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 d.id in (' . $this->getListSQL($filter_data['user_id'], $ph) . ') ';
}
if (isset($filter_data['template_id']) and isset($filter_data['template_id'][0]) and !in_array(-1, (array) $filter_data['template_id'])) {
$query .= ' AND b.id in (' . $this->getListSQL($filter_data['template_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 d.status_id in (' . $this->getListSQL($filter_data['status_id'], $ph) . ') ';
}
if (isset($filter_data['group_id']) and isset($filter_data['group_id'][0]) and !in_array(-1, (array) $filter_data['group_id'])) {
if (isset($filter_data['include_subgroups']) and (bool) $filter_data['include_subgroups'] == TRUE) {
$uglf = new UserGroupListFactory();
$filter_data['group_id'] = $uglf->getByCompanyIdAndGroupIdAndSubGroupsArray($company_id, $filter_data['group_id'], TRUE);
}
$query .= ' AND d.group_id in (' . $this->getListSQL($filter_data['group_id'], $ph) . ') ';
}
if (isset($filter_data['default_branch_id']) and isset($filter_data['default_branch_id'][0]) and !in_array(-1, (array) $filter_data['default_branch_id'])) {
$query .= ' AND d.default_branch_id in (' . $this->getListSQL($filter_data['default_branch_id'], $ph) . ') ';
}
if (isset($filter_data['default_department_id']) and isset($filter_data['default_department_id'][0]) and !in_array(-1, (array) $filter_data['default_department_id'])) {
$query .= ' AND d.default_department_id in (' . $this->getListSQL($filter_data['default_department_id'], $ph) . ') ';
}
if (isset($filter_data['title_id']) and isset($filter_data['title_id'][0]) and !in_array(-1, (array) $filter_data['title_id'])) {
$query .= ' AND d.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') ';
}
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['start_date']);
$query .= ' AND a.start_date >= ?';
}
if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$ph[] = $this->db->BindDate($filter_data['end_date']);
$query .= ' AND a.start_date <= ?';
//.........这里部分代码省略.........