source: branches/comu-ver2/data/class/SC_SelectSql.php @ 17579

Revision 17579, 7.4 KB checked in by Seasoft, 16 years ago (diff)

merge r17463,r17464,r17465,r17468,r17472,r17473,r17493,r17497,r17500,r17501,r17504

  • Property svn:eol-style set to LF
  • Property svn:keywords set to Id
  • Property svn:mime-type set to text/x-httpd-php; charset=UTF-8
Line 
1<?php
2/*
3 * This file is part of EC-CUBE
4 *
5 * Copyright(c) 2000-2007 LOCKON CO.,LTD. All Rights Reserved.
6 *
7 * http://www.lockon.co.jp/
8 *
9 * This program is free software; you can redistribute it and/or
10 * modify it under the terms of the GNU General Public License
11 * as published by the Free Software Foundation; either version 2
12 * of the License, or (at your option) any later version.
13 *
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
17 * GNU General Public License for more details.
18 *
19 * You should have received a copy of the GNU General Public License
20 * along with this program; if not, write to the Free Software
21 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
22 */
23
24/* ---- SQL文を作るクラス ---- */
25class SC_SelectSql {
26
27    var $sql;
28
29    var $select;
30    var $where;
31    var $order;
32    var $group;
33    var $limit;
34    var $offset;
35    var $arrSql;
36    var $arrVal;
37
38    //-- コンストラクタ。
39    function SC_SelectSql($array = "") {
40        if (is_array($array)) {
41            $this->arrSql = $array;
42        }
43    }
44
45    //-- SQL分生成
46    function getSql( $mode = "" ){
47        $this->sql = $this->select ." ". $this->where ." ". $this->group ." ";
48
49        // $mode == 1 は limit & offset無し
50        if ($mode == 2) {
51            $this->sql .= $this->order;
52        }elseif ( $mode != 1 ){
53            $this->sql .= $this->order . " " .$this->limit ." ". $this->offset;
54        }
55
56        return $this->sql;
57    }
58
59        // 検索用
60    function addSearchStr($val) {
61        $return = SC_Utils_Ex::sfManualEscape($val);
62        $return = "%" .$return. "%";
63        return $return;
64    }
65
66    //-- 範囲検索(○ ~ ○ まで)
67    function selectRange($from, $to, $column) {
68
69        // ある単位のみ検索($from = $to)
70        if(  $from == $to ) {
71            $this->setWhere( $column ." = ?" );
72            $return = array($from);
73        // ~$toまで検索
74        } elseif(  strlen($from) == 0 && strlen($to) > 0 ) {
75            $this->setWhere( $column ." <= ? ");
76            $return = array($to);
77        // ~$from以上を検索
78        } elseif(  strlen($from) > 0 && strlen($to) == 0 ) {
79            $this->setWhere( $column ." >= ? ");
80            $return = array($from);
81        // $from~$toの検索
82        } else {
83            $this->setWhere( $column ." BETWEEN ? AND ?" );
84            $return = array($from, $to);
85        }
86        return $return;
87    }
88
89    //-- 期間検索(○年○月○日か~○年○月○日まで)
90    function selectTermRange($from_year, $from_month, $from_day, $to_year, $to_month, $to_day, $column) {
91
92        // 開始期間の構築
93        $date1 = $from_year . "/" . $from_month . "/" . $from_day;
94
95        // 終了期間の構築
96        // @see http://svn.ec-cube.net/open_trac/ticket/328
97        // FIXME とりあえずintvalで対策...
98        $date2 = mktime (0, 0, 0, intval($to_month), intval($to_day), intval($to_year));
99        $date2 = $date2 + 86400;
100        // SQL文のdate関数に与えるフォーマットは、yyyy/mm/ddで指定する。
101        $date2 = date('Y/m/d', $date2);
102
103        // 開始期間だけ指定の場合
104        if( ( $from_year != "" ) && ( $from_month != "" ) && ( $from_day != "" ) && ( $to_year == "" ) && ( $to_month == "" ) && ( $to_day == "" ) ) {
105            $this->setWhere( $column ." >= '" . $date1 . "'");
106        }
107
108        // 開始~終了
109        if( ( $from_year != "" ) && ( $from_month != "" ) && ( $from_day != "" ) &&
110            ( $to_year != "" ) && ( $to_month != "" ) && ( $to_day != "" ) ) {
111            $this->setWhere( $column ." >= '" . $date1 ."' AND ". $column . " < date('" . $date2 . "')" );
112        }
113
114        // 終了期間だけ指定の場合
115        if( ( $from_year == "" ) && ( $from_month == "" ) && ( $from_day == "" ) && ( $to_year != "" ) && ( $to_month != "" ) && ( $to_day != "" ) ) {
116            $this->setWhere( $column ." < date('" . $date2 . "')");
117        }
118    }
119
120    // checkboxなどで同一カラム内で単一、もしくは複数選択肢が有る場合 例: AND ( sex = xxx OR sex = xxx OR sex = xxx  ) AND ...
121    function setItemTerm( $arr, $ItemStr ) {
122
123        foreach( $arr as $data ) {
124
125            if( count( $arr ) > 1 ) {
126                if( ! is_null( $data ) ) $item .= $ItemStr . " = ? OR ";
127            } else {
128                if( ! is_null( $data ) ) $item = $ItemStr . " = ?";
129            }
130            $return[] = $data;
131        }
132
133        if( count( $arr ) > 1 )  $item = "( " . rtrim( $item, " OR " ) . " )";
134        $this->setWhere( $item );
135        return $return;
136    }
137
138    // NULL値が必要な場合
139    function setItemTermWithNull( $arr, $ItemStr ) {
140
141        $item = " ${ItemStr} IS NULL ";
142
143        if ( $arr ){
144            foreach( $arr as $data ) {
145                if ($data != "不明") {
146                    $item .= " OR ${ItemStr} = ?";
147                    $return[] = $data;
148                }
149            }
150        }
151
152        $item = "( ${item} ) ";
153        $this->setWhere( $item );
154        return $return;
155    }
156    // NULLもしくは''で検索する場合
157    function setItemTermWithNullAndSpace( $arr, $ItemStr ) {
158        $count = count($arr);
159        $item = " ${ItemStr} IS NULL OR ${ItemStr} = '' ";
160        $i = 1;
161        if ( $arr ){
162            foreach( $arr as $data ) {
163                if ($i == $count) break;
164                $item .= " OR ${ItemStr} = ?";
165                $return[] = $data;
166                $i ++;
167            }
168        }
169        $item = "( ${item} ) ";
170        $this->setWhere( $item );
171        return $return;
172    }
173
174
175
176    /* 複数のカラムでORで優先検索する場合 例: AND ( item_flag1 = xxx OR item_flag2 = xxx OR item_flag3 = xxx  ) AND ...
177
178        配列の構造例 
179        if ( $_POST['show_site1'] ) $arrShowsite_1 = array( "column" => "show_site1",
180                                                            "value"  => $_POST['show_site1'] );
181
182    */
183    function setWhereByOR( $arrWhere ){
184
185        $count = count( $arrWhere );
186
187        for( $i = 0; $i < $count; $i++ ) {
188
189            if( isset( $arrWhere[$i]["value"] ) ) $statement .= $arrWhere[$i]["column"] ." = " . SC_Utils_Ex::sfQuoteSmart($arrWhere[$i]["value"]) ." OR "  ;
190        }
191
192        $statement = "( " . rtrim( $statement, " OR " ) . " )";
193
194        if( $this->where ) {
195
196            $this->where .= " AND " . $statement;
197
198        } else {
199
200            $this->where = "WHERE " . $statement;
201        }
202    }
203
204    function setWhere($where){
205        if ($where != "") {
206            if( $this->where ) {
207
208                $this->where .= " AND " . $where;
209
210            } else {
211
212                $this->where = "WHERE " . $where;
213            }
214        }
215    }
216
217    function setOrder($order){
218
219            $this->order =  "ORDER BY " . $order;
220
221    }
222
223    function setGroup( $group ) {
224
225        $this->group =  "GROUP BY " . $group;
226
227    }
228
229
230    function setLimitOffset( $limit, $offset ){
231
232        if ( is_numeric($limit) and is_numeric($offset) ){
233
234            $this->limit = " LIMIT " .$limit;
235            $this->offset = " OFFSET " .$offset;
236        }
237    }
238
239    function clearSql(){
240        $this->select = "";
241        $this->where = "";
242        $this->group = "";
243        $this->order = "";
244        $this->limit = "";
245        $this->offset = "";
246    }
247
248    function setSelect($sql) {
249        $this->select = $sql;
250    }
251}
252?>
Note: See TracBrowser for help on using the repository browser.