source: branches/version-2_5-dev/data/class/SC_CustomerList.php @ 18792

Revision 18792, 18.8 KB checked in by nanasess, 11 years ago (diff)

r18789 の変更に伴い SC_Utils::sfManualEscape() が不具合を発生していたので, 使用しないように修正(#801)

  • 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-2010 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/*  [名称] SC_CustomerList
25 *  [概要] 会員検索用クラス
26 */
27class SC_CustomerList extends SC_SelectSql {
28
29    var $arrColumnCSV;
30
31    function SC_CustomerList($array, $mode = '') {
32        parent::SC_SelectSql($array);
33
34        $masterData = new SC_DB_MasterData_Ex();
35        $arrMobileDomain = $masterData->getMasterData("mtb_mobile_domain");
36
37        $objDb = new SC_Helper_DB_Ex();
38        $dbFactory = SC_DB_DBFactory::getInstance();
39
40        if($mode == "") {
41            // 会員本登録会員で削除していない会員
42            $this->setWhere("status = 2 AND del_flg = 0 ");
43            // 登録日を示すカラム
44            $regdate_col = 'dtb_customer.update_date';
45        }
46
47        if($mode == "customer") {
48            // 管理機能顧客検索の場合仮登録会員も検索
49            //$this->setWhere( "(status = 1 OR status = 2) AND del_flg = 0 ");
50            $this->setWhere( " del_flg = 0 ");
51            // 登録日を示すカラム
52            $regdate_col = 'dtb_customer.update_date';
53        }
54
55        // メールマガジンの場合
56        if($mode == "magazine") {
57            $this->setWhere("(del_flg = 0 OR del_flg IS NULL)");
58            $this->setWhere("status = 2");
59            // 登録日を示すカラム
60            $regdate_col = 'dtb_customer.create_date';
61        }
62
63        // 顧客ID
64        if (!isset($this->arrSql['customer_id'])) $this->arrSql['customer_id'] = "";
65        if ( strlen($this->arrSql['customer_id']) > 0 ) {
66            $this->setWhere( "customer_id =  ?" );
67            $this->arrVal[] = $this->arrSql['customer_id'];
68        }
69
70        // 名前
71        if (!isset($this->arrSql['name'])) $this->arrSql['name'] = "";
72        if ( strlen($this->arrSql['name']) > 0 ) {
73            $this->setWhere("(" . $dbFactory->concatColumn(array("name01", "name02")) . " LIKE ?)" );
74            $searchName = $this->addSearchStr($this->arrSql['name']);
75            $this->arrVal[] = mb_ereg_replace("[  ]+","",$searchName);
76        }
77
78        // 名前(カナ)
79        if (!isset($this->arrSql['kana'])) $this->arrSql['kana'] = "";
80        if ( strlen($this->arrSql['kana']) > 0 ) {
81            $this->setWhere("(" . $dbFactory->concatColumn(array("kana01", "kana02")) . " LIKE ?)" );
82            $searchKana = $this->addSearchStr($this->arrSql['kana']);
83            $this->arrVal[] = mb_ereg_replace("[  ]+","",$searchKana);
84        }
85
86        // 都道府県
87        if (!isset($this->arrSql['pref'])) $this->arrSql['pref'] = "";
88        if ( strlen($this->arrSql['pref']) > 0 ) {
89            $this->setWhere( "pref = ?" );
90            $this->arrVal[] = $this->arrSql['pref'];
91        }
92
93        // 電話番号
94        if (!isset($this->arrSql['tel'])) $this->arrSql['tel'] = "";
95        if ( is_numeric( $this->arrSql['tel'] ) ) {
96            $this->setWhere("(" . $dbFactory->concatColumn(array("tel01", "tel02", "tel03")) . " LIKE ?)" );
97            $searchTel = $this->addSearchStr($this->arrSql['tel']);
98            $this->arrVal[] = ereg_replace("-", "", $searchTel);
99        }
100
101        //性別
102        if (!isset($this->arrSql['sex'])) $this->arrSql['sex'] = "";
103        if ( is_array( $this->arrSql['sex'] ) ){
104            $arrSexVal = $this->setItemTerm( $this->arrSql['sex'] ,"sex" );
105            foreach ($arrSexVal as $data) {
106                $this->arrVal[] = $data;
107            }
108        }
109
110        //職業
111        if (!isset($this->arrSql['job'])) $this->arrSql['job'] = "";
112        if ( is_array( $this->arrSql['job'] ) ){
113            if ( in_array("不明", $this->arrSql['job'] ) ) {
114                $arrJobVal = $this->setItemTermWithNull( $this->arrSql['job'] ,"job" );
115            } else {
116                $arrJobVal = $this->setItemTerm( $this->arrSql['job'] ,"job" );
117            }
118            if (is_array($arrJobVal)) {
119                foreach ($arrJobVal as $data) {
120                    $this->arrVal[] = $data;
121                }
122            }
123        }
124
125        // E-MAIL
126        if (!isset($this->arrSql['email'])) $this->arrSql['email'] = "";
127        if (strlen($this->arrSql['email']) > 0) {
128            //カンマ区切りで複数の条件指定可能に
129            $this->arrSql['email'] = explode(",", $this->arrSql['email']);
130            $sql_where = "";
131            foreach($this->arrSql['email'] as $val) {
132                $val = trim($val);
133                //検索条件を含まない
134                if($this->arrSql['not_emailinc'] == '1') {
135                    if($sql_where == "") {
136                        $sql_where .= "dtb_customer.email NOT ILIKE ? ";
137                    } else {
138                        $sql_where .= "AND dtb_customer.email NOT ILIKE ? ";
139                    }
140                } else {
141                    if($sql_where == "") {
142                        $sql_where .= "dtb_customer.email ILIKE ? ";
143                    } else {
144                        $sql_where .= "OR dtb_customer.email ILIKE ? ";
145                    }
146                }
147                $searchEmail = $this->addSearchStr($val);
148                $this->arrVal[] = $searchEmail;
149            }
150            $this->setWhere($sql_where);
151        }
152
153        // E-MAIL(mobile)
154        if (!isset($this->arrSql['email_mobile'])) $this->arrSql['email_mobile'] = "";
155       
156        if (strlen($this->arrSql['email_mobile']) > 0) {
157            //カンマ区切りで複数の条件指定可能に
158            $this->arrSql['email_mobile'] = explode(",", $this->arrSql['email_mobile']);
159            $sql_where = "";
160            foreach($this->arrSql['email_mobile'] as $val) {
161                $val = trim($val);
162                //検索条件を含まない
163                if($this->arrSql['not_email_mobileinc'] == '1') {
164                    if($sql_where == "") {
165                        $sql_where .= "dtb_customer.email_mobile NOT ILIKE ? ";
166                    } else {
167                        $sql_where .= "AND dtb_customer.email_mobile NOT ILIKE ? ";
168                    }
169                } else {
170                    if($sql_where == "") {
171                        $sql_where .= "dtb_customer.email_mobile ILIKE ? ";
172                    } else {
173                        $sql_where .= "OR dtb_customer.email_mobile ILIKE ? ";
174                    }
175                }
176                $searchemail_mobile = $this->addSearchStr($val);
177                $this->arrVal[] = $searchemail_mobile;
178            }
179            $this->setWhere($sql_where);
180        }
181
182        // 配信メールアドレス種別
183        if ( $mode == 'magazine' ){
184            $sqlEmailMobileIsEmpty = "(dtb_customer.email_mobile IS NULL OR dtb_customer.email_mobile = '')";
185            if (!isset($this->arrSql['mail_type'])) $this->arrSql['mail_type'] = "";
186            switch ($this->arrSql['mail_type']) {
187                // PCメールアドレス
188                case 1:
189                    $this->setWhere("(dtb_customer.email <> dtb_customer.email_mobile OR $sqlEmailMobileIsEmpty)");
190                    break;
191                // 携帯メールアドレス
192                case 2:
193                    $this->setWhere("NOT $sqlEmailMobileIsEmpty");
194                    break;
195                // PCメールアドレス (携帯メールアドレスを登録している顧客は除外)
196                case 3:
197                    $this->setWhere($sqlEmailMobileIsEmpty);
198                    break;
199                // 携帯メールアドレス (PCメールアドレスを登録している顧客は除外)
200                case 4:
201                    $this->setWhere('dtb_customer.email = dtb_customer.email_mobile');
202                    break;
203            }
204        }
205
206        // HTML-mail
207        if ( $mode == 'magazine' ){
208            if (!isset($this->arrSql['htmlmail'])) $this->arrSql['htmlmail'] = "";
209            if ( strlen($this->arrSql['htmlmail']) > 0 ) {
210                $this->setWhere( " mailmaga_flg = ? ");
211                $this->arrVal[] = $this->arrSql['htmlmail'];
212            } else {
213                $this->setWhere( " (mailmaga_flg = 1 or mailmaga_flg = 2) ");
214            }
215        }
216
217        // 購入金額指定
218        if (!isset($this->arrSql['buy_total_from'])) $this->arrSql['buy_total_from'] = "";
219        if (!isset($this->arrSql['buy_total_to'])) $this->arrSql['buy_total_to'] = "";
220        if( is_numeric( $this->arrSql["buy_total_from"] ) || is_numeric( $this->arrSql["buy_total_to"] ) ) {
221            $arrBuyTotal = $this->selectRange($this->arrSql["buy_total_from"], $this->arrSql["buy_total_to"], "buy_total");
222            foreach ($arrBuyTotal as $data1) {
223                $this->arrVal[] = $data1;
224            }
225        }
226
227        // 購入回数指定
228        if (!isset($this->arrSql['buy_times_from'])) $this->arrSql['buy_times_from'] = "";
229        if (!isset($this->arrSql['buy_times_to'])) $this->arrSql['buy_times_to'] = "";
230        if( is_numeric( $this->arrSql["buy_times_from"] ) || is_numeric( $this->arrSql["buy_times_to"] ) ) {
231            $arrBuyTimes = $this->selectRange($this->arrSql["buy_times_from"], $this->arrSql["buy_times_to"], "buy_times");
232            foreach ($arrBuyTimes as $data2) {
233                $this->arrVal[] = $data2;
234            }
235        }
236
237        // 誕生日期間指定
238        if (!isset($this->arrSql['b_start_year'])) $this->arrSql['b_start_year'] = "";
239        if (!isset($this->arrSql['b_start_month'])) $this->arrSql['b_start_month'] = "";
240        if (!isset($this->arrSql['b_start_day'])) $this->arrSql['b_start_day'] = "";
241        if (!isset($this->arrSql['b_end_year'])) $this->arrSql['b_end_year'] = "";
242        if (!isset($this->arrSql['b_end_month'])) $this->arrSql['b_end_month'] = "";
243        if (!isset($this->arrSql['b_end_day'])) $this->arrSql['b_end_day'] = "";
244        if ( (strlen($this->arrSql['b_start_year']) > 0 && strlen($this->arrSql['b_start_month']) > 0 && strlen($this->arrSql['b_start_day']) > 0) ||
245              strlen($this->arrSql['b_end_year']) > 0 && strlen($this->arrSql['b_end_month']) > 0 && strlen($this->arrSql['b_end_day']) > 0) {
246
247            $arrBirth = $this->selectTermRange($this->arrSql['b_start_year'], $this->arrSql['b_start_month'], $this->arrSql['b_start_day']
248                      , $this->arrSql['b_end_year'], $this->arrSql['b_end_month'], $this->arrSql['b_end_day'], "birth");
249            if (is_array($arrBirth)) {
250                foreach ($arrBirth as $data3) {
251                    $this->arrVal[] = $data3;
252                }
253            }
254        }
255
256        // 誕生月の検索
257        if (!isset($this->arrSql['birth_month'])) $this->arrSql['birth_month'] = "";
258        if (is_numeric($this->arrSql["birth_month"])) {
259            $this->setWhere(" EXTRACT(month from birth) = ?");
260            $this->arrVal[] = $this->arrSql["birth_month"];
261        }
262
263        // 登録期間指定
264        if (!isset($this->arrSql['start_year'])) $this->arrSql['start_year'] = "";
265        if (!isset($this->arrSql['start_month'])) $this->arrSql['start_month'] = "";
266        if (!isset($this->arrSql['start_day'])) $this->arrSql['start_day'] = "";
267        if (!isset($this->arrSql['end_year'])) $this->arrSql['end_year'] = "";
268        if (!isset($this->arrSql['end_month'])) $this->arrSql['end_month'] = "";
269        if (!isset($this->arrSql['end_day'])) $this->arrSql['end_day'] = "";
270        if ( (strlen($this->arrSql['start_year']) > 0 && strlen($this->arrSql['start_month']) > 0 && strlen($this->arrSql['start_day']) > 0 ) ||
271                (strlen($this->arrSql['end_year']) > 0 && strlen($this->arrSql['end_month']) >0 && strlen($this->arrSql['end_day']) > 0) ) {
272
273            $arrRegistTime = $this->selectTermRange($this->arrSql['start_year'], $this->arrSql['start_month'], $this->arrSql['start_day']
274                            , $this->arrSql['end_year'], $this->arrSql['end_month'], $this->arrSql['end_day'], $regdate_col);
275            if (is_array($arrRegistTime)) {
276                foreach ($arrRegistTime as $data4) {
277                    $this->arrVal[] = $data4;
278                }
279            }
280        }
281
282        // 最終購入日指定
283        if (!isset($this->arrSql['buy_start_year'])) $this->arrSql['buy_start_year'] = "";
284        if (!isset($this->arrSql['buy_start_month'])) $this->arrSql['buy_start_month'] = "";
285        if (!isset($this->arrSql['buy_start_day'])) $this->arrSql['buy_start_day'] = "";
286        if (!isset($this->arrSql['buy_end_year'])) $this->arrSql['buy_end_year'] = "";
287        if (!isset($this->arrSql['buy_end_month'])) $this->arrSql['buy_end_month'] = "";
288        if (!isset($this->arrSql['buy_end_day'])) $this->arrSql['buy_end_day'] = "";
289
290        if ( (strlen($this->arrSql['buy_start_year']) > 0 && strlen($this->arrSql['buy_start_month']) > 0 && strlen($this->arrSql['buy_start_day']) > 0 ) ||
291                (strlen($this->arrSql['buy_end_year']) > 0 && strlen($this->arrSql['buy_end_month']) >0 && strlen($this->arrSql['buy_end_day']) > 0) ) {
292            $arrRegistTime = $this->selectTermRange($this->arrSql['buy_start_year'], $this->arrSql['buy_start_month'], $this->arrSql['buy_start_day']
293                            , $this->arrSql['buy_end_year'], $this->arrSql['buy_end_month'], $this->arrSql['buy_end_day'], "last_buy_date");
294            if (is_array($arrRegistTime)) {
295                foreach ($arrRegistTime as $data4) {
296                    $this->arrVal[] = $data4;
297                }
298            }
299        }
300
301        //購入商品コード
302        if (!isset($this->arrSql['buy_product_code'])) $this->arrSql['buy_product_code'] = "";
303        if ( strlen($this->arrSql['buy_product_code']) > 0 ) {
304            $this->setWhere( "customer_id IN (SELECT customer_id FROM dtb_order WHERE order_id IN (SELECT order_id FROM dtb_order_detail WHERE product_code LIKE ? ))");
305            $search_buyproduct_code = $this->addSearchStr($this->arrSql['buy_product_code']);
306            $this->arrVal[] = $search_buyproduct_code;
307        }
308
309        //購入商品名称
310        if (!isset($this->arrSql['buy_product_name'])) $this->arrSql['buy_product_name'] = "";
311        if ( strlen($this->arrSql['buy_product_name']) > 0 ) {
312            $this->setWhere( "customer_id IN (SELECT customer_id FROM dtb_order WHERE order_id IN (SELECT order_id FROM dtb_order_detail WHERE product_name LIKE ? ))");
313            $search_buyproduct_name = $this->addSearchStr($this->arrSql['buy_product_name']);
314            $this->arrVal[] = $search_buyproduct_name;
315        }
316
317        //カテゴリーを選択している場合のみ絞込検索を行う
318        if (!isset($this->arrSql['category_id'])) $this->arrSql['category_id'] = "";
319        if ( strlen($this->arrSql['category_id']) > 0){
320            //カテゴリーで絞込検索を行うSQL文生成
321            list($tmp_where, $tmp_arrval) = $objDb->sfGetCatWhere($this->arrSql['category_id']);
322
323            //カテゴリーで絞込みが可能の場合
324            if($tmp_where != "") {
325                $this->setWhere( " customer_id IN (SELECT distinct customer_id FROM dtb_order WHERE order_id IN (SELECT distinct order_id FROM dtb_order_detail WHERE product_id IN (SELECT product_id FROM dtb_product_categories WHERE ".$tmp_where." ))) ");
326                $this->arrVal = array_merge((array)$this->arrVal, (array)$tmp_arrval);
327            }
328        }
329        //携帯電話番号
330        if (!isset($this->arrSql['cell'])) $this->arrSql['cell'] = "";
331        if ( is_numeric( $this->arrSql['cell'] ) ) {
332            $this->setWhere( "(cell01 || cell02 || cell03 LIKE ?)" );
333            $searchTel = $this->addSearchStr($this->arrSql['cell']);
334            $this->arrVal[] = ereg_replace("-", "", $searchTel);
335        }
336
337        //キャンペーン
338        if (!isset($this->arrSql['campaign_id'])) $this->arrSql['campaign_id'] = "";
339        if ( is_numeric( $this->arrSql['campaign_id'] ) ) {
340            $this->setWhere( " customer_id IN (SELECT distinct customer_id FROM dtb_campaign_order WHERE campaign_id = ?)" );
341            $this->arrVal[] = $this->arrSql['campaign_id'];
342        }
343
344        //会員状態
345        if (!isset($this->arrSql['status'])) $this->arrSql['status'] = "";
346        if ( is_array( $this->arrSql['status'] ) ){
347            $arrStatusVal = $this->setItemTerm( $this->arrSql['status'] ,"status" );
348            foreach ($arrStatusVal as $data) {
349                $this->arrVal[] = $data;
350            }
351        }
352
353        $this->setOrder( "customer_id DESC" );
354    }
355
356    // 検索用SQL
357    function getList() {
358        $this->select = "SELECT customer_id,name01,name02,kana01,kana02,sex,email,tel01,tel02,tel03,pref,status FROM dtb_customer ";
359        return $this->getSql(0);
360    }
361
362    function getListMailMagazine($is_mobile = false) {
363
364        $colomn = $this->getMailMagazineColumn($is_mobile);
365        $this->select = "
366            SELECT
367                $colomn
368            FROM
369                dtb_customer";
370        return $this->getSql(0);
371    }
372
373    function getMailMagazineColumn($is_mobile= false) {
374        if($is_mobile == true) {
375            $email_column = "dtb_customer.email_mobile as email";
376        } else {
377            $email_column = "dtb_customer.email";
378        }
379
380        $column ="dtb_customer.customer_id,
381                dtb_customer.name01,
382                dtb_customer.name02,
383                dtb_customer.kana01,
384                dtb_customer.kana02,
385                dtb_customer.sex,
386                $email_column,
387                dtb_customer.tel01,
388                dtb_customer.tel02,
389                dtb_customer.tel03,
390                dtb_customer.pref,
391                dtb_customer.create_date,
392                dtb_customer.mailmaga_flg";
393
394        return $column;
395    }
396
397    // 検索総数カウント用SQL
398    function getListCount() {
399        $this->select = "SELECT COUNT(customer_id) FROM dtb_customer ";
400        return $this->getSql(1);
401    }
402
403    // CSVダウンロード用SQL
404    function getListCSV($arrColumnCSV) {
405        $this->arrColumnCSV = $arrColumnCSV;
406        $i = 0;
407        foreach ($this->arrColumnCSV as $val) {
408            if ($i != 0) $state .= ", ";
409            $state .= $val["sql"];
410            $i ++;
411        }
412
413        $this->select = "SELECT " .$state. " FROM dtb_customer ";
414        return $this->getSql(2);
415    }
416
417    function getWhere() {
418        return array($this->where, $this->arrVal);
419    }
420}
421?>
Note: See TracBrowser for help on using the repository browser.