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;
    }
Пример #2
0
    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;
    }
Пример #3
0
    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;
 }