/** * * @param array $searchData * @return QueryBuilder */ public function getQueryBuilderBySearchDataForAdmin($searchData) { $qb = $this->createQueryBuilder('o'); // order_id_start if (isset($searchData['order_id_start']) && Str::isNotBlank($searchData['order_id_start'])) { $qb->andWhere('o.id >= :order_id_start')->setParameter('order_id_start', $searchData['order_id_start']); } // multi if (isset($searchData['multi']) && Str::isNotBlank($searchData['multi'])) { $multi = preg_match('/^\\d+$/', $searchData['multi']) ? $searchData['multi'] : null; $qb->andWhere('o.id = :multi OR o.name01 LIKE :likemulti OR o.name02 LIKE :likemulti OR ' . 'o.kana01 LIKE :likemulti OR o.kana02 LIKE :likemulti OR o.company_name LIKE :likemulti')->setParameter('multi', $multi)->setParameter('likemulti', '%' . $searchData['multi'] . '%'); } // order_id_end if (isset($searchData['order_id_end']) && Str::isNotBlank($searchData['order_id_end'])) { $qb->andWhere('o.id <= :order_id_end')->setParameter('order_id_end', $searchData['order_id_end']); } // status $filterStatus = false; if (!empty($searchData['status']) && $searchData['status']) { $qb->andWhere('o.OrderStatus = :status')->setParameter('status', $searchData['status']); $filterStatus = true; } if (!empty($searchData['multi_status']) && count($searchData['multi_status'])) { $qb->andWhere($qb->expr()->in('o.OrderStatus', ':multi_status'))->setParameter('multi_status', $searchData['multi_status']->toArray()); $filterStatus = true; } if (!$filterStatus) { // 購入処理中は検索対象から除外 $OrderStatuses = $this->getEntityManager()->getRepository('Eccube\\Entity\\Master\\OrderStatus')->findNotContainsBy(array('id' => $this->app['config']['order_processing'])); $qb->andWhere($qb->expr()->in('o.OrderStatus', ':status'))->setParameter('status', $OrderStatuses); } // name if (isset($searchData['name']) && Str::isNotBlank($searchData['name'])) { $qb->andWhere('CONCAT(o.name01, o.name02) LIKE :name')->setParameter('name', '%' . $searchData['name'] . '%'); } // kana if (isset($searchData['kana']) && Str::isNotBlank($searchData['kana'])) { $qb->andWhere('CONCAT(o.kana01, o.kana02) LIKE :kana')->setParameter('kana', '%' . $searchData['kana'] . '%'); } // email if (isset($searchData['email']) && Str::isNotBlank($searchData['email'])) { $qb->andWhere('o.email like :email')->setParameter('email', '%' . $searchData['email'] . '%'); } // tel if (isset($searchData['tel']) && Str::isNotBlank($searchData['tel'])) { $qb->andWhere('CONCAT(o.tel01, o.tel02, o.tel03) LIKE :tel')->setParameter('tel', '%' . $searchData['tel'] . '%'); } // sex if (!empty($searchData['sex']) && count($searchData['sex']) > 0) { $qb->andWhere($qb->expr()->in('o.Sex', ':sex'))->setParameter('sex', $searchData['sex']->toArray()); } // payment if (!empty($searchData['payment']) && count($searchData['payment'])) { $payments = array(); foreach ($searchData['payment'] as $payment) { $payments[] = $payment->getId(); } $qb->leftJoin('o.Payment', 'p')->andWhere($qb->expr()->in('p.id', ':payments'))->setParameter('payments', $payments); } // oreder_date if (!empty($searchData['order_date_start']) && $searchData['order_date_start']) { $date = $searchData['order_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('o.order_date >= :order_date_start')->setParameter('order_date_start', $date); } if (!empty($searchData['order_date_end']) && $searchData['order_date_end']) { $date = clone $searchData['order_date_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('o.order_date < :order_date_end')->setParameter('order_date_end', $date); } // payment_date if (!empty($searchData['payment_date_start']) && $searchData['payment_date_start']) { $date = $searchData['payment_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('o.payment_date >= :payment_date_start')->setParameter('payment_date_start', $date); } if (!empty($searchData['payment_date_end']) && $searchData['payment_date_end']) { $date = clone $searchData['payment_date_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('o.payment_date < :payment_date_end')->setParameter('payment_date_end', $date); } // commit_date if (!empty($searchData['commit_date_start']) && $searchData['commit_date_start']) { $date = $searchData['commit_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('o.commit_date >= :commit_date_start')->setParameter('commit_date_start', $date); } if (!empty($searchData['commit_date_end']) && $searchData['commit_date_end']) { $date = clone $searchData['commit_date_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('o.commit_date < :commit_date_end')->setParameter('commit_date_end', $date); } // update_date if (!empty($searchData['update_date_start']) && $searchData['update_date_start']) { $date = $searchData['update_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('o.update_date >= :update_date_start')->setParameter('update_date_start', $date); } if (!empty($searchData['update_date_end']) && $searchData['update_date_end']) { $date = clone $searchData['update_date_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('o.update_date < :update_date_end')->setParameter('update_date_end', $date); } // payment_total if (isset($searchData['payment_total_start']) && Str::isNotBlank($searchData['payment_total_start'])) { $qb->andWhere('o.payment_total >= :payment_total_start')->setParameter('payment_total_start', $searchData['payment_total_start']); } if (isset($searchData['payment_total_end']) && Str::isNotBlank($searchData['payment_total_end'])) { $qb->andWhere('o.payment_total <= :payment_total_end')->setParameter('payment_total_end', $searchData['payment_total_end']); } // buy_product_name if (isset($searchData['buy_product_name']) && Str::isNotBlank($searchData['buy_product_name'])) { $qb->leftJoin('o.OrderDetails', 'od')->andWhere('od.product_name LIKE :buy_product_name')->setParameter('buy_product_name', '%' . $searchData['buy_product_name'] . '%'); } // Order By $qb->addOrderBy('o.update_date', 'DESC'); return $qb; }
/** * 商品規格情報を更新 */ protected function updateProductClass($row, Product $Product, ProductClass $ProductClass, $app, $data) { $ProductClass->setProduct($Product); if ($row['商品種別(ID)'] == '') { $this->addErrors($data->key() + 1 . '行目の商品種別(ID)が設定されていません。'); } else { if (preg_match('/^\\d+$/', $row['商品種別(ID)'])) { $ProductType = $app['eccube.repository.master.product_type']->find($row['商品種別(ID)']); if (!$ProductType) { $this->addErrors($data->key() + 1 . '行目の商品種別(ID)が存在しません。'); } else { $ProductClass->setProductType($ProductType); } } else { $this->addErrors($data->key() + 1 . '行目の商品種別(ID)が存在しません。'); } } // 規格分類1、2をそれぞれセットし作成 if ($row['規格分類1(ID)'] != '') { if (preg_match('/^\\d+$/', $row['規格分類1(ID)'])) { $ClassCategory = $app['eccube.repository.class_category']->find($row['規格分類1(ID)']); if (!$ClassCategory) { $this->addErrors($data->key() + 1 . '行目の規格分類1(ID)が存在しません。'); } else { $ProductClass->setClassCategory1($ClassCategory); } } else { $this->addErrors($data->key() + 1 . '行目の規格分類1(ID)が存在しません。'); } } if ($row['規格分類2(ID)'] != '') { if (preg_match('/^\\d+$/', $row['規格分類2(ID)'])) { $ClassCategory = $app['eccube.repository.class_category']->find($row['規格分類2(ID)']); if (!$ClassCategory) { $this->addErrors($data->key() + 1 . '行目の規格分類2(ID)が存在しません。'); } else { $ProductClass->setClassCategory2($ClassCategory); } } else { $this->addErrors($data->key() + 1 . '行目の規格分類2(ID)が存在しません。'); } } if ($row['発送日目安(ID)'] != '') { if (preg_match('/^\\d+$/', $row['発送日目安(ID)'])) { $DeliveryDate = $app['eccube.repository.delivery_date']->find($row['発送日目安(ID)']); if (!$DeliveryDate) { $this->addErrors($data->key() + 1 . '行目の発送日目安(ID)が存在しません。'); } else { $ProductClass->setDeliveryDate($DeliveryDate); } } else { $this->addErrors($data->key() + 1 . '行目の発送日目安(ID)が存在しません。'); } } if (Str::isNotBlank($row['商品コード'])) { $ProductClass->setCode(Str::trimAll($row['商品コード'])); } else { $ProductClass->setCode(null); } if ($row['在庫数無制限フラグ'] == '') { $this->addErrors($data->key() + 1 . '行目の在庫数無制限フラグが設定されていません。'); } else { if ($row['在庫数無制限フラグ'] == (string) Constant::DISABLED) { $ProductClass->setStockUnlimited(Constant::DISABLED); // 在庫数が設定されていなければエラー if ($row['在庫数'] == '') { $this->addErrors($data->key() + 1 . '行目の在庫数が設定されていません。'); } else { $stock = str_replace(',', '', $row['在庫数']); if (preg_match('/^\\d+$/', $stock) && $stock >= 0) { $ProductClass->setStock($row['在庫数']); } else { $this->addErrors($data->key() + 1 . '行目の在庫数は0以上の数値を設定してください。'); } } } else { if ($row['在庫数無制限フラグ'] == (string) Constant::ENABLED) { $ProductClass->setStockUnlimited(Constant::ENABLED); $ProductClass->setStock(null); } else { $this->addErrors($data->key() + 1 . '行目の在庫数無制限フラグが設定されていません。'); } } } if ($row['販売制限数'] != '') { $saleLimit = str_replace(',', '', $row['販売制限数']); if (preg_match('/^\\d+$/', $saleLimit) && $saleLimit >= 0) { $ProductClass->setSaleLimit($saleLimit); } else { $this->addErrors($data->key() + 1 . '行目の販売制限数は0以上の数値を設定してください。'); } } if ($row['通常価格'] != '') { $price01 = str_replace(',', '', $row['通常価格']); if (preg_match('/^\\d+$/', $price01) && $price01 >= 0) { $ProductClass->setPrice01($price01); } else { $this->addErrors($data->key() + 1 . '行目の通常価格は0以上の数値を設定してください。'); } } if ($row['販売価格'] == '') { $this->addErrors($data->key() + 1 . '行目の販売価格が設定されていません。'); } else { $price02 = str_replace(',', '', $row['販売価格']); if (preg_match('/^\\d+$/', $price02) && $price02 >= 0) { $ProductClass->setPrice02($price02); } else { $this->addErrors($data->key() + 1 . '行目の販売価格は0以上の数値を設定してください。'); } } if ($row['商品規格削除フラグ'] == '') { $ProductClass->setDelFlg(Constant::DISABLED); } else { if ($row['商品規格削除フラグ'] == (string) Constant::DISABLED || $row['商品規格削除フラグ'] == (string) Constant::ENABLED) { $ProductClass->setDelFlg($row['商品規格削除フラグ']); } else { $this->addErrors($data->key() + 1 . '行目の商品規格削除フラグが設定されていません。'); } } $ProductStock = $ProductClass->getProductStock(); if (!$ProductClass->getStockUnlimited()) { $ProductStock->setStock($ProductClass->getStock()); } else { // 在庫無制限時はnullを設定 $ProductStock->setStock(null); } return $ProductClass; }
public function getQueryBuilderBySearchData($searchData) { $qb = $this->createQueryBuilder('c')->select('c')->andWhere('c.del_flg = 0'); if (isset($searchData['multi']) && Str::isNotBlank($searchData['multi'])) { //スペース除去 $clean_key_multi = preg_replace('/\\s+|[ ]+/u', '', $searchData['multi']); if (preg_match('/^\\d+$/', $clean_key_multi)) { $qb->andWhere('c.id = :customer_id')->setParameter('customer_id', $clean_key_multi); } else { $qb->andWhere('CONCAT(c.name01, c.name02) LIKE :name OR CONCAT(c.kana01, c.kana02) LIKE :kana OR c.email LIKE :email')->setParameter('name', '%' . $clean_key_multi . '%')->setParameter('kana', '%' . $clean_key_multi . '%')->setParameter('email', '%' . $clean_key_multi . '%'); } } // Pref if (!empty($searchData['pref']) && $searchData['pref']) { $qb->andWhere('c.Pref = :pref')->setParameter('pref', $searchData['pref']->getId()); } // sex if (!empty($searchData['sex']) && count($searchData['sex']) > 0) { $sexs = array(); foreach ($searchData['sex'] as $sex) { $sexs[] = $sex->getId(); } $qb->andWhere($qb->expr()->in('c.Sex', ':sexs'))->setParameter('sexs', $sexs); } if (!empty($searchData['birth_month']) && $searchData['birth_month']) { $qb->andWhere('EXTRACT(MONTH FROM c.birth) = :birth_month')->setParameter('birth_month', $searchData['birth_month']); } // birth if (!empty($searchData['birth_start']) && $searchData['birth_start']) { $date = $searchData['birth_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.birth >= :birth_start')->setParameter('birth_start', $date); } if (!empty($searchData['birth_end']) && $searchData['birth_end']) { $date = clone $searchData['birth_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.birth < :birth_end')->setParameter('birth_end', $date); } // tel if (isset($searchData['tel']) && Str::isNotBlank($searchData['tel'])) { $qb->andWhere('CONCAT(c.tel01, c.tel02, c.tel03) LIKE :tel')->setParameter('tel', '%' . $searchData['tel'] . '%'); } // buy_total if (isset($searchData['buy_total_start']) && Str::isNotBlank($searchData['buy_total_start'])) { $qb->andWhere('c.buy_total >= :buy_total_start')->setParameter('buy_total_start', $searchData['buy_total_start']); } if (isset($searchData['buy_total_end']) && Str::isNotBlank($searchData['buy_total_end'])) { $qb->andWhere('c.buy_total <= :buy_total_end')->setParameter('buy_total_end', $searchData['buy_total_end']); } // buy_times if (!empty($searchData['buy_times_start']) && $searchData['buy_times_start']) { $qb->andWhere('c.buy_times >= :buy_times_start')->setParameter('buy_times_start', $searchData['buy_times_start']); } if (!empty($searchData['buy_times_end']) && $searchData['buy_times_end']) { $qb->andWhere('c.buy_times <= :buy_times_end')->setParameter('buy_times_end', $searchData['buy_times_end']); } // create_date if (!empty($searchData['create_date_start']) && $searchData['create_date_start']) { $date = $searchData['create_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.create_date >= :create_date_start')->setParameter('create_date_start', $date); } if (!empty($searchData['create_date_end']) && $searchData['create_date_end']) { $date = clone $searchData['create_date_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.create_date < :create_date_end')->setParameter('create_date_end', $date); } // update_date if (!empty($searchData['update_date_start']) && $searchData['update_date_start']) { $date = $searchData['update_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.update_date >= :update_date_start')->setParameter('update_date_start', $date); } if (!empty($searchData['update_date_end']) && $searchData['update_date_end']) { $date = clone $searchData['update_date_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.update_date < :update_date_end')->setParameter('update_date_end', $date); } // last_buy if (!empty($searchData['last_buy_start']) && $searchData['last_buy_start']) { $date = $searchData['last_buy_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.last_buy_date >= :last_buy_start')->setParameter('last_buy_start', $date); } if (!empty($searchData['last_buy_end']) && $searchData['last_buy_end']) { $date = clone $searchData['last_buy_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.last_buy_date < :last_buy_end')->setParameter('last_buy_end', $date); } // status if (!empty($searchData['customer_status']) && count($searchData['customer_status']) > 0) { $qb->andWhere($qb->expr()->in('c.Status', ':statuses'))->setParameter('statuses', $searchData['customer_status']); } // buy_product_name、buy_product_code if (isset($searchData['buy_product_code']) && Str::isNotBlank($searchData['buy_product_code'])) { $qb->leftJoin('c.Orders', 'o')->leftJoin('o.OrderDetails', 'od')->andWhere('od.product_name LIKE :buy_product_name OR od.product_code LIKE :buy_product_name')->setParameter('buy_product_name', '%' . $searchData['buy_product_code'] . '%'); } // Order By $qb->addOrderBy('c.update_date', 'DESC'); return $qb; }
/** * get query builder. * * @param array $searchData * @return \Doctrine\ORM\QueryBuilder */ public function getQueryBuilderBySearchDataForAdmin($searchData) { $qb = $this->createQueryBuilder('p')->innerJoin('p.ProductClasses', 'pc'); // id if (isset($searchData['id']) && Str::isNotBlank($searchData['id'])) { $id = preg_match('/^\\d+$/', $searchData['id']) ? $searchData['id'] : null; $qb->andWhere('p.id = :id OR p.name LIKE :likeid OR pc.code LIKE :likeid')->setParameter('id', $id)->setParameter('likeid', '%' . $searchData['id'] . '%'); } // code /* if (!empty($searchData['code']) && $searchData['code']) { $qb ->innerJoin('p.ProductClasses', 'pc') ->andWhere('pc.code LIKE :code') ->setParameter('code', '%' . $searchData['code'] . '%'); } // name if (!empty($searchData['name']) && $searchData['name']) { $keywords = preg_split('/[\s ]+/u', $searchData['name'], -1, PREG_SPLIT_NO_EMPTY); foreach ($keywords as $keyword) { $qb ->andWhere('p.name LIKE :name') ->setParameter('name', '%' . $keyword . '%'); } } */ // category if (!empty($searchData['category_id']) && $searchData['category_id']) { $Categories = $searchData['category_id']->getSelfAndDescendants(); if ($Categories) { $qb->innerJoin('p.ProductCategories', 'pct')->innerJoin('pct.Category', 'c')->andWhere($qb->expr()->in('pct.Category', ':Categories'))->setParameter('Categories', $Categories); } } // status if (!empty($searchData['status']) && $searchData['status']->toArray()) { $qb->andWhere($qb->expr()->in('p.Status', ':Status'))->setParameter('Status', $searchData['status']->toArray()); } // link_status if (isset($searchData['link_status'])) { $qb->andWhere($qb->expr()->in('p.Status', ':Status'))->setParameter('Status', $searchData['link_status']); } // stock status if (isset($searchData['stock_status'])) { $qb->andWhere('pc.stock_unlimited = :StockUnlimited AND pc.stock = 0')->setParameter('StockUnlimited', $searchData['stock_status']); } // crate_date if (!empty($searchData['create_date_start']) && $searchData['create_date_start']) { $date = $searchData['create_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('p.create_date >= :create_date_start')->setParameter('create_date_start', $date); } if (!empty($searchData['create_date_end']) && $searchData['create_date_end']) { $date = clone $searchData['create_date_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('p.create_date < :create_date_end')->setParameter('create_date_end', $date); } // update_date if (!empty($searchData['update_date_start']) && $searchData['update_date_start']) { $date = $searchData['update_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('p.update_date >= :update_date_start')->setParameter('update_date_start', $date); } if (!empty($searchData['update_date_end']) && $searchData['update_date_end']) { $date = clone $searchData['update_date_end']; $date = $date->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('p.update_date < :update_date_end')->setParameter('update_date_end', $date); } // Order By $qb->orderBy('p.update_date', 'DESC'); return $qb; }
/** * @expectedException PHPUnit_Framework_Error_Deprecated */ public function testIsNotBlankWithArray() { $text = array(); $this->actual = Str::isNotBlank($text); // E_USER_DEPRECATED がスローされるのでテストできないが false になるはず $this->assertFalse($this->actual); }
/** * 検索条件での検索を行う。 * s * @param unknown $searchData * @return \Doctrine\ORM\QueryBuilder */ public function getQueryBuilderBySearchData($searchData) { // メルマガを受け取るカスタマーのIDを取得する $mailmagaCustomerIds = $this->getMailmagaCustomerIds(); $qb = $this->createQueryBuilder('c')->select('c')->andWhere('c.del_flg = 0'); // メルマガを受け取るカスタマーのみに絞る if (count($mailmagaCustomerIds) > 0) { // メルマガ送付カスタマーがいれば対象カスタマーのみ対象とする $qb->andWhere($qb->expr()->in('c.id', $mailmagaCustomerIds)); } else { // メルマガ送付カスタマーがいなければ強制的に非表示にする条件を追加する $qb->andWhere('c.id < 0'); } if (isset($searchData['multi']) && Str::isNotBlank($searchData['multi'])) { //スペース除去 $clean_key_multi = preg_replace('/\\s+|[ ]+/u', '', $searchData['multi']); if (preg_match('/^\\d+$/', $clean_key_multi)) { $qb->andWhere('c.id = :customer_id')->setParameter('customer_id', $clean_key_multi); } else { $qb->andWhere('CONCAT(c.name01, c.name02) LIKE :name OR CONCAT(c.kana01, c.kana02) LIKE :kana OR c.email LIKE :email')->setParameter('name', '%' . $clean_key_multi . '%')->setParameter('kana', '%' . $clean_key_multi . '%')->setParameter('email', '%' . $clean_key_multi . '%'); } } // Pref if (!empty($searchData['pref']) && $searchData['pref']) { $qb->andWhere('c.Pref = :pref')->setParameter('pref', $searchData['pref']->getId()); } // sex if (!empty($searchData['sex']) && count($searchData['sex']) > 0) { $sexs = array(); foreach ($searchData['sex'] as $sex) { $sexs[] = $sex->getId(); } $qb->andWhere($qb->expr()->in('c.Sex', ':sexs'))->setParameter('sexs', $sexs); } // birth_month if (is_int($searchData['birth_month'])) { //Birth month start from 0 so we need plus 1. $searchData['birth_month']++; $birthMonth = $searchData['birth_month']; if ($searchData['birth_month'] < 10) { $birthMonth = '0' . $searchData['birth_month']; } //because can not use SUBSTRING with timestamp field. Use concat for convert to date => String(can not use convert func in doctrine) $qb->andWhere("SUBSTRING(CONCAT(c.birth, '0'), 6, 2) = :birth_month")->setParameter('birth_month', $birthMonth); } // birth if (!empty($searchData['birth_start']) && $searchData['birth_start']) { $date = $searchData['birth_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.birth >= :birth_start')->setParameter('birth_start', $date); } if (!empty($searchData['birth_end']) && $searchData['birth_end']) { $date = $searchData['birth_end']->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.birth < :birth_end')->setParameter('birth_end', $date); } // tel if (!empty($searchData['tel01']) && $searchData['tel01']) { $qb->andWhere('c.tel01 = :tel01')->setParameter('tel01', $searchData['tel01']); } if (!empty($searchData['tel02']) && $searchData['tel02']) { $qb->andWhere('c.tel02 = :tel02')->setParameter('tel02', $searchData['tel02']); } if (!empty($searchData['tel03']) && $searchData['tel03']) { $qb->andWhere('c.tel03 = :tel03')->setParameter('tel03', $searchData['tel03']); } // buy_total if (!empty($searchData['buy_total_start']) && $searchData['buy_total_start']) { $qb->andWhere('c.buy_total >= :buy_total_start')->setParameter('buy_total_start', $searchData['buy_total_start']); } if (!empty($searchData['buy_total_end']) && $searchData['buy_total_end']) { $qb->andWhere('c.buy_total <= :buy_total_end')->setParameter('buy_total_end', $searchData['buy_total_end']); } // buy_times if (!empty($searchData['buy_times_start']) && $searchData['buy_times_start']) { $qb->andWhere('c.buy_times >= :buy_times_start')->setParameter('buy_times_start', $searchData['buy_times_start']); } if (!empty($searchData['buy_times_end']) && $searchData['buy_times_end']) { $qb->andWhere('c.buy_times <= :buy_times_end')->setParameter('buy_times_end', $searchData['buy_times_end']); } // create_date if (!empty($searchData['create_date_start']) && $searchData['create_date_start']) { $date = $searchData['create_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.create_date >= :create_date_start')->setParameter('create_date_start', $date); } if (!empty($searchData['create_date_end']) && $searchData['create_date_end']) { $date = $searchData['create_date_end']->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.create_date < :create_date_end')->setParameter('create_date_end', $date); } // update_date if (!empty($searchData['update_date_start']) && $searchData['update_date_start']) { $date = $searchData['update_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.update_date >= :update_date_start')->setParameter('update_date_start', $date); } if (!empty($searchData['update_date_end']) && $searchData['update_date_end']) { $date = $searchData['update_date_end']->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.update_date < :update_date_end')->setParameter('update_date_end', $date); } // last_buy if (!empty($searchData['last_buy_start']) && $searchData['last_buy_start']) { $date = $searchData['last_buy_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.last_buy_date >= :last_buy_start')->setParameter('last_buy_start', $date); } if (!empty($searchData['last_buy_end']) && $searchData['last_buy_end']) { $date = $searchData['last_buy_end']->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.last_buy_date < :last_buy_end')->setParameter('last_buy_end', $date); } // status if (!empty($searchData['customer_status']) && count($searchData['customer_status']) > 0) { $customerStatus = array(); foreach ($searchData['customer_status'] as $val) { $customerStatus[] = $val->getId(); } $qb->andWhere($qb->expr()->in('c.Status', ':statuses'))->setParameter('statuses', $customerStatus); } // buy_product_name、buy_product_code if (!empty($searchData['buy_product_code']) && $searchData['buy_product_code']) { $qb->leftJoin('c.Orders', 'o')->leftJoin('o.OrderDetails', 'od')->andWhere('od.product_name LIKE :buy_product_name OR od.product_code LIKE :buy_product_name')->setParameter('buy_product_name', '%' . $searchData['buy_product_code'] . '%'); } // Order By $qb->addOrderBy('c.update_date', 'DESC'); return $qb; }
/** * 検索条件での検索を行う。 * s * @param unknown $searchData * @return \Doctrine\ORM\QueryBuilder */ public function getQueryBuilderBySearchData($searchData) { // メルマガを受け取るカスタマーのIDを取得する $mailmagaCustomerIds = $this->getMailmagaCustomerIds(); $qb = $this->createQueryBuilder('c')->select('c')->andWhere('c.del_flg = 0'); // メルマガを受け取るカスタマーのみに絞る if (count($mailmagaCustomerIds) > 0) { // メルマガ送付カスタマーがいれば対象カスタマーのみ対象とする $qb->andWhere($qb->expr()->in('c.id', $mailmagaCustomerIds)); } else { // メルマガ送付カスタマーがいなければ強制的に非表示にする条件を追加する $qb->andWhere('c.id < 0'); } if (isset($searchData['multi']) && Str::isNotBlank($searchData['multi'])) { //スペース除去 $clean_key_multi = preg_replace('/\\s+|[ ]+/u', '', $searchData['multi']); if (preg_match('/^\\d+$/', $clean_key_multi)) { $qb->andWhere('c.id = :customer_id')->setParameter('customer_id', $clean_key_multi); } else { $qb->andWhere('CONCAT(c.name01, c.name02) LIKE :name OR CONCAT(c.kana01, c.kana02) LIKE :kana OR c.email LIKE :email')->setParameter('name', '%' . $clean_key_multi . '%')->setParameter('kana', '%' . $clean_key_multi . '%')->setParameter('email', '%' . $clean_key_multi . '%'); } } // Pref if (!empty($searchData['pref']) && $searchData['pref']) { $qb->andWhere('c.Pref = :pref')->setParameter('pref', $searchData['pref']->getId()); } // sex if (!empty($searchData['sex']) && count($searchData['sex']) > 0) { $sexs = array(); foreach ($searchData['sex'] as $sex) { $sexs[] = $sex->getId(); } $qb->andWhere($qb->expr()->in('c.Sex', ':sexs'))->setParameter('sexs', $sexs); } // birth_month if (!empty($searchData['birth_month']) && $searchData['birth_month']) { // TODO: http://docs.symfony.gr.jp/symfony2/cookbook/doctrine/custom_dql_functions.html // $qb // ->andWhere('extract(month from c.birth) = :birth_month') // ->setParameter('birth_month', $searchData['birth_month']); } // birth if (!empty($searchData['birth_start']) && $searchData['birth_start']) { $date = $searchData['birth_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.birth >= :birth_start')->setParameter('birth_start', $date); } if (!empty($searchData['birth_end']) && $searchData['birth_end']) { $date = $searchData['birth_end']->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.birth < :birth_end')->setParameter('birth_end', $date); } // tel if (!empty($searchData['tel01']) && $searchData['tel01']) { $qb->andWhere('c.tel01 = :tel01')->setParameter('tel01', $searchData['tel01']); } if (!empty($searchData['tel02']) && $searchData['tel02']) { $qb->andWhere('c.tel02 = :tel02')->setParameter('tel02', $searchData['tel02']); } if (!empty($searchData['tel03']) && $searchData['tel03']) { $qb->andWhere('c.tel03 = :tel03')->setParameter('tel03', $searchData['tel03']); } // buy_total if (!empty($searchData['buy_total_start']) && $searchData['buy_total_start']) { $qb->andWhere('c.buy_total >= :buy_total_start')->setParameter('buy_total_start', $searchData['buy_total_start']); } if (!empty($searchData['buy_total_end']) && $searchData['buy_total_end']) { $qb->andWhere('c.buy_total <= :buy_total_end')->setParameter('buy_total_end', $searchData['buy_total_end']); } // buy_times if (!empty($searchData['buy_times_start']) && $searchData['buy_times_start']) { $qb->andWhere('c.buy_times >= :buy_times_start')->setParameter('buy_times_start', $searchData['buy_times_start']); } if (!empty($searchData['buy_times_end']) && $searchData['buy_times_end']) { $qb->andWhere('c.buy_times <= :buy_times_end')->setParameter('buy_times_end', $searchData['buy_times_end']); } // create_date if (!empty($searchData['create_date_start']) && $searchData['create_date_start']) { $date = $searchData['create_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.create_date >= :create_date_start')->setParameter('create_date_start', $date); } if (!empty($searchData['create_date_end']) && $searchData['create_date_end']) { $date = $searchData['create_date_end']->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.create_date < :create_date_end')->setParameter('create_date_end', $date); } // update_date if (!empty($searchData['update_date_start']) && $searchData['update_date_start']) { $date = $searchData['update_date_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.update_date >= :update_date_start')->setParameter('update_date_start', $date); } if (!empty($searchData['update_date_end']) && $searchData['update_date_end']) { $date = $searchData['update_date_end']->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.update_date < :update_date_end')->setParameter('update_date_end', $date); } // last_buy if (!empty($searchData['last_buy_start']) && $searchData['last_buy_start']) { $date = $searchData['last_buy_start']->format('Y-m-d H:i:s'); $qb->andWhere('c.last_buy_date >= :last_buy_start')->setParameter('last_buy_start', $date); } if (!empty($searchData['last_buy_end']) && $searchData['last_buy_end']) { $date = $searchData['last_buy_end']->modify('+1 days')->format('Y-m-d H:i:s'); $qb->andWhere('c.last_buy_date < :last_buy_end')->setParameter('last_buy_end', $date); } // status if (!empty($searchData['customer_status']) && count($searchData['customer_status']) > 0) { $customerStatus = array(); foreach ($searchData['customer_status'] as $val) { $customerStatus[] = $val->getId(); } $qb->andWhere($qb->expr()->in('c.Status', ':statuses'))->setParameter('statuses', $customerStatus); } // buy_product_name、buy_product_code if (!empty($searchData['buy_product_code']) && $searchData['buy_product_code']) { $qb->leftJoin('c.Orders', 'o')->leftJoin('o.OrderDetails', 'od')->andWhere('od.product_name LIKE :buy_product_name OR od.product_code LIKE :buy_product_name')->setParameter('buy_product_name', '%' . $searchData['buy_product_code'] . '%'); } // Order By $qb->addOrderBy('c.update_date', 'DESC'); return $qb; }