Viewing file:  sa_member_model.php (12.06 KB) -rwxr-xr-x
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
include_once("da_sa_member.php");
class Sa_member_model extends Da_sa_member {
/*
* 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 sa_member
$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 get_by_key_2($withSetAttributeValue=FALSE) {
$sql = "SELECT *
FROM $this->sa_dbname.sa_member
INNER JOIN $this->rg_dbname.rg_Student ON mb_std_id = stdId
WHERE mb_id = ?";
$query = $this->db->query($sql, array($this->mb_id));
if ( $withSetAttributeValue ) {
$this->row2attribute( $query->row() );
} else {
return $query ;
}
}
function get_by_mpj() {
$sql = "SELECT *
FROM $this->sa_dbname.sa_member
WHERE mb_mpj_id = ?";
$query = $this->db->query($sql, array($this->mb_mpj_id));
return $query;
}
function get_by_acp() {
$sql = "SELECT *
FROM $this->sa_dbname.sa_member
WHERE mb_acp_id = ?";
$query = $this->db->query($sql, array($this->mb_acp_id));
return $query;
}
function get_by_smo() {
$sql = "SELECT *
FROM $this->sa_dbname.sa_member
WHERE mb_smo_id = ? OR mb_cl_id = ?";
$query = $this->db->query($sql, array($this->mb_smo_id, $this->mb_smo_id));
return $query;
}
function get_smo() {
$sql = "SELECT *
FROM $this->sa_dbname.sa_member
INNER JOIN $this->sa_dbname.sa_smo_club ON mb_smo_id = sc_id
WHERE mb_std_id = ?
ORDER BY mb_year,
mb_fr_date,
mb_to_date";
$query = $this->db->query($sql, array($this->mb_std_id));
return $query;
}
function get_cl() {
$sql = "SELECT *
FROM $this->sa_dbname.sa_member
INNER JOIN $this->sa_dbname.sa_smo_club ON mb_cl_id = sc_id
WHERE mb_std_id = ?
ORDER BY mb_year,
mb_fr_date,
mb_to_date";
$query = $this->db->query($sql, array($this->mb_std_id));
return $query;
}
function get_mpj() {
$sql = "SELECT *
FROM $this->sa_dbname.sa_member
INNER JOIN $this->sa_dbname.sa_detail_project ON mb_mpj_id = dpj_id
WHERE mb_std_id = ?
AND mb_acp_id IS NULL
ORDER BY mb_year,
mb_fr_date,
mb_to_date";
$query = $this->db->query($sql, array($this->mb_std_id));
return $query;
}
function get_acp() {
/*$sql = "SELECT *
FROM $this->sa_dbname.sa_member
INNER JOIN $this->sa_dbname.sa_activity_project ON mb_acp_id = acp_id
INNER JOIN $this->sa_dbname.sa_main_project ON acp_mpj_id = mpj_id
WHERE mb_std_id = ?
ORDER BY mb_year,
mb_fr_date,
mb_to_date";*/
$sql = "SELECT acp.*, mb.*, acp.dpj_sub_name AS acp_name, mpj.dpj_sub_name AS mpj_name
FROM $this->sa_dbname.sa_member mb
INNER JOIN $this->sa_dbname.sa_detail_project acp ON mb_acp_id = acp.dpj_id
INNER JOIN $this->sa_dbname.sa_detail_project mpj ON acp.dpj_mpj_id = mpj.dpj_id
WHERE mb_std_id = ?
ORDER BY mb_year,
mb_fr_date,
mb_to_date";
$query = $this->db->query($sql, array($this->mb_std_id));
return $query;
}
function get_duplicate() {
$cond1 = ($this->mb_acp_id) ? " = ".$this->mb_acp_id : " IS NULL";
$cond2 = '';
if ($this->mb_id) {
$cond2 = " AND mb_id <> ".$this->mb_id;
}
$sql = "SELECT *
FROM $this->sa_dbname.sa_member
WHERE mb_std_id = ?
AND mb_to_date >= ?
AND mb_fr_date <= ?
AND (
mb_smo_id = ?
OR mb_cl_id = ?
OR (mb_mpj_id = ? AND mb_acp_id ".$cond1.")
)".$cond2;
$query = $this->db->query($sql, array($this->mb_std_id, $this->mb_fr_date, $this->mb_to_date, $this->mb_smo_id, $this->mb_cl_id, $this->mb_mpj_id));
return $query;
}
function get_by_cond($fr_date, $to_date, $year='', $stdCode='', $smo_id='', $cl_id='', $mpj_id='', $acp_id='') {
$cond = '';
if ($year) {
$cond .= " AND mb_year = '$year'";
}
if ($stdCode) {
$cond .= " AND stdCode LIKE '%$stdCode%'";
}
if ($smo_id) {
$cond .= " AND mb_smo_id = '$smo_id'";
}
if ($cl_id) {
$cond .= " AND mb_cl_id = '$cl_id'";
}
if ($mpj_id) {
$cond .= " AND mb_mpj_id = '$mpj_id'";
}
if ($acp_id) {
$cond .= " AND mb_acp_id = '$acp_id'";
}
/*$sql = "SELECT *,
smo.sc_name AS smo_name,
cl.sc_name AS cl_name,
mpj.dpj_sub_name AS mpj_name,
acp.dpj_sub_name AS acp_name
FROM $this->sa_dbname.sa_member
INNER JOIN $this->rg_dbname.rg_Student ON mb_std_id = stdId
INNER JOIN $this->ppc_dbname.Prefix ON stdPfId = prefixId
LEFT JOIN $this->sa_dbname.sa_smo_club smo ON mb_smo_id = smo.sc_id
LEFT JOIN $this->sa_dbname.sa_smo_club cl ON mb_cl_id = cl.sc_id
LEFT JOIN $this->sa_dbname.sa_detail_project mpj ON mb_mpj_id = mpj.dpj_id
LEFT JOIN $this->sa_dbname.sa_detail_project acp ON mb_acp_id = acp.dpj_id
WHERE (mb_to_date >= ?
AND mb_fr_date <= ?) ".$cond."
ORDER BY stdCode,
mb_fr_date";*/
$sql = "SELECT *,
smo.sc_name AS smo_name,
cl.sc_name AS cl_name,
mpj.dpj_sub_name AS mpj_name,
acp.dpj_sub_name AS acp_name
FROM $this->sa_dbname.sa_member
INNER JOIN $this->rg_dbname.rg_Student ON mb_std_id = stdId
INNER JOIN $this->ppc_dbname.Prefix ON stdPfId = prefixId
LEFT JOIN $this->sa_dbname.sa_smo_club smo ON mb_smo_id = smo.sc_id
LEFT JOIN $this->sa_dbname.sa_smo_club cl ON mb_cl_id = cl.sc_id
LEFT JOIN $this->sa_dbname.sa_detail_project mpj ON mb_mpj_id = mpj.dpj_id
LEFT JOIN $this->sa_dbname.sa_detail_project acp ON mb_acp_id = acp.dpj_id
WHERE 1=1 ".$cond."
ORDER BY stdCode,
mb_fr_date";
$query = $this->db->query($sql, array($fr_date, $to_date));
//echo $this->db->last_query();
return $query;
}
function get_by_cond2(){
$cond = '';
$cond .= ($this->mb_smo_id!='')? "AND mb_smo_id = $this->mb_smo_id":'';
$cond .= ($this->mb_cl_id!='')? "AND mb_smo_id = $this->mb_cl_id":'';
$sql = "SELECT *, smo.sc_name AS smo_name, cl.sc_name AS cl_name, dpj.dpj_sub_name AS mpj_name,
acp.dpj_sub_name AS acp_name
FROM $this->sa_dbname.sa_member
INNER JOIN $this->rg_dbname.rg_Student ON mb_std_id = stdId
INNER JOIN $this->ppc_dbname.Prefix ON stdPfId = prefixId
LEFT JOIN $this->sa_dbname.sa_smo_club smo ON mb_smo_id = smo.sc_id
LEFT JOIN $this->sa_dbname.sa_smo_club cl ON mb_cl_id = cl.sc_id
LEFT JOIN $this->sa_dbname.sa_detail_project dpj ON mb_mpj_id = dpj.dpj_id
LEFT JOIN $this->sa_dbname.sa_detail_project acp ON mb_acp_id = acp.dpj_id
WHERE (mb_to_date >= ?
AND mb_fr_date <= ?) ".$cond."
ORDER BY stdCode,
mb_fr_date";
$query = $this->db->query($sql, array($this->mb_fr_date, $this->mb_to_date));
return $query;
}
function get_std_member() {
$sql = "
SELECT *
FROM $this->sa_dbname.sa_member
INNER JOIN $this->sa_dbname.sa_smo_club ON mb_cl_id = sc_id
WHERE sc_type = 2 AND mb_std_id = ?
ORDER BY mb_year, mb_tmId, mb_id
";
$query = $this->db->query($sql, array($this->mb_std_id));
return $query;
}
function get_ct_by_type($withSetAttributeValue=FALSE) {
$sql = "
SELECT tb1.*, (tb1.sc_max_member - ifnull(tb2.num,0)) as num, mb_std_id as std, sa_config.*, ifnull(tb4.num_c,0) as num_c, ifnull(tb2.num,0) as num_rt
FROM $this->sa_dbname.sa_smo_club as tb1
LEFT JOIN ( SELECT count( mb_id ) as num, mb_cl_id
FROM $this->sa_dbname.sa_member
WHERE mb_year =?
AND mb_tmId =?
GROUP BY mb_cl_id
) as tb2 ON tb1.sc_id = tb2.mb_cl_id
LEFT JOIN ( SELECT mb_std_id, mb_cl_id
FROM $this->sa_dbname.sa_member
WHERE mb_year =?
AND mb_tmId =?
AND mb_std_id =?
) as tb3 ON tb1.sc_id = tb3.mb_cl_id
LEFT JOIN ( SELECT count(mb_std_id) as num_c
FROM $this->sa_dbname.sa_member
WHERE mb_year =?
AND mb_tmId =?
AND mb_std_id =?
AND mb_cl_id IS NOT NULL
GROUP BY mb_std_id
) as tb4 ON 1=1
LEFT JOIN sa_config ON 1=1
WHERE tb1.sc_type =2
";
$query = $this->db->query($sql, array($this->mb_year, $this->mb_tmId, $this->mb_year, $this->mb_tmId, $this->mb_std_id,$this->mb_year, $this->mb_tmId, $this->mb_std_id));
return $query ;
}
function search_dpj_con($type=''){
$cond = '';
$cond .= ($this->mb_year!='')? " AND mb_year = $this->mb_year":'';
if ($type==1){
$cond .= " AND mb_acp_id IS NULL";
} else if ($type==2) {
$cond .= " AND mb_acp_id IS NOT NULL";
}
$cond .= ($this->mb_mpj_id!='')? " AND mb_mpj_id = $this->mb_mpj_id":'';
$cond .= ($this->mb_acp_id!='')? " AND mb_acp_id = $this->mb_acp_id":'';
$cond .= ($this->mb_std_id!='')? " AND (stdCode LIKE '%$this->mb_std_id%' OR stdName LIKE '%$this->mb_std_id%' OR stdSurname LIKE '%$this->mb_std_id%')":'';
$sql = "SELECT stdCode, prefixName, stdName, stdSurname, mb_year, mb_tmId, mb_syId, mb_fr_date, dpj.dpj_sub_name AS dpj_name, acp.dpj_sub_name AS acp_name
FROM $this->sa_dbname.sa_member
INNER JOIN $this->rg_dbname.rg_Student ON mb_std_id = stdId
INNER JOIN $this->ppc_dbname.Prefix ON stdPfId = prefixId
LEFT JOIN $this->sa_dbname.sa_detail_project dpj ON mb_mpj_id = dpj.dpj_id
LEFT JOIN $this->sa_dbname.sa_detail_project acp ON mb_acp_id = acp.dpj_id
WHERE mb_smo_id IS NULL AND mb_cl_id IS NULL $cond
ORDER BY mb_year, mb_tmId, mb_syId, mb_mpj_id, mb_acp_id";
$query = $this->db->query($sql);
return $query;
}
function search_sc_con($type=''){
$cond = '';
$cond .= ($this->mb_year!='')? " AND mb_year = $this->mb_year":'';
if ($type==1){
$cond .= " AND mb_smo_id IS NOT NULL";
} else if ($type==2) {
$cond .= " AND mb_cl_id IS NOT NULL";
}
$cond .= ($this->mb_smo_id!='')? " AND mb_smo_id = $this->mb_smo_id":'';
$cond .= ($this->mb_cl_id!='')? " AND mb_cl_id = $this->mb_cl_id":'';
$cond .= ($this->mb_std_id!='')? " AND (stdCode LIKE '%$this->mb_std_id%' OR stdName LIKE '%$this->mb_std_id%' OR stdSurname LIKE '%$this->mb_std_id%')":'';
$sql = "SELECT stdCode, prefixName, stdName, stdSurname, mb_year, mb_tmId, mb_syId, mb_fr_date, smo.sc_name AS smo_name, cl.sc_name AS cl_name
FROM $this->sa_dbname.sa_member
INNER JOIN $this->rg_dbname.rg_Student ON mb_std_id = stdId
INNER JOIN $this->ppc_dbname.Prefix ON stdPfId = prefixId
LEFT JOIN $this->sa_dbname.sa_smo_club smo ON mb_smo_id = smo.sc_id
LEFT JOIN $this->sa_dbname.sa_smo_club cl ON mb_cl_id = cl.sc_id
LEFT JOIN $this->sa_dbname.sa_main_project ON mb_mpj_id = mpj_id
LEFT JOIN $this->sa_dbname.sa_activity_project ON mb_acp_id = acp_id
WHERE mb_mpj_id IS NULL AND mb_acp_id IS NULL $cond
ORDER BY mb_year, mb_tmId, mb_syId, mb_mpj_id, mb_acp_id";
$query = $this->db->query($sql);
return $query;
}
} // end class Sa_member_model
?>
|