function getByCompanyId($company_id, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } $sf = new StationFactory(); $ph = array('company_id' => $company_id); $query = ' select a.* from ' . $this->getTable() . ' as a, ' . $sf->getTable() . ' as b where b.id = a.station_id AND b.company_id = ? '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order); $this->ExecuteSQL($query, $ph); return $this; }
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 from ' . $this->getTable() . ' as tmp2_a LEFT JOIN ' . $pcf->getTable() . ' as tmp2_b ON tmp2_a.punch_control_id = tmp2_b.id LEFT JOIN ' . $udf->getTable() . ' as tmp2_c ON tmp2_b.user_date_id = tmp2_c.id LEFT JOIN ' . $uf->getTable() . ' as tmp2_d ON tmp2_c.user_id = tmp2_d.id WHERE tmp2_d.company_id = ?'; if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') { $ph[] = $this->db->BindDate($filter_data['start_date']); $query .= ' AND tmp2_c.date_stamp >= ?'; } if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') { $ph[] = $this->db->BindDate($filter_data['end_date']); $query .= ' AND tmp2_c.date_stamp <= ?'; } $query .= ' AND tmp2_a.time_stamp is not null AND ( tmp2_a.deleted = 0 AND tmp2_b.deleted = 0 AND tmp2_c.deleted = 0 ) group by tmp2_d.id ) as tmp2 ON c.user_id = tmp2.id AND a.time_stamp = tmp2.max_punch_time_stamp '; if (getTTProductEdition() >= TT_PRODUCT_CORPORATE) { $query .= ' LEFT JOIN ' . $jf->getTable() . ' as x ON b.job_id = x.id'; $query .= ' LEFT JOIN ' . $jif->getTable() . ' as y ON b.job_item_id = y.id'; } $ph[] = $company_id; $query .= ' WHERE tmp2.id IS NOT NULL AND d.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 d.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 d.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 d.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 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['punch_branch_id']) and isset($filter_data['punch_branch_id'][0]) and !in_array(-1, (array) $filter_data['punch_branch_id'])) { $query .= ' AND b.branch_id in (' . $this->getListSQL($filter_data['punch_branch_id'], $ph) . ') '; } if (isset($filter_data['punch_department_id']) and isset($filter_data['punch_department_id'][0]) and !in_array(-1, (array) $filter_data['punch_department_id'])) { $query .= ' AND b.department_id in (' . $this->getListSQL($filter_data['punch_department_id'], $ph) . ') '; } //Use the job_id in the punch_control table so we can filter by '0' or No Job if (isset($filter_data['include_job_id']) and isset($filter_data['include_job_id'][0]) and !in_array(-1, (array) $filter_data['include_job_id'])) { $query .= ' AND b.job_id in (' . $this->getListSQL($filter_data['include_job_id'], $ph) . ') '; } if (isset($filter_data['exclude_job_id']) and isset($filter_data['exclude_job_id'][0]) and !in_array(-1, (array) $filter_data['exclude_job_id'])) { $query .= ' AND b.job_id not in (' . $this->getListSQL($filter_data['exclude_job_id'], $ph) . ') '; } if (isset($filter_data['job_group_id']) and isset($filter_data['job_group_id'][0]) and !in_array(-1, (array) $filter_data['job_group_id'])) { if (isset($filter_data['include_job_subgroups']) and (bool) $filter_data['include_job_subgroups'] == TRUE) { $uglf = new UserGroupListFactory(); $filter_data['job_group_id'] = $uglf->getByCompanyIdAndGroupIdAndjob_subgroupsArray($company_id, $filter_data['job_group_id'], TRUE); } $query .= ' AND x.group_id in (' . $this->getListSQL($filter_data['job_group_id'], $ph) . ') '; } if (isset($filter_data['job_item_id']) and isset($filter_data['job_item_id'][0]) and !in_array(-1, (array) $filter_data['job_item_id'])) { $query .= ' AND b.job_item_id in (' . $this->getListSQL($filter_data['job_item_id'], $ph) . ') '; } if (isset($filter_data['start_date']) and trim($filter_data['start_date']) != '') { /* $ph[] = $this->db->BindDate($filter_data['start_date']); $query .= ' AND c.date_stamp >= ?'; */ $ph[] = $this->db->BindTimeStamp($filter_data['start_date']); $query .= ' AND a.time_stamp >= ?'; } if (isset($filter_data['end_date']) and trim($filter_data['end_date']) != '') { $ph[] = $this->db->BindTimeStamp($filter_data['end_date']); $query .= ' AND a.time_stamp <= ?'; } //The Transfer where clause is an attempt to keep transferred punches from appearing twice. $query .= ' AND ( a.transfer = 0 OR ( a.transfer = 1 AND a.status_id = 10) ) AND ( a.deleted = 0 AND b.deleted =0 AND c.deleted = 0 AND d.deleted = 0 ) '; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict, $additional_order_fields); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function getByCompanyIDAndStationIDAndStatusAndEmployeeNumber($company_id, $station_id, $status_id, $employee_number, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if ($station_id == '') { return FALSE; } if ($status_id == '') { return FALSE; } if ($order == NULL) { $order = array('a.id' => 'asc'); $strict = FALSE; } else { $strict = TRUE; } $sf = new StationFactory(); $sugf = new StationUserGroupFactory(); $sbf = new StationBranchFactory(); $sdf = new StationDepartmentFactory(); $siuf = new StationIncludeUserFactory(); $seuf = new StationExcludeUserFactory(); $uif = new UserIdentificationFactory(); $ph = array('company_id' => $company_id, 'station_id' => $station_id, 'status_id' => $status_id, 'employee_number' => $employee_number); //Also include users with user_identifcation rows that have been *created* after the given date //so the first supervisor/admin enrolled on a timeclock is properly updated to lock the menu. $query = ' select a.* from ' . $this->getTable() . ' as a LEFT JOIN ' . $sf->getTable() . ' as z ON (1=1) where a.company_id = ? AND z.id = ? AND a.status_id = ? AND a.employee_number = ? AND ( ( ( ( ( z.user_group_selection_type_id = 10 OR ( z.user_group_selection_type_id = 20 AND a.group_id in ( select b.group_id from ' . $sugf->getTable() . ' as b WHERE z.id = b.station_id ) ) OR ( z.user_group_selection_type_id = 30 AND a.group_id not in ( select b.group_id from ' . $sugf->getTable() . ' as b WHERE z.id = b.station_id ) ) ) AND ( z.branch_selection_type_id = 10 OR ( z.branch_selection_type_id = 20 AND a.default_branch_id in ( select c.branch_id from ' . $sbf->getTable() . ' as c WHERE z.id = c.station_id ) ) OR ( z.branch_selection_type_id = 30 AND a.default_branch_id not in ( select c.branch_id from ' . $sbf->getTable() . ' as c WHERE z.id = c.station_id ) ) ) AND ( z.department_selection_type_id = 10 OR ( z.department_selection_type_id = 20 AND a.default_department_id in ( select d.department_id from ' . $sdf->getTable() . ' as d WHERE z.id = d.station_id ) ) OR ( z.department_selection_type_id = 30 AND a.default_department_id not in ( select d.department_id from ' . $sdf->getTable() . ' as d WHERE z.id = d.station_id ) ) ) AND a.id not in ( select f.user_id from ' . $seuf->getTable() . ' as f WHERE z.id = f.station_id ) ) OR a.id in ( select e.user_id from ' . $siuf->getTable() . ' as e WHERE z.id = e.station_id ) ) ) ) '; $query .= ' AND ( a.deleted = 0 AND z.deleted = 0 )'; //Debug::Arr($ph, 'Query: '. $query, __FILE__, __LINE__, __METHOD__,10); $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict); $this->ExecuteSQL($query, $ph, $limit, $page); return $this; }
function getByCompanyIDAndStationIDAndStatusAndDate($company_id, $station_id, $status_id, $date = NULL, $limit = NULL, $page = NULL, $where = NULL, $order = NULL) { if ($company_id == '') { return FALSE; } if ($station_id == '') { return FALSE; } if ($status_id == '') { return FALSE; } if ($date == '') { $date = 0; } if ($order == NULL) { $order = array('a.last_name' => 'asc'); $strict = FALSE; } else { $strict = TRUE; } $sf = new StationFactory(); $sugf = new StationUserGroupFactory(); $sbf = new StationBranchFactory(); $sdf = new StationDepartmentFactory(); $siuf = new StationIncludeUserFactory(); $seuf = new StationExcludeUserFactory(); $ph = array('company_id' => $company_id, 'station_id' => $station_id, 'status_id' => $status_id, 'date' => $date, 'date2' => $date); $query = ' select a.* from ' . $this->getTable() . ' as a, ' . $sf->getTable() . ' as z where a.company_id = ? AND z.id = ? AND a.status_id = ? AND ( ( ( z.user_group_selection_type_id = 10 OR ( z.user_group_selection_type_id = 20 AND a.group_id in ( select b.group_id from ' . $sugf->getTable() . ' as b WHERE z.id = b.station_id ) ) OR ( z.user_group_selection_type_id = 30 AND a.group_id not in ( select b.group_id from ' . $sugf->getTable() . ' as b WHERE z.id = b.station_id ) ) ) AND ( z.branch_selection_type_id = 10 OR ( z.branch_selection_type_id = 20 AND a.default_branch_id in ( select c.branch_id from ' . $sbf->getTable() . ' as c WHERE z.id = c.station_id ) ) OR ( z.branch_selection_type_id = 30 AND a.default_branch_id not in ( select c.branch_id from ' . $sbf->getTable() . ' as c WHERE z.id = c.station_id ) ) ) AND ( z.department_selection_type_id = 10 OR ( z.department_selection_type_id = 20 AND a.default_department_id in ( select d.department_id from ' . $sdf->getTable() . ' as d WHERE z.id = d.station_id ) ) OR ( z.department_selection_type_id = 30 AND a.default_department_id not in ( select d.department_id from ' . $sdf->getTable() . ' as d WHERE z.id = d.station_id ) ) ) AND a.id not in ( select f.user_id from ' . $seuf->getTable() . ' as f WHERE z.id = f.station_id ) ) OR a.id in ( select e.user_id from ' . $siuf->getTable() . ' as e WHERE z.id = e.station_id ) ) AND ( a.created_date >= ? OR a.updated_date >= ? ) AND ( a.deleted = 0 AND z.deleted = 0 )'; $query .= $this->getWhereSQL($where); $query .= $this->getSortSQL($order, $strict); //Debug::text('Query: '. $query, __FILE__, __LINE__, __METHOD__, 10); if ($limit == NULL) { $this->rs = $this->db->Execute($query, $ph); } else { $this->rs = $this->db->PageExecute($query, $limit, $page, $ph); } return $this; }
function postSave() { $this->removeCache($this->getId()); if ($this->getDeleted() == TRUE) { Debug::Text('UnAssign Hours from Branch: ' . $this->getId(), __FILE__, __LINE__, __METHOD__, 10); //Unassign hours from this branch. $pcf = new PunchControlFactory(); $udtf = new UserDateTotalFactory(); $uf = new UserFactory(); $sf = new StationFactory(); $sf_b = new ScheduleFactory(); $udf = new UserDefaultFactory(); $rstf = new RecurringScheduleTemplateFactory(); $query = 'update ' . $pcf->getTable() . ' set branch_id = 0 where branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $udtf->getTable() . ' set branch_id = 0 where branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $sf_b->getTable() . ' set branch_id = 0 where branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $uf->getTable() . ' set default_branch_id = 0 where company_id = ' . $this->getCompany() . ' AND default_branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $udf->getTable() . ' set default_branch_id = 0 where company_id = ' . $this->getCompany() . ' AND default_branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $sf->getTable() . ' set branch_id = 0 where company_id = ' . $this->getCompany() . ' AND branch_id = ' . $this->getId(); $this->db->Execute($query); $query = 'update ' . $rstf->getTable() . ' set branch_id = 0 where branch_id = ' . $this->getId(); $this->db->Execute($query); } return TRUE; }