public static function UpdateRecordEducation($id, $updateFactID, $updateCourse, $updateLevel, $updateYear) { $user = User::findBySql("UPDATE user_education ue " . "JOIN education_information ei on ei.ei_id = ue.ei_id " . "SET ei.inst_id ='" . $updateFactID . "', ei.course_id ='" . $updateCourse . "', " . "ei.el_id = '" . $updateLevel . "', ei.ei_graduation_year ='" . $updateYear . "' " . "WHERE ue.id =" . $id . "")->asArray()->all(); return $user; }
public static function getWorkingReport($start) { $user = User::findBySql("SELECT COUNT(ue.id) as totalstudent, us.working_status FROM user_education ue " . "join education_information ei on ue.ei_id = ei.ei_id " . "join institution i on ei.inst_id = i.inst_id " . "join university u on i.uni_id = u.uni_id " . 'join user us on ue.id = us.id ' . 'join personal_information pi on us.pi_id = pi.pi_id ' . 'WHERE ei.ei_graduation_year = ' . $start . ' ' . 'AND u.uni_status = "1" ' . 'GROUP BY us.working_status')->asArray()->all(); return $user; }
public static function getResultAdvancedReport($s_id, $race, $age, $gender, $advFaculty, $advCourse, $level, $year, $stateWork, $workstatus, $position) { $student = ""; $condition = ""; $joinRegion = ""; if ($s_id != 0) { $condition .= "AND s.s_id ='" . $s_id . "' "; $joinRegion .= "pi.pi_zipcode_permanent = r.r_zipcode"; } if ($race != 0) { $condition .= "AND pi.pi_race ='" . $race . "' "; } if ($age != 0) { $currentYear = date('Y', time()); if ($age == "20-25") { $firstYear = $currentYear - 20; $lastYear = $currentYear - 25; $result1 = substr($firstYear, 2, 4); $result2 = substr($lastYear, 2, 4); $condition .= "AND SUBSTRING(pi.pi_ic_or_passport ,1,2)<= '" . $result1 . "' AND SUBSTRING(pi.pi_ic_or_passport ,1,2) >= '" . $result2 . "' "; } else { if ($age == "26-30") { $firstYear = $currentYear - 26; $lastYear = $currentYear - 30; $result1 = substr($firstYear, 2, 4); $result2 = substr($lastYear, 2, 4); $condition .= "AND SUBSTRING(pi.pi_ic_or_passport ,1,2)<= '" . $result1 . "' AND SUBSTRING(pi.pi_ic_or_passport ,1,2) >= '" . $result2 . "' "; } else { if ($age == "31-35") { $firstYear = $currentYear - 31; $lastYear = $currentYear - 35; $result1 = substr($firstYear, 2, 4); $result2 = substr($lastYear, 2, 4); $condition .= "AND SUBSTRING(pi.pi_ic_or_passport ,1,2)<= '" . $result1 . "' AND SUBSTRING(pi.pi_ic_or_passport ,1,2) >= '" . $result2 . "' "; } else { if ($age == "36-40") { $firstYear = $currentYear - 36; $lastYear = $currentYear - 40; $result1 = substr($firstYear, 2, 4); $result2 = substr($lastYear, 2, 4); $condition .= "AND SUBSTRING(pi.pi_ic_or_passport ,1,2)<= '" . $result1 . "' AND SUBSTRING(pi.pi_ic_or_passport ,1,2) >= '" . $result2 . "' "; } else { if ($age == "41") { $firstYear = $currentYear - 41; $result1 = substr($firstYear, 2, 4); $condition .= "AND SUBSTRING(pi.pi_ic_or_passport ,1,2) <= '" . $result1 . "'"; } } } } } } if ($gender != null) { $condition .= "AND pi.pi_gender ='" . $gender . "' "; } if ($advFaculty != 0) { $condition .= "AND ei.inst_id ='" . $advFaculty . "' "; } if ($advCourse != 0) { $condition .= "AND ei.course_id ='" . $advCourse . "' "; } if ($level != 0) { $condition .= "AND ei.el_id ='" . $level . "' "; } if ($year != 0) { $condition .= "AND ei.ei_graduation_year ='" . $year . "' "; } if ($workstatus != null) { $condition .= "AND u.working_status ='" . $workstatus . "' "; } if ($position != "") { $condition .= "AND wi.wi_position LIKE'%" . $position . "%' "; } if ($stateWork != 0) { if ($s_id == 0) { $joinRegion .= "pi.pi_zipcode_permanent = r.r_zipcode"; } else { $joinRegion .= " OR pi.pi_zipcode = r.r_zipcode"; } $condition .= "AND s.s_id ='" . $stateWork . "' "; } $student = User::findBySql("SELECT DISTINCT pi.*, u.id from personal_information pi " . "JOIN user u on pi.pi_id = u.pi_id " . "LEFT JOIN user_working uw on uw.id = u.id " . "LEFT JOIN working_information wi on uw.wi_id = wi.wi_id " . "JOIN user_education ue on u.id = ue.id " . "JOIN education_information ei on ue.ei_id = ei.ei_id " . "JOIN region r on " . $joinRegion . " " . "JOIN state s on r.s_id = s.s_id " . "WHERE 1=1 " . $condition)->asArray()->all(); return $student; }
public static function getUserPositionByYear($year) { $user = User::findBySql("SELECT COUNT(pi.pi_id) as totalstudent, r.* FROM personal_information pi " . "JOIN user u on pi.pi_id = u.pi_id " . "JOIN user_education ue on u.id = ue.id " . "JOIN education_information ei on ue.ei_id = ei.ei_id " . "JOIN region r on pi.pi_zipcode = r.r_zipcode " . " WHERE ei.ei_graduation_year ='{$year}' GROUP BY r.r_zipcode")->asArray()->all(); return $user; }
public function getCourseByFaculty($factID) { $user = User::findBySql("SELECT c.* FROM course c " . "JOIN institution_course ic on c.course_id = ic.course_id " . "JOIN institution i on ic.inst_id = i.inst_id " . 'join university u on i.uni_id = u.uni_id ' . "WHERE i.inst_id = " . $factID . " ")->asArray()->all(); return $user; }