function getByCompanyIdAndUserIdAndStartDateAndEndDate($company_id, $user_id, $start_date = NULL, $end_date = NULL, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($user_id == '') {
return FALSE;
}
if ($company_id == '') {
return FALSE;
}
/*
if ( $start_date == '') {
return FALSE;
}
if ( $end_date == '') {
return FALSE;
}
*/
$strict_order = TRUE;
if ($order == NULL) {
$order = array('a.effective_date' => 'desc', 'a.status_id' => 'asc', 'b.last_name' => 'asc');
$strict_order = FALSE;
}
$ulf = new UserListFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*
from ' . $this->getTable() . ' as a,
' . $ulf->getTable() . ' as b
where a.user_id = b.id
AND b.company_id = ?
';
if ($user_id != '' and isset($user_id[0]) and !in_array(-1, (array) $user_id)) {
$query .= ' AND a.user_id in (' . $this->getListSQL($user_id, $ph) . ') ';
}
if ($start_date != '') {
$ph[] = $start_date;
$ph[] = $end_date;
$query .= ' AND a.effective_date >= ? AND a.effective_date <= ? ';
}
$query .= ' AND a.deleted = 0';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict_order);
if ($limit == NULL) {
$this->rs = $this->db->Execute($query, $ph);
} else {
$this->rs = $this->db->PageExecute($query, $limit, $page, $ph);
}
return $this;
}
function getReportByStartDateAndEndDateAndUserIdList($start_date, $end_date, $user_ids, $order = NULL)
{
if ($user_ids == '') {
return FALSE;
}
if ($start_date == '') {
return FALSE;
}
if ($end_date == '') {
return FALSE;
}
//$order = array( 'b.pay_period_id' => 'asc', 'b.user_id' => 'asc' );
$order = array('z.last_name' => 'asc');
/*
if ( $order == NULL ) {
$order = array( 'b.pay_period_id' => 'asc', 'b.user_id' => 'asc' );
$strict = FALSE;
} else {
$strict = TRUE;
}
*/
$ulf = new UserListFactory();
$udf = new UserDateFactory();
$uwf = new UserWageFactory();
$otpf = new OverTimePolicyFactory();
$apf = new AbsencePolicyFactory();
$ppf = new PremiumPolicyFactory();
$ph = array('start_date' => $this->db->BindDate($start_date), 'end_date' => $this->db->BindDate($end_date));
//Make it so employees with 0 hours still show up!! Very important!
//Order dock hours first, so it can be deducted from regular time.
$query = '
select z.id, tmp.*
from ' . $ulf->getTable() . ' as z
LEFT JOIN
( select b.user_id,
b.date_stamp as date_stamp,
a.status_id as status_id,
a.type_id as type_id,
a.over_time_policy_id as over_time_policy_id,
n.id as over_time_policy_wage_id,
n.effective_date as over_time_policy_wage_effective_date,
a.absence_policy_id as absence_policy_id,
p.id as absence_policy_wage_id,
p.effective_date as absence_policy_wage_effective_date,
a.premium_policy_id as premium_policy_id,
r.id as premium_policy_wage_id,
r.effective_date as premium_policy_wage_effective_date,
z.id as user_wage_id,
z.effective_date as user_wage_effective_date,
sum(total_Time) as total_time,
sum(actual_total_Time) as actual_total_time
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $udf->getTable() . ' as b ON a.user_date_id = b.id
LEFT JOIN ' . $otpf->getTable() . ' as m ON a.over_time_policy_id = m.id
LEFT JOIN ' . $uwf->getTable() . ' as n ON n.id = (select n.id
from ' . $uwf->getTable() . ' as n
where n.user_id = b.user_id
and n.wage_group_id = m.wage_group_id
and n.effective_date <= b.date_stamp
and n.deleted = 0
order by n.effective_date desc limit 1)
LEFT JOIN ' . $apf->getTable() . ' as o ON a.absence_policy_id = o.id
LEFT JOIN ' . $uwf->getTable() . ' as p ON p.id = (select p.id
from ' . $uwf->getTable() . ' as p
where p.user_id = b.user_id
and p.wage_group_id = o.wage_group_id
and p.effective_date <= b.date_stamp
and p.deleted = 0
order by p.effective_date desc limit 1)
LEFT JOIN ' . $ppf->getTable() . ' as q ON a.premium_policy_id = q.id
LEFT JOIN ' . $uwf->getTable() . ' as r ON r.id = (select r.id
from ' . $uwf->getTable() . ' as r
where r.user_id = b.user_id
and r.wage_group_id = q.wage_group_id
and r.effective_date <= b.date_stamp
and r.deleted = 0
order by r.effective_date desc limit 1)
LEFT JOIN ' . $uwf->getTable() . ' as z ON z.id = (select z.id
from ' . $uwf->getTable() . ' as z
where z.user_id = b.user_id
and z.effective_date <= b.date_stamp
and z.deleted = 0
order by z.effective_date desc limit 1)
where
b.date_stamp >= ?
AND b.date_stamp <= ?
AND b.user_id in (' . $this->getListSQL($user_ids, $ph) . ')
AND a.status_id in (10,30)
AND ( a.deleted = 0 AND b.deleted = 0)
group by b.user_id, b.date_stamp, user_wage_id, user_wage_effective_date, over_time_policy_wage_id, over_time_policy_wage_effective_date, absence_policy_wage_id, absence_policy_wage_effective_date, premium_policy_wage_id, premium_policy_wage_effective_date, a.status_id, a.type_id, a.over_time_policy_id, a.absence_policy_id, a.premium_policy_id
) as tmp ON z.id = tmp.user_id
WHERE z.id in (' . $this->getListSQL($user_ids, $ph) . ')
AND z.deleted = 0
';
$query .= $this->getSortSQL($order, FALSE);
$this->ExecuteSQL($query, $ph);
return $this;
}
function getByCompanyIdAndPayPeriodId($company_id, $pay_period_id, $limit = NULL, $page = NULL, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
if ($pay_period_id == '') {
return FALSE;
}
$strict_order = TRUE;
if ($order == NULL or !is_array($order)) {
$order = array('a.transaction_date' => 'desc', 'a.advance' => 'asc', 'b.last_name' => 'asc');
$strict_order = FALSE;
}
$ulf = new UserListFactory();
$pplf = new PayPeriodListFactory();
$ph = array('company_id' => $company_id);
$query = '
select a.*
from ' . $this->getTable() . ' as a,
' . $ulf->getTable() . ' as b,
' . $pplf->getTable() . ' as c
where a.user_id = b.id
AND a.pay_period_id = c.id
AND b.company_id = ?
AND a.pay_period_id in (' . $this->getListSQL($pay_period_id, $ph) . ')
AND a.deleted = 0';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict_order);
if ($limit == NULL) {
$this->rs = $this->db->Execute($query, $ph);
} else {
$this->rs = $this->db->PageExecute($query, $limit, $page, $ph);
}
//$this->rs = $this->db->Execute($query, $ph);
return $this;
}
