File manager

File manager - Edit - /home/wwwroot/camplus.hk/master.camplus.hk/public_html/application/modules/appraisal/models/Report_model.php

Back
<?php Class Report_model extends CI_Model{ function getUsers($id) { $sql = 'SELECT ap_form_section_unit_user.,users. FROM `ap_form_section_unit_user` JOIN users ON users.id = user_id JOIN ap_form_section_unit ON ap_form_section_unit_user.unit_id = ap_form_section_unit.id JOIN ap_form_sections ON ap_form_sections.id = ap_form_section_unit.section WHERE ap_form_sections.ap_form = ' . $id; if(!$this->acl->hasPermission('VIEW_ALL_AP_REPORTA') && !$this->acl->hasPermission('CHECK_APPRAISAL_STATUS')) { $related_units = $this->getRelatedUnits(); $this->db->select('group_concat(DISTINCT(appraisee)) as users'); $oa_users = $this->db->get_where('ap_oa',array('oa'=>$this->session->userdata('id'),'ap_form'=>$id))->row_array(); // print_r($oa_users);exit; if(empty($related_units) && count($oa_users['users'])==0 ) show_error($this->lang->line('no_permission_error')); else { if(!empty($related_units)) $sql .= ' AND (ap_form_section_unit.id IN (' . $related_units .') AND ap_form_section_unit_user.user_id != "' . $this->session->userdata('id') . '" AND ap_form_section_unit_user.type = "MEMBER" '; if(!empty($oa_users['users'])) { if(!empty($related_units)) { $sql.= ' OR '; } else { $sql .= ' AND ('; } $sql .= '(ap_form_section_unit_user.user_id IN ('.$oa_users['users'].') AND ap_form_section_unit_user.type = "MEMBER")'; } $sql.= ')'; } } $sql .= ' GROUP BY users.id'; $return = $this->db->query($sql)->result_array(); // echo $this->db->last_query(); // print_r($return);exit; return $return; } function getRelatedUnits() { $this->db->query('SET SESSION group_concat_max_len = 1000000;'); $this->db->select('group_concat(DISTINCT(ap_form_section_unit_user.unit_id)) as units'); // $this->db->group_by('unit_id'); $return = $this->db->get_where('ap_form_section_unit_user',array('ap_form_section_unit_user.type !=' => "MEMBER", 'ap_form_section_unit_user.user_id' => $this->session->userdata('id')))->row_array()['units']; // echo $this->db->last_query(); return $return; } function getRelatedUsers() { $units = $this->getRelatedUnits(); if(!empty($units)) return $this->db->query('select group_concat(DISTINCT(ap_form_section_unit_user.user_id)) as users from ap_form_section_unit_user where ap_form_section_unit_user.type="MEMBER" AND ap_form_section_unit_user.unit_id IN ('.$units.')')->row_array()['users']; else return $this->db->query('select group_concat(DISTINCT(ap_form_section_unit_user.user_id)) as users from ap_form_section_unit_user where ap_form_section_unit_user.type="MEMBER"')->row_array()['users']; } function getReport($id,$appraisee) { if($appraisee=='') { $appraisee = $this->session->userdata('id'); } //all the related user in related units of the current logged in user $related = $this->getRelatedUsers(); //all the OA member of this current form $oa = $this->getOA($id,$appraisee); if((!empty($related)&& in_array($appraisee,explode(',',$related)) && $this->acl->hasPermission('VIEW_MEMBER_AP_REPORTA')) \|\| (!empty($oa)&& in_array($this->session->userdata('id'),$oa)) \|\| $this->acl->hasPermission('VIEW_ALL_AP_REPORTA') \|\| ($this->acl->hasPermission('VIEW_MY_AP_REPORTA') && $appraisee==$this->session->userdata('id'))) { $form = $this->db->get_where('ap_form',array('id'=>$id))->row_array(); $this->db->order_by('weight','asc'); $form['sections']= $this->db->get_where('ap_form_sections',array('ap_form'=>$form['id']))->result_array(); foreach($form['sections'] as $key=>$section) { $this->db->select('ap_form_section_unit.,ap_form_section_unit_user.user_id'); $this->db->join('ap_form_section_unit_user','ap_form_section_unit_user.unit_id = ap_form_section_unit.id'); $this->db->where('ap_form_section_unit_user.user_id = ' . $appraisee); $this->db->where('ap_form_section_unit_user.type != "RELATED"'); $form['sections'][$key]['units'] = $this->db->get_where('ap_form_section_unit',array('section'=>$section['id']))->result_array(); foreach($form['sections'][$key]['units'] as $key2 => $unit) { //For report section filtering $this->db->select('group_concat(user_id) as uid'); $this->db->select('ap_form_section_unit_user.'); $form['sections'][$key]['units'][$key2]['head'] = explode(',',$this->db->get_where('ap_form_section_unit_user',array('unit_id'=>$unit['id'],'type'=>'HEAD'))->row_array()['uid']); $this->db->select('group_concat(user_id) as uid'); $form['sections'][$key]['units'][$key2]['member'] = explode(',',$this->db->get_where('ap_form_section_unit_user',array('unit_id'=>$unit['id'],'type'=>'MEMBER'))->row_array()['uid']); //For report section filtering $this->db->select('group_concat(user_id) as users'); $form['sections'][$key]['units'][$key2]['viewer'] = $this->db->get_where('ap_form_section_unit_user',array('unit_id'=>$unit['id'],'type !='=>'MEMBER'))->row_array()['users']; //START ----- This is to fix the error caused in DB (2018-04-14) if($section['type']=='self') $form['sections'][$key]['units'][$key2]['answers'] = $this->db->get_where('ap_filled_form',array('appraiser'=>$appraisee,'form'=>$section['form'],'unit'=>$unit['id']))->result_array(); else //END ----- This is to fix the error caused in DB (2018-04-14) $form['sections'][$key]['units'][$key2]['answers'] = $this->db->get_where('ap_filled_form',array('appraisee'=>$appraisee,'form'=>$section['form'],'unit'=>$unit['id']))->result_array(); } } return $form; } else { show_error($this->lang->line('no_permission_error')); } } function getSign($unit,$head,$member) { // $head_sign = $this->db->select('signature')->get_where('ap_form_sign',array('unit_id'=>$unit,'user_id'=>$head))->result_array(); // if(count($head_sign)>1) $return['head'] = $this->db->select('signature')->get_where('ap_form_sign',array('unit_id'=>$unit,'user_id'=>$head,'counter'=>$member))->row_array()['signature']; // else // $return['head'] = $this->db->select('signature')->get_where('ap_form_sign',array('unit_id'=>$unit,'user_id'=>$head))->row_array()['signature']; // $member_sign = $this->db->select('signature')->get_where('ap_form_sign',array('unit_id'=>$unit,'user_id'=>$head))->result_array(); // if(count($member_sign)>1) $return['member'] = $this->db->select('signature')->get_where('ap_form_sign',array('unit_id'=>$unit,'user_id'=>$member,'counter'=>$head))->row_array()['signature']; // else // $return['member'] = $this->db->select('signature')->get_where('ap_form_sign',array('unit_id'=>$unit,'user_id'=>$member))->row_array()['signature']; return $return; } function filledUser($id) { return $this->db->query('select ap_form_sections.id, group_concat(distinct appraiser) as user from ap_form join ap_form_sections on ap_form_sections.ap_form = ap_form.id join ap_filled_form on ap_filled_form.form=ap_form_sections.form WHERE ap_filled_form.unit IN (SELECT id FROM `ap_form_section_unit` WHERE ap_form_section_unit.section IN (select ap_form_sections.id from ap_form_sections where ap_form='.$id.')) group by ap_form_sections.id')->result_array(); } function submittedUser($id) { return $this->db->query('select ap_form_sections.id, group_concat(distinct user_id) as user from ap_form LEFT join ap_form_sections on ap_form_sections.ap_form = ap_form.id join ap_form_section_user on ap_form_section_user.section_id=ap_form_sections.id where ap_form_section_user.status="SUBMITTED" AND ap_form='.$id . ' group by ap_form_sections.id')->result_array(); } function releasedUser($id) { return $this->db->query('select ap_form_sections.id, group_concat(distinct user_id) as user from ap_form LEFT join ap_form_sections on ap_form_sections.ap_form = ap_form.id join ap_form_section_user on ap_form_section_user.section_id=ap_form_sections.id where ap_form_section_user.status="RELEASED" AND ap_form='.$id . ' group by ap_form_sections.id')->result_array(); } function signReport($unit,$uid,$counter) { return $this->db->insert('ap_form_sign',array('signature'=>$this->input->post('imgBase64'),'unit_id'=>$unit,'user_id'=>$uid,'counter'=>$counter)); } function getRelations($form_id) { $sections = $this->db->get_where('ap_form_sections',array('ap_form'=>$form_id))->result_array(); foreach($sections as $key=>$section) { $this->db->select('ap_form_section_unit_user.user_id, ap_form_section_unit_user.type, ap_form_section_unit.name , ap_form_section_unit_user.unit_id'); if($_COOKIE['language']=='en') $this->db->select('name_english as username'); else $this->db->select('name_chinese as username'); $this->db->join('ap_form_section_unit_user','ap_form_section_unit_user.unit_id = ap_form_section_unit.id'); $this->db->join('users','users.id= ap_form_section_unit_user.user_id'); $sections[$key]['users'] = $this->db->get_where('ap_form_section_unit',array('section'=>$section['id']))->result_array(); } return $sections; } function save_oa($ap_form, $uid) { if($this->session->userdata("user_id")) { $oas = explode(',',$this->input->post('oa')); $this->db->delete('ap_oa',array('ap_form'=>$ap_form, 'appraisee'=>$uid)); foreach($oas as $oa) { $this->db->insert('ap_oa',array('ap_form'=>$ap_form, 'appraisee'=>$uid,'oa'=>$oa)); } return true; } else { redirect("login"); } } function getOA($ap_form,$appraisee=0) { if($this->session->userdata("user_id")) { $this->db->group_by('appraisee'); if($appraisee!=0) { $this->db->select('group_concat(oa) as oa'); return explode(',',$this->db->get_where('ap_oa',array('ap_form'=>$ap_form,'appraisee'=>$appraisee))->row_array()['oa']); } else { $this->db->select('group_concat(oa) as oa, appraisee'); return $this->db->get_where('ap_oa',array('ap_form'=>$ap_form))->result_array(); } } else { redirect("login"); } } function getSections($ap_form) { $sections = $this->db->query('SELECT * FROM ap_form_sections WHERE ap_form = '.$ap_form)->result_array(); foreach($sections as $key=>$section) { $sections[$key]['users'] = array(); $sections[$key]['form_name'] = $this->db->query('SELECT * FROM form WHERE id=' . $section['form'])->row_array()['title']; switch($section['type']) { case 'u2d'; $users = $this->db->query('SELECT group_concat(user_id) as users FROM ap_form_section_unit_user JOIN ap_form_section_unit ON ap_form_section_unit.id = ap_form_section_unit_user.unit_id WHERE ap_form_section_unit.section = ' . $section['id'] . ' AND ap_form_section_unit_user.type = "HEAD"')->row_array(); if(!empty($users)) $sections[$key]['users'] = $users['users']; break; case 'd2u'; $users = $this->db->query('SELECT group_concat(user_id) as users FROM ap_form_section_unit_user JOIN ap_form_section_unit ON ap_form_section_unit.id = ap_form_section_unit_user.unit_id WHERE ap_form_section_unit.section = ' . $section['id'] . ' AND ap_form_section_unit_user.type = "MEMBER"')->row_array(); if(!empty($users)) $sections[$key]['users'] = $users['users']; break; case 'self'; $users = $this->db->query('SELECT group_concat(user_id) as users FROM ap_form_section_unit_user JOIN ap_form_section_unit ON ap_form_section_unit.id = ap_form_section_unit_user.unit_id WHERE ap_form_section_unit.section = ' . $section['id'] . ' AND ap_form_section_unit_user.type = "MEMBER"')->row_array(); if(!empty($users)) $sections[$key]['users'] = $users['users']; break; case 'peer'; $users = $this->db->query('SELECT group_concat(user_id) as users FROM ap_form_section_unit_user JOIN ap_form_section_unit ON ap_form_section_unit.id = ap_form_section_unit_user.unit_id WHERE ap_form_section_unit.section = ' . $section['id'] . ' AND ap_form_section_unit_user.type = "MEMBER"')->row_array(); if(!empty($users)) $sections[$key]['users'] = $users['users']; break; } } return $sections; } function export($section) { $units = $this->db->query('select group_concat(id) as units FROM ap_form_section_unit WHERE section =' . $section['id'])->row_array(); if(!empty($units['units'])) { $forms = $this->db->query('SELECT ap_form_section_unit.name as unit, concat(u1.name_english ," (",u1.username,")") as appraiser, concat(u2.name_english ," (",u2.username,")") as appraisee, ap_filled_form.answer FROM `ap_filled_form` JOIN ap_form_section_unit ON ap_filled_form.unit = ap_form_section_unit.id JOIN users as u1 ON u1.id = ap_filled_form.appraiser JOIN users as u2 ON u2.id = ap_filled_form.appraisee WHERE ap_filled_form.form =' . $section['form'] . ' AND ap_filled_form.unit IN ('.$units['units'].') ORDER by ap_filled_form.unit ASC')->result_array(); } if(!empty($forms)) { return $forms; } else { return false; } } }

| ver. 1.4 | Github | . | PHP 7.2.34 | Generation time: 0.24 | proxy | phpinfo | Settings