/** * クエリを構築する. * * 検索条件のキーに応じた 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; } }