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