public function getWhere()
{
return array(parent::getWhere(), $this->arrVal);
}
/**
* クエリを構築する.
*
* 検索条件のキーに応じた WHERE 句と, クエリパラメーターを構築する.
* クエリパラメーターは, FormParam の入力値から取得する.
*
* 構築内容は, 引数の $where 及び $arrValues にそれぞれ追加される.
*
* @param string $key 検索条件のキー
* @param string $where 構築する WHERE 句
* @param array $arrValues 構築するクエリパラメーター
* @param FormParam $objFormParam FormParam インスタンス
* @return void
*/
public function buildQuery($key, &$where, &$arrValues, &$objFormParam)
{
/* @var $dbFactory DBFactory */
$dbFactory = Application::alias('eccube.db.factory');
switch ($key) {
case 'search_product_name':
$where .= ' AND EXISTS (SELECT 1 FROM dtb_order_detail od WHERE od.order_id = dtb_order.order_id AND od.product_name LIKE ?)';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
case 'search_order_name':
$where .= ' AND ' . $dbFactory->concatColumn(array('order_name01', 'order_name02')) . ' LIKE ?';
$arrValues[] = sprintf('%%%s%%', preg_replace('/[ ]/u', '', $objFormParam->getValue($key)));
break;
case 'search_order_kana':
$where .= ' AND ' . $dbFactory->concatColumn(array('order_kana01', 'order_kana02')) . ' LIKE ?';
$arrValues[] = sprintf('%%%s%%', preg_replace('/[ ]/u', '', $objFormParam->getValue($key)));
break;
case 'search_order_id1':
$where .= ' AND order_id >= ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
case 'search_order_id2':
$where .= ' AND order_id <= ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
case 'search_order_sex':
$tmp_where = '';
foreach ($objFormParam->getValue($key) as $element) {
if ($element != '') {
if (Utils::isBlank($tmp_where)) {
$tmp_where .= ' AND (order_sex = ?';
} else {
$tmp_where .= ' OR order_sex = ?';
}
$arrValues[] = $element;
}
}
if (!Utils::isBlank($tmp_where)) {
$tmp_where .= ')';
$where .= " {$tmp_where} ";
}
break;
case 'search_order_tel':
$where .= ' AND (' . $dbFactory->concatColumn(array('order_tel01', 'order_tel02', 'order_tel03')) . ' LIKE ?)';
$arrValues[] = SelectSql::addSearchStr(preg_replace('/[()-]+/', '', $objFormParam->getValue($key)));
break;
case 'search_order_email':
$where .= ' AND order_email LIKE ?';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
case 'search_payment_id':
$tmp_where = '';
foreach ($objFormParam->getValue($key) as $element) {
if ($element != '') {
if ($tmp_where == '') {
$tmp_where .= ' AND (payment_id = ?';
} else {
$tmp_where .= ' OR payment_id = ?';
}
$arrValues[] = $element;
}
}
if (!Utils::isBlank($tmp_where)) {
$tmp_where .= ')';
$where .= " {$tmp_where} ";
}
break;
case 'search_total1':
$where .= ' AND total >= ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
case 'search_total2':
$where .= ' AND total <= ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
case 'search_sorderyear':
$date = Utils::sfGetTimestamp($objFormParam->getValue('search_sorderyear'), $objFormParam->getValue('search_sordermonth'), $objFormParam->getValue('search_sorderday'));
$where .= ' AND create_date >= ?';
$arrValues[] = $date;
break;
case 'search_eorderyear':
$date = Utils::sfGetTimestamp($objFormParam->getValue('search_eorderyear'), $objFormParam->getValue('search_eordermonth'), $objFormParam->getValue('search_eorderday'), true);
$where .= ' AND create_date <= ?';
$arrValues[] = $date;
break;
case 'search_supdateyear':
$date = Utils::sfGetTimestamp($objFormParam->getValue('search_supdateyear'), $objFormParam->getValue('search_supdatemonth'), $objFormParam->getValue('search_supdateday'));
$where .= ' AND update_date >= ?';
$arrValues[] = $date;
break;
case 'search_eupdateyear':
$date = Utils::sfGetTimestamp($objFormParam->getValue('search_eupdateyear'), $objFormParam->getValue('search_eupdatemonth'), $objFormParam->getValue('search_eupdateday'), true);
$where .= ' AND update_date <= ?';
$arrValues[] = $date;
break;
case 'search_sbirthyear':
$date = Utils::sfGetTimestamp($objFormParam->getValue('search_sbirthyear'), $objFormParam->getValue('search_sbirthmonth'), $objFormParam->getValue('search_sbirthday'));
$where .= ' AND order_birth >= ?';
$arrValues[] = $date;
break;
case 'search_ebirthyear':
$date = Utils::sfGetTimestamp($objFormParam->getValue('search_ebirthyear'), $objFormParam->getValue('search_ebirthmonth'), $objFormParam->getValue('search_ebirthday'), true);
$where .= ' AND order_birth <= ?';
$arrValues[] = $date;
break;
case 'search_order_status':
$where .= ' AND status = ?';
$arrValues[] = $objFormParam->getValue($key);
break;
default:
break;
}
}
