/** * * @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; }