Viewing file: m_hisaward.php (5.74 KB) -rwxr-xr-x Select action/file-type: (+) | (+) | (+) | Code (+) | Session (+) | (+) | SDB (+) | (+) | (+) | (+) | (+) | (+) |
<?php
include_once("da_HisAward.php");
class M_hisaward extends Da_hisaward {
/*
* 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 HisAward
$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;
}
public function getNextSeq($condition="") {
$where = $this->checkCondition($condition);
$c1 = substr($where, 0, -3);
$sql = "SELECT IFNULL(MAX(seqId), 0) + 1 AS seqId
FROM $this->ea_dbname.HisAward
$c1";
$query = $this->db->query($sql);
return $query->row()->seqId;
}
function qryHa($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.HisAward
$c1
$c3
$c2";
$query = $this->db->query($sql);
return $query;
}
function qryHaJoinTaLaAmPf($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.HisAward ha
LEFT JOIN $this->ea_dbname.TypeAward ta ON ha.typeAwardId=ta.typeAwardId
LEFT JOIN $this->ea_dbname.LevelAward la ON ha.levelAwardId=la.levelAwardId
LEFT JOIN $this->ea_dbname.AlumniMain am ON ha.alumniId=am.alumniId
LEFT JOIN $this->ppc_dbname.Prefix pf ON am.prefixId=pf.prefixId
$c1
$c3
$c2";
$query = $this->db->query($sql);
return $query;
}
function qryHaNotSeqIdAndAmId($awardNameT,$awardCompany,$typeAwardId,$levelAwardId,$receiveDate,$seqId,$alumniId) {
$sql = "SELECT *
FROM $this->ea_dbname.HisAward
WHERE awardNameT=?
AND awardCompany=?
AND typeAwardId=?
AND levelAwardId=?
AND receiveDate=?
AND seqId <> ?
AND alumniId=?";
$query = $this->db->query($sql, array($awardNameT,$awardCompany,$typeAwardId,$levelAwardId,$receiveDate,$seqId,$alumniId));
return $query;
}
public function qryHwByCurStdLevAndType($levelAwardId, $levelAwardName, $programAlumniId, $graduateY, $year1, $year2) {
$y1 = ($year1!="") ? $year1-543 : "0000";
$y2 = ($year2!="") ? $year2-543 : "9999";
$cond = "";
$cond.="AND YEAR(receiveDate) BETWEEN $y1 AND $y2";
$i = 0;
$arr = array();
$sql = "SELECT *
FROM $this->ea_dbname.TypeAward";
$query = $this->db->query($sql);
if($query->num_rows()) {
foreach($query->result() as $row_query) {
$sql_countAm = "SELECT *
FROM $this->ea_dbname.HisAward ha
INNER JOIN $this->ea_dbname.AlumniMain am ON ha.alumniId=am.alumniId
INNER JOIN $this->ea_dbname.TypeAward ta ON ha.typeAwardId=ta.typeAwardId
WHERE ta.typeAwardId=$row_query->typeAwardId
AND ha.levelAwardId=?
AND am.amProgramAlumni=?
AND am.graduateYear=?
$cond
GROUP BY ha.alumniId";
$query_countAm = $this->db->query($sql_countAm,array($levelAwardId, $programAlumniId, $graduateY));
$arr[$i]['level'] = ($i==0) ? $levelAwardName : "";
$arr[$i]['typeaward'] = $row_query->typeAwardNameT;
$arr[$i]['count'] = $query_countAm->num_rows();
$arr[$i]['url'] = "{'levelAward':".$levelAwardId.", 'typeAward':".$row_query->typeAwardId.", 'programAlumniId':".$programAlumniId.", 'graduateY':".$graduateY."}";
$i++;
}
}
return $arr;
}
public function qryHwByStdLevAndType($levelAwardId, $levelAwardName, $year1, $year2, $studentCode) {
$y1 = ($year1!="") ? $year1-543 : "0000";
$y2 = ($year2!="") ? $year2-543 : "9999";
$cond = "";
$cond.="AND YEAR(receiveDate) BETWEEN $y1 AND $y2";
$i = 0;
$arr = array();
$sql = "SELECT *
FROM $this->ea_dbname.TypeAward";
$query = $this->db->query($sql);
if($query->num_rows()) {
foreach($query->result() as $row_query) {
$sql_countAm = "SELECT *
FROM $this->ea_dbname.HisAward ha
INNER JOIN $this->ea_dbname.AlumniMain am ON ha.alumniId=am.alumniId
INNER JOIN $this->ea_dbname.TypeAward ta ON ha.typeAwardId=ta.typeAwardId
WHERE ta.typeAwardId=$row_query->typeAwardId
AND ha.levelAwardId=?
AND am.studentCode=?
$cond
GROUP BY ha.alumniId";
$query_countAm = $this->db->query($sql_countAm,array($levelAwardId, $studentCode));
$arr[$i]['level'] = ($i==0) ? $levelAwardName : "";
$arr[$i]['typeaward'] = $row_query->typeAwardNameT;
$arr[$i]['count'] = $query_countAm->num_rows();
$arr[$i]['url'] = "{'levelAward':".$levelAwardId.", 'typeAward':".$row_query->typeAwardId.", 'stdCode':".$studentCode."}";
$i++;
}
}
return $arr;
} // add your functions here
} // end class M_hisaward
?>
|