/**
* Returns an array with all activities associated to projects, grouped by project
* or only from the project id passed by parameter
* <br/>
* <p>Params:</p>
* project_id
* status =0,1,2,3,4
*/
public function getActivitiesByProject($appaccount_id, $params = null)
{
$db = $this->getDefaultAdapter();
$sql = $db->select()->from(array('proj' => 'project'), array('project_id' => 'id', 'project_name' => 'name', 'project_status' => 'status'))->join(array('act' => 'atividade_assistencia'), 'act.project_id = proj.id', array('assistance_id' => 'id', 'assistance_date', 'assistance_time', 'id_by_finger_key'))->join(array('task' => 'task_type'), 'act.task_type_id = task.id', array('task_id' => 'id', 'task_name' => 'name'))->join(array('p' => 'person'), 'act.person_helped_id = p.id', array('person_id' => 'id', 'person_name' => 'name'))->joinLeft(array('task_parent' => 'task_type'), 'task.parent_id = task_parent.id', array('task_parent_id' => 'id', 'task_parent_name' => 'name'))->order(array('proj.name', 'task_parent.name', 'task.name', 'act.assistance_date', 'p.name'));
$sql->where('proj.appaccount_id = ?', $appaccount_id);
/**
* TRATAMENTO DOS PARAMETROS
* */
$projectId = isset($params['project_id']) ? $params['project_id'] : -1;
if ((int) $projectId > 0) {
$sql->where('project_id = ?', $projectId);
}
$status = isset($params['status']) ? $params['status'] : -1;
if ((int) $status > 0) {
$sql->where('status = ?', $status);
}
$dateFrom = isset($params['date_from']) ? $params['date_from'] : '';
$dateTo = isset($params['date_to']) ? $params['date_to'] : '';
if (trim($dateFrom) !== '') {
if (Agana_Util_DateTime::validate($dateFrom)) {
$sql->where('assistance_date >= ?', $dateFrom);
} else {
}
}
if (trim($dateTo) !== '') {
if (Agana_Util_DateTime::validate($dateTo)) {
$sql->where('assistance_date <= ?', $dateTo);
}
}
$db->setFetchMode(Zend_DB::FETCH_ASSOC);
$r = $db->fetchAll($sql);
return $r;
}
/**
* Returns an array with all persons associated to projects, grouped by project
* or only from the project id passed by parameter
* <br/>
* <p>Params:</p>
* project_id
* status =0,1,2,3,4
*/
public function getPersonsByProject($appaccount_id, $params = null)
{
$db = $this->getDefaultAdapter();
$sql = $db->select()->from(array('proj' => 'project'), array('project_name' => 'name', 'project_startdatereal' => 'startdatereal', 'project_finishdatereal' => 'finishdatereal', 'project_status' => 'status'))->joinLeft(array('phd' => 'person_helped_project'), 'proj.id = phd.project_id')->joinLeft(array('p' => 'person'), 'phd.person_id = p.id', array('person_name' => 'name'))->order(array('proj.name', 'p.name'));
$sql->where('proj.appaccount_id = ?', $appaccount_id);
/**
* TRATAMENTO DOS PARAMETROS
* */
$projectId = isset($params['project_id']) ? $params['project_id'] : -1;
if ((int) $projectId > 0) {
$sql->where('project_id = ?', $projectId);
}
$status = isset($params['status']) ? $params['status'] : -1;
if ((int) $status > 0) {
$sql->where('status = ?', $status);
}
$dateInStart = isset($params['date_in_start']) ? $params['date_in_start'] : '';
$dateInEnd = isset($params['date_in_end']) ? $params['date_in_end'] : '';
if (trim($dateInStart) !== '') {
$test_arr = explode('/', $dateInStart);
if (Agana_Util_DateTime::validate($dateInStart)) {
$sql->where('date_in >= ?', $dateInStart);
}
}
if (trim($dateInEnd) !== '') {
$test_arr = explode('/', $dateInEnd);
if (Agana_Util_DateTime::validate($dateInStart)) {
$sql->where('date_in <= ?', $dateInEnd);
}
}
$dateOutStart = isset($params['date_out_start']) ? $params['date_out_start'] : '';
$dateOutEnd = isset($params['date_out_end']) ? $params['date_out_end'] : '';
if (trim($dateOutStart) !== '') {
$test_arr = explode('/', $dateOutStart);
if (Agana_Util_DateTime::validate($dateOutStart)) {
$sql->where('date_out >= ?', $dateOutStart);
}
}
if (trim($dateOutEnd) !== '') {
$test_arr = explode('/', $dateOutEnd);
if (Agana_Util_DateTime::validate($dateOutStart)) {
$sql->where('date_out <= ?', $dateOutEnd);
}
}
$db->setFetchMode(Zend_DB::FETCH_ASSOC);
$r = $db->fetchAll($sql);
return $r;
}