Viewing file:  mo_rg_person.php (12.82 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
include_once("da_rg_Person.php");
class mo_rg_person extends Da_rg_Person {
/**
* หน้าที่ของฟังก์ชั่น คือ หารหัส และรายละเอียดของบุคลากรตามเงื่อนไข
*
* @access public
* @param array condition โดยรูปแบบของ array จะอยู่ในรูป condition('key' => value) ไว้สำหรับระบุเงื่อนไขของคำสั่ง SELECT
* @param array order โดยรูปแบบของ array จะอยู่ในรูป condition('key' => value) ไว้สำหรับระบุเงื่อนไขการเรียงลำดับ ORDER BY
* @param array group โดยรูปแบบของ array จะอยู่ในรูป condition('key' => value) ไว้สำหรับระบุเงื่อนไขการจัดกลุ่ม GROUP BY
* @return query >= 1 rows () : rg_Person.*
*/
public function qryPrs($condition="",$order="",$group="") {
$where = $this->checkCondition($condition);
$order = $this->checkOrderBy($order);
$group = $this->checkGroupBy($group);
$c1= substr($where, 0, -3);
$c2= substr($order, 0, -1);
$c3= substr($group, 0, -1);
$sql = "SELECT *
FROM $this->rg_dbname.rg_Person
$c1
$c3
$c2";
$query = $this->db->query($sql);
return $query;
}
/**
* หน้าที่ของฟังก์ชั่น คือ หารายชื่อบุคลากรตามเงื่อนไข
*
* @access public
* @param array condition โดยรูปแบบของ array จะอยู่ในรูป condition('key' => value) ไว้สำหรับระบุเงื่อนไขของคำสั่ง SELECT
* @param array order โดยรูปแบบของ array จะอยู่ในรูป condition('key' => value) ไว้สำหรับระบุเงื่อนไขการเรียงลำดับ ORDER BY
* @param array group โดยรูปแบบของ array จะอยู่ในรูป condition('key' => value) ไว้สำหรับระบุเงื่อนไขการจัดกลุ่ม GROUP BY
* @return มี 2 รูปแบบ
* - query >= 1 row: prsId, CONCAT(prefixName,fName,' ',lName)
* - $this->prsId, $this->name
* @todo use
*/
public function qryPrsJoinPPC($condition="", $order="", $group="", $withSetAttributeValue=FALSE) {
$where = $this->checkCondition($condition);
$order = $this->checkOrderBy($order);
$group = $this->checkGroupBy($group);
$NameTeach = isset($_GET['name_teach'])? $_GET['name_teach']:'';
$c1= substr($where, 0, -3)." and ( fName like '%".$NameTeach."%' or lName like '%".$NameTeach."%') ";
$c2= substr($order, 0, -1);
$c3= substr($group, 0, -1);
$sql = "SELECT * FROM (
SELECT prs.*, pf.*,
'' AS psoutId,
ps.personId AS personId,
ps.personCode AS personCode,
ps.fName AS fName,
ps.lName AS lName,
pst.fName2 AS fName2,
pst.lName2 AS lName2,
CONCAT(pf.prefixName, ps.fName, ' ', ps.lName) AS name,
CONCAT(pf.prefixNameEng, pst.fName2, ' ', pst.lName2) AS nameE,
pst.emailAddr AS emailAddr,
pst.pic AS pic
FROM $this->rg_dbname.rg_Person prs
INNER JOIN $this->ppc_dbname.Person ps ON prsUsId = ps.personId
INNER JOIN $this->ppc_dbname.PersonT pst ON ps.personId = pst.personId
INNER JOIN $this->ppc_dbname.Prefix pf ON ps.prefixId = pf.prefixId
WHERE prsItId = 1
UNION
SELECT prs.*, pfo.*,
pso.psoutId AS psoutId,
'' AS personId,
'' AS personCode,
pso.fName AS fName,
pso.lName AS lName,
'' AS fName2,
'' AS lName2,
CONCAT(pfo.prefixName, pso.fName, ' ', pso.lName) AS name,
'' AS nameE,
'' AS emailAddr,
'' AS pic
FROM $this->rg_dbname.rg_Person prs
INNER JOIN $this->ppc_dbname.Personout pso ON prsUsId = pso.psoutId
INNER JOIN $this->ppc_dbname.Prefix pfo ON pso.prefixId = pfo.prefixId
WHERE prsItId = 2
) AS tb
LEFT JOIN $this->rg_dbname.rg_PersonType pt ON prsPtId = ptId
LEFT JOIN $this->rg_dbname.rg_InstructorType it ON prsItId = itId
LEFT JOIN $this->rg_dbname.rg_Organization org ON prsOrgId = orgId
$c1
$c2
$c3";
/*$sql = "SELECT *,
IF(prsItId=1, CONCAT(pf.prefixName, ps.fName, ' ', ps.lName), CONCAT(pfo.prefixName, pso.fName, ' ', pso.lName)) AS name,
IF(prsItId=1, CONCAT(pf.prefixNameEng, pst.fName2, ' ', pst.lName2), '') AS nameE ,
IF(prsItId=1, ps.fName, pso.fName) AS fName,
IF(prsItId=1, ps.lName, pso.lName) AS lName,
IF(prsItId=1, pf.prefixId, pfo.prefixId) AS prefixId,
IF(prsItId=1, pf.prefixName, pfo.prefixName) AS prefixName,
IF(prsItId=1, fip.fipPsId, 1) AS fipPsId
FROM $this->rg_dbname.rg_Person prs
LEFT JOIN $this->ppc_dbname.Person ps ON prsUsId = ps.personId
LEFT JOIN $this->ppc_dbname.PersonT pst ON ps.personId = pst.personId
LEFT JOIN $this->ppc_dbname.Prefix pf ON ps.prefixId = pf.prefixId
LEFT JOIN $this->rg_dbname.rg_FStatusInPs fip ON ps.fStatus = fip.fipFStatus
LEFT JOIN $this->ppc_dbname.Personout pso ON prsUsId = pso.psoutId
LEFT JOIN $this->ppc_dbname.Prefix pfo ON pso.prefixId = pfo.prefixId
LEFT JOIN $this->rg_dbname.rg_PersonType pt ON prsPtId = ptId
LEFT JOIN $this->rg_dbname.rg_InstructorType it ON prsItId = itId
LEFT JOIN $this->rg_dbname.rg_Organization org ON prsOrgId = orgId
$c1
$c2
$c3";*/
$query = $this->db->query($sql);
if ($withSetAttributeValue) {
$this->row2attribute($query->row());
} else {
return $query ;
}
}
function qryPrsJoinRG($condition="", $order="", $withSetAttributeValue=FALSE) {
$where = $this->checkCondition($condition);
$order = $this->checkOrderBy($order);
$c1= substr($where, 0, -3);
$c2= substr($order, 0, -1);
/*$sql = "SELECT *
FROM $this->ppc_dbname.Person ps
INNER JOIN $this->ppc_dbname.PersonT pst ON ps.personId = pst.personId
INNER JOIN $this->ppc_dbname.Prefix pf ON ps.prefixId = pf.prefixId
INNER JOIN $this->rg_dbname.rg_FStatusInPs fip ON ps.fStatus = fipFStatus
LEFT JOIN $this->rg_dbname.rg_Person prs ON ps.personId = prsUsId
LEFT JOIN $this->rg_dbname.rg_PersonType pt ON prsPtId = ptId
LEFT JOIN $this->rg_dbname.rg_InstructorType it ON prsItId = itId
LEFT JOIN $this->rg_dbname.rg_Organization org ON prsOrgId = orgId
$c1
$c2";*/
$sql = "SELECT * FROM (
SELECT prs.*,
'' AS psoutId,
ps.personId AS personId,
ps.personCode AS personCode,
pf.prefixName AS prefixName,
ps.fName AS fName,
ps.lName AS lName
FROM $this->ppc_dbname.Person ps
INNER JOIN $this->ppc_dbname.Prefix pf ON ps.prefixId = pf.prefixId
LEFT JOIN $this->rg_dbname.rg_Person prs ON ps.personId = prsUsId
WHERE prsItId = 1
UNION
SELECT prs.*,
pso.psoutId AS psoutId,
'' AS personId,
'' AS personCode,
pfo.prefixName AS prefixName,
pso.fName AS fName,
pso.lName AS lName
FROM $this->ppc_dbname.Personout pso
INNER JOIN $this->ppc_dbname.Prefix pfo ON pso.prefixId = pfo.prefixId
LEFT JOIN $this->rg_dbname.rg_Person prs ON pso.psoutId = prsUsId
WHERE prsItId = 2
) AS tb
$c1
$c2";
$query = $this->db->query($sql);
if ($withSetAttributeValue) {
$this->row2attribute($query->row());
} else {
return $query ;
}
}
/**
* หน้าที่ของฟังก์ชั่น คือ หาอาจารย์ประจำ
*
* @access
* @param
* @return
*/
function qryPsJoinPPC($condition="", $order="", $withSetAttributeValue=FALSE) {
$where = $this->checkCondition($condition);
$order = $this->checkOrderBy($order);
$c1= substr($where, 0, -3);
$c2= substr($order, 0, -1);
$sql = "SELECT * FROM $this->rg_dbname.rg_Person
INNER JOIN $this->ppc_dbname.Person ps ON prsUsId = ps.personId
$c1
$c2";
$query = $this->db->query($sql);
if ($withSetAttributeValue) {
$this->row2attribute($query->row());
} else {
return $query ;
}
}
// public function qryPrsJoinAvsJoinStdJoinPf($condition="",$order="",$group="") {
// $where = $this->checkCondition($condition);
// $order = $this->checkOrderBy($order);
// $group = $this->checkGroupBy($group);
//
// $c1= substr($where, 0, -3);
// $c2= substr($order, 0, -1);
// $c3= substr($group, 0, -1);
//
// $sql = "SELECT *
// FROM $this->rg_dbname.rg_Person
// LEFT JOIN $this->rg_dbname.rg_Adviser ON prsId=advPrsId
// INNER JOIN $this->rg_dbname.rg_Student ON advStdId=stdId
// LEFT JOIN $this->ppc_dbname.Prefix AS pf ON stdPfId=pf.prefixId
// $c1
// $c2
// $c3";
// $query = $this->db->query($sql);
// return $query;
// }
/**
* หน้าที่ของฟังก์ชั่น คือ หาอาจารย์ตามเงื่อนไข
*
* @access public
* @param array condition
* @return query >= 1 rows ()
*/
public function qrySearchPrs($code, $name, $surname) {
$sql = "SELECT prs.*,
IF(prsItId=1, ps.personCode, '') AS personCode,
IF(prsItId=1, pf.prefixName, pfo.prefixName) AS prefixName,
IF(prsItId=1, ps.fName, pso.fName) AS fName,
IF(prsItId=1, ps.lName, pso.lName) AS lName
FROM $this->rg_dbname.rg_Person prs
LEFT JOIN $this->ppc_dbname.Person ps ON prsUsId = ps.personId
LEFT JOIN $this->ppc_dbname.Prefix pf ON ps.prefixId = pf.prefixId
LEFT JOIN $this->ppc_dbname.Personout pso ON prsUsId = pso.psoutId
LEFT JOIN $this->ppc_dbname.Prefix pfo ON pso.prefixId = pfo.prefixId
WHERE (ps.personCode LIKE ?
AND ps.fName LIKE ?
AND ps.lName LIKE ?)
OR (pso.fName LIKE ?
AND pso.lName LIKE ?)
ORDER BY personCode,
CONVERT (pso.fName USING tis620),
CONVERT (pso.lName USING tis620),
CONVERT (ps.fName USING tis620),
CONVERT (ps.lName USING tis620)";
/*$sql = "SELECT *
FROM $this->rg_dbname.rg_Person
INNER JOIN $this->ppc_dbname.Person AS pp ON prsUsId=pp.personId
LEFT JOIN $this->ppc_dbname.Prefix AS pf ON pp.prefixId=pf.prefixId
WHERE pp.personCode LIKE ?
AND pp.fName LIKE ?
AND pp.lName LIKE ?
ORDER BY personCode,
CONVERT (fName USING tis620),
CONVERT (lName USING tis620)";*/
$query = $this->db->query($sql,array("%".$code."%", "%".$name."%", "%".$surname."%", "%".$name."%", "%".$surname."%"));
return $query;
}
// ฟังก์ชั่นหลังจากนี้ ก่อนวันที่ 6 ตุลาคม 2553
public function get_options($condition="",$order="",$group="", $optional='y') {
$query = $this->qryPrsJoinPPC($condition,$order,$group);
$opt = "";
if($optional=='y') $opt[''] = '';
foreach($query->result() as $row) {
$opt[$row->prsId] = $row->name;
}
return $opt;
}
// Timetable Edit
public function qryPrsJoinPPCtoTT($type,$prsId){
$ptb = "";
$key = "";
if($type==1){
$ptb = "Person";
$key = "personId";
}else{
$ptb = "Personout";
$key = "psoutId";
}
$sql = "SELECT * FROM $this->rg_dbname.rg_Person INNER JOIN $this->ppc_dbname.$ptb ON prsUsId=$key where prsUsId = '$prsId'";
$query = $this->db->query($sql);
return $query;
}
/*
* เขียนคำสั่งดึงรายชื่ออาจารย์ในระบบ ให้ระบบจัดการฐานข้อมูลนักวิจัยเรียกใช้
* AUTHOR : meuzicxx
* DATE : 2013-06-19
* OUTPUT : prsId, name
*/
function getAllPsJoinPeople(){
$sql = "SELECT *
FROM ( SELECT prsId, pf.prefixName, CONCAT(ps.fName, ' ', ps.lName) AS name
FROM $this->rg_dbname.rg_Person
INNER JOIN $this->ppc_dbname.Person ps ON prsUsId = ps.personId
INNER JOIN $this->ppc_dbname.Prefix pf ON ps.prefixId = pf.prefixId
WHERE prsItId = 1
UNION
SELECT prsId, pfo.prefixName, CONCAT(pso.fName, ' ', pso.lName) AS name
FROM $this->rg_dbname.rg_Person
INNER JOIN $this->ppc_dbname.Personout pso ON prsUsId = pso.psoutId
INNER JOIN $this->ppc_dbname.Prefix pfo ON pso.prefixId = pfo.prefixId
WHERE prsItId = 2
) AS ps
ORDER BY CONVERT(name USING tis620) ASC";
$query = $this->db->query($sql);
return $query;
}
} // end class mo_rg_person
?>
|