function getPayPeriodsWithPayStubsByCompanyId($id, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($id == '') {
return FALSE;
}
if ($order == NULL) {
$order = array('a.transaction_date' => 'desc');
$strict = FALSE;
} else {
$strict = TRUE;
}
$ph = array('id' => $id);
$psf = new PayStubFactory();
$query = '
select a.*
from ' . $this->getTable() . ' as a
where a.company_id = ?
AND EXISTS ( select z.id from ' . $psf->getTable() . ' as z where z.pay_period_id = a.id AND z.deleted = 0 )
AND a.deleted=0';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order);
if ($limit == NULL) {
$this->rs = $this->db->Execute($query, $ph);
} else {
$this->rs = $this->db->PageExecute($query, $limit, $page, $ph);
}
return $this;
}
function getPayPeriodsWithPayStubsByCompanyId($id, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($id == '') {
return FALSE;
}
if ($order == NULL) {
$order = array('a.transaction_date' => 'desc');
$strict = FALSE;
} else {
$strict = TRUE;
}
$ph = array('id' => $id);
$uf = new UserFactory();
$psf = new PayStubFactory();
//Make sure just one row per pay period is returned.
/*
//This is way too slow on older versions of PGSQL.
$query = '
select a.*
from '. $this->getTable() .' as a
where a.company_id = ?
AND ( a.deleted = 0 )
AND EXISTS ( select id from '. $psf->getTable() .' as b WHERE a.id = b.pay_period_id AND b.deleted = 0)';
*/
$query = ' select distinct a.*
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $psf->getTable() . ' as b on ( a.id = b.pay_period_id )
where a.company_id = ?
AND ( a.deleted = 0 AND b.deleted = 0 )';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order);
$this->ExecuteSQL($query, $ph, $limit, $page);
return $this;
}
function getReportByCompanyIdAndArrayCriteria($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('pay_period_id');
if ($order == NULL) {
$order = array('b.user_id' => 'asc');
$strict = FALSE;
} else {
//Do order by column conversions, because if we include these columns in the SQL
//query, they contaminate the data array.
/*
if ( isset($order['default_branch']) ) {
$order['b.name'] = $order['default_branch'];
unset($order['default_branch']);
}
//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['last_name']) ) {
$order['last_name'] = 'asc';
}
if ( !isset($order['first_name']) ) {
$order['first_name'] = 'asc';
}
*/
$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['currency_ids'])) {
$filter_data['currency_id'] = $filter_data['currency_ids'];
}
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$psf = new PayStubFactory();
$uf = new UserFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
b.user_id as user_id,
b.pay_period_id as pay_period_id,
b.start_date as pay_stub_start_date,
b.end_date as pay_stub_end_date,
b.transaction_date as pay_stub_transaction_date,
b.currency_id as currency_id,
b.currency_rate as currency_rate,
a.pay_stub_entry_name_id as pay_stub_entry_name_id,
a.amount as amount,
a.ytd_amount as ytd_amount
from (
select aa.pay_stub_id as pay_stub_id,
aa.pay_stub_entry_name_id as pay_stub_entry_name_id,
sum(aa.amount) as amount,
max(aa.ytd_amount) as ytd_amount
from ' . $this->getTable() . ' as aa
LEFT JOIN ' . $psf->getTable() . ' as bb ON aa.pay_stub_id = bb.id
LEFT JOIN ' . $uf->getTable() . ' as cc ON bb.user_id = cc.id
LEFT JOIN ' . $bf->getTable() . ' as dd ON cc.default_branch_id = dd.id
LEFT JOIN ' . $df->getTable() . ' as ee ON cc.default_department_id = ee.id
LEFT JOIN ' . $ugf->getTable() . ' as ff ON cc.group_id = ff.id
LEFT JOIN ' . $utf->getTable() . ' as gg ON cc.title_id = gg.id
where cc.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 cc.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 cc.id in (' . $this->getListSQL($filter_data['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 cc.id in (' . $this->getListSQL($filter_data['user_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 cc.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 cc.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 cc.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 cc.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 cc.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 cc.title_id in (' . $this->getListSQL($filter_data['title_id'], $ph) . ') ';
}
if (isset($filter_data['sex_id']) and isset($filter_data['sex_id'][0]) and !in_array(-1, (array) $filter_data['sex_id'])) {
$query .= ' AND cc.sex_id in (' . $this->getListSQL($filter_data['sex_id'], $ph) . ') ';
}
if (isset($filter_data['currency_id']) and isset($filter_data['currency_id'][0]) and !in_array(-1, (array) $filter_data['currency_id'])) {
$query .= ' AND bb.currency_id in (' . $this->getListSQL($filter_data['currency_id'], $ph) . ') ';
}
if (isset($filter_data['pay_period_ids']) and isset($filter_data['pay_period_ids'][0]) and !in_array(-1, (array) $filter_data['pay_period_ids'])) {
$query .= ' AND bb.pay_period_id in (' . $this->getListSQL($filter_data['pay_period_ids'], $ph) . ') ';
}
if (isset($filter_data['transaction_start_date']) and trim($filter_data['transaction_start_date']) != '') {
$ph[] = $this->db->BindTimeStamp(strtolower(trim($filter_data['transaction_start_date'])));
$query .= ' AND bb.transaction_date >= ?';
}
if (isset($filter_data['transaction_end_date']) and trim($filter_data['transaction_end_date']) != '') {
$ph[] = $this->db->BindTimeStamp(strtolower(trim($filter_data['transaction_end_date'])));
$query .= ' AND bb.transaction_date <= ?';
}
if (isset($filter_data['transaction_date']) and trim($filter_data['transaction_date']) != '') {
$ph[] = $this->db->BindTimeStamp(strtolower(trim($filter_data['transaction_date'])));
$query .= ' AND bb.transaction_date = ?';
}
$query .= '
AND (aa.deleted = 0 AND bb.deleted = 0 AND cc.deleted=0)
group by aa.pay_stub_id,aa.pay_stub_entry_name_id
) a
LEFT JOIN ' . $psf->getTable() . ' as b ON a.pay_stub_id = b.id
LEFT JOIN ' . $uf->getTable() . ' as c ON b.user_id = c.id
where 1=1
';
$query .= '
AND (c.deleted=0)
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
if ($limit == NULL) {
$this->rs = $this->db->Execute($query, $ph);
} else {
$this->rs = $this->db->PageExecute($query, $limit, $page, $ph);
}
return $this;
}
function getAPIReportByCompanyIdAndArrayCriteria($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('default_branch', 'default_department', 'group', 'title', 'currency', 'pay_period_transaction_date', 'pay_stub_transaction_date', 'user_id');
if ($order == NULL) {
$order = array('b.user_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);
$ppf = new PayPeriodFactory();
$bf = new BranchFactory();
$df = new DepartmentFactory();
$ugf = new UserGroupFactory();
$utf = new UserTitleFactory();
$psf = new PayStubFactory();
$uf = new UserFactory();
$psaf = new PayStubAmendmentFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*,
b.user_id as user_id,
b.pay_period_id as pay_period_id,
ppf.start_date as pay_period_start_date,
ppf.end_date as pay_period_end_date,
ppf.transaction_date as pay_period_transaction_date,
b.start_date as pay_stub_start_date,
b.end_date as pay_stub_end_date,
b.transaction_date as pay_stub_transaction_date,
b.currency_id as currency_id,
b.currency_rate as currency_rate,
a.pay_stub_entry_name_id as pay_stub_entry_name_id,
a.rate as rate,
a.units as units,
a.amount as amount,
a.ytd_amount as ytd_amount
from (
select aa.pay_stub_id as pay_stub_id,
aa.pay_stub_entry_name_id as pay_stub_entry_name_id,
avg(aa.rate) as rate,
sum(aa.units) as units,
sum(aa.amount) as amount,
max(aa.ytd_amount) as ytd_amount
from ' . $this->getTable() . ' as aa
LEFT JOIN ' . $psaf->getTable() . ' as hh ON aa.pay_stub_amendment_id = hh.id
LEFT JOIN ' . $psf->getTable() . ' as bb ON aa.pay_stub_id = bb.id
LEFT JOIN ' . $uf->getTable() . ' as cc ON bb.user_id = cc.id
LEFT JOIN ' . $bf->getTable() . ' as dd ON cc.default_branch_id = dd.id
LEFT JOIN ' . $df->getTable() . ' as ee ON cc.default_department_id = ee.id
LEFT JOIN ' . $ugf->getTable() . ' as ff ON cc.group_id = ff.id
LEFT JOIN ' . $utf->getTable() . ' as gg ON cc.title_id = gg.id
where cc.company_id = ? ';
$query .= isset($filter_data['permission_children_ids']) ? $this->getWhereClauseSQL('cc.id', $filter_data['permission_children_ids'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['id']) ? $this->getWhereClauseSQL('cc.id', $filter_data['id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['user_id']) ? $this->getWhereClauseSQL('cc.id', $filter_data['user_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['include_user_id']) ? $this->getWhereClauseSQL('cc.id', $filter_data['include_user_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['exclude_user_id']) ? $this->getWhereClauseSQL('cc.id', $filter_data['exclude_user_id'], 'not_numeric_list', $ph) : NULL;
$query .= isset($filter_data['status_id']) ? $this->getWhereClauseSQL('cc.status_id', $filter_data['status_id'], 'numeric_list', $ph) : NULL;
if (isset($filter_data['exclude_ytd_adjustment']) and (bool) $filter_data['exclude_ytd_adjustment'] == TRUE) {
$query .= ' AND ( hh.ytd_adjustment is NULL OR hh.ytd_adjustment = 0 )';
}
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 .= isset($filter_data['group_id']) ? $this->getWhereClauseSQL('cc.group_id', $filter_data['group_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['default_branch_id']) ? $this->getWhereClauseSQL('cc.default_branch_id', $filter_data['default_branch_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['default_department_id']) ? $this->getWhereClauseSQL('cc.default_department_id', $filter_data['default_department_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['title_id']) ? $this->getWhereClauseSQL('cc.title_id', $filter_data['title_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['sex_id']) ? $this->getWhereClauseSQL('cc.sex_id', $filter_data['sex_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['currency_id']) ? $this->getWhereClauseSQL('bb.currency_id', $filter_data['currency_id'], 'numeric_list', $ph) : NULL;
$query .= isset($filter_data['pay_period_id']) ? $this->getWhereClauseSQL('bb.pay_period_id', $filter_data['pay_period_id'], 'numeric_list', $ph) : NULL;
//$query .= ( isset($filter_data['tag']) ) ? $this->getWhereClauseSQL( 'a.id', array( 'company_id' => $company_id, 'object_type_id' => 200, 'tag' => $filter_data['tag'] ), 'tag', $ph ) : NULL;
if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') {
$ph[] = $this->db->BindTimeStamp(strtolower(trim($filter_data['start_date'])));
$query .= ' AND bb.transaction_date >= ?';
}
if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') {
$ph[] = $this->db->BindTimeStamp(strtolower(trim($filter_data['end_date'])));
$query .= ' AND bb.transaction_date <= ?';
}
/*
if ( isset($filter_data['transaction_date']) AND trim($filter_data['transaction_date']) != '' ) {
$ph[] = $this->db->BindTimeStamp( strtolower(trim($filter_data['transaction_date'])) );
$query .= ' AND bb.transaction_date = ?';
}
*/
$query .= '
AND (aa.deleted = 0 AND bb.deleted = 0 AND cc.deleted=0)
group by aa.pay_stub_id,aa.pay_stub_entry_name_id
) a
LEFT JOIN ' . $psf->getTable() . ' as b ON a.pay_stub_id = b.id
LEFT JOIN ' . $uf->getTable() . ' as c ON b.user_id = c.id
LEFT JOIN ' . $ppf->getTable() . ' as ppf ON b.pay_period_id = ppf.id
where 1=1
';
$query .= '
AND (c.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;
}
