Viewing file:  sa_bmi_model.php (6.66 KB) -rwxr-xr-x
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
include_once("da_sa_bmi.php");
class Sa_bmi_model extends Da_sa_bmi {
/*
* 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_bmi
$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;
}
function get_options2($optional='y') {
$sql = "SELECT * FROM $this->sa_dbname.sa_bmi WHERE '".getNowDate()."' BETWEEN `bmi_fr_date` AND `bmi_to_date` OR `bmi_to_date` like '0000-00-00' GROUP BY `bmi_group`";
$qry = $this->db->query($sql);
if ($optional=='y') $opt[''] = '-----เลือก-----';
foreach ($qry->result() as $row) {
$opt[$row->bmi_group] = $row->bmi_group;
}
return $opt;
}
// add your functions here
function getByGroup($gname){
$sql = "SELECT * FROM $this->sa_dbname.sa_bmi where bmi_group like '$gname'
ORDER BY bmi_min, bmi_max";
$query = $this->db->query($sql);
return $query;
}
function getGroup(){
$sql = "SELECT * FROM $this->sa_dbname.sa_bmi group by `bmi_group` order by bmi_fr_date desc";
$query = $this->db->query($sql);
return $query;
}
function updateByGroup($gname) {
$sql = "UPDATE $this->sa_dbname.sa_bmi
SET bmi_group=?, bmi_fr_date=?, bmi_to_date=?
WHERE bmi_group like '$gname'";
return $this->db->query($sql, array($this->bmi_group, $this->bmi_fr_date, $this->bmi_to_date));
}
function deleteByGroup($gname) {
// if there is no primary key, please remove WHERE clause.
$sql = "DELETE FROM $this->sa_dbname.sa_bmi
WHERE bmi_group like '$gname'";
return $this->db->query($sql, array($this->bmi_id));
}
function qryBmiBetweenMinAndMax($val, $bmi_group, $bmi_id) {
$sql = "SELECT *
FROM $this->sa_dbname.sa_bmi
WHERE ? BETWEEN bmi_min AND bmi_max
AND bmi_group=?
AND bmi_id <> ?";
$query = $this->db->query($sql, array($val, $bmi_group, $bmi_id));
return $query;
}
function qryBmi_minByBmi_group($bmi_group, $val, $bmi_id) {
$sql = "SELECT MIN(bmi_min) AS bmi_min
FROM $this->sa_dbname.sa_bmi
WHERE bmi_group=?
AND bmi_min >= ?
AND bmi_id <> ?";
$query = $this->db->query($sql, array($bmi_group, $val, $bmi_id));
return $query->row()->bmi_min;
}
function qryByBmi_groupAndNotBmi_id($bmi_group, $bmi_id) {
$sql = "SELECT *
FROM $this->sa_dbname.sa_bmi
WHERE bmi_group LIKE ?
AND bmi_id NOT IN ($bmi_id)";
$query = $this->db->query($sql, array($bmi_group));
return $query;
}
function qryByBmiGroupAndBmiName($bmi_group, $bmi_name) {
$sql = "SELECT *
FROM $this->sa_dbname.sa_bmi
WHERE bmi_group=?
AND bmi_name=?";
$query = $this->db->query($sql, array($bmi_group, $bmi_name));
return $query;
}
function qryByBmiGroupAndBmiNameNotId($bmi_group, $bmi_name, $bmi_id) {
$sql = "SELECT *
FROM $this->sa_dbname.sa_bmi
WHERE bmi_group=?
AND bmi_name=?
AND bmi_id<>?";
$query = $this->db->query($sql, array($bmi_group, $bmi_name, $bmi_id));
return $query;
}
function calBmi($bmi_group, $val){
$sql = "SELECT * FROM $this->sa_dbname.sa_bmi WHERE bmi_group like '$bmi_group' and '$val' between `bmi_min` and `bmi_max`";
$query = $this->db->query($sql);
return $query;
}
function getBmi($num, $from, $to){
$sql = "SELECT * FROM $this->sa_dbname.sa_bmi WHERE ( ? between bmi_min AND bmi_max ) AND (? >=bmi_fr_date AND ? <= bmi_to_date)";
$query = $this->db->query($sql, array($num, $from, $to));
if ($query->num_rows() > 0) {
return $query->row()->bmi_id;
} else {
$sql = "SELECT * FROM $this->sa_dbname.sa_bmi WHERE ( ? between bmi_min AND bmi_max ) AND (? >= bmi_fr_date AND bmi_to_date = '0000-00-00')";
$query = $this->db->query($sql, array($num, $from));
if ($query->num_rows() > 0) {
return $query->row()->bmi_id;
} else {
return 0;
}
}
}
function qryBmiByFrDateOrToDate($date) {
$flg = 0;
$sql1 = "SELECT * FROM $this->sa_dbname.sa_bmi
GROUP BY bmi_fr_date, bmi_to_date";
$query1 = $this->db->query($sql1);
if($query1->num_rows()) {
foreach($query1->result() as $row_query1) {
if($row_query1->bmi_to_date=='0000-00-00') {
$to_date = '9999-99-99';
} else {
$to_date = $row_query1->bmi_to_date;
}
/*$sql2 = "SELECT * FROM $this->sa_dbname.sa_bmi
WHERE ? BETWEEN ".$row_query1->bmi_fr_date." AND ".$to_date;*/
$sql2 = "SELECT * FROM $this->sa_dbname.sa_bmi
WHERE ? BETWEEN ? AND ?";
$query2 = $this->db->query($sql2, array($date, $row_query1->bmi_fr_date, $to_date));
if($query2->num_rows) {
$flg = 1;
}
}
}
return $flg;
}
function qryBmiByToDate($toDate) {
$sql = "SELECT * FROM
$this->sa_dbname.sa_bmi
WHERE bmi_to_date=?";
$query = $this->db->query($sql,array($toDate));
if($query->num_rows()) {
return 1;
} else {
return 0;
}
}
function qryBmiByFrDateOrToDateNotGroup($date, $bmi_group) {
$flg = 0;
$sql1 = "SELECT * FROM $this->sa_dbname.sa_bmi
WHERE bmi_group<>?
GROUP BY bmi_fr_date, bmi_to_date";
$query1 = $this->db->query($sql1,array($bmi_group));
if($query1->num_rows()) {
foreach($query1->result() as $row_query1) {
if($row_query1->bmi_to_date=='0000-00-00') {
$to_date = '9999-99-99';
} else {
$to_date = $row_query1->bmi_to_date;
}
/*$sql2 = "SELECT * FROM $this->sa_dbname.sa_bmi
WHERE ? BETWEEN ".$row_query1->bmi_fr_date." AND ".$to_date;*/
$sql2 = "SELECT * FROM $this->sa_dbname.sa_bmi
WHERE ? BETWEEN ? AND ? AND bmi_group<>?";
$query2 = $this->db->query($sql2, array($date, $row_query1->bmi_fr_date, $to_date, $bmi_group));
if($query2->num_rows) {
$flg = 1;
}
}
}
return $flg;
}
function qryBmiByToDateNotGroup($toDate, $bmi_group) {
$sql = "SELECT * FROM
$this->sa_dbname.sa_bmi
WHERE bmi_to_date=?
AND bmi_group<>?";
$query = $this->db->query($sql,array($toDate, $bmi_group));
if($query->num_rows()) {
return 1;
} else {
return 0;
}
}
} // end class M_sa_bmi
?>
|