PHP User::findBySql方法代码示例

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