/**
*
* @param int $id
* @return Zend_Db_Table_Rowset
*/
public function listClient($id)
{
$mapperClient = new Client_Model_Mapper_Client();
$selectClient = $mapperClient->selectClient();
$dbGroupContact = App_Model_DbTable_Factory::get('SmsGroupContact');
$selectClient->join(array('gc' => $dbGroupContact), 'gc.fk_id_perdata = c.id_perdata', array())->where('gc.fk_id_sms_group = ?', $id);
return $dbGroupContact->fetchAll($selectClient);
}
/**
*
* @param array $rows
* @return Zend_Db_Table_Rowset
*/
public function getFilteredRows($filters = array())
{
$dbPerData = App_Model_DbTable_Factory::get('PerData');
$dbDistrict = App_Model_DbTable_Factory::get('AddDistrict');
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$select->join(array('ds' => $dbDistrict), 'ds.acronym = c.num_district', array('District'));
$filters = $this->_data;
$date = new Zend_Date();
if (!empty($filters['year'])) {
$select->where('YEAR( c.date_registration ) = ?', $filters['year']);
}
if (!empty($filters['date_start'])) {
$select->where('c.date_registration >= ?', $date->set($filters['date_start'])->toString('yyyy-MM-dd'));
}
if (!empty($filters['date_finish'])) {
$select->where('c.date_registration <= ?', $date->set($filters['date_finish'])->toString('yyyy-MM-dd'));
}
if (!empty($filters['fk_id_dec'])) {
$select->where('c.fk_id_dec = ?', $filters['fk_id_dec']);
}
$rows = $dbPerData->fetchAll($select);
return $rows;
}
/**
*
* @return Zend_Db_Table_Rowset
*/
public function listBeneficiaries()
{
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$dbBusinessPlan = App_Model_DbTable_Factory::get('BusinessPlan');
$select->join(array('bp' => $dbBusinessPlan), 'bp.bussines_plan_developer = c.id_perdata', array())->group(array('c.id_perdata'));
return $dbBusinessPlan->fetchAll($select);
}
/**
*
* @return int
* @throws Exception
*/
protected function _getAge()
{
switch (true) {
case !empty($this->_dataModel['age']):
return $this->_dataModel['age'];
case !empty($this->_dataModel['client']) && is_object($this->_dataModel['client']):
return $this->_dataModel['client']->age;
case !empty($this->_dataModel['client']) && is_array($this->_dataModel['client']):
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$select->where('c.id_perdata IN(?)', (array) $this->_dataModel['client']);
$this->_dataModel['client'] = $this->_dbTable->fetchAll($select);
$ages = array();
foreach ($this->_dataModel['client'] as $client) {
$ages[] = $client->age;
}
return $ages;
case !empty($this->_dataModel['fk_id_perdata']):
$mapperClient = new Client_Model_Mapper_Client();
$this->_dataModel['client'] = $mapperClient->detailClient($this->_dataModel['fk_id_perdata']);
return $this->_dataModel['client']->age;
default:
$message = 'La hetan kliente tinan ba halo validasaun';
$this->_messageModel->addMessage($message, App_Message::ERROR);
throw new Exception($message);
}
}
/**
*
* @param array $filters
* @return Zend_Db_Table_Rowset
*/
public function filterAppliedJob($filters)
{
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$dbShortlist = App_Model_DbTable_Factory::get('ShortlistVacancy');
$dbJobVacancy = App_Model_DbTable_Factory::get('JOBVacancy');
$select->join(array('sv' => $dbShortlist), 'sv.fk_id_perdata = c.id_perdata', array())->join(array('jv' => $dbJobVacancy), 'jv.id_jobvacancy = sv.fk_id_jobvacancy', array());
if (!empty($filters['year'])) {
$select->where('YEAR( jv.registration_date ) = ?', $filters['year']);
}
if (!empty($filters['fk_id_dec'])) {
$select->where('jv.fk_id_dec = ?', $filters['fk_id_dec']);
}
$rows = $dbShortlist->fetchAll($select);
return $rows;
}
/**
*
* @return Zend_Db_Table_Rowset
*/
public function selectGraduated()
{
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$dbStudents = App_Model_DbTable_Factory::get('FEFEPStudentClass_has_PerData');
$dbStudentClass = App_Model_DbTable_Factory::get('FEFPStudentClass');
$dbScholarity = App_Model_DbTable_Factory::get('PerScholarity');
$dbAreaScholarity = App_Model_DbTable_Factory::get('ScholarityArea');
$dbDistrict = App_Model_DbTable_Factory::get('AddDistrict');
$dbEduInstitution = App_Model_DbTable_Factory::get('FefpEduInstitution');
$select->join(array('spd' => $dbStudents), 'spd.fk_id_perdata = c.id_perdata', array())->join(array('sc' => $dbStudentClass), 'spd.fk_id_fefpstudentclass = sc.id_fefpstudentclass', array())->join(array('s' => $dbScholarity), 'sc.fk_id_perscholarity = s.id_perscholarity', array('scholarity'))->join(array('sa' => $dbAreaScholarity), 's.fk_id_scholarity_area = sa.id_scholarity_area', array('scholarity_area'))->join(array('ds' => $dbDistrict), 'ds.acronym = c.num_district', array('District'))->join(array('ei' => $dbEduInstitution), 'ei.id_fefpeduinstitution = sc.fk_id_fefpeduinstitution', array('institution'))->where('spd.status = ?', StudentClass_Model_Mapper_StudentClass::GRADUATED);
$date = new Zend_Date();
$filters = $this->_data;
if (!empty($filters['date_start'])) {
$select->where('sc.real_finish_date >= ?', $date->set($filters['date_start'])->toString('yyyy-MM-dd'));
}
if (!empty($filters['date_finish'])) {
$select->where('sc.real_finish_date <= ?', $date->set($filters['date_finish'])->toString('yyyy-MM-dd'));
}
if (!empty($filters['fk_id_dec'])) {
$select->where('sc.fk_id_dec = ?', $filters['fk_id_dec']);
}
if (!empty($filters['fk_id_fefpeduinstitution'])) {
$select->where('sc.fk_id_fefpeduinstitution = ?', $filters['fk_id_fefpeduinstitution']);
}
if (!empty($filters['fk_id_scholarity_area'])) {
$select->where('s.fk_id_scholarity_area = ?', $filters['fk_id_scholarity_area']);
}
if (!empty($filters['fk_id_perscholarity'])) {
$select->where('sc.fk_id_perscholarity = ?', $filters['fk_id_perscholarity']);
}
return $dbStudentClass->fetchAll($select);
}
/**
*
* @param int $id
* @return Zend_Db_Table_Rowset
*/
public function listQuickList($id)
{
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$dbQuickShortlistPerData = App_Model_DbTable_Factory::get('QuickShortlist_has_PerData');
$select->join(array('qlp' => $dbQuickShortlistPerData), 'qlp.fk_id_perdata = c.id_perdata', array())->where('qlp.fk_id_quick_shortlist = ?', $id)->group('id_perdata')->order(array('first_name', 'last_name'));
return $dbQuickShortlistPerData->fetchAll($select);
}
/**
*
* @param int|null $id
* @return array|Zend_Db_Table_Rowset
*/
public function listClient($id)
{
$mapperClient = new Client_Model_Mapper_Client();
$selectClient = $mapperClient->selectClient();
if (empty($id)) {
if (empty($this->_session->client_list)) {
return array();
}
$selectClient->where('c.id_perdata IN(?)', $this->_session->client_list);
} else {
$dbJobListPerdata = App_Model_DbTable_Factory::get('JOBListPrintHasPerdata');
$selectClient->join(array('jlp' => $dbJobListPerdata), 'jlp.fk_id_perdata = c.id_perdata', array('printed'))->where('jlp.fk_id_joblistprint = ?', $id);
}
return $this->_dbTable->fetchAll($selectClient);
}
/**
*
* @param int $case
* @param int $jobTraining
* @return Zend_Db_Table_Rowset
*/
public function listClientJobTraining($case, $jobTraining)
{
$mapperClient = new Client_Model_Mapper_Client();
$selectClient = $mapperClient->selectClient();
$dbActionPlan = App_Model_DbTable_Factory::get('Action_Plan');
$dbActionPlanHasGroup = App_Model_DbTable_Factory::get('Action_Plan_has_Group');
$dbActionPlanReferences = App_Model_DbTable_Factory::get('Action_Plan_References');
$selectClient->join(array('ap1' => $dbActionPlan), 'ap1.fk_id_perdata = c.id_perdata', array('id_action_plan'))->join(array('apg' => $dbActionPlanHasGroup), 'apg.fk_id_action_plan = ap1.id_action_plan', array())->joinLeft(array('apr' => $dbActionPlanReferences), 'apr.fk_id_action_plan = ap1.id_action_plan
AND apr.fk_id_jobtraining = ' . $jobTraining, array('id_references'))->where('apg.fk_id_action_plan_group = ?', $case)->where('ap1.active != ?', 2)->where('ap1.type = ?', 'G')->group(array('ap1.id_action_plan'));
return $dbActionPlanHasGroup->fetchAll($selectClient);
}
/**
*
* @param int $id
* @return Zend_Db_Table_Rowset
*/
public function listBeneficiaries($id)
{
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$dbStudentClassPerData = App_Model_DbTable_Factory::get('FEFEPStudentClass_has_PerData');
$dbHandicapped = App_Model_DbTable_Factory::get('Handicapped');
$dbFPBeneficiary = App_Model_DbTable_Factory::get('FPBeneficiary');
$dbFPContract = App_Model_DbTable_Factory::get('FP_Contract');
$select->join(array('sc' => $dbStudentClassPerData), 'sc.fk_id_perdata = c.id_perdata', array('status_class' => 'status', 'date_drop_out'))->join(array('fb' => $dbFPBeneficiary), 'fb.fk_id_perdata = c.id_perdata', array('amount'))->join(array('fc' => $dbFPContract), 'fc.id_fp_contract = fb.fk_id_fp_contract
AND sc.fk_id_fefpstudentclass = fc.fk_id_fefpstudentclass', array())->joinLeft(array('hc' => $dbHandicapped), 'sc.fk_id_perdata = hc.fk_id_perdata', array('id_handicapped'))->where('fc.id_fp_contract = ?', $id)->group('id_perdata')->order(array('first_name', 'last_name'));
return $dbStudentClassPerData->fetchAll($select);
}
/**
*
* @return Zend_Db_Table_Rowset
*/
public function _listAppointmentsExpired()
{
$mapperClient = new Client_Model_Mapper_Client();
$selectClient = $mapperClient->selectClient();
$dbActionPlan = App_Model_DbTable_Factory::get('Action_Plan');
$dbDec = App_Model_DbTable_Factory::get('Dec');
$dbUser = App_Model_DbTable_Factory::get('SysUser');
$dbAppointment = App_Model_DbTable_Factory::get('Appointment');
$selectClient->join(array('apl' => $dbActionPlan), 'apl.fk_id_perdata = c.id_perdata', array('id_action_plan', 'fk_id_counselor'))->join(array('ce' => $dbDec), 'ce.id_dec = apl.fk_id_dec', array('ceop_case' => 'name_dec'))->join(array('co' => $dbUser), 'apl.fk_id_counselor = co.id_sysuser', array('counselor' => 'name'))->join(array('app' => $dbAppointment), 'app.fk_id_action_plan = apl.id_action_plan', array('date_appointment'))->where('apl.active = ?', 1)->where('app.appointment_filled = ?', 0)->where('DATE(app.date_appointment) < ?', Zend_Date::now()->toString('yyyy-MM-dd'));
return $dbActionPlan->fetchAll($selectClient);
}
/**
*
* @param int $id
* @return Zend_Db_Table_Rowset
*/
public function listCandidate($id)
{
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$dbCandidates = App_Model_DbTable_Factory::get('JOBVacancy_Candidates');
$select->join(array('lc' => $dbCandidates), 'lc.fk_id_perdata = c.id_perdata', array('shortlisted'))->where('lc.fk_id_jobvacancy = ?', $id)->group('id_perdata')->order(array('first_name', 'last_name'));
return $dbCandidates->fetchAll($select);
}
/**
*
* @return Zend_Db_Table_Rowset
*/
public function listBeneficiaries()
{
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$dbFeContract = App_Model_DbTable_Factory::get('FEContract');
$select->join(array('fec' => $dbFeContract), 'fec.fk_id_perdata = c.id_perdata', array())->group(array('id_perdata'));
return $dbFeContract->fetchAll($select);
}
/**
*
* @access public
* @return boolean
*/
public function loginExternal()
{
$valid = false;
try {
$auth = Zend_Auth::getInstance();
$auth->getStorage()->clear();
unset($this->_session->client);
$mapperClient = new Client_Model_Mapper_Client();
$selectClient = $mapperClient->selectClient();
$selectClient->where('CONCAT( c.num_district, "-", c.num_subdistrict, "-", c.num_servicecode, "-", c.num_year, "-", c.num_sequence) = ?', $this->_data['evidence_card'])->where('DATE_FORMAT( c.birth_date, "%d/%m/%Y" ) = ?', $this->_data['birth_date'])->where('c.active = ?', 1);
$dbPerData = App_Model_DbTable_Factory::get('PerData');
$client = $dbPerData->fetchRow($selectClient);
// Check is there is client with evidence card and date birth
if (!empty($client)) {
$userBusinessMapper = new Admin_Model_Mapper_UserBusiness();
$userCeop = $userBusinessMapper->searchUserCeop($client->fk_id_dec);
// Check if there is user defined for its ceop.
if (!empty($userCeop)) {
$auth->getStorage()->write($this->detailUser($userCeop->id_sysuser));
$this->_session->client = $client;
$valid = true;
}
}
return $valid;
} catch (Exception $e) {
return $valid;
}
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$client = new Client_Model_Mapper_Client();
$select = $client->selectClient();
$dbRegistrationForm = App_Model_DbTable_Factory::get('FERegistration');
$dbRegistrationEntity = App_Model_DbTable_Factory::get('FERegistrationEntity');
$dbRegistrationFormation = App_Model_DbTable_Factory::get('FERegistrationFormation');
$dbFEContract = App_Model_DbTable_Factory::get('FEContract');
$select->join(array('fer' => $dbRegistrationForm), 'fer.fk_id_perdata = c.id_perdata')->joinLeft(array('fee' => $dbRegistrationEntity), 'fee.fk_id_fe_registration = fer.id_fe_registration', array())->joinLeft(array('fef' => $dbRegistrationFormation), 'fef.fk_id_fe_registration = fer.id_fe_registration', array())->joinLeft(array('fec' => $dbFEContract), 'fec.fk_id_fe_registration = fer.id_fe_registration', array('id_fe_contract'))->group(array('id_fe_registration'));
return $select;
}
/**
*
* @param int $id
* @return Zend_Db_Table_Rowset
*/
public function listClientJobTraining($id)
{
$mapperClient = new Client_Model_Mapper_Client();
$select = $mapperClient->selectClient();
$dbJobTrainingTrainee = App_Model_DbTable_Factory::get('JOBTraining_Trainee');
$select->join(array('jtt' => $dbJobTrainingTrainee), 'jtt.fk_id_perdata = c.id_perdata', array('*', 'date_start_formated' => new Zend_Db_Expr('DATE_FORMAT( jtt.date_start, "%d/%m/%Y" )'), 'date_finish_formated' => new Zend_Db_Expr('DATE_FORMAT( jtt.date_finish, "%d/%m/%Y" )')))->where('jtt.fk_id_jobtraining = ?', $id)->group('id_perdata')->order(array('first_name', 'last_name'));
return $dbJobTrainingTrainee->fetchAll($select);
}
