Viewing file:  m_alumnimain.php (12.12 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
include_once("da_AlumniMain.php");
class M_alumnimain extends Da_alumnimain {
/*
* aOrderBy = array('fieldname' => 'ASC|DESC', ... )
*/
function get_all($aOrderBy=""){
$orderBy = "";
if ( is_array($aOrderBy) ) {
$orderBy.= "ORDER BY ";
foreach ($aOrderBy as $key => $value) {
$orderBy.= "$key $value, ";
}
$orderBy = substr($orderBy, 0, strlen($orderBy)-2);
}
$sql = "SELECT *
FROM $this->ea_dbname.AlumniMain
$orderBy";
$query = $this->db->query($sql);
return $query;
}
/*
* create array of pk field and value for generate select list in view, must edit PK_FIELD and FIELD_NAME manually
* the first line of select list is '-----เลือก-----' by default.
* if you do not need the first list of select list is '-----เลือก-----', please pass $optional parameter to other values.
* you can delete this function if it not necessary.
*/
function get_options($optional='y') {
$qry = $this->get_all();
if ($optional=='y') $opt[''] = '-----เลือก-----';
foreach ($qry->result() as $row) {
$opt[$row->PK_FIELD] = $row->FIELD_NAME;
}
return $opt;
}
// add your functions here
function qryAm($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->ea_dbname.AlumniMain
$c1
$c3
$c2";
$query = $this->db->query($sql);
return $query;
}
function qryAmJoinAbPf($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->ea_dbname.AlumniMain am
INNER JOIN $this->ea_dbname.AlumniBio ab ON am.alumniId=ab.alumniId
LEFT JOIN $this->ppc_dbname.Prefix pf ON am.prefixId=pf.prefixId
$c1
$c3
$c2";
$query = $this->db->query($sql);
return $query;
}
function qryAmJoinAbPfCurd($stdCode, $citizenId, $name, $curId, $graduateY, $graduateY_2) {
$str_graduateY = ($graduateY!="") ? $graduateY : '0000';
$str_graduateY_2 = ($graduateY_2!="") ? $graduateY_2 : "9999";
$cond = "WHERE ";
$cond.=($stdCode!="") ? "studentCode LIKE '%$stdCode%' AND " : "";
$cond.=($citizenId!="") ? "citizenId LIKE '%$citizenId%' AND " : "";
$cond.=($name!="") ? "studentName LIKE '%$name%' OR studentSurname LIKE '%$name%' AND " : "";
$cond.=($curId!="") ? "programId=$curId AND " : "";
$cond.=($graduateY!="" || $graduateY_2!="") ? "graduateYear BETWEEN $str_graduateY AND $str_graduateY_2 AND " : "";
$con = substr($cond, 0, -4);
$sql = "SELECT *
FROM $this->ea_dbname.AlumniMain am
INNER JOIN $this->ea_dbname.AlumniBio ab ON am.alumniId=ab.alumniId
LEFT JOIN $this->ppc_dbname.Prefix pf ON am.prefixId=pf.prefixId
LEFT JOIN $this->ea_dbname.ea_CurriculumDetails curd ON am.programId=curd.curId
$con
ORDER BY studentCode";
$query = $this->db->query($sql);
return $query;
}
function qryAmJoinAbPfCurdAq($stdCode, $citizenId, $name, $curId, $graduateY, $graduateY_2, $ansQn) {
$str_graduateY = ($graduateY!="") ? $graduateY : '0000';
$str_graduateY_2 = ($graduateY_2!="") ? $graduateY_2 : "9999";
$cond = "WHERE ";
$cond.=($stdCode!="") ? "studentCode LIKE '%$stdCode%' AND " : "";
$cond.=($citizenId!="") ? "citizenId LIKE '%$citizenId%' AND " : "";
$cond.=($name!="") ? "studentName LIKE '%$name%' OR studentSurname LIKE '%$name%' AND " : "";
$cond.=($curId!="") ? "programId=$curId AND " : "";
$cond.=($graduateY!="" || $graduateY_2!="") ? "graduateYear BETWEEN $str_graduateY AND $str_graduateY_2 AND " : "";
$cond.=($ansQn!="") ? "amAnsQn='$ansQn' AND " : "";
$con = substr($cond, 0, -4);
$sql = "SELECT *
FROM $this->ea_dbname.AlumniMain am
INNER JOIN $this->ea_dbname.AlumniBio ab ON am.alumniId=ab.alumniId
LEFT JOIN $this->ppc_dbname.Prefix pf ON am.prefixId=pf.prefixId
LEFT JOIN $this->ea_dbname.ea_CurriculumDetails curd ON am.programId=curd.curId
LEFT JOIN $this->ea_dbname.ea_AlumniQn aq ON am.alumniId=aq.aqAlumniId
$con
GROUP BY studentCode
ORDER BY studentCode";
$query = $this->db->query($sql);
return $query;
}
function qryDistinctGraduateY() {
$sql = "SELECT DISTINCT graduateYear
FROM $this->ea_dbname.AlumniMain";
$query = $this->db->query($sql);
return $query;
}
public function get_optionsDistinctGraduateY($optional='y') {
$query = $this->qryDistinctGraduateY();
if($optional=='y') $opt[''] = '';
foreach($query->result() as $row) {
$opt[$row->graduateYear] = $row->graduateYear;
}
return $opt;
}
function qryCitizenIdNotAmId($programId,$admitAcadYear,$citizenId,$alumniId) {
$sql = "SELECT *
FROM $this->ea_dbname.AlumniMain
WHERE programId=?
AND admitAcadYear=?
AND citizenId=?
AND alumniId <> ?";
$query = $this->db->query($sql,array($programId,$admitAcadYear,$citizenId,$alumniId));
return $query;
}
function qryAmJoinCurd($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->ea_dbname.AlumniMain
LEFT JOIN $this->ea_dbname.ea_CurriculumDetails ON programId=curId
$c1
$c3
$c2";
$query = $this->db->query($sql);
return $query;
}
function updateAmProgramAlumniByProgramId($amProgramAlumni, $programId, $graduateYear) {
$sql = "UPDATE $this->ea_dbname.AlumniMain
SET amProgramAlumni=?
WHERE programId=?
AND graduateYear=?";
$query = $this->db->query($sql,array($amProgramAlumni, $programId, $graduateYear));
return $query;
}
public function qryTotalAm($amProgramAlumni, $graduateYear, $year) {
$str = "";
if($year!=0) {
for($i=0; $i<$year; $i++) {
$str.= $graduateYear.", ";
$graduateYear--;
}
$year = substr($str, 0, -2);
}
$sql_alumni = "SELECT COUNT(alumniId) AS total
FROM $this->ea_dbname.AlumniMain
WHERE amProgramAlumni=?
AND graduateYear IN ($year)";
$query_alumni = $this->db->query($sql_alumni,array($amProgramAlumni));
return $query_alumni->row()->total;
}
public function qryAmByAnsQn($amProgramAlumni, $graduateYear, $year, $condition="", $status="") {
$str = "";
if($year!=0) {
for($i=0; $i<$year; $i++) {
$str.= $graduateYear.", ";
$graduateYear--;
}
$year = substr($str, 0, -2);
}
$cond = "";
if($condition!="") {
foreach($condition as $key => $val) {
if($key=="preAdmitPositionId") {
$cond.="AND $key IN ($val)";
} else {
$cond.= "AND $key='$val'";
}
}
}
$condSst = "";
if($status==1) {
$condSst.="AND preAdmitPositionId NOT IN (2, 3)";
}
$sql_alumni = "SELECT COUNT(alumniId) AS total
FROM $this->ea_dbname.AlumniMain
WHERE amProgramAlumni=?
AND graduateYear IN ($year)
$condSst";
$query_alumni = $this->db->query($sql_alumni,array($amProgramAlumni));
$total = $query_alumni->row()->total;
$sql = "SELECT
COUNT(CASE studentSex WHEN 'M' THEN 1 END) AS male,
COUNT(CASE studentSex WHEN 'F' THEN 1 END) AS female,
COUNT(am.alumniId) AS totalQn,
(COUNT(am.alumniId) * 100)/$total AS percent,
$total AS total
FROM $this->ea_dbname.AlumniMain am
LEFT JOIN $this->ea_dbname.ea_AlumniQn aq ON am.alumniId=aq.aqAlumniId
WHERE am.amProgramAlumni=?
AND graduateYear IN ($year)
AND amAnsQn='Y'
$cond";
$query = $this->db->query($sql,array($amProgramAlumni, $year));
return $query;
}
public function qryAmByAnsQnForLastItem($amProgramAlumni, $graduateYear, $year, $condition="", $status="") {
$str = "";
if($year!=0) {
for($i=0; $i<$year; $i++) {
$str.= $graduateYear.", ";
$graduateYear--;
}
$year = substr($str, 0, -2);
}
$cond = "";
if($condition!="") {
foreach($condition as $key => $val) {
if($key=="preAdmitPositionId") {
$cond.="AND $key IN ($val)";
} else {
$cond.= "AND $key='$val'";
}
}
}
$condSst = "";
if($status==1) {
$condSst.="AND preAdmitPositionId NOT IN (2, 3)";
}
$sql_alumni = "SELECT COUNT(alumniId) AS total
FROM $this->ea_dbname.AlumniMain
WHERE amProgramAlumni=?
AND graduateYear IN ($year)
$condSst";
$query_alumni = $this->db->query($sql_alumni,array($amProgramAlumni));
$total = $query_alumni->row()->total;
$sql = "SELECT
COUNT(CASE studentSex WHEN 'M' THEN 1 END) AS male,
COUNT(CASE studentSex WHEN 'F' THEN 1 END) AS female,
COUNT(am.alumniId) AS totalQn,
(COUNT(am.alumniId) * 100)/$total AS percent,
$total AS total
FROM $this->ea_dbname.AlumniMain am
LEFT JOIN $this->ea_dbname.ea_AlumniQn aq ON am.alumniId=aq.aqAlumniId
WHERE am.amProgramAlumni=?
AND graduateYear IN ($year)
$cond";
$query = $this->db->query($sql,array($amProgramAlumni, $year));
return $query;
}
public function qryAmJoinPf($condition="",$order="",$group="", $graduateY="") {
$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);
$c4 = "";
if($graduateY!="") {
$c4 = "AND graduateYear IN ($graduateY)";
}
$sql = "SELECT *
FROM $this->ea_dbname.AlumniMain am
LEFT JOIN $this->ppc_dbname.Prefix pf ON am.prefixId=pf.prefixId
$c1
AND preAdmitPositionId IN (2,3)
$c4
$c3
$c2";
$query = $this->db->query($sql);
return $query;
}
public function qryAmFollowUpdateHistory($graduateYear, $amProgramAlumni) {
$sql = "SELECT am.studentCode, am.studentName, am.studentSurname, am.finishDate, am.glantDate, am.updateDateTime, am.glantreqDate, IFNULL(hw.alumniId, 0) AS hwAlumniId, IFNULL(hwa.alumniId, 0) AS hwaAlumniId, IFNULL(hedAlumniId, 0) AS hedAlumniId, prefixName
FROM $this->ea_dbname.AlumniMain am
LEFT JOIN $this->ea_dbname.HisWork hw ON am.alumniId = hw.alumniId AND hw.seqId=1
LEFT JOIN $this->ea_dbname.HisAward hwa ON am.alumniId = hwa.alumniId AND hwa.seqId=1
LEFT JOIN $this->ea_dbname.ea_HisEducation ON am.alumniId = hedAlumniId AND hedSeqId=1
LEFT JOIN $this->ppc_dbname.Prefix pf ON am.prefixId=pf.prefixId
WHERE graduateYear=?
AND amProgramAlumni=?
GROUP BY am.alumniId
ORDER BY am.studentCode";
$query = $this->db->query($sql,array($graduateYear, $amProgramAlumni));
return $query;
}
public function qryAmByAlumniId($id) {
$sql = "SELECT *
FROM $this->ea_dbname.AlumniMain am
LEFT JOIN $this->ppc_dbname.Prefix pf ON am.prefixId=pf.prefixId
WHERE alumniId IN ($id)";
$query = $this->db->query($sql);
return $query;
}
public function qryAmForPreLogin($stdCode, $name, $adY, $regCurId, $eaCurId) {
$cond = "WHERE ";
$cond.=($stdCode!="") ? "studentCode LIKE '%$stdCode%' AND " : "";
$cond.=($name!="") ? "studentName LIKE '%$name%' OR studentSurname LIKE '%$name%' AND " : "";
$cond.=($adY!="") ? "admitAcadYear LIKE '%$adY%' AND " : "";
$cond.=($regCurId!="") ? "programId=$regCurId AND " : "";
$cond.=($eaCurId!="") ? "amProgramAlumni=$eaCurId AND " : "";
$con = substr($cond, 0, -4);
$sql = "SELECT *
FROM $this->ea_dbname.AlumniMain am
INNER JOIN $this->ea_dbname.AlumniBio ab ON am.alumniId=ab.alumniId
LEFT JOIN $this->ppc_dbname.Prefix pf ON am.prefixId=pf.prefixId
LEFT JOIN $this->ea_dbname.ea_CurriculumDetails curd ON am.programId=curd.curId
LEFT JOIN $this->ea_dbname.ea_AlumniQn aq ON am.alumniId=aq.aqAlumniId
$con
GROUP BY studentCode
ORDER BY studentCode";
$query = $this->db->query($sql);
return $query;
}
} // end class M_alumnimain
?>
|