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

Revision 19892, 18.2 KB checked in by fukuda, 13 years ago (diff)

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