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;
}
