/**
*
* @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;
}
