2系にあったCSV出力項目設定の「高度な設定」機能をEC-CUBE4で実装する方法

{
  "name": "ec-cube/customcsvexport4",
  "version": "1.0.0",
  "description": "カスタムCSV出力プラグイン",
  "type": "eccube-plugin",
  "require": {
    "ec-cube/plugin-installer": "~0.0.6"
  },
  "extra": {
    "code": "CustomCsvExport4"
  }
}

PluginManager.phpの作成

初期データの投入・更新・削除を行うためのPluginManager.phpをCustomCsvExport4ディレクトリ直下に作成します。今回は特に実装は行いません。

<?php

namespace Plugin\CustomCsvExport4;

use Eccube\Application;
use Eccube\Plugin\AbstractPluginManager;

class PluginManager extends AbstractPluginManager
{
}

CustomCsvExportNav.phpの作成

管理画面メニューへ表示させるためのCustomCsvExportNav.phpをCustomCsvExport4ディレクトリ直下に作成します。

<?php

namespace Plugin\CustomCsvExport4;

use Eccube\Common\EccubeNav;

class CustomCsvExportNav implements EccubeNav
{
    /**
     * {@inheritdoc}
     *
     * @return array
     */
    public static function getNav()
    {
        return [
            'setting' => [
                'children' => [
                    'shop' => [
                        'children' => [
                            'admin_custom_csv_export' => [
                                'name' => 'カスタムCSV出力',
                                'url' => 'plugin_custom_csv_export',
                            ],
                        ],
                    ],
                ],
            ],
        ];
    }
}

Controllerの作成

CustomCsvExportController.phpをCustomCsvExport4/Controllerディレクトリ直下に作成します。

<?php

namespace Plugin\CustomCsvExport4\Controller;

use Eccube\Application;
use Eccube\Controller\AbstractController;
use Plugin\CustomCsvExport4\Entity\CustomCsvExport;
use Plugin\CustomCsvExport4\Form\Type\CustomCsvExportType;
use Plugin\CustomCsvExport4\Repository\CustomCsvExportRepository;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Symfony\Component\HttpKernel\Exception\NotFoundHttpException;
use Symfony\Component\Routing\Annotation\Route;

class CustomCsvExportController extends AbstractController
{

    /**
     * @var CustomCsvExportRepository
     */
    private $customCsvExportRepository;

    /**
     * CustomCsvExportController constructor.
     *
     * @param CustomCsvExportRepository $customCsvExportRepository
     */
    public function __construct(CustomCsvExportRepository $customCsvExportRepository)
    {
        $this->customCsvExportRepository = $customCsvExportRepository;
    }


    /**
     * CSV一覧画面
     *
     * @param Request $request
     * @param null $id
     * @return array|\Symfony\Component\HttpFoundation\RedirectResponse|\Symfony\Component\HttpFoundation\Response
     *
     * @Route("/%eccube_admin_route%/plugin/custom_csv_export", name="plugin_custom_csv_export")
     * @Route("/%eccube_admin_route%/plugin/custom_csv_export/{id}/edit", requirements={"id" = "\d+"}, name="plugin_custom_csv_export_edit")
     * @Template("@CustomCsvExport4/admin/index.twig")
     */
    public function index(Request $request, $id = null)
    {
        if ($id) {
            $TargetCustomCsvExport = $this->customCsvExportRepository->find($id);
            if (!$TargetCustomCsvExport) {
                throw new NotFoundHttpException();
            }
        } else {
            $TargetCustomCsvExport = new CustomCsvExport();
        }

        $builder = $this->formFactory->createBuilder(CustomCsvExportType::class, $TargetCustomCsvExport);

        $form = $builder->getForm();

        $form->handleRequest($request);

        if ($form->isSubmitted() && $form->isValid()) {

            $sql = 'SELECT '.$form['custom_sql']->getData();
            try {
                $result = $this->customCsvExportRepository->query($sql);

                if ($result) {
                    $status = $this->customCsvExportRepository->save($TargetCustomCsvExport);

                    if ($status) {
                        $this->addSuccess('SQLを保存しました。', 'admin');

                        return $this->redirectToRoute('plugin_custom_csv_export');
                    } else {
                        $this->addError('SQLを保存できませんでした。', 'admin');
                    }
                } else {
                    $this->addError('SQLを保存できませんでした。', 'admin');
                }
            } catch (\Exception $e) {
                $this->addError('SQLを保存できませんでした。SQL文を正しく入力してください。', 'admin');
            }
        }

        $CustomCsvExports = $this->customCsvExportRepository->getList();

        return [
            'form' => $form->createView(),
            'CustomCsvExports' => $CustomCsvExports,
            'TargetCustomCsvExport' => $TargetCustomCsvExport,
        ];
    }

    /**
     * CSV削除
     *
     * @param Application $app
     * @param Request $request
     * @param $id
     * @return \Symfony\Component\HttpFoundation\RedirectResponse
     * @throws \Doctrine\DBAL\ConnectionException
     *
     * @Route("/%eccube_admin_route%/plugin/custom_csv_export/{id}/delete", requirements={"id" = "\d+"}, name="plugin_custom_csv_export_delete")
     */
    public function delete(Request $request, $id)
    {
        $this->isTokenValid();

        $TargetCustomCsvExport = $this->customCsvExportRepository->find($id);

        if (!$TargetCustomCsvExport) {
            $this->deleteMessage();

            return $this->redirectToRoute('plugin_custom_csv_export');
        }

        $status = $this->customCsvExportRepository->delete($TargetCustomCsvExport);

        if ($status) {
            $this->addSuccess('SQLを削除しました。', 'admin');
        } else {
            $this->addError('SQLを削除できませんでした。', 'admin');
        }

        return $this->redirectToRoute('plugin_custom_csv_export');
    }

    /**
     * CSV出力.
     *
     * @param Request $request
     * @param null $id
     * @return \Symfony\Component\HttpFoundation\RedirectResponse|StreamedResponse
     * @throws \Doctrine\DBAL\DBALException
     *
     * @Route("/%eccube_admin_route%/plugin/custom_csv_export/{id}/output", requirements={"id" = "\d+"}, name="plugin_custom_csv_export_output")
     */
    public function csvOutput(Request $request, $id = null)
    {
        // タイムアウトを無効にする.
        set_time_limit(0);

        // sql loggerを無効にする.
        $em = $this->entityManager;
        $em->getConfiguration()->setSQLLogger(null);

        $TargetCustomCsvExport = $this->customCsvExportRepository->find($id);
        if (!$TargetCustomCsvExport) {
            throw new NotFoundHttpException();
        }

        $response = new StreamedResponse();

        $csv_data = $this->customCsvExportRepository->getArrayList($TargetCustomCsvExport->getCustomSql());

        if (count($csv_data) > 0) {

            // ヘッダー行の抽出
            $csv_header = array();
            foreach ($csv_data as $csv_row) {

                foreach ($csv_row as $key => $value) {
                    $csv_header[$key] = mb_convert_encoding($key, $this->eccubeConfig['eccube_csv_export_encoding'], 'UTF-8');
                }
                break;
            }

            $response->setCallback(function() use ($request, $csv_header, $csv_data) {

                $fp = fopen('php://output', 'w');
                // ヘッダー行の出力
                fputcsv($fp, $csv_header, $this->eccubeConfig['eccube_csv_export_separator']);

                // データを出力
                foreach ($csv_data as $csv_row) {
                    $row = array();
                    foreach ($csv_header as $headerKey => $header_name) {
                        mb_convert_variables($this->eccubeConfig['eccube_csv_export_encoding'], 'UTF-8', $csv_row[$headerKey]);
                        $row[] = $csv_row[$headerKey];
                    }
                    fputcsv($fp, $row, $this->eccubeConfig['eccube_csv_export_separator']);
                }

                fclose($fp);

            });

            $now = new \DateTime();
            $filename = 'csv_'.$now->format('YmdHis').'.csv';
            $response->headers->set('Content-Type', 'application/octet-stream');
            $response->headers->set('Content-Disposition', 'attachment; filename='.$filename);
            $response->send();

            return $response;
        }

        $this->addError('CSVを出力できませんでした。', 'admin');

        return $this->redirectToRoute('plugin_custom_csv_export');
    }

    /**
     * SQL確認.
     *
     * @param Request $request
     * @param null $id
     * @return array
     *
     * @Route("/%eccube_admin_route%/plugin/custom_csv_export/{id}/confirm", requirements={"id" = "\d+"}, name="plugin_custom_csv_export_edit_confirm")
     * @Route("/%eccube_admin_route%/plugin/custom_csv_export/confirm", name="plugin_custom_csv_export_confirm")
     * @Template("@CustomCsvExport4/admin/index.twig")
     */
    public function sqlConfirm(Request $request, $id = null)
    {
        if ($id) {
            $TargetCustomCsvExport = $this->customCsvExportRepository->find($id);
            if (!$TargetCustomCsvExport) {
                throw new NotFoundHttpException();
            }
        } else {
            $TargetCustomCsvExport = new CustomCsvExport();
        }

        $builder = $this->formFactory->createBuilder(CustomCsvExportType::class, $TargetCustomCsvExport);

        $form = $builder->getForm();

        $form->handleRequest($request);

        $message = null;
        if ($form->isSubmitted() && $form->isValid()) {

            if (!is_null($form['custom_sql']->getData())) {
                $sql = 'SELECT '.$form['custom_sql']->getData();
                try {
                    $result = $this->customCsvExportRepository->query($sql);
                    if ($result) {
                        $message = 'エラーはありません。';
                    } else {
                        $message = 'エラーが発生しました。';
                    }
                } catch (\Exception $e) {
                    $message = $e->getMessage();
                }
            }
        }

        $CustomCsvExports = $this->customCsvExportRepository->getList();

        return [
            'form' => $form->createView(),
            'CustomCsvExports' => $CustomCsvExports,
            'message' => $message,
            'TargetCustomCsvExport' => $TargetCustomCsvExport,
        ];
    }
}

Entityの作成

作成したSQLを保持するためのCustomCsvExport.phpをCustomCsvExport4/Entityディレクトリ直下に作成します。

<?php

namespace Plugin\CustomCsvExport4\Entity;

use Doctrine\ORM\Mapping as ORM;
use Eccube\Entity\AbstractEntity;

/**
 * CustomCsvExport
 *
 * @ORM\Table(name="plg_custom_csv_export")
 * @ORM\InheritanceType("SINGLE_TABLE")
 * @ORM\DiscriminatorColumn(name="discriminator_type", type="string", length=255)
 * @ORM\HasLifecycleCallbacks()
 * @ORM\Entity(repositoryClass="Plugin\CustomCsvExport4\Repository\CustomCsvExportRepository")
 */
class CustomCsvExport extends AbstractEntity
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer", options={"unsigned":true})
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="sql_name", type="string", length=255)
     */
    private $sql_name;

    /**
     * @var string
     *
     * @ORM\Column(name="custom_sql", type="text")
     */
    private $custom_sql;

    /**
     * @var boolean
     *
     * @ORM\Column(name="deletable", type="boolean", options={"default":false})
     */
    private $deletable = false;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="create_date", type="datetimetz")
     */
    private $create_date;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="update_date", type="datetimetz")
     */
    private $update_date;


    /**
     * Get id.
     *
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set sqlName.
     *
     * @param string $sqlName
     *
     * @return CustomCsvExport
     */
    public function setSqlName($sqlName)
    {
        $this->sql_name = $sqlName;

        return $this;
    }

    /**
     * Get sqlName.
     *
     * @return string
     */
    public function getSqlName()
    {
        return $this->sql_name;
    }

    /**
     * Set customSql.
     *
     * @param string $customSql
     *
     * @return CustomCsvExport
     */
    public function setCustomSql($customSql)
    {
        $this->custom_sql = $customSql;

        return $this;
    }

    /**
     * Get customSql.
     *
     * @return string
     */
    public function getCustomSql()
    {
        return $this->custom_sql;
    }

    /**
     * Set deletable.
     *
     * @param bool $deletable
     *
     * @return CustomCsvExport
     */
    public function setDeletable($deletable)
    {
        $this->deletable = $deletable;

        return $this;
    }

    /**
     * Get deletable.
     *
     * @return bool
     */
    public function getDeletable()
    {
        return $this->deletable;
    }

    /**
     * Set createDate.
     *
     * @param \DateTime $createDate
     *
     * @return CustomCsvExport
     */
    public function setCreateDate($createDate)
    {
        $this->create_date = $createDate;

        return $this;
    }

    /**
     * Get createDate.
     *
     * @return \DateTime
     */
    public function getCreateDate()
    {
        return $this->create_date;
    }

    /**
     * Set updateDate.
     *
     * @param \DateTime $updateDate
     *
     * @return CustomCsvExport
     */
    public function setUpdateDate($updateDate)
    {
        $this->update_date = $updateDate;

        return $this;
    }

    /**
     * Get updateDate.
     *
     * @return \DateTime
     */
    public function getUpdateDate()
    {
        return $this->update_date;
    }
}

FormTypeの作成

フォーム画面用のCustomCsvExportType.phpをCustomCsvExport4/Form/Typeディレクトリ直下に作成します。

<?php

namespace Plugin\CustomCsvExport4\Form\Type;

use Eccube\Common\EccubeConfig;
use Plugin\CustomCsvExport4\Validator as Asserts;
use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\Extension\Core\Type\TextareaType;
use Symfony\Component\Form\Extension\Core\Type\TextType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\Validator\Constraints as Assert;

class CustomCsvExportType extends AbstractType
{

    /**
     * @var EccubeConfig
     */
    protected $eccubeConfig;

    /**
     * CustomCsvExportType constructor.
     *
     * @param EccubeConfig $eccubeConfig
     */
    public function __construct(EccubeConfig $eccubeConfig)
    {
        $this->eccubeConfig = $eccubeConfig;
    }


    /**
     * {@inheritdoc}
     */
    public function buildForm(FormBuilderInterface $builder, array $options)
    {

        $builder
            ->add('sql_name', TextType::class, array(
                'label' => '名称',
                'constraints' => array(
                    new Assert\NotBlank(),
                    new Assert\Length(array(
                        'max' => $this->eccubeConfig['eccube_stext_len'],
                    )),
                ),
            ))
            ->add('custom_sql', TextareaType::class, array(
                'label' => 'SQL文(最初のSELECTは記述しないでください。最後の;(セミコロン)も不要です。)',
                'constraints' => array(
                    new Asserts\SqlCheck(),
                ),
            ));
    }

    /**
     * {@inheritdoc}
     */
    public function getName()
    {
        return 'admin_custom_csv_export';
    }
}

Repositoryの作成

DBアクセス用のCustomCsvExportRepository.phpをCustomCsvExport4/Repositoryディレクトリ直下に作成します。

<?php

namespace Plugin\CustomCsvExport4\Repository;

use Eccube\Repository\AbstractRepository;
use Plugin\CustomCsvExport4\Entity\CustomCsvExport;
use Symfony\Bridge\Doctrine\RegistryInterface;

class CustomCsvExportRepository extends AbstractRepository
{
    /**
     * CouponRepository constructor.
     *
     * @param RegistryInterface $registry
     */
    public function __construct(RegistryInterface $registry)
    {
        parent::__construct($registry, CustomCsvExport::class);
    }

    /**
     * 設定SQL一覧を取得する.
     *
     * @return mixed 設定SQLの配列
     */
    public function getList()
    {
        $qb = $this->createQueryBuilder('cs')
            ->where('cs.deletable = 0');
        $CustomCsvExports = $qb->getQuery()->getResult();

        return $CustomCsvExports;
    }

    /**
     * CSV出力用の設定SQL実行結果を取得する.
     *
     * @param $custom_csv_export SQL文
     * @return mixed[]
     * @throws \Doctrine\DBAL\DBALException
     */
    public function getArrayList($custom_csv_export)
    {
        $em = $this->getEntityManager();
        $qb = $em->getConnection()->prepare('SELECT '.$custom_csv_export);
        $qb->execute();
        $result = $qb->fetchAll();

        return $result;
    }

    /**
     * 入力されたSQL文が正しいかどうか判定する
     *
     * @param $sql SQL文
     * @return bool SQLの実行結果
     * @throws \Doctrine\DBAL\DBALException
     */
    public function query($sql)
    {
        $em = $this->getEntityManager();
        $qb = $em->getConnection()->prepare($sql);

        $result = $qb->execute();

        return $result;
    }

    /**
     * 設定SQLを保存する.
     *
     * @param CustomCsvExport $CustomCsvExport 設定SQL
     * @return bool 成功した場合 true
     * @throws \Doctrine\DBAL\ConnectionException
     */
    public function save($CustomCsvExport)
    {
        $em = $this->getEntityManager();
        $em->getConnection()->beginTransaction();
        try {
            if (!$CustomCsvExport->getId()) {
                $CustomCsvExport->setDeletable(false);

                $em->createQueryBuilder()
                    ->update('Plugin\CustomCsvExport4\Entity\CustomCsvExport', 'cs')
                    ->getQuery();
            }

            $em->persist($CustomCsvExport);
            $em->flush();

            $em->getConnection()->commit();
        } catch (\Exception $e) {
            $em->getConnection()->rollback();

            return false;
        }

        return true;
    }

    /**
     * 設定SQLを削除する.
     *
     * @param CustomCsvExport $CustomCsvExport 削除対象の設定SQL
     * @return bool 成功した場合 true
     * @throws \Doctrine\DBAL\ConnectionException
     */
    public function delete($CustomCsvExport)
    {
        $em = $this->getEntityManager();
        $em->getConnection()->beginTransaction();
        try {
            $CustomCsvExport->setDeletable(true);
            $em->flush($CustomCsvExport);

            $em->getConnection()->commit();
        } catch (\Exception $e) {
            $em->getConnection()->rollback();

            return false;
        }

        return true;
    }
}

twigの作成

管理画面であるindex.twigをCustomCsvExport4/Resource/template/adminディレクトリ直下に作成します。

{% extends '@admin/default_frame.twig' %}

{% set menus = ['setting', 'shop', 'admin_custom_csv_export'] %}

{% block title %}カスタムCSV出力{% endblock %}
{% block sub_title %}店舗設定{% endblock %}

{% form_theme form '@admin/Form/bootstrap_4_horizontal_layout.html.twig' %}

{% block javascript %}
    <script>
        $(function() {
            $('#register_btn').click(function() {
                $('#form1').attr('action', "{% if TargetCustomCsvExport.id %}{{ path('plugin_custom_csv_export_edit', {id: TargetCustomCsvExport.id}) }}{% else %}{{ url('plugin_custom_csv_export') }}{% endif %}");
                $('#form1').submit();
                return false;
            });

            $('#check_btn').click(function() {
                $('#form1').attr('action', "{% if TargetCustomCsvExport.id %}{{ path('plugin_custom_csv_export_edit_confirm', {id: TargetCustomCsvExport.id}) }}{% else %}{{ url('plugin_custom_csv_export_confirm') }}{% endif %}");
                $('#form1').submit();
                return false;
            });

            // 削除モーダルのhrefとmessageの変更
            $('#DeleteModal').on('shown.bs.modal', function(event) {
                var target = $(event.relatedTarget);
                // hrefの変更
                $(this).find('[data-method="delete"]').attr('href', target.data('url'));

                // messageの変更
                $(this).find('p.modal-message').text(target.data('message'));
            });
        });
    </script>
{% endblock %}

{% block main %}

    <form id="form1" name="form1" method="post">
        {{ form_widget(form._token) }}
        <div class="c-contentsArea__cols">
            <div class="c-contentsArea__primaryCol">
                <div class="c-primaryCol">
                    <div class="card rounded border-0 mb-4">
                        <div class="card-body p-0">
                            <div class="card rounded border-0">
                                <ul class="list-group list-group-flush sortable-container">
                                    {% if CustomCsvExports|length > 0 %}
                                        {% for CustomCsvExport in CustomCsvExports %}
                                            <li id="ex-delivery-{{ CustomCsvExport.id }}" class="list-group-item" data-id="{{ CustomCsvExport.id }}">
                                                <div class="row justify-content-around">
                                                    <div class="col d-flex align-items-center">
                                                        <a href="{{ url('plugin_custom_csv_export_edit', {id: CustomCsvExport.id}) }}">{{ CustomCsvExport.sql_name }}</a>
                                                    </div>
                                                    <div class="col-auto text-right">
                                                        <div class="d-inline-block mr-2" data-tooltip="true" data-placement="top" title="CSV出力">
                                                            <a class="btn btn-ec-actionIcon text-body" href="{{ url('plugin_custom_csv_export_output', {id: CustomCsvExport.id}) }}">
                                                                <i class="fas fa-file-export fa-lg"></i>
                                                            </a>
                                                        </div>
                                                        <div class="d-inline-block mr-2" data-tooltip="true" data-placement="top" title="{{ 'admin.common.delete'|trans }}">
                                                            <a class="btn btn-ec-actionIcon" data-toggle="modal" data-target="#DeleteModal"
                                                               data-url="{{ url('plugin_custom_csv_export_delete', {id: CustomCsvExport.id}) }}"
                                                               data-message="{{ 'admin.common.delete_modal__message'|trans({ "%name%" : CustomCsvExport.sql_name }) }}">
                                                                <i class="fas fa-close fa-lg"></i>
                                                            </a>
                                                        </div>
                                                    </div>
                                                </div>
                                            </li>
                                        {% endfor %}
                                    {% else %}
                                        <li class="list-group-item">
                                            <div class="row justify-content-around">
                                                <div class="col d-flex align-items-center">
                                                    データはありません。
                                                </div>
                                            </div>
                                        </li>
                                    {% endif %}
                                </ul>
                                <!-- 削除モーダル -->
                                <div class="modal fade" id="DeleteModal" tabindex="-1" role="dialog"
                                     aria-labelledby="DeleteModal" aria-hidden="true">
                                    <div class="modal-dialog" role="document">
                                        <div class="modal-content">
                                            <div class="modal-header">
                                                <h5 class="modal-title font-weight-bold">
                                                    {{ 'admin.common.delete_modal__title'|trans }}
                                                </h5>
                                                <button class="close" type="button" data-dismiss="modal" aria-label="Close">
                                                    <span aria-hidden="true">×</span>
                                                </button>
                                            </div>
                                            <div class="modal-body text-left">
                                                <p class="text-left modal-message"><!-- jsでメッセージを挿入 --></p>
                                            </div>
                                            <div class="modal-footer">
                                                <button class="btn btn-ec-sub" type="button" data-dismiss="modal">
                                                    {{ 'admin.common.cancel'|trans }}
                                                </button>
                                                <a class="btn btn-ec-delete" href="#" {{ csrf_token_for_anchor() }}
                                                   data-method="delete" data-confirm="false">
                                                    {{ 'admin.common.delete'|trans }}
                                                </a>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>


                    <div class="card rounded border-0 mb-4">
                        <div class="card-header">
                            <div class="row">
                                <div class="col-8">
                                    <div class="d-inline-block">
                                        <span class="card-title">SQL設定
                                            {% if TargetCustomCsvExport.id %}
                                                (編集中：{{ TargetCustomCsvExport.sql_name }})
                                            {% else %}
                                                (新規入力)
                                            {% endif %}</span>
                                    </div>
                                </div>
                                <div class="col-4 text-right">
                                    <a data-toggle="collapse" href="#product-info" aria-expanded="false"
                                       aria-controls="freeArea">
                                        <i class="fa fa-angle-up fa-lg"></i>
                                    </a>
                                </div>
                            </div>
                        </div>
                        <div class="collapse show ec-cardCollapse" id="product-info">
                            <div class="card-body">
                                {% if message is defined and message is not null %}
                                    <div class="row">
                                        <div class="col-3">
                                            <div class="d-inline-block">
                                                <span>SQL確認結果</span>
                                            </div>
                                        </div>
                                        <div class="col mb-2 text-success">
                                            {{ message }}
                                        </div>
                                    </div>
                                {% endif %}

                                {{ form_widget(form._token) }}
                                {{ form_row(form.sql_name, {attr: {placeholder: '保存するSQL名を入力'}}) }}
                                {{ form_row(form.custom_sql, {attr: {'rows': 10, placeholder: 'SQL文を入力、SQL文には読み込み関係以外のSQLコマンドおよび";"記号は入力できません。'}}) }}
                                <div class="col-sm-0 col-sm-offset-3 col-md-12 col-md-offset-0">
                                    <button id="check_btn" class="btn btn-ec-conversion btn-block px-5">SQLチェック</button>
                                </div>

                            </div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="c-conversionArea">
                <div class="c-conversionArea__container">
                    <div class="row justify-content-between align-items-center">
                        <div class="col-6">
                            <div class="c-conversionArea__leftBlockItem"></div>
                        </div>
                        <div class="col-6">
                            <div id="ex-conversion-action" class="row align-items-center justify-content-end">
                                <div class="col-auto">
                                    <button id="register_btn" type="submit" class="btn btn-ec-conversion px-5">{{ 'admin.common.registration'|trans }}</button>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </form>

{% endblock %}

入力チェックの作成

入力チェック用クラスSqlCheckValidator.php、SqlCheck.phpをCustomCsvExport4/Validatorディレクトリ直下に作成します。

<?php

namespace Plugin\CustomCsvExport4\Validator;

use Symfony\Component\Validator\Constraint;
use Symfony\Component\Validator\ConstraintValidator;
use Symfony\Component\Validator\Context\ExecutionContextInterface;
use Symfony\Component\Validator\Exception\UnexpectedTypeException;

class SqlCheckValidator extends ConstraintValidator
{
    /**
     * {@inheritdoc}
     */
    public function validate($value, Constraint $constraint)
    {
        if (!$constraint instanceof SqlCheck) {
            throw new UnexpectedTypeException($constraint, __NAMESPACE__.'\SqlCheck');
        }

        $error = $this->sqlValidation($value);
        if ($error) {
            $this->context->buildViolation($constraint->message)
                ->setParameter('{{ value }}', $this->formatValue($value))
                ->addViolation();
        }

        if (false === $value || (empty($value) && '0' != $value)) {
            if ($this->context instanceof ExecutionContextInterface) {
                $this->context->buildViolation($constraint->message)
                    ->setParameter('{{ value }}', $this->formatValue($value))
                    ->addViolation();
            } else {
                $constraint->message = 'SQL文を入力してください。';
                $this->context->buildViolation($constraint->message)
                    ->setParameter('{{ value }}', $this->formatValue($value))
                    ->addViolation();
            }
        }
    }

    /**
     * SQLの入力チェック.
     *
     * @param $sql SQL文
     * @return bool
     */
    private function sqlValidation($sql)
    {
        // 入力チェック
        $error = false;

        $denyList = $this->lfGetSqlDenyList();

        $prohibitedStr = str_replace(array('|', '/'), array('\|', '\/'), $denyList);
        $pattern = '/'.join('|', $prohibitedStr).'/i';
        if (preg_match_all($pattern, $sql, $matches)) {
            $error = true;
        }

        return $error;
    }

    /**
     * SQL文に含めることを許可しないSQLキーワード
     * 基本的にEC-CUBEのデータを取得するために必要なコマンドしか許可しない。複数クエリも不可.
     *
     * FIXME: キーワードの精査。危険な部分なのでプログラム埋め込みで実装しました。mtb化の有無判断必要。
     *
     * @return string[] 不許可ワード配列
     */
    private function lfGetSqlDenyList()
    {
        $arrList = array(
            ';',
            'CREATE\s',
            'INSERT\s',
            'UPDATE\s',
            'DELETE\s',
            'ALTER\s',
            'ABORT\s',
            'ANALYZE\s',
            'CLUSTER\s',
            'COMMENT\s',
            'COPY\s',
            'DECLARE\s',
            'DISCARD\s',
            'DO\s',
            'DROP\s',
            'EXECUTE\s',
            'EXPLAIN\s',
            'GRANT\s',
            'LISTEN\s',
            'LOAD\s',
            'LOCK\s',
            'NOTIFY\s',
            'PREPARE\s',
            'REASSIGN\s',
            'RELEASE\sSAVEPOINT',
            'RENAME\s',
            'REST\s',
            'REVOKE\s',
            'SAVEPOINT\s',
            '\sSET\s', // OFFSETを誤検知しないように先頭・末尾に\sを指定
            'SHOW\s',
            'START\sTRANSACTION',
            'TRUNCATE\s',
            'UNLISTEN\s',
            'VACCUM\s',
            'HANDLER\s',
            'LOAD\sDATA\s',
            'LOAD\sXML\s',
            'REPLACE\s',
            'OPTIMIZE\s',
            'REPAIR\s',
            'INSTALL\sPLUGIN\s',
            'UNINSTALL\sPLUGIN\s',
            'BINLOG\s',
            'KILL\s',
            'RESET\s',
            'PURGE\s',
            'CHANGE\sMASTER',
            'START\sSLAVE',
            'STOP\sSLAVE',
            'MASTER\sPOS\sWAIT',
            'SIGNAL\s',
            'RESIGNAL\s',
            'RETURN\s',
            'USE\s',
            'HELP\s',
        );

        return $arrList;
    }
}

<?php

namespace Plugin\CustomCsvExport4\Validator;

use Symfony\Component\Validator\Constraint;

class SqlCheck extends Constraint
{
    public $message = 'SQL文が不正です。SQL文を見直してください';
}

ディレクトリ構成

今回作成したディレクトリ構成は以下のようになります。

CustomCsvExport4
├── Controller
│ └── CustomCsvExportController.php
├── CustomCsvExportNav.php
├── Entity
│ └── CustomCsvExport.php
├── Form
│ └── Type
│     └── CustomCsvExportType.php
├── PluginManager.php
├── Repository
│ └── CustomCsvExportRepository.php
├── Resource
│ └── template
│     └── admin
│         └── index.twig
├── Validator
│ ├── SqlCheck.php
│ └── SqlCheckValidator.php
└── composer.json

プラグインのインストール

プラグインをインストールする方法は2種類あり、

管理画面からプラグインをインストールする方法
コマンドラインからインストールする方法

がありますが、今回はコマンドラインからインストールする方法とします。

コマンドプロンプトやターミナルで、EC-CUBEルートディレクトリまで移動後、

php bin/console eccube:plugin:install --code=CustomCsvExport4

を実行後、Installed.と表示されるとプラグインがインストールされます。

プラグインをインストール後、管理画面の「オーナーズストア」→「プラグイン」→「プラグイン一覧」から有効化してください。

「設定」→「店舗設定」に「カスタムCSV出力」が表示されるようになります。

GitHubからDownloadされた方はそのままでは追加できないため、解凍後に以下のURLを参考にして再圧縮後、

doc4.ec-cube.net

管理画面から「オーナーズストア」→「プラグイン」→「プラグイン一覧」から「アップロードして新規追加」で登録してください。

カスタムCSV出力が必要な方はこちらを参考にしてください。