/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbPCEContract = App_Model_DbTable_Factory::get('PCEContract');
$dbStudentClass = App_Model_DbTable_Factory::get('FEFPStudentClass');
$dbIsicDivision = App_Model_DbTable_Factory::get('ISICDivision');
$dbISICClassTimor = App_Model_DbTable_Factory::get('ISICClassTimor');
$dbScholarity = App_Model_DbTable_Factory::get('PerScholarity');
$dbAddAddress = App_Model_DbTable_Factory::get('AddAddress');
$dbAddDistrict = App_Model_DbTable_Factory::get('AddDistrict');
$dbAddSubDistrict = App_Model_DbTable_Factory::get('AddSubDistrict');
$select->join(array('pcc' => $dbPCEContract), 'pcc.fk_id_fefop_contract = c.id_fefop_contract')->join(array('sc' => $dbStudentClass), 'pcc.fk_id_fefpstudentclass = sc.id_fefpstudentclass', array('class_name', 'date_start' => new Zend_Db_Expr('sc.start_date'), 'date_finish' => new Zend_Db_Expr('sc.schedule_finish_date'), 'duration' => new Zend_Db_Expr('DATEDIFF(sc.schedule_finish_date, sc.start_date)')))->join(array('scc' => $dbScholarity), 'scc.id_perscholarity = sc.fk_id_perscholarity', array('scholarity', 'external_code'))->joinLeft(array('add' => $dbAddAddress), 'add.fk_id_fefpeduinstitution = sc.fk_id_fefpeduinstitution', array())->joinLeft(array('addd' => $dbAddDistrict), 'addd.id_adddistrict = add.fk_id_adddistrict', array('district_course' => 'District'))->joinLeft(array('adsd' => $dbAddSubDistrict), 'adsd.id_addsubdistrict = add.fk_id_addsubdistrict', array('sub_district'))->join(array('id' => $dbIsicDivision), 'pcc.fk_id_isicdivision = id.id_isicdivision', array('name_disivion'))->join(array('ct' => $dbISICClassTimor), 'pcc.fk_id_isicclasstimor = ct.id_isicclasstimor', array('name_classtimor'))->group(array('id_pce_contract'));
return $select;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbRiContract = App_Model_DbTable_Factory::get('RIContract');
$dbInstitute = App_Model_DbTable_Factory::get('FefpEduInstitution');
$dbSubDistrict = App_Model_DbTable_Factory::get('AddSubDistrict');
$dbBudgetContract = App_Model_DbTable_Factory::get('BudgetCategoryHasFEFOPContract');
$select->join(array('ric' => $dbRiContract), 'ric.fk_id_fefop_contract = c.id_fefop_contract')->join(array('ei' => $dbInstitute), 'ei.id_fefpeduinstitution = ric.fk_id_fefpeduinstitution', array('institute' => 'institution'))->join(array('sd' => $dbSubDistrict), 'ric.fk_id_addsubdistrict = sd.id_addsubdistrict', array('sub_district'))->joinLeft(array('bcc' => $dbBudgetContract), 'bcc.fk_id_fefop_contract = c.id_fefop_contract', array())->group(array('ric.id_ri_contract'));
return $select;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbPERContract = App_Model_DbTable_Factory::get('PerContract');
$dbPERArea = App_Model_DbTable_Factory::get('PerArea');
$dbSubDistrict = App_Model_DbTable_Factory::get('AddSubDistrict');
$dbAddSucu = App_Model_DbTable_Factory::get('AddSucu');
$dbEnterprise = App_Model_DbTable_Factory::get('FEFPEnterprise');
$dbFEFOPModules = App_Model_DbTable_Factory::get('FEFOPModules');
$select->join(array('pec' => $dbPERContract), 'pec.fk_id_fefop_contract = c.id_fefop_contract')->join(array('e' => $dbEnterprise), 'e.id_fefpenterprise = pec.fk_id_fefpenterprise', array('enterprise' => 'enterprise_name'))->join(array('sd' => $dbSubDistrict), 'pec.fk_id_addsubdistrict = sd.id_addsubdistrict', array('sub_district'))->join(array('sk' => $dbAddSucu), 'pec.fk_id_addsucu = sk.id_addsucu', array('sucu'))->join(array('pa' => $dbPERArea), 'pec.fk_id_per_area = pa.id_per_area', array('area' => 'description'))->join(array('fm' => $dbFEFOPModules), 'pec.fk_id_fefop_modules = fm.id_fefop_modules', array('module' => 'acronym'))->group(array('pec.id_per_contract'));
return $select;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbFpContract = App_Model_DbTable_Factory::get('FPContract');
$dbInstitute = App_Model_DbTable_Factory::get('FefpEduInstitution');
$dbFPAnnualPlanning = App_Model_DbTable_Factory::get('FPAnnualPlanning');
$dbFPPlanningCourse = App_Model_DbTable_Factory::get('FPPlanningCourse');
$dbStudentClass = App_Model_DbTable_Factory::get('FEFPStudentClass');
$dbUnitCost = App_Model_DbTable_Factory::get('UnitCost');
$dbScholarity = App_Model_DbTable_Factory::get('PerScholarity');
$dbLevelScholarity = App_Model_DbTable_Factory::get('PerLevelScholarity');
$select->join(array('fpc' => $dbFpContract), 'fpc.fk_id_fefop_contract = c.id_fefop_contract')->join(array('ap' => $dbFPAnnualPlanning), 'ap.id_annual_planning = fpc.fk_id_annual_planning')->join(array('pc' => $dbFPPlanningCourse), 'pc.id_planning_course = fpc.fk_id_planning_course')->join(array('ei' => $dbInstitute), 'ei.id_fefpeduinstitution = ap.fk_id_fefpeduinstitution', array('institute' => 'institution'))->join(array('sc' => $dbStudentClass), 'fpc.fk_id_fefpstudentclass = sc.id_fefpstudentclass', array('class_name'))->join(array('sl' => $dbScholarity), 'pc.fk_id_perscholarity = sl.id_perscholarity', array('scholarity', 'external_code'))->joinLeft(array('ls' => $dbLevelScholarity), 'ls.id_perlevelscholarity = sl.fk_id_perlevelscholarity', array('level_scholarity'))->join(array('uc' => $dbUnitCost), 'uc.id_unit_cost = fpc.fk_id_unit_cost', array('unit_cost' => 'cost'));
return $select;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbFeContract = App_Model_DbTable_Factory::get('FEContract');
$dbInstitute = App_Model_DbTable_Factory::get('FefpEduInstitution');
$dbEnterprise = App_Model_DbTable_Factory::get('FEFPEnterprise');
$dbClient = App_Model_DbTable_Factory::get('PerData');
$dbSubDistrict = App_Model_DbTable_Factory::get('AddSubDistrict');
$dbBudgetContract = App_Model_DbTable_Factory::get('BudgetCategoryHasFEFOPContract');
$dbScholarityArea = App_Model_DbTable_Factory::get('ScholarityArea');
$dbOcupation = App_Model_DbTable_Factory::get('PROFOcupationTimor');
$select->join(array('fec' => $dbFeContract), 'fec.fk_id_fefop_contract = c.id_fefop_contract')->joinLeft(array('ei' => $dbInstitute), 'ei.id_fefpeduinstitution = fec.fk_id_fefpeduinstitution', array())->joinLeft(array('et' => $dbEnterprise), 'et.id_fefpenterprise = fec.fk_id_fefpenterprise', array('entity' => new Zend_Db_Expr('IFNULL(enterprise_name, institution)')))->join(array('sd' => $dbSubDistrict), 'fec.fk_id_addsubdistrict = sd.id_addsubdistrict', array('sub_district'))->join(array('sa' => $dbScholarityArea), 'fec.fk_id_scholarity_area = sa.id_scholarity_area', array('scholarity_area'))->join(array('po' => $dbOcupation), 'fec.fk_id_profocupationtimor = po.id_profocupationtimor', array('ocupation_name_timor'))->join(array('cl' => $dbClient), 'cl.id_perdata = fec.fk_id_perdata', array('beneficiary' => new Zend_Db_Expr('CONVERT(CONCAT( cl.first_name, " ", IFNULL(cl.medium_name, ""), " ", cl.last_name ) USING utf8 ) COLLATE utf8_unicode_ci')))->join(array('bcc' => $dbBudgetContract), 'bcc.fk_id_fefop_contract = c.id_fefop_contract', array('contract_amount' => 'amount'))->group(array('fec.id_fe_contract'));
return $select;
}
/**
*
* @return Zend_Db_Select
*/
public function getSelect()
{
$mapperContract = new Fefop_Model_Mapper_Contract();
$select = $mapperContract->getSelect();
$dbDRHContract = App_Model_DbTable_Factory::get('DRHContract');
$dbDRHTrainingPlan = App_Model_DbTable_Factory::get('DRHTrainingPlan');
$dbInstitution = App_Model_DbTable_Factory::get('FefpEduInstitution');
$dbScholarityArea = App_Model_DbTable_Factory::get('ScholarityArea');
$dbOcupationTimor = App_Model_DbTable_Factory::get('PROFOcupationTimor');
$dbAddCountry = App_Model_DbTable_Factory::get('AddCountry');
$dbDRHBeneficiary = App_Model_DbTable_Factory::get('DRHBeneficiary');
$dbStaff = App_Model_DbTable_Factory::get('Staff');
$dbPerData = App_Model_DbTable_Factory::get('PerData');
$select->join(array('drhc' => $dbDRHContract), 'drhc.fk_id_fefop_contract = c.id_fefop_contract')->join(array('dhtp' => $dbDRHTrainingPlan), 'dhtp.id_drh_trainingplan = drhc.fk_id_drh_trainingplan', array('modality', 'city'))->join(array('ei' => $dbInstitution), 'ei.id_fefpeduinstitution = dhtp.fk_id_fefpeduinstitution', array('entity' => 'institution'))->join(array('sa' => $dbScholarityArea), 'sa.id_scholarity_area = dhtp.fk_id_scholarity_area', array('scholarity_area'))->join(array('ot' => $dbOcupationTimor), 'ot.id_profocupationtimor = dhtp.fk_id_profocupationtimor', array('ocupation_name_timor'))->join(array('ac' => $dbAddCountry), 'ac.id_addcountry = dhtp.fk_id_addcountry', array('country'))->join(array('db' => $dbDRHBeneficiary), 'db.id_drh_beneficiary = drhc.fk_id_drh_beneficiary', array('handicapped', 'gender'))->join(array('s' => $dbStaff), 's.id_staff = db.fk_id_staff', array('id_staff', 'fk_id_perdata'))->join(array('eis' => $dbInstitution), 's.fk_id_fefpeduinstitution = eis.id_fefpeduinstitution', array('institution', 'responsible' => 'id_fefpeduinstitution'))->join(array('cli' => $dbPerData), 's.fk_id_perdata = cli.id_perdata', array('staff_name' => new Zend_Db_Expr("CONCAT( cli.first_name, ' ', IFNULL(cli.medium_name, ''), ' ', cli.last_name )")));
return $select;
}
/**
*
* @return array
*/
public function calcTotals()
{
// Total preview to the contracts
$mapperContract = new Fefop_Model_Mapper_Contract();
$selectContract = $mapperContract->getSelect();
$statusContract = array(Fefop_Model_Mapper_Status::INITIAL, Fefop_Model_Mapper_Status::PROGRESS, Fefop_Model_Mapper_Status::REVIEWED, Fefop_Model_Mapper_Status::SEMI);
$selectContract->where('cs.id_fefop_status IN(?)', $statusContract);
$selectTotal = $this->_dbTable->select()->setIntegrityCheck(false)->from(array('t' => new Zend_Db_Expr('(' . $selectContract . ')')), array('total' => new Zend_Db_Expr('SUM(t.total)')));
// Current total
$dbTransaction = App_Model_DbTable_Factory::get('FEFOPTransaction');
$dbTransactionStatus = App_Model_DbTable_Factory::get('FEFOPTransactionStatus');
$dbTransactionHistory = App_Model_DbTable_Factory::get('FEFOPTransactionHistory');
$selectStatus = $dbTransactionStatus->select()->from(array('th' => $dbTransactionHistory), array('fk_id_fefop_transaction'))->setIntegrityCheck(false)->join(array('ts' => $dbTransactionStatus), 'ts.id_fefop_transaction_status = th.fk_id_fefop_transaction_status')->where('th.status = ?', 1)->order('id_fefop_transaction_history DESC');
$selectCurrentTotal = $dbTransaction->select()->setIntegrityCheck(false)->from(array('t' => $dbTransaction), array('total' => new Zend_Db_Expr('SUM( t.amount * IF(t.operation = "D", -1, 1) )')))->join(array('ts' => new Zend_Db_Expr('(' . $selectStatus . ')')), 't.id_fefop_transaction = ts.fk_id_fefop_transaction', array('id_fefop_transaction_status'))->where('ts.id_fefop_transaction_status <> ?', self::INACTIVE);
$selectTotalPayment = clone $selectCurrentTotal;
$selectTotalPayment->where('t.fk_id_fefop_type_transaction = ?', self::TYPE_CONTRACT);
$selectTotalReimbursement = clone $selectCurrentTotal;
$selectTotalReimbursement->where('t.fk_id_fefop_type_transaction = ?', self::TYPE_REIMBURSEMENT);
$totalContract = (double) $this->_dbTable->fetchRow($selectTotal)->total;
$totalPayment = (double) $this->_dbTable->fetchRow($selectTotalPayment)->total;
$totalReimbursement = (double) $this->_dbTable->fetchRow($selectTotalReimbursement)->total;
$totals = array('total_contract' => $totalContract, 'total_payments' => abs($totalPayment), 'total_reimbursement' => abs($totalReimbursement), 'total_real' => abs($totalPayment) - $totalReimbursement);
return $totals;
}
