Symfony Profiler

<?php
namespace Customize\Repository;
use Customize\Entity\EstimateShipping;
use Doctrine\ORM\NoResultException;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry as RegistryInterface;
use Eccube\Doctrine\Query\Queries;
use Eccube\Entity\Customer;
use Customize\Entity\Master\EstimateStatus;
use Eccube\Entity\Master\Sex;
use Customize\Entity\Estimate;
use Eccube\Entity\Payment;
use Eccube\Repository\AbstractRepository;
use Eccube\Util\StringUtil;
/**
* EstimateRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class EstimateRepository extends AbstractRepository
{
/**
* @var Queries
*/
protected $queries;
public const COLUMNS = [
'estimate' => 'e.name01', 'estimator' => 'e.id', 'estimate_shipping_id' => 's.id', 'purchase_product' => 'ei.product_name', 'quantity' => 'ei.quantity', 'payment_method' => 'e.payment_method', 'estimate_status' => 'e.EstimateStatus', 'purchase_price' => 'e.total', 'shipping_status' => 's.shipping_date', 'tracking_number' => 's.tracking_number', 'delivery' => 's.name01',
];
/**
* EstimateRepository constructor.
*
* @param RegistryInterface $registry
* @param Queries $queries
*/
public function __construct(RegistryInterface $registry, Queries $queries)
{
parent::__construct($registry, Estimate::class);
$this->queries = $queries;
}
/**
* @param int $estimateId
* @param EstimateStatus $Status
*/
public function changeStatus($estimateId, EstimateStatus $Status)
{
$Estimate = $this
->find($estimateId)
->setEstimateStatus($Status)
;
switch ($Status->getId()) {
case '6': // 入金済へ
$Estimate->setPaymentDate(new \DateTime());
break;
}
$em = $this->getEntityManager();
$em->persist($Estimate);
$em->flush();
}
/**
* @param array{
* estimate_id?:string|int,
* estimate_no?:string,
* estimate_id_start?:string|int,
* estimate_id_end?:string|int,
* multi?:string|int|null,
* status?:EstimateStatus[]|int[],
* company_name?:string,
* name?:string,
* kana?:string,
* email?:string,
* phone_number?:string,
* sex?:Sex[],
* payment?:Payment[],
* estimate_datetime_start?:\DateTime,
* estimate_datetime_end?:\DateTime,
* estimate_date_start?:\DateTime,
* estimate_date_end?:\DateTime,
* payment_datetime_start?:\DateTime,
* payment_datetime_end?:\DateTime,
* payment_date_start?:\DateTime,
* payment_date_end?:\DateTime,
* update_datetime_start?:\DateTime,
* update_datetime_end?:\DateTime,
* update_date_start?:\DateTime,
* update_date_end?:\DateTime,
* payment_total_start?:string|int,
* payment_total_end?:string|int,
* payment_product_name?:string,
* shipping_mail?:Shipping::SHIPPING_MAIL_UNSENT|Shipping::SHIPPING_MAIL_SENT,
* tracking_number?:string,
* shipping_delivery_datetime_start?:\DateTime,
* shipping_delivery_datetime_end?:\DateTime,
* shipping_delivery_date_start?:\DateTime,
* shipping_delivery_date_end?:\DateTime,
* sortkey?:string,
* sorttype?:string
* } $searchData
*
* @return QueryBuilder
*/
public function getQueryBuilderBySearchDataForAdmin($searchData)
{
$qb = $this->createQueryBuilder('e')
->select('e, s')
->addSelect('ei', 'pref')
->leftJoin('e.EstimateItems', 'ei')
->leftJoin('e.Pref', 'pref')
->innerJoin('e.EstimateShippings', 's');
// estimate_id_start
if (isset($searchData['estimate_id']) && StringUtil::isNotBlank($searchData['estimate_id'])) {
$qb
->andWhere('e.id = :estimate_id')
->setParameter('estimate_id', $searchData['estimate_id']);
}
// estimate_no
if (isset($searchData['estimate_no']) && StringUtil::isNotBlank($searchData['estimate_no'])) {
$qb
->andWhere('e.estimate_no = :estimate_no')
->setParameter('estimate_no', $searchData['estimate_no']);
}
// sticker_code
if (isset($searchData['sticker_code']) && StringUtil::isNotBlank($searchData['sticker_code'])) {
$qb
->andWhere('ei.sticker_code LIKE :sticker_code')
->setParameter('sticker_code', '%'.$searchData['sticker_code'].'%');
}
// estimate_id_start
if (isset($searchData['estimate_id_start']) && StringUtil::isNotBlank($searchData['estimate_id_start'])) {
$qb
->andWhere('e.id >= :estimate_id_start')
->setParameter('estimate_id_start', $searchData['estimate_id_start']);
}
// multi
if (isset($searchData['multi']) && StringUtil::isNotBlank($searchData['multi'])) {
// スペース除去
$clean_key_multi = preg_replace('/\s+|[　]+/u', '', $searchData['multi']);
$multi = preg_match('/^\d{0,10}$/', $clean_key_multi) ? $clean_key_multi : null;
if ($multi && $multi > '2147483647' && $this->isPostgreSQL()) {
$multi = null;
}
$qb
->andWhere('e.id = :multi OR CONCAT(e.name01, e.name02) LIKE :likemulti OR '.
"CONCAT(COALESCE(e.kana01, ''), COALESCE(e.kana02, '')) LIKE :likemulti OR e.company_name LIKE :multi_company_name OR ".
'e.estimate_no LIKE :likemulti OR e.email LIKE :likemulti OR e.phone_number LIKE :likemulti')
->setParameter('multi', $multi)
->setParameter('likemulti', '%'.$clean_key_multi.'%')
->setParameter('multi_company_name', '%'.$searchData['multi'].'%'); // 会社名はスペースを除去せず検索
}
// estimate_id_end
if (isset($searchData['estimate_id_end']) && StringUtil::isNotBlank($searchData['estimate_id_end'])) {
$qb
->andWhere('e.id <= :estimate_id_end')
->setParameter('estimate_id_end', $searchData['estimate_id_end']);
}
// status
$filterStatus = false;
if (!empty($searchData['status']) && count($searchData['status'])) {
$qb
->andWhere($qb->expr()->in('e.EstimateStatus', ':status'))
->setParameter('status', $searchData['status']);
$filterStatus = true;
}
if (!$filterStatus) {
// 購入処理中, 決済処理中は検索対象から除外
$qb->andWhere($qb->expr()->notIn('e.EstimateStatus', ':status'))
->setParameter('status', [EstimateStatus::APPROVAL_PENDING]);
}
// company_name
if (isset($searchData['company_name']) && StringUtil::isNotBlank($searchData['company_name'])) {
$qb
->andWhere('e.company_name LIKE :company_name')
->setParameter('company_name', '%'.$searchData['company_name'].'%');
}
// name
if (isset($searchData['name']) && StringUtil::isNotBlank($searchData['name'])) {
$clean_name = preg_replace('/\s+|[　]+/u', '', $searchData['name']);
$qb
->andWhere('CONCAT(e.name01, e.name02) LIKE :name')
->setParameter('name', '%'.$clean_name.'%');
}
// kana
if (isset($searchData['kana']) && StringUtil::isNotBlank($searchData['kana'])) {
$clean_kana = preg_replace('/\s+|[　]+/u', '', $searchData['kana']);
$qb
->andWhere("CONCAT(COALESCE(e.kana01, ''), COALESCE(e.kana02, '')) LIKE :kana")
->setParameter('kana', '%'.$clean_kana.'%');
}
// email
if (isset($searchData['email']) && StringUtil::isNotBlank($searchData['email'])) {
$qb
->andWhere('e.email like :email')
->setParameter('email', '%'.$searchData['email'].'%');
}
// tel
if (isset($searchData['phone_number']) && StringUtil::isNotBlank($searchData['phone_number'])) {
$tel = preg_replace('/[^0-9]/', '', $searchData['phone_number']);
$qb
->andWhere('e.phone_number LIKE :phone_number')
->setParameter('phone_number', '%'.$tel.'%');
}
// sex
if (!empty($searchData['sex']) && count($searchData['sex']) > 0) {
$qb
->andWhere($qb->expr()->in('e.Sex', ':sex'))
->setParameter('sex', $searchData['sex']->toArray());
}
// payment
if (!empty($searchData['payment']) && count($searchData['payment'])) {
$payments = [];
foreach ($searchData['payment'] as $payment) {
$payments[] = $payment->getId();
}
$qb
->leftJoin('e.Payment', 'p')
->andWhere($qb->expr()->in('p.id', ':payments'))
->setParameter('payments', $payments);
}
// oreder_date
if (!empty($searchData['estimate_datetime_start']) && $searchData['estimate_datetime_start']) {
$date = $searchData['estimate_datetime_start'];
$qb
->andWhere('e.estimate_date >= :estimate_date_start')
->setParameter('estimate_date_start', $date);
} elseif (!empty($searchData['estimate_date_start']) && $searchData['estimate_date_start']) {
$date = $searchData['estimate_date_start'];
$qb
->andWhere('e.estimate_date >= :estimate_date_start')
->setParameter('estimate_date_start', $date);
}
if (!empty($searchData['estimate_datetime_end']) && $searchData['estimate_datetime_end']) {
$date = $searchData['estimate_datetime_end'];
$qb
->andWhere('e.estimate_date < :estimate_date_end')
->setParameter('estimate_date_end', $date);
} elseif (!empty($searchData['estimate_date_end']) && $searchData['estimate_date_end']) {
$date = clone $searchData['estimate_date_end'];
$date = $date
->modify('+1 days');
$qb
->andWhere('e.estimate_date < :estimate_date_end')
->setParameter('estimate_date_end', $date);
}
// payment_date
if (!empty($searchData['payment_datetime_start']) && $searchData['payment_datetime_start']) {
$date = $searchData['payment_datetime_start'];
$qb
->andWhere('e.payment_date >= :payment_date_start')
->setParameter('payment_date_start', $date);
} elseif (!empty($searchData['payment_date_start']) && $searchData['payment_date_start']) {
$date = $searchData['payment_date_start'];
$qb
->andWhere('e.payment_date >= :payment_date_start')
->setParameter('payment_date_start', $date);
}
if (!empty($searchData['payment_datetime_end']) && $searchData['payment_datetime_end']) {
$date = $searchData['payment_datetime_end'];
$qb
->andWhere('e.payment_date < :payment_date_end')
->setParameter('payment_date_end', $date);
} elseif (!empty($searchData['payment_date_end']) && $searchData['payment_date_end']) {
$date = clone $searchData['payment_date_end'];
$date = $date
->modify('+1 days');
$qb
->andWhere('e.payment_date < :payment_date_end')
->setParameter('payment_date_end', $date);
}
// update_date
if (!empty($searchData['update_datetime_start']) && $searchData['update_datetime_start']) {
$date = $searchData['update_datetime_start'];
$qb
->andWhere('e.update_date >= :update_date_start')
->setParameter('update_date_start', $date);
} elseif (!empty($searchData['update_date_start']) && $searchData['update_date_start']) {
$date = $searchData['update_date_start'];
$qb
->andWhere('e.update_date >= :update_date_start')
->setParameter('update_date_start', $date);
}
if (!empty($searchData['update_datetime_end']) && $searchData['update_datetime_end']) {
$date = $searchData['update_datetime_end'];
$qb
->andWhere('e.update_date < :update_date_end')
->setParameter('update_date_end', $date);
} elseif (!empty($searchData['update_date_end']) && $searchData['update_date_end']) {
$date = clone $searchData['update_date_end'];
$date = $date
->modify('+1 days');
$qb
->andWhere('e.update_date < :update_date_end')
->setParameter('update_date_end', $date);
}
// payment_total
if (isset($searchData['payment_total_start']) && StringUtil::isNotBlank($searchData['payment_total_start'])) {
$qb
->andWhere('e.payment_total >= :payment_total_start')
->setParameter('payment_total_start', $searchData['payment_total_start']);
}
if (isset($searchData['payment_total_end']) && StringUtil::isNotBlank($searchData['payment_total_end'])) {
$qb
->andWhere('e.payment_total <= :payment_total_end')
->setParameter('payment_total_end', $searchData['payment_total_end']);
}
// buy_product_name
if (isset($searchData['buy_product_name']) && StringUtil::isNotBlank($searchData['buy_product_name'])) {
$qb
->andWhere('ei.product_name LIKE :buy_product_name')
->setParameter('buy_product_name', '%'.$searchData['buy_product_name'].'%');
}
// 発送メール送信/未送信.
if (isset($searchData['shipping_mail']) && $count = count($searchData['shipping_mail'])) {
// 送信済/未送信両方にチェックされている場合は検索条件に追加しない
if ($count < 2) {
$checked = current($searchData['shipping_mail']);
if ($checked == EstimateShipping::SHIPPING_MAIL_UNSENT) {
// 未送信
$qb
->andWhere('s.mail_send_date IS NULL');
} elseif ($checked == EstimateShipping::SHIPPING_MAIL_SENT) {
// 送信
$qb
->andWhere('s.mail_send_date IS NOT NULL');
}
}
}
// 送り状番号.
if (!empty($searchData['tracking_number'])) {
$qb
->andWhere('s.tracking_number = :tracking_number')
->setParameter('tracking_number', $searchData['tracking_number']);
}
// お届け予定日(Shipping.delivery_date)
if (!empty($searchData['shipping_delivery_datetime_start']) && $searchData['shipping_delivery_datetime_start']) {
$date = $searchData['shipping_delivery_datetime_start'];
$qb
->andWhere('s.shipping_delivery_date >= :shipping_delivery_date_start')
->setParameter('shipping_delivery_date_start', $date);
} elseif (!empty($searchData['shipping_delivery_date_start']) && $searchData['shipping_delivery_date_start']) {
$date = $searchData['shipping_delivery_date_start'];
$qb
->andWhere('s.shipping_delivery_date >= :shipping_delivery_date_start')
->setParameter('shipping_delivery_date_start', $date);
}
if (!empty($searchData['shipping_delivery_datetime_end']) && $searchData['shipping_delivery_datetime_end']) {
$date = $searchData['shipping_delivery_datetime_end'];
$qb
->andWhere('s.shipping_delivery_date < :shipping_delivery_date_end')
->setParameter('shipping_delivery_date_end', $date);
} elseif (!empty($searchData['shipping_delivery_date_end']) && $searchData['shipping_delivery_date_end']) {
$date = clone $searchData['shipping_delivery_date_end'];
$date = $date
->modify('+1 days');
$qb
->andWhere('s.shipping_delivery_date < :shipping_delivery_date_end')
->setParameter('shipping_delivery_date_end', $date);
}
// Estimate By
if (isset($searchData['sortkey']) && !empty($searchData['sortkey'])) {
$sortEstimate = (isset($searchData['sorttype']) && $searchData['sorttype'] == 'a') ? 'ASC' : 'DESC';
$qb->orderBy(self::COLUMNS[$searchData['sortkey']], $sortEstimate);
$qb->addOrderBy('e.update_date', 'DESC');
$qb->addOrderBy('e.id', 'DESC');
} else {
$qb->orderBy('e.update_date', 'DESC');
$qb->addOrderBy('e.id', 'DESC');
}
return $this->queries->customize(CustomQueryKey::ESTIMATE_SEARCH_ADMIN, $qb, $searchData);
}
/**
* @param \Eccube\Entity\Customer $Customer
*
* @return QueryBuilder
*/
public function getQueryBuilderByCustomer(Customer $Customer)
{
$qb = $this->createQueryBuilder('e')
->where('e.Customer = :Customer')
->setParameter('Customer', $Customer);
// Order By
$qb->addOrderBy('e.id', 'DESC');
return $this->queries->customize(CustomQueryKey::ESTIMATE_SEARCH_BY_CUSTOMER, $qb, ['customer' => $Customer]);
}
/**
* ステータスごとの受注件数を取得する.
*
* @param integer $EstimateStatusOrId
*
* @return int
*
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function countByEstimateStatus($EstimateStatusOrId)
{
return (int) $this->createQueryBuilder('e')
->select('COALESCE(COUNT(e.id), 0)')
->where('e.EstimateStatus = :EstimateStatus')
->setParameter('EstimateStatus', $EstimateStatusOrId)
->getQuery()
->getSingleScalarResult();
}
/**
* 会員の購入金額, 購入回数, 初回購入日, 最終購入費を更新する
*
* @param Customer $Customer
* @param array $EstimateStatuses
*/
public function updateEstimateSummary(Customer $Customer, array $EstimateStatuses = [EstimateStatus::NEW, EstimateStatus::IN_PROGRESS, EstimateStatus::APPROVAL_PENDING, EstimateStatus::FIXED])
{
try {
$result = $this->createQueryBuilder('e')
->select('COUNT(e.id) AS buy_times, SUM(e.total) AS buy_total, MIN(e.id) AS first_estimate_id, MAX(e.id) AS last_estimate_id')
->where('e.Customer = :Customer')
->andWhere('e.EstimateStatus in (:EstimateStatuses)')
->setParameter('Customer', $Customer)
->setParameter('EstimateStatuses', $EstimateStatuses)
->groupBy('e.Customer')
->getQuery()
->getSingleResult();
} catch (NoResultException $e) {
// 受注データが存在しなければ初期化
$Customer->setFirstBuyDate(null);
$Customer->setLastBuyDate(null);
$Customer->setBuyTimes(0);
$Customer->setBuyTotal(0);
return;
}
$FirstEstimate = $this->find(['id' => $result['first_estimate_id']]);
$LastEstimate = $this->find(['id' => $result['last_estimate_id']]);
$Customer->setBuyTimes($result['buy_times']);
$Customer->setBuyTotal($result['buy_total']);
$Customer->setFirstBuyDate($FirstEstimate->getEstimateDate());
$Customer->setLastBuyDate($LastEstimate->getEstimateDate());
}
/**
* 指定した顧客の有効な見積を取得する為のQueryBuilderを取得
*
* @param Customer $Customer
* @return QueryBuilder
*/
public function getQueryValidEstimatesByCustomer(Customer $Customer)
{
global $kernel;
$config = $kernel->getContainer()->getParameter('sticker');
// ステッカーの商品IDを取得
$sticker_product_ids = [];
foreach ($config['sticker_type'] as $sticker_type => $row) {
$sticker_product_ids[] = $row['product_id'];
}
// サブクエリ
// @NOTE dtb_estimate_itemからデータ不足なものを除外する為のサブクエリ
$subQb = $this->getEntityManager()->createQueryBuilder();
$subQb
->select('IDENTITY(tmp_ei.Estimate)')
->from('Customize\Entity\EstimateItem', 'tmp_ei')
->andWhere('tmp_ei.Product IN(:sticker_product_ids)')
->andWhere(
$subQb->expr()->orX(
$subQb->expr()->lte('tmp_ei.price', 0),
$subQb->expr()->lte('tmp_ei.quantity', 0),
$subQb->expr()->isNull('tmp_ei.materials'),
$subQb->expr()->isNull('tmp_ei.options'),
)
)
->groupBy('tmp_ei.Estimate')
;
$qb = $this->getQueryBuilderByCustomer($Customer);
$qb
->andWhere($qb->expr()->notIn('e.id', $subQb->getDQL()))
->setParameter(':sticker_product_ids', $sticker_product_ids)
;
return $qb;
}
/**
* 指定した顧客の有効な見積を取得
*
* @param Customer $Customer
* @return
*/
public function getValidEstimatesByCustomer(Customer $Customer)
{
$qb = $this->getQueryValidEstimatesByCustomer($Customer);
return $qb->getQuery()->getResult();
}
}

app/Customize/Repository/EstimateRepository.php line 41