Viewing file:  mo_rg_timetable.php (25.21 KB) -rwxr-xr-x
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
include_once("da_rg_TimeTable.php");
class mo_rg_timetable extends Da_rg_TimeTable {
/**
* หน้าที่ของฟังก์ชั่น คือ ต่อสตริงของ sql->order ในรูปแบบ CONVERT
*
* @access public
* @param array condition โดยรูปแบบของ array จะอยู่ในรูป condition('key' => value)
* @return string con โดยอยู่ในรูปแบบ ORDER('$value' USING TIS620)
* @todo use
*/
public function checkOrderBy_2($condition="") {
$con = "";
if($condition) {
$con .= "ORDER BY ";
foreach($condition as $key => $value) {
$con .= "CONVERT($value USING TIS620),";
}
}
return $con;
}
/**
* หน้าที่ของฟังก์ชั่น คือ หารายวิชาที่ถูกจัดตารางสอน
*
* @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_TimeTable.*
* @todo use
*/
public function qryTt($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_TimeTable
$c1
$c2
$c3";
$query = $this->db->query($sql);
return $query;
}
function getSumHr($condition="",$order="") {
$where = $this->checkCondition($condition);
$c1= substr($where, 0, -3);
$sql = "SELECT IFNULL(SUM(ttPrdToTime - ttPrdFrTime + 1)) AS num
FROM $this->rg_dbname.rg_TimeTable
$c1";
$query = $this->db->query($sql);
return $query;
}
function qryTtGroupPrsId($condition="",$order="") {
$where = $this->checkCondition($condition);
$order = $this->checkOrderBy($order);
$c1= substr($where, 0, -3);
$c2= substr($order, 0, -1);
$sql = "SELECT ttPrsId
FROM $this->rg_dbname.rg_TimeTable
$c1
$c2
GROUP BY ttPrsId";
$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 query >= 1 rows () : rg_TimeTable.*, peoplecenter.Person, peoplecenter.Prefix
* @todo use
*/
public function qryTtJoinPeopleCenter($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 *, 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_TimeTable
INNER JOIN $this->rg_dbname.rg_Person ON ttPrsId = prsId
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
$c1
$c2
$c3";
$query = $this->db->query($sql);
return $query;
}
/**
* หน้าที่ของฟังก์ชั่น คือ หาอาจารย์ผู้สอนประจำรายวิชา
*
* @access public
* @param array condition
* @return query >= 1 rows ()
*/
// public function qryTeacherCo($condition="") {
// $qry = $this->checkCondition($condition);
// $con= substr($qry, 0, -3);
//
// $sql = "SELECT rg_TimeTable.*, rg_CourseOpen.coId , pp.personId, pp.prefixId, pp.personCode, pp.fName, pp.lName, pf.prefixId, pf.prefixName
// FROM $this->rg_dbname.rg_TimeTable
// LEFT JOIN $this->rg_dbname.rg_CourseOpen ON ttCoId=coId
// LEFT JOIN $this->ppc_dbname.Person AS pp ON ttPrsId=pp.personId
// LEFT JOIN $this->ppc_dbname.Prefix AS pf ON pp.prefixId=pf.prefixId
// $con";
// $query = $this->db->query($sql);
// return $query;
// }
public function qryTtJoinCo($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_TimeTable
LEFT JOIN $this->rg_dbname.rg_CourseOpen ON ttCoId=coId
$c1
$c2
$c3";
$query = $this->db->query($sql);
//echo $sql."<hr>";
return $query;
}
// ฟังก์ชั่น หลังจากนี้เป็นฟังก์ชั่น ก่อนวันที่ 6 ตุลาคม 2553
/*
* 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->rg_dbname.rg_TimeTable
$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
/**
* หน้าที่ของฟังก์ชั่น คือ หาอาจารย์ผู้สอน
*
* @access public
* @param int course open id
* @return query >= 1 rows ()
* @todo เพิ่ม อาจารย์ประจำ อาจารย์พิเศษ
* @todo พิมพ์ v_printSendGradePDF
*/
public function qryTeacher($clIdStr) {
$sql = "SELECT GROUP_CONCAT(IF(prsItId=1, CONCAT(pf.prefixName,ps.fName,' ',ps.lName),
CONCAT(pfo.prefixName,pso.fName,' ',pso.lName)) SEPARATOR ', ') AS prsName
FROM (
SELECT ttPrsId
FROM $this->rg_dbname.rg_TimeTable
WHERE ttCoId IN (".$clIdStr.")
GROUP BY ttPrsId
) AS tt
INNER JOIN $this->rg_dbname.rg_Person ON tt.ttPrsId = prsId
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";
$query = $this->db->query($sql);
return $query;
}
function qryTeacher2($clIdStr) {
$sql = "SELECT *, 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 (
SELECT ttPrsId
FROM $this->rg_dbname.rg_TimeTable
WHERE ttCoId IN ($clIdStr)
GROUP BY ttPrsId
) AS tt
INNER JOIN $this->rg_dbname.rg_Person ON tt.ttPrsId = prsId
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";
$query = $this->db->query($sql);
return $query;
}
/**
* หน้าที่ของฟังก์ชั่น คือ หาอาจารย์ผู้สอน
*
* @access public
* @param int course open id
* @return query >= 1 rows ()
*/
// public function qryTeacherNogrpConcat($ttCoId) {
// $sql = "SELECT ttId, ttPrsId, CONCAT(pf.prefixName,pp.fname,' ',pp.lname) AS prsName
// FROM $this->rg_dbname.rg_TimeTable
// LEFT JOIN $this->ppc_dbname.Person AS pp ON ttPrsId=pp.personId
// LEFT JOIN $this->ppc_dbname.Prefix AS pf ON pp.prefixId=pf.prefixId
// WHERE ttCoId=?";
// $query = $this->db->query($sql,array($ttCoId));
// return $query;
// }
//use ตารางสอนรวมประจำภาคการศึกษา
// function qryTTJOINRoom($coId, $dy, $pr) {
// $sql = "SELECT *, ttId, ttPrsId, CONCAT(pf.prefixName,pp.fname,' ',pp.lname) AS prsName
// FROM $this->rg_dbname.rg_TimeTable
// LEFT JOIN $this->ppc_dbname.spc_Place ON plHwId = ttRmId
// LEFT JOIN $this->ppc_dbname.Person AS pp ON ttPrsId=pp.personId
// LEFT JOIN $this->ppc_dbname.Prefix AS pf ON pp.prefixId=pf.prefixId
// WHERE ttCoId =?
// AND ttWdAcYWeekNo =?
// AND ? BETWEEN ttPrdFrTime AND ttPrdToTime
// ";
// $query = $this->db->query($sql,array($coId, $dy, $pr));
// $this->load->database('rg',TRUE);
// return $query;
// }
/**
* หน้าที่ของฟังก์ชั่น คือ หาคาบที่มีการจัดการเรียนการสอนในช่วงเวลาตามเงื่อนไข
*
* @access public
* @param array condition โดยรูปแบบของ array จะอยู่ในรูป condition('key' => value) ไว้สำหรับระบุเงื่อนไขของคำสั่ง SELECT
* @param frTime: รหัสของเวลาเริ่มต้น
* @param toTime: รหัสของเวลาสิ้นสุด
* @return query >= 1 rows () rg_CourseOpen.*, rg_TimeTable.*
*/
function qryTtBetweenFrTimeAndToTime($condition="",$frTime,$toTime) {
$where = $this->checkCondition($condition);
$c1= substr($where, 0, -3);
/*$sql = "SELECT *
FROM rg_CourseOpen
LEFT JOIN rg_TimeTable ON coId=ttCoId
WHERE coAcY=?
AND ttWdAcYWeekNo=?
AND ttDyId=?
AND ttPrdFrTime>=?
AND ttPrdToTime<=?";*/
$sql = "SELECT *
FROM $this->rg_dbname.rg_CourseOpen
LEFT JOIN $this->rg_dbname.rg_TimeTable ON coId=ttCoId
$c1
AND ttPrdFrTime>=?
AND ttPrdToTime<=?";
$query = $this->db->query($sql,array($toTime,$frTime));
return $query;
}
public function qryRm($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->ppc_dbname.spc_Place
$c1
$c3
$c2";
$query = $this->db->query($sql);
return $query;
}
function GetMaxOVLByAcYAndPgIdStrAndStYAndAcYWeekNoAndDyId($acY, $curId="", $syId="", $ttWdAcYWeekNo, $dyId){
if($curId=="NULL" or $curId==""){
$cur = " and coCurId IS NULL";
}else{
$cur = "and coCurId ='$curId'";
}
if($syId==""){
$sy = NULL;
}else{
$sy = "and coSyId ='$syId'";
}
$sql = "select IFNULL(max(numOvl), 0) as num
from (select t1.ttId, count(*) as numOvl
from $this->rg_dbname.rg_CourseOpen c
inner join $this->rg_dbname.rg_TimeTable t1 on c.coId=t1.ttCoId
inner join (select t.*
from $this->rg_dbname.rg_CourseOpen c, $this->rg_dbname.rg_TimeTable t
where coAcY='".$acY."' ".$cur." ".$sy." and c.coId=t.ttCoId
) as t2 on t1.ttWdAcYWeekNo=t2.ttWdAcYWeekNo and t1.ttDyId=t2.ttDyId
where coAcY='".$acY."' ".$cur." ".$sy." and t1.ttWdAcYWeekNo='".$ttWdAcYWeekNo."' and t1.ttDyId='".$dyId."'
and t1.ttPrdFrTime between t2.ttPrdFrTime and t2.ttPrdToTime
group by t1.ttId
) as tt";
$query = $this->db->query($sql);
if($query->num_rows()>0){
return $query->row()->num;
}else{
return 1;
}
}
function GetMaxOVLByAcYAndRmIdAndAcYWeekNoAndDyId($acY, $rmId="", $acYWeekNo, $dyId){
$sql = "select max(numOvl) as num
from (select t1.ttId, count(*) as numOvl
from $this->rg_dbname.rg_CourseOpen c
inner join $this->rg_dbname.rg_TimeTable t1 on c.coId=t1.ttCoId
inner join (select t.*
from $this->rg_dbname.rg_CourseOpen c, $this->rg_dbname.rg_TimeTable t
where coAcY='$acY' and c.coId=t.ttCoId
) as t2 on t1.ttRmId=t2.ttRmId
and t1.ttWdAcYWeekNo=t2.ttWdAcYWeekNo and t1.ttDyId=t2.ttDyId
where coAcY='$acY' and t1.ttRmId='$rmId'
and t1.ttWdAcYWeekNo='$acYWeekNo' and t1.ttDyId='$dyId'
and t1.ttPrdFrTime between t2.ttPrdFrTime and t2.ttPrdToTime
group by t1.ttId
) as tt";
$query = $this->db->query($sql);
if($query->num_rows()>0){
return $query->row()->num;
}else{
return 1;
}
}
function GetMaxOVLByAcYAndEpMepIdAndEpSepIdAndAcYWeekNoAndDyId($acY, $epMepId, $epSepId, $acYWeekNo, $dyId){
$sql = "select max(numOvl) as num
from (select t1.ttId, count(*) as numOvl
from $this->rg_dbname.rg_CourseOpen c
inner join $this->rg_dbname.rg_TimeTable t1 on c.coId=t1.ttCoId
inner join (select t.*
from $this->rg_dbname.rg_CourseOpen c, $this->rg_dbname.rg_TimeTable t
where coAcY='$acY' and c.coId=t.ttCoId
) as t2 on t1.ttEtpMepId=t2.ttEtpMepId and t1.ttEtpSepId=t2.ttEtpSepId
and t1.ttWdAcYWeekNo=t2.ttWdAcYWeekNo and t1.ttDyId=t2.ttDyId
where coAcY='$acY'
and t1.ttEtpMepId='$epMepId' and t1.ttEtpSepId='$epSepId'
and t1.ttWdAcYWeekNo='$acYWeekNo' and t1.ttDyId='$dyId'
and t1.ttPrdFrTime between t2.ttPrdFrTime and t2.ttPrdToTime
group by t1.ttId
) as tt";
$query = $this->db->query($sql);
if($query->num_rows()>0){
return $query->row()->num;
}else{
return 1;
}
}
function GetMaxOVLByAcYAndOfIdAndAcYWeekNoAndDyId($acY, $ofId, $acYWeekNo, $dyId){
$sql = "select max(numOvl) as num
from (select t1.ttId, count(*) as numOvl
from $this->rg_dbname.rg_CourseOpen c
inner join $this->rg_dbname.rg_TimeTable t1 on c.coId=t1.ttCoId
inner join (select t.*
from $this->rg_dbname.rg_CourseOpen c, $this->rg_dbname.rg_TimeTable t
where coAcY='$acY' and c.coId=t.ttCoId
) as t2 on t1.ttPrsId=t2.ttPrsId
and t1.ttWdAcYWeekNo=t2.ttWdAcYWeekNo and t1.ttDyId=t2.ttDyId
where coAcY='$acY'
and t1.ttPrsId='$ofId'
and t1.ttWdAcYWeekNo='$acYWeekNo' and t1.ttDyId='$dyId'
and t1.ttPrdFrTime between t2.ttPrdFrTime and t2.ttPrdToTime
group by t1.ttId
) as tt";
$query = $this->db->query($sql);
if($query->num_rows()>0){
return $query->row()->num;
}else{
return 1;
}
}
function GetMaxOVLByAcYAndCoIdAndDyId($acY, $coId, $dyId){
$sql = "select max(numOvl) as num
from (select t1.ttId, count(*) as numOvl
from $this->rg_dbname.rg_CourseOpen c
inner join $this->rg_dbname.rg_TimeTable t1 on c.coId=t1.ttCoId
inner join (select t.*
from $this->rg_dbname.rg_CourseOpen c, $this->rg_dbname.rg_TimeTable t
where coAcY='$acY' and coCrsId='$coId' and c.coId=t.ttCoId
) as t2 on t1.ttDyId=t2.ttDyId
where coAcY='$acY'
and coCrsId='$coId'
and t1.ttDyId='$dyId'
and t1.ttPrdFrTime between t2.ttPrdFrTime and t2.ttPrdToTime
group by t1.ttId
) as tt";
$query = $this->db->query($sql);
if($query->num_rows()>0){
return $query->row()->num;
}else{
return 1;
}
}
function GetMaxOVLByAcYAndCoIdAndClIdAndDyId($acY, $coId, $clId, $dyId){
$sql = "select max(numOvl) as num
from (select t1.ttId, count(*) as numOvl
from $this->rg_dbname.rg_CourseOpen c
inner join $this->rg_dbname.rg_TimeTable t1 on c.coId=t1.ttCoId
inner join (select t.*
from $this->rg_dbname.rg_CourseOpen c, $this->rg_dbname.rg_TimeTable t
where coCrsId='$coId' and coId='$clId' and c.coId=t.ttCoId
) as t2 on t1.ttDyId=t2.ttDyId
where coAcY='$acY'
and coCrsId='$coId' and coId='$clId'
and t1.ttDyId='$dyId'
and t1.ttPrdFrTime between t2.ttPrdFrTime and t2.ttPrdToTime
group by t1.ttId
) as tt";
$query = $this->db->query($sql);
if($query->num_rows()>0){
return $query->row()->num;
}else{
return 1;
}
}
//test Karn select IFNULL(max(numOvl), 0) as num and coSection='$sec'
function GetMaxOVLByAcYAndCoIdAndClIdAndDyId2($acY, $coId, $cur,$sec, $dyId){
if($cur==""){
$c = "and coCurId IS NULL";
}else{
$c = "and coCurId='$cur'";
}
$sql = "select max(numOvl) as num
from (select t1.ttId, count(*) as numOvl
from $this->rg_dbname.rg_CourseOpen c
inner join $this->rg_dbname.rg_TimeTable t1 on c.coId=t1.ttCoId
inner join (select t.*
from $this->rg_dbname.rg_CourseOpen c, $this->rg_dbname.rg_TimeTable t
where coCrsId='$coId' ".$c." and coSection='$sec' and c.coId=t.ttCoId
) as t2 on t1.ttDyId=t2.ttDyId
where coAcY='$acY'
and coCrsId='$coId' ".$c." and coSection='$sec'
and t1.ttDyId='$dyId'
and t1.ttPrdFrTime between t2.ttPrdFrTime and t2.ttPrdToTime
group by t1.ttId
) as tt";
$query = $this->db->query($sql);
if($query->num_rows()>0){
return $query->row()->num;
}else{
return 1;
}
}
function GetMaxOVLByStIdAndAcYAndAcYWeekNoAndDyId($stId, $acY, $acYWeekNo, $dyId){
$sql = "select max(numOvl) as num
from (select t1.ttId, count(*) as numOvl
from $this->rg_dbname.rg_RegistDetails e
inner join $this->rg_dbname.rg_TimeTable t1 on e.rdCoId=t1.ttCoId
inner join (select t.*
from $this->rg_dbname.rg_RegistDetails e, $this->rg_dbname.rg_TimeTable t
where rdStdId ='$stId' and rdAcY='$acY' and e.rdCoId=t.ttCoId
) as t2 on t1.ttWdAcYWeekNo=t2.ttWdAcYWeekNo and t1.ttDyId=t2.ttDyId
where rdStdId ='$stId' and rdAcY='$acY'
and t1.ttWdAcYWeekNo='$acYWeekNo' and t1.ttDyId='$dyId'
and t1.ttPrdFrTime between t2.ttPrdFrTime and t2.ttPrdToTime
group by t1.ttId
) as tt";
$query = $this->db->query($sql);
if($query->num_rows()>0){
return $query->row()->num;
}else{
return 1;
}
}
function rdJointt($stId, $acY, $acYWeekNo, $dyId){
$sql = "SELECT * FROM ($this->rg_dbname.rg_RegistDetails rd inner join $this->rg_dbname.rg_TimeTable tt on rd.rdCoId=tt.ttCoId) inner join $this->rg_dbname.rg_CourseOpen co on rd.rdCoId=co.coId where rdStdId=? and rdAcY = ? and ttWdAcYWeekNo = ? and ttDyId=?";
$query = $this->db->query($sql, array($stId, $acY, $acYWeekNo, $dyId));
return $query;
}
function getCountJoinCo($condition="") {
$where = $this->checkCondition($condition);
$c1= substr($where, 0, -3);
$sql = "SELECT IFNULL(COUNT(*), 0) AS num
FROM $this->rg_dbname.rg_TimeTable
INNER JOIN $this->rg_dbname.rg_CourseOpen ON ttCoId = coId
$c1";
$query = $this->db->query($sql);
return $query->row()->num;
}
function qryTtByCoIdGroupAllOrderAcYWeekNoDyIdFrPr($coId) {
$sql = "SELECT ttRmId, ttEtpMepId, ttEtpSepId, ttWdAcYWeekNo, ttDyId, ttPrdFrTime, ttPrdToTime, ttStId, ttTitle,
COUNT(ttPrsId) AS ttPrsId FROM $this->rg_dbname.rg_TimeTable
WHERE ttCoId = ?
GROUP BY ttRmId, ttEtpMepId, ttEtpSepId, ttWdAcYWeekNo, ttDyId, ttPrdFrTime, ttPrdToTime, ttStId, ttTitle
ORDER BY ttWdAcYWeekNo, ttDyId, ttPrdFrTime";
$query = $this->db->query($sql, array($coId));
return $query;
}
function qryTtByAcYCrsIdGroupAllOrderAcYWeekNoDyIdFrPr($acY, $crsId) {
$sql = "SELECT ttCoId, ttRmId, ttEtpMepId, ttEtpSepId, ttWdAcYWeekNo, ttDyId, ttPrdFrTime, ttPrdToTime,
ttStId, ttTitle, COUNT(ttPrsId) AS ttPrsId
FROM $this->rg_dbname.rg_CourseOpen
INNER JOIN $this->rg_dbname.rg_TimeTable ON coId = ttCoId
WHERE coAcY = ?
AND coCrsId = ?
GROUP BY ttCoId, ttRmId, ttEtpMepId, ttEtpSepId, ttWdAcYWeekNo, ttDyId, ttPrdFrTime, ttPrdToTime, ttStId, ttTitle
ORDER BY ttWdAcYWeekNo, ttDyId, ttPrdFrTime, CAST(coSection AS SIGNED)";
$query = $this->db->query($sql, array($acY, $crsId));
return $query;
}
function qryTtByAll($coId, $rmId, $etpMepId, $etpSepId, $acYWeekNo, $dyId, $frPr, $toPr, $stId, $title) {
$sql = "SELECT * FROM $this->rg_dbname.rg_TimeTable
WHERE ttCoId = ? AND ttRmId = ? AND ttEtpMepId IS NULL AND ttEtpSepId IS NULL
AND ttWdAcYWeekNo = ? AND ttDyId = ?
AND ttPrdFrTime = ? AND ttPrdToTime = ?
AND ttStId = ? AND ttTitle = ?
UNION
SELECT * FROM $this->rg_dbname.rg_TimeTable
WHERE ttCoId = ? AND ttRmId IS NULL AND ttEtpMepId = ? AND ttEtpSepId = ?
AND ttWdAcYWeekNo = ? and ttDyId = ?
AND ttPrdFrTime = ? AND ttPrdToTime = ?
AND ttStId = ? AND ttTitle = ?";
$query = $this->db->query($sql, array($coId, $rmId, $acYWeekNo, $dyId, $frPr, $toPr, $stId, $title, $coId, $etpMepId, $etpSepId, $acYWeekNo, $dyId, $frPr, $toPr, $stId, $title));
return $query;
}
//use
function rsTTDuplicateTime($acY, $tmId, $dyId, $rmId, $sDate, $eDate) {
$sql = "SELECT tt.*, co.*, crs.*, d.dyName, ppcP.*, p1.prdFrTime, p2.prdToTime
FROM $this->rg_dbname.rg_TimeTable AS tt
LEFT JOIN $this->rg_dbname.rg_CourseOpen AS co ON ttCoId=coId
LEFT JOIN $this->rg_dbname.rg_Course AS crs ON coCrsId=crsId
LEFT JOIN $this->rg_dbname.rg_Day AS d ON ttDyId=dyId
LEFT JOIN $this->ppc_dbname.spc_Place ppcP ON ttRmId = plHwId
LEFT JOIN $this->rg_dbname.rg_Period AS p1 ON ttPrdFrTime=p1.prdId
LEFT JOIN $this->rg_dbname.rg_Period AS p2 ON ttPrdToTime=p2.prdId
WHERE coAcY=? AND coTmId=?
AND ttDyId =? AND ttRmId=?
AND ttPrdFrTime<=? AND ttPrdToTime>=?
";
$query = $this->db->query($sql,array($acY, $tmId, $dyId, $rmId, $sDate, $eDate));
$this->load->database('rg',TRUE);
return $query;
}
function RSrg_TimeTableByAcYAndSeAndOfIdGroupClIdOrderPgId($acY, $tmId, $prsId){
$sql ="select ttCoId from $this->rg_dbname.rg_CourseOpen c, $this->rg_dbname.rg_TimeTable t
where coAcY='$acY' and coTmId='$tmId' and c.coId=t.ttCoId and ttPrsId='$prsId'
group by ttCoId order by `coCrsId`";
$query = $this->db->query($sql);
return $query;
}
function getTTByUTF8($year,$tmId,$curId,$crsId,$tbwdAcYWeekNo=''){
$cond = ($tbwdAcYWeekNo != '')?"AND ttWdAcYWeekNo='$tbwdAcYWeekNo' ":'';
$sql = "SELECT * FROM $this->rg_dbname.rg_TimeTable LEFT JOIN $this->rg_dbname.rg_CourseOpen ON ttCoId=coId WHERE coAcY='$year' AND coCrsId='$crsId' AND coCurId='$curId' $cond ORDER BY ttWdAcYWeekNo ASC, ttDyId ASC ";
$query = $this->db->query($sql);
return $query;
}
/*
เขียนคำสั่งดึงข้อมูลรายวิชาที่สอนของอาจารย์ย้อนหลัง 3 ปี ให้ระบบจัดการฐานข้อมูลนักวิจัยเรียกใช้
INPUT : ปีการศึกษาปัจจุบัน tmcAcY, รหัสอาจารย์ ttPrsId
OUTPUT : ttPrsId, coAcY, coTmId, crsCode, crsName
*/
function getTTByTmcAcYAndPrsId($tmcAcY, $ttPrsId){
$sql = "SELECT ttPrsId, coAcY, coTmId, crsCode, crsName
FROM $this->rg_dbname.rg_TimeTable
INNER JOIN $this->rg_dbname.rg_CourseOpen ON coId = ttCoId
INNER JOIN $this->rg_dbname.rg_Course ON coCrsId = crsId
WHERE (coAcY BETWEEN ( '$tmcAcY' -2 ) AND '$tmcAcY')
AND ttPrsId = $ttPrsId
GROUP BY coAcY, coTmId, crsId";
$query = $this->db->query($sql);
return $query;
}
} // end class Rg_timetable_model
?>
|