source: branches/dev/data/class/SC_SelectSql.php @ 12431

<?php
/*
 * Copyright(c) 2000-2007 LOCKON CO.,LTD. All Rights Reserved.
 *
 * http://www.lockon.co.jp/
 */

/* ---- SQLʸ¤òºî¤ë¥¯¥é¥¹ ---- */
class SC_SelectSql {
    
    var $sql;
    
    var $select;    
    var $where;
    var $order;
    var $group;
    var $limit;
    var $offset;
    var $arrSql;
    var $arrVal;

    //--¡¡¥³¥ó¥¹¥È¥é¥¯¥¿¡£
    function SC_SelectSql($array = "") {
        if (is_array($array)) {
            $this->arrSql = $array;
        }
    }

    //-- SQLʸÀ¸À®
    function getSql( $mode = "" ){
        $this->sql = $this->select ." ". $this->where ." ". $this->group ." ";
                        
        // $mode == 1 ¤Ï limit & offset̵¤·                     
        if ($mode == 2) {
            $this->sql .= $this->order;
        }elseif ( $mode != 1 ){
            $this->sql .= $this->order . " " .$this->limit ." ". $this->offset; 
        }

        return $this->sql;  
    }

        // ¸¡º÷ÍÑ
    function addSearchStr($val) {
        $return = sfManualEscape($val);
        $return = "%" .$return. "%";
        return $return;
    }
    
    function addSearchEnd($val) {
        $return = sfManuaiEscape($val);
        $return = "%" .$return;
        return $return;
    }
    
    //-- Èϰϸ¡º÷¡Ê¡û¡¡¢·¡¡¡û¡¡¤Þ¤Ç¡Ë
    function selectRange($from, $to, $column) {

        // ¤¢¤ëñ°Ì¤Î¤ß¸¡º÷($from = $to)
        if(  $from == $to ) {
            $this->setWhere( $column ." = ?" );
            $return = array($from);
        //¡¡¢·$to¤Þ¤Ç¸¡º÷
        } elseif(  strlen($from) == 0 && strlen($to) > 0 ) {
            $this->setWhere( $column ." <= ? "); 
            $return = array($to);
        //¡¡¢·$from°Ê¾å¤ò¸¡º÷
        } elseif(  strlen($from) > 0 && strlen($to) == 0 ) {
            $this->setWhere( $column ." >= ? ");
            $return = array($from);
        //¡¡$from¢·$to¤Î¸¡º÷
        } else {
            $this->setWhere( $column ." BETWEEN ? AND ?" ); 
            $return = array($from, $to);
        }
        return $return;
    }

    //--¡¡´ü´Ö¸¡º÷¡Ê¡ûǯ¡û·î¡ûÆü¤«¢·¡ûǯ¡û·î¡ûÆü¤Þ¤Ç¡Ë
    function selectTermRange($from_year, $from_month, $from_day, $to_year, $to_month, $to_day, $column) {

        // FROM
        $date1 = $from_year . "/" . $from_month . "/" . $from_day;
        
        // TO
        $date2 = mktime (0, 0, 0, $to_month, $to_day,  $to_year);
        $date2 = $date2 + 86400;
        $date2 = date('y/m/d', $date2);
        
        // ³«»Ï´ü´Ö¤À¤±»ØÄê¤Î¾ì¹ç
        if( ( $from_year != "" ) && ( $from_month != "" ) && ( $from_day != "" ) && ( $to_year == "" ) && ( $to_month == "" ) && ( $to_day == "" ) ) {
            $this->setWhere( $column ." >= '" . $date1 . "'");
        }

        //¡¡³«»Ï¡Á½ªÎ»
        if( ( $from_year != "" ) && ( $from_month != "" ) && ( $from_day != "" ) && 
            ( $to_year != "" ) && ( $to_month != "" ) && ( $to_day != "" ) ) {
            $this->setWhere( $column ." >= '" . $date1 ."' AND ". $column . " < date('" . $date2 . "')" );
        }

        // ½ªÎ»´ü´Ö¤À¤±»ØÄê¤Î¾ì¹ç
        if( ( $from_year == "" ) && ( $from_month == "" ) && ( $from_day == "" ) && ( $to_year != "" ) && ( $to_month != "" ) && ( $to_day != "" ) ) {
            $this->setWhere( $column ." < date('" . $date2 . "')");
        }
        return $return;
    }   

    // checkbox¤Ê¤É¤ÇƱ°ì¥«¥é¥àÆâ¤Çñ°ì¡¢¤â¤·¤¯¤ÏÊ£¿ôÁªÂò»è¤¬Í­¤ë¾ì¹ç¡¡Îã: AND ( sex = xxx OR sex = xxx OR sex = xxx  ) AND ... 
    function setItemTerm( $arr, $ItemStr ) {

        foreach( $arr as $data ) {
    
            if( count( $arr ) > 1 ) {
                if( ! is_null( $data ) ) $item .= $ItemStr . " = ? OR ";    
            } else {
                if( ! is_null( $data ) ) $item = $ItemStr . " = ?"; 
            }
            $return[] = $data;
        }

        if( count( $arr ) > 1 )  $item = "( " . rtrim( $item, " OR " ) . " )";
        $this->setWhere( $item );
        return $return;
    }

    //¡¡NULLÃͤ¬É¬Íפʾì¹ç
    function setItemTermWithNull( $arr, $ItemStr ) {

        $item = " ${ItemStr} IS NULL ";
        
        if ( $arr ){
            foreach( $arr as $data ) {  
                if ($data != "ÉÔÌÀ") {
                    $item .= " OR ${ItemStr} = ?";
                    $return[] = $data;
                }
            }
        }
        
        $item = "( ${item} ) ";
        $this->setWhere( $item );
        return $return;
    }
    // NULL¤â¤·¤¯¤Ï''¤Ç¸¡º÷¤¹¤ë¾ì¹ç
    function setItemTermWithNullAndSpace( $arr, $ItemStr ) {
        $count = count($arr);
        $item = " ${ItemStr} IS NULL OR ${ItemStr} = '' ";
        $i = 1;
        if ( $arr ){
            foreach( $arr as $data ) {  
                if ($i == $count) break;
                $item .= " OR ${ItemStr} = ?";  
                $return[] = $data;
                $i ++;
            }
        }
        $item = "( ${item} ) ";
        $this->setWhere( $item );
        return $return;
    }
    


    /* Ê£¿ô¤Î¥«¥é¥à¤ÇOR¤ÇÍ¥À踡º÷¤¹¤ë¾ì¹ç¡¡Î㡧¡¡AND ( item_flag1 = xxx OR item_flag2 = xxx OR item_flag3 = xxx  ) AND ... 

        ÇÛÎó¤Î¹½Â¤Îã¡¡
        if ( $_POST['show_site1'] ) $arrShowsite_1 = array( "column" => "show_site1",
                                                            "value"  => $_POST['show_site1'] );

    */
    function setWhereByOR( $arrWhere ){

        $count = count( $arrWhere );

        for( $i = 0; $i < $count; $i++ ) {

            if( isset( $arrWhere[$i]["value"] ) ) $statement .= $arrWhere[$i]["column"] ." = '" . addslashes( $arrWhere[$i]["value"] ) ."' OR "  ;
        }

        $statement = "( " . rtrim( $statement, " OR " ) . " )";

        if( $this->where ) {

            $this->where .= " AND " . $statement;

        } else {

            $this->where = "WHERE " . $statement;
        }
    }

    function setWhere($where){
        if ($where != "") {     
            if( $this->where ) {
    
                $this->where .= " AND " . $where;
    
            } else {
    
                $this->where = "WHERE " . $where;
            }
        }
    }

    function setOrder($order){
        
            $this->order =  "ORDER BY " . $order;
        
    }

    function setGroup( $group ) {
        
        $this->group =  "GROUP BY " . $group;
        
    }

    
    function setLimitOffset( $limit, $offset ){

        if ( is_numeric($limit) and is_numeric($offset) ){

            $this->limit = " LIMIT " .$limit;
            $this->offset = " OFFSET " .$offset;
        }   
    }
    
    function clearSql(){
        $this->select = "";
        $this->where = "";
        $this->group = "";
        $this->order = "";
        $this->limit = "";
        $this->offset = "";
    }
    
    function setSelect($sql) {
        $this->select = $sql;
    }
}
?>