/**
* クエリを構築する.
*
* 検索条件のキーに応じた WHERE 句と, クエリパラメーターを構築する.
* クエリパラメーターは, FormParam の入力値から取得する.
*
* 構築内容は, 引数の $where 及び $arrValues にそれぞれ追加される.
*
* @param string $key 検索条件のキー
* @param string $where 構築する WHERE 句
* @param array $arrValues 構築するクエリパラメーター
* @param FormParam $objFormParam FormParam インスタンス
* @param FormParam $objDb DbHelper インスタンス
* @return void
*/
public function buildQuery($key, &$where, &$arrValues, &$objFormParam, &$objDb)
{
/* @var $dbFactory DBFactory */
$dbFactory = Application::alias('eccube.db.factory');
switch ($key) {
// 商品ID
case 'search_product_id':
$where .= ' AND product_id = ?';
$arrValues[] = sprintf('%d', $objFormParam->getValue($key));
break;
// 商品コード
// 商品コード
case 'search_product_code':
$where .= ' AND product_id IN (SELECT product_id FROM dtb_products_class WHERE product_code ILIKE ? AND del_flg = 0)';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
// 商品名
// 商品名
case 'search_name':
$where .= ' AND name LIKE ?';
$arrValues[] = sprintf('%%%s%%', $objFormParam->getValue($key));
break;
// カテゴリ
// カテゴリ
case 'search_category_id':
list($tmp_where, $tmp_Values) = $objDb->getCatWhere($objFormParam->getValue($key));
if ($tmp_where != '') {
$where .= ' AND product_id IN (SELECT product_id FROM dtb_product_categories WHERE ' . $tmp_where . ')';
$arrValues = array_merge((array) $arrValues, (array) $tmp_Values);
}
break;
// 種別
// 種別
case 'search_status':
$tmp_where = '';
foreach ($objFormParam->getValue($key) as $element) {
if ($element != '') {
if (Utils::isBlank($tmp_where)) {
$tmp_where .= ' AND (status = ?';
} else {
$tmp_where .= ' OR status = ?';
}
$arrValues[] = $element;
}
}
if (!Utils::isBlank($tmp_where)) {
$tmp_where .= ')';
$where .= " {$tmp_where} ";
}
break;
// 登録・更新日(開始)
// 登録・更新日(開始)
case 'search_startyear':
$date = Utils::sfGetTimestamp($objFormParam->getValue('search_startyear'), $objFormParam->getValue('search_startmonth'), $objFormParam->getValue('search_startday'));
$where .= ' AND update_date >= ?';
$arrValues[] = $date;
break;
// 登録・更新日(終了)
// 登録・更新日(終了)
case 'search_endyear':
$date = Utils::sfGetTimestamp($objFormParam->getValue('search_endyear'), $objFormParam->getValue('search_endmonth'), $objFormParam->getValue('search_endday'), true);
$where .= ' AND update_date <= ?';
$arrValues[] = $date;
break;
// 商品ステータス
// 商品ステータス
case 'search_product_statuses':
$arrPartVal = $objFormParam->getValue($key);
$count = count($arrPartVal);
if ($count >= 1) {
$where .= ' ' . 'AND product_id IN (' . ' SELECT product_id FROM dtb_product_status WHERE product_status_id IN (' . Utils::repeatStrWithSeparator('?', $count) . ')' . ')';
$arrValues = array_merge($arrValues, $arrPartVal);
}
break;
default:
break;
}
}
/**
* データの一時登録を行う.
*
* 非会員向けの処理
* @param integer $uniqid 受注一時テーブルのユニークID
* @param PurchaseHelper $objPurchase PurchaseHelper インスタンス
* @param Customer $objCustomer Customer インスタンス
* @param FormParam $objFormParam FormParam インスタンス
* @param boolean $isMultiple 複数配送の場合 true
*/
public function lfRegistData($uniqid, &$objPurchase, Customer &$objCustomer, &$objFormParam, $isMultiple = false)
{
$arrParams = $objFormParam->getHashArray();
// 注文者をお届け先とする配列を取得
$arrShippingOwn = array();
$objPurchase->copyFromOrder($arrShippingOwn, $arrParams);
// 都度入力されたお届け先
$arrShipping = $objPurchase->extractShipping($arrParams);
if ($isMultiple) {
$objPurchase->unsetOneShippingTemp(0);
$objPurchase->unsetOneShippingTemp(1);
$objPurchase->saveShippingTemp($arrShippingOwn, 0);
if ($arrParams['deliv_check'] == '1') {
$objPurchase->saveShippingTemp($arrShipping, 1);
}
} else {
$objPurchase->unsetAllShippingTemp(true);
if ($arrParams['deliv_check'] == '1') {
$objPurchase->saveShippingTemp($arrShipping, 1);
} else {
$objPurchase->saveShippingTemp($arrShippingOwn, 0);
}
}
$arrValues = $objFormParam->getDbArray();
// 登録データの作成
$arrValues['order_birth'] = Utils::sfGetTimestamp($arrParams['order_year'], $arrParams['order_month'], $arrParams['order_day']);
$arrValues['update_date'] = 'CURRENT_TIMESTAMP';
$arrValues['customer_id'] = '0';
$objPurchase->saveOrderTemp($uniqid, $arrValues, $objCustomer);
}
/**
* 会員情報を登録する
*
* @param FormParam $objFormParam
* @param mixed $customer_id
* @access private
* @return void
*/
public function lfRegistCustomerData(&$objFormParam, $customer_id)
{
$arrRet = $objFormParam->getHashArray();
$sqlval = $objFormParam->getDbArray();
$sqlval['birth'] = Utils::sfGetTimestamp($arrRet['year'], $arrRet['month'], $arrRet['day']);
Application::alias('eccube.helper.customer')->sfEditCustomerData($sqlval, $customer_id);
}
/**
* DB更新処理
*
* @param integer $order_id 受注ID
* @param PurchaseHelper $objPurchase PurchaseHelper インスタンス
* @param FormParam $objFormParam FormParam インスタンス
* @param string $message 通知メッセージ
* @param array $arrValuesBefore 更新前の受注情報
* @return integer $order_id 受注ID
*
* エラー発生時は負数を返す。
*/
public function doRegister($order_id, PurchaseHelper &$objPurchase, &$objFormParam, &$message, &$arrValuesBefore)
{
$objQuery = Application::alias('eccube.query');
$arrValues = $objFormParam->getDbArray();
$where = 'order_id = ?';
$objQuery->begin();
// 支払い方法が変更されたら、支払い方法名称も更新
if ($arrValues['payment_id'] != $arrValuesBefore['payment_id']) {
$arrValues['payment_method'] = $this->arrPayment[$arrValues['payment_id']];
$arrValuesBefore['payment_id'] = NULL;
}
// 生年月日の調整
$arrValues['order_birth'] = Utils::sfGetTimestamp($arrValues['order_birth_year'], $arrValues['order_birth_month'], $arrValues['order_birth_day']);
// 受注テーブルの更新
$order_id = $objPurchase->registerOrder($order_id, $arrValues);
$arrDetail = $objFormParam->getSwapArray(array('product_id', 'product_class_id', 'product_code', 'product_name', 'price', 'quantity', 'point_rate', 'classcategory_name1', 'classcategory_name2', 'tax_rate', 'tax_rule'));
// 変更しようとしている商品情報とDBに登録してある商品情報を比較することで、更新すべき数量を計算
$max = count($arrDetail);
$k = 0;
$arrStockData = array();
for ($i = 0; $i < $max; $i++) {
if (!empty($arrDetail[$i]['product_id'])) {
$arrPreDetail = $objQuery->select('*', 'dtb_order_detail', 'order_id = ? AND product_class_id = ?', array($order_id, $arrDetail[$i]['product_class_id']));
if (!empty($arrPreDetail) && $arrPreDetail[0]['quantity'] != $arrDetail[$i]['quantity']) {
// 数量が変更された商品
$arrStockData[$k]['product_class_id'] = $arrDetail[$i]['product_class_id'];
$arrStockData[$k]['quantity'] = $arrPreDetail[0]['quantity'] - $arrDetail[$i]['quantity'];
++$k;
} elseif (empty($arrPreDetail)) {
// 新しく追加された商品 もしくは 違う商品に変更された商品
$arrStockData[$k]['product_class_id'] = $arrDetail[$i]['product_class_id'];
$arrStockData[$k]['quantity'] = -$arrDetail[$i]['quantity'];
++$k;
}
$objQuery->delete('dtb_order_detail', 'order_id = ? AND product_class_id = ?', array($order_id, $arrDetail[$i]['product_class_id']));
}
}
// 上記の新しい商品のループでDELETEされなかった商品は、注文より削除された商品
$arrPreDetail = $objQuery->select('*', 'dtb_order_detail', 'order_id = ?', array($order_id));
foreach ($arrPreDetail as $key => $val) {
$arrStockData[$k]['product_class_id'] = $val['product_class_id'];
$arrStockData[$k]['quantity'] = $val['quantity'];
++$k;
}
// 受注詳細データの更新
$objPurchase->registerOrderDetail($order_id, $arrDetail);
// 在庫数調整
if (ORDER_DELIV != $arrValues['status'] && ORDER_CANCEL != $arrValues['status']) {
foreach ($arrStockData as $stock) {
$objQuery->update('dtb_products_class', array(), 'product_class_id = ?', array($stock['product_class_id']), array('stock' => 'stock + ?'), array($stock['quantity']));
}
}
$arrAllShipping = $objFormParam->getSwapArray($this->arrShippingKeys);
$arrAllShipmentItem = $objFormParam->getSwapArray($this->arrShipmentItemKeys);
$arrDelivTime = Application::alias('eccube.helper.delivery')->getDelivTime($objFormParam->getValue('deliv_id'));
//商品単価を複数配送にも適応
$arrShippingValues = array();
$arrIsNotQuantityUp = array();
foreach ($arrAllShipping as $shipping_index => $arrShipping) {
$shipping_id = $arrShipping['shipping_id'];
$arrShippingValues[$shipping_index] = $arrShipping;
$arrShippingValues[$shipping_index]['shipping_date'] = Utils::sfGetTimestamp($arrShipping['shipping_date_year'], $arrShipping['shipping_date_month'], $arrShipping['shipping_date_day']);
//商品単価を複数配送にも反映する
foreach ($arrDetail as $product_detail) {
foreach ($arrAllShipmentItem[$shipping_index]['shipment_product_class_id'] as $relation_index => $shipment_product_class_id) {
if ($product_detail['product_class_id'] == $shipment_product_class_id) {
$arrAllShipmentItem[$shipping_index]['shipment_price'][$relation_index] = $product_detail['price'];
}
}
}
// 配送業者IDを取得
$arrShippingValues[$shipping_index]['deliv_id'] = $objFormParam->getValue('deliv_id');
// お届け時間名称を取得
$arrShippingValues[$shipping_index]['shipping_time'] = $arrDelivTime[$arrShipping['time_id']];
// 複数配送の場合は配送商品を登録
if (!Utils::isBlank($arrAllShipmentItem)) {
$arrShipmentValues = array();
foreach ($arrAllShipmentItem[$shipping_index] as $key => $arrItem) {
// TODO $arrItemが配列でない場合があるのを見直した方が良いかもしれない
if (is_array($arrItem)) {
$i = 0;
foreach ($arrItem as $item) {
$arrShipmentValues[$shipping_index][$i][str_replace('shipment_', '', $key)] = $item;
$i++;
}
}
}
$objPurchase->registerShipmentItem($order_id, $shipping_id, $arrShipmentValues[$shipping_index]);
}
}
$objPurchase->registerShipping($order_id, $arrShippingValues, false);
$objQuery->commit();
return $order_id;
}
/**
* 会員登録に必要なSQLパラメーターの配列を生成する.
*
* フォームに入力された情報を元に, SQLパラメーターの配列を生成する.
* モバイル端末の場合は, email を email_mobile にコピーし,
* mobile_phone_id に携帯端末IDを格納する.
*
* @param FormParam $objFormParam
* @access private
* @return $arrResults
*/
public function lfMakeSqlVal(&$objFormParam)
{
$arrForm = $objFormParam->getHashArray();
$arrResults = $objFormParam->getDbArray();
// 生年月日の作成
$arrResults['birth'] = Utils::sfGetTimestamp($arrForm['year'], $arrForm['month'], $arrForm['day']);
// 仮会員 1 本会員 2
$arrResults['status'] = CUSTOMER_CONFIRM_MAIL == true ? '1' : '2';
/*
* secret_keyは、テーブルで重複許可されていない場合があるので、
* 本会員登録では利用されないがセットしておく。
*/
$arrResults['secret_key'] = Application::alias('eccube.helper.customer')->sfGetUniqSecretKey();
// 入会時ポイント
$CONF = Application::alias('eccube.helper.db')->getBasisData();
$arrResults['point'] = $CONF['welcome_point'];
if (Application::alias('eccube.display')->detectDevice() == DEVICE_TYPE_MOBILE) {
// 携帯メールアドレス
$arrResults['email_mobile'] = $arrResults['email'];
// PHONE_IDを取り出す
$arrResults['mobile_phone_id'] = MobileUserAgent::getId();
}
return $arrResults;
}
/**
* ReviewHelperインスタンスへ渡す検索条件の配列を作成.
*
* @param array $data
* @return array
*/
private function makeQuery($data = array())
{
$query = array();
foreach ($data as $key => $val) {
if (empty($val)) {
continue;
}
switch ($key) {
case 'search_reviewer_name':
$query['reviewer_name'] = $val;
break;
case 'search_reviewer_url':
$query['reviewer_url'] = $val;
break;
case 'search_name':
$query['product_name'] = $val;
break;
case 'search_product_code':
$query['product_code'] = $val;
break;
case 'search_sex':
$query['reviewer_sex'] = $val;
break;
case 'search_recommend_level':
$query['recommend_level'] = $val;
break;
case 'search_startyear':
if (isset($_POST['search_startyear']) && isset($_POST['search_startmonth']) && isset($_POST['search_startday'])) {
$date = Utils::sfGetTimestamp($_POST['search_startyear'], $_POST['search_startmonth'], $_POST['search_startday']);
$query['date_from'] = $date;
}
break;
case 'search_endyear':
if (isset($_POST['search_startyear']) && isset($_POST['search_startmonth']) && isset($_POST['search_startday'])) {
$date = Utils::sfGetTimestamp($_POST['search_endyear'], $_POST['search_endmonth'], $_POST['search_endday']);
$end_date = date('Y/m/d', strtotime('1 day', strtotime($date)));
$query['date_to'] = $end_date;
}
break;
default:
break;
}
}
return $query;
}
/**
* クエリを構築する.
*
* 検索条件のキーに応じた 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;
}
}
