source: temp/trunk/data/class/SC_CustomerList.php @ 2156

Revision 2156, 10.0 KB checked in by kakinaka, 20 years ago (diff)

blank
  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1<?php
2
3/*  [̾¾Î] SC_CustomerList
4 *  [³µÍ×] ²ñ°÷¸¡º÷ÍÑ¥¯¥é¥¹
5 */
6class SC_CustomerList extends SC_SelectSql {
7
8    var $arrColumnCSV;
9   
10    function SC_CustomerList($array, $mode = '') {
11        parent::SC_SelectSql($array);
12       
13        if($mode == "") {
14            // ²ñ°÷ËÜÅÐÏ¿²ñ°÷¤Çºï½ü¤·¤Æ¤¤¤Ê¤¤²ñ°÷
15            $this->setWhere("status = 2 AND delete = 0 ");     
16            // ÅÐÏ¿Æü¤ò¼¨¤¹¥«¥é¥à
17            $regdate_col = 'dtb_customer.update_date';
18        }
19       
20        if($mode == "customer") {
21            // ´ÉÍý¼Ô¥Ú¡¼¥¸¸ÜµÒ¸¡º÷¤Î¾ì¹ç²¾ÅÐÏ¿²ñ°÷¤â¸¡º÷
22            $this->setWhere( "(status = 1 OR status = 2) AND delete = 0 ");     
23            // ÅÐÏ¿Æü¤ò¼¨¤¹¥«¥é¥à
24            $regdate_col = 'dtb_customer.update_date';
25        }
26               
27        // ¥á¡¼¥ë¥Þ¥¬¥¸¥ó¤Î¾ì¹ç     
28        if($mode == "magazine") {
29            $this->setWhere("(delete = 0 OR delete IS NULL)");
30           
31            if(is_array($this->arrSql['customer'])) {
32                $tmp_where = "";
33                foreach($this->arrSql['customer'] as $val) {
34                    if($tmp_where != "") {
35                        $tmp_where.= " OR ";
36                    }                   
37                    switch($val) {
38                    // ²ñ°÷
39                    case '1':
40                        $tmp_where.= "status = 2";
41                        break;
42                    // ¥á¥ë¥Þ¥¬ÅÐÏ¿
43                    case '2':
44                        $tmp_where.= "customer_id IS NULL";
45                        break;
46                    // CSVÅÐÏ¿
47                    case '3':
48                        $tmp_where.= "customer_id IS NULL";
49                        break;
50                    default:
51                        $tmp_where = "";
52                        break;
53                    }
54                }
55                if($tmp_where != "") {
56                    $tmp_where = "(" . $tmp_where . ")";
57                }
58                $this->setWhere($tmp_where);
59            }
60            // ÅÐÏ¿Æü¤ò¼¨¤¹¥«¥é¥à
61            $regdate_col = 'dtb_customer_mail.create_date';
62        }
63               
64        // ¸ÜµÒ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 ( strlen($this->arrSql['name']) > 0 ) {
72            $this->setWhere("(name01 || name02 LIKE ?)" );
73            $searchName = $this->addSearchStr($this->arrSql['name']);
74            $this->arrVal[] = mb_ereg_replace("[ ¡¡]+","",$searchName);
75        }
76
77        //¡¡Ì¾Á°¡Ê¥«¥Ê¡Ë
78        if ( strlen($this->arrSql['kana']) > 0 ) {
79            $this->setWhere("(kana01 || kana02 LIKE ?)");
80            $searchKana = $this->addSearchStr($this->arrSql['kana']);
81            $this->arrVal[] = mb_ereg_replace("[ ¡¡]+","",$searchKana);
82        }
83       
84        //¡¡ÅÔÆ»Éܸ©
85        if ( strlen($this->arrSql['pref']) > 0 ) {
86            $this->setWhere( "pref = ?" );
87            $this->arrVal[] = $this->arrSql['pref'];
88        }
89
90        //¡¡ÅÅÏÃÈÖ¹æ
91        if ( is_numeric( $this->arrSql['tel'] ) ) {
92            $this->setWhere( "(tel01 || tel02 || tel03 LIKE ?)" );
93            $searchTel = $this->addSearchStr($this->arrSql['tel']);
94            $this->arrVal[] = ereg_replace("-", "", $searchTel);
95        }
96       
97        //¡¡À­ÊÌ
98        if ( is_array( $this->arrSql['sex'] ) ){
99            $arrSexVal = $this->setItemTerm( $this->arrSql['sex'] ,"sex" );
100            foreach ($arrSexVal as $data) {
101                $this->arrVal[] = $data;
102            }
103        }
104
105        //¡¡¿¦¶È
106        if ( is_array( $this->arrSql['job'] ) ){
107            if ( in_array("ÉÔÌÀ", $this->arrSql['job'] ) ) {
108                $arrJobVal = $this->setItemTermWithNull( $this->arrSql['job'] ,"job" );
109            } else {
110                $arrJobVal = $this->setItemTerm( $this->arrSql['job'] ,"job" );
111            }
112            if (is_array($arrJobVal)) {
113                foreach ($arrJobVal as $data) {
114                    $this->arrVal[] = $data;
115                }
116            }
117        }
118
119        //¡¡E-MAIL
120        if (strlen($this->arrSql['email']) > 0) {
121            //¥«¥ó¥Þ¶èÀÚ¤ê¤ÇÊ£¿ô¤Î¾ò·ï»ØÄê²Äǽ¤Ë
122            $this->arrSql['email'] = explode(",", $this->arrSql['email']);
123            $sql_where = "";
124            foreach($this->arrSql['email'] as $val) {
125                $val = trim($val);
126                //¸¡º÷¾ò·ï¤ò´Þ¤Þ¤Ê¤¤
127                if($this->arrSql['not_emailinc'] == '1') {
128                    if($sql_where == "") {
129                        $sql_where .= "email NOT ILIKE ? ";
130                    } else {
131                        $sql_where .= "AND email NOT ILIKE ? ";
132                    }
133                } else {               
134                    if($sql_where == "") {
135                        $sql_where .= "email ILIKE ? ";
136                    } else {
137                        $sql_where .= "OR email ILIKE ? ";
138                    }
139                }
140                $searchEmail = $this->addSearchStr($val);
141                $this->arrVal[] = $searchEmail;
142            }
143            $this->setWhere($sql_where);
144        }
145        //¡¡HTML-mail
146        if ( $mode == 'magazine' ){
147            if ( strlen($this->arrSql['htmlmail']) > 0 ) {
148                $this->setWhere( " mail_flag = ? ");
149                $this->arrVal[] = $this->arrSql['htmlmail'];
150            } else {
151                $this->setWhere( " (mail_flag = 1 or mail_flag = 2) ");
152            }
153        }
154       
155        // ¹ØÆþ¶â³Û»ØÄê
156        if( is_numeric( $this->arrSql["buy_total_from"] ) || is_numeric( $this->arrSql["buy_total_to"] ) ) {
157            $arrBuyTotal = $this->selectRange($this->arrSql["buy_total_from"], $this->arrSql["buy_total_to"], "buy_total");
158            foreach ($arrBuyTotal as $data1) {
159                $this->arrVal[] = $data1;
160            }
161        }
162
163        // ¹ØÆþ²ó¿ô»ØÄê
164        if( is_numeric( $this->arrSql["buy_times_from"] ) || is_numeric( $this->arrSql["buy_times_to"] ) ) {
165            $arrBuyTimes = $this->selectRange($this->arrSql["buy_times_from"], $this->arrSql["buy_times_to"], "buy_times");
166            foreach ($arrBuyTimes as $data2) {
167                $this->arrVal[] = $data2;
168            }
169        }
170       
171        // ÃÂÀ¸Æü´ü´Ö»ØÄê
172        if ( (strlen($this->arrSql['b_start_year']) > 0 && strlen($this->arrSql['b_start_month']) > 0 && strlen($this->arrSql['b_start_day']) > 0) ||
173              strlen($this->arrSql['b_end_year']) > 0 && strlen($this->arrSql['b_end_month']) > 0 && strlen($this->arrSql['b_end_day']) > 0) {
174
175            $arrBirth = $this->selectTermRange($this->arrSql['b_start_year'], $this->arrSql['b_start_month'], $this->arrSql['b_start_day']
176                      , $this->arrSql['b_end_year'], $this->arrSql['b_end_month'], $this->arrSql['b_end_day'], "birth");
177            if (is_array($arrBirth)) {
178                foreach ($arrBirth as $data3) {
179                    $this->arrVal[] = $data3;
180                }
181            }
182        }
183               
184        // ÃÂÀ¸·î¤Î¸¡º÷
185        sfprintr($this->arrSql);
186        sfprintr($this->arrVal);
187        if (is_numeric($this->arrSql["birth_month"])) {
188            $this->setWhere(" EXTRACT(month from birth) = ?"); 
189            $this->arrVal[] = $this->arrSql["birth_month"];
190        }
191           
192        // ÅÐÏ¿´ü´Ö»ØÄê
193        if ( (strlen($this->arrSql['start_year']) > 0 && strlen($this->arrSql['start_month']) > 0 && strlen($this->arrSql['start_day']) > 0 ) ||
194                (strlen($this->arrSql['end_year']) > 0 && strlen($this->arrSql['end_month']) >0 && strlen($this->arrSql['end_day']) > 0) ) {
195
196            $arrRegistTime = $this->selectTermRange($this->arrSql['start_year'], $this->arrSql['start_month'], $this->arrSql['start_day']
197                            , $this->arrSql['end_year'], $this->arrSql['end_month'], $this->arrSql['end_day'], $regdate_col);
198            if (is_array($arrRegistTime)) {
199                foreach ($arrRegistTime as $data4) {
200                    $this->arrVal[] = $data4;
201                }
202            }
203        }
204           
205        // ºÇ½ª¹ØÆþÆü»ØÄê
206        if ( (strlen($this->arrSql['buy_start_year']) > 0 && strlen($this->arrSql['buy_start_month']) > 0 && strlen($this->arrSql['buy_start_day']) > 0 ) ||
207                (strlen($this->arrSql['buy_end_year']) > 0 && strlen($this->arrSql['buy_end_month']) >0 && strlen($this->arrSql['buy_end_day']) > 0) ) {
208            $arrRegistTime = $this->selectTermRange($this->arrSql['buy_start_year'], $this->arrSql['buy_start_month'], $this->arrSql['buy_start_day']
209                            , $this->arrSql['buy_end_year'], $this->arrSql['buy_end_month'], $this->arrSql['buy_end_day'], "last_buy_date");
210            if (is_array($arrRegistTime)) {
211                foreach ($arrRegistTime as $data4) {
212                    $this->arrVal[] = $data4;
213                }
214            }
215        }
216       
217        //¹ØÆþ¾¦ÉÊ¥³¡¼¥É
218        if ( strlen($this->arrSql['buy_product_code']) > 0 ) {
219            $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 ? ))");
220            $search_buyproduct_code = $this->addSearchStr($this->arrSql['buy_product_code']);
221            $this->arrVal[] = $search_buyproduct_code;
222        }
223
224        //¹ØÆþ¾¦ÉÊ̾¾Î
225        if ( strlen($this->arrSql['buy_product_name']) > 0 ) {
226            $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 ? ))");
227            $search_buyproduct_name = $this->addSearchStr($this->arrSql['buy_product_name']);
228            $this->arrVal[] = $search_buyproduct_name;
229        }
230       
231        //¥«¥Æ¥´¥ê¡¼¤òÁªÂò¤·¤Æ¤¤¤ë¾ì¹ç¤Î¤ß¹Ê¹þ¸¡º÷¤ò¹Ô¤¦
232        if ( strlen($this->arrSql['category_id']) != ""){
233            //¥«¥Æ¥´¥ê¡¼¤Ç¹Ê¹þ¸¡º÷¤ò¹Ô¤¦SQLʸÀ¸À®
234            list($tmp_where, $tmp_arrval) = sfGetCatWhere(sfManualEscape($this->arrSql['category_id']));
235
236            //¥«¥Æ¥´¥ê¡¼¤Ç¹Ê¹þ¤ß¤¬²Äǽ¤Î¾ì¹ç
237            if($tmp_where != "") {
238                $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_products WHERE ".$tmp_where." ))) ");
239                $this->arrVal = array_merge($this->arrVal, $tmp_arrval);
240            }
241        }
242        //¡¡·ÈÂÓÅÅÏÃÈÖ¹æ
243        if ( is_numeric( $this->arrSql['cell'] ) ) {
244            $this->setWhere( "(cell01 || cell02 || cell03 LIKE ?)" );
245            $searchTel = $this->addSearchStr($this->arrSql['cell']);
246            $this->arrVal[] = ereg_replace("-", "", $searchTel);
247        }
248       
249        $this->setOrder( "customer_id DESC" );
250    }
251
252    // ¸¡º÷ÍÑSQL
253    function getList() {
254        $this->select = "SELECT customer_id,name01,name02,kana01,kana02,sex,email,tel01,tel02,tel03,pref,status FROM dtb_customer ";
255        return $this->getSql(0);   
256    }
257
258    function getListMailMagazine() {
259        $this->select = "SELECT customer_id,name01,name02,kana01,kana02,sex,email,tel01,tel02,tel03,pref, mail_flag FROM dtb_customer_mail LEFT OUTER JOIN dtb_customer USING(email)";
260        return $this->getSql(0);   
261    }
262   
263    function getListMailMagazineCount() {
264        $this->select = "SELECT COUNT(*) FROM dtb_customer_mail LEFT OUTER JOIN dtb_customer USING(email)";
265        return $this->getSql(0);   
266    }
267    //¹ØÆþ¾¦ÉÊ¥³¡¼¥É¸¡º÷ÍÑSQL
268    function getBuyList(){
269        $this->select = "SELECT A.customer_id, A.name01, A.name02, A.kana01, A.kana02, A.sex, A.email, A.tel01, A.tel02, A.tel03, A.pref, A.mail_flag, B.order_email, B.order_id, C.product_code
270                        FROM (dtb_customer LEFT OUTER JOIN dtb_customer_mail USING (email)) AS A LEFT OUTER JOIN dtb_order AS B ON
271                        A.email=B.order_email LEFT OUTER JOIN dtb_order_detail AS C ON B.order_id = C.order_id";
272    }
273
274    //¡¡¸¡º÷Áí¿ô¥«¥¦¥ó¥ÈÍÑSQL
275    function getListCount() {
276        $this->select = "SELECT COUNT (customer_id) FROM dtb_customer ";   
277        return $this->getSql(1);
278    }
279
280    //¡¡CSV¥À¥¦¥ó¥í¡¼¥ÉÍÑSQL
281    function getListCSV($arrColumnCSV) {
282        $this->arrColumnCSV = $arrColumnCSV;
283        $i = 0;
284        foreach ($this->arrColumnCSV as $val) {
285            if ($i != 0) $state .= ", ";
286            $state .= $val["sql"];
287            $i ++;
288        }
289
290        $this->select = "SELECT " .$state. " FROM dtb_customer ";
291        return $this->getSql(2);   
292    }
293   
294    function getWhere() {
295        return array($this->where, $this->arrVal);
296    }
297}
298?>
Note: See TracBrowser for help on using the repository browser.