Symfony Profiler

<?php
/**
* @version EC=CUBE4
* @copyright 株式会社翔 kakeru.co.jp
* @author
* 2021年12月17日作成
*
* app\Customize\Converter\CustomerConverter.php
*
*
* コンバーター
* 会員郡のコンバーを行う
*
*
* C= C= C= ┌(;･_･)┘ﾄｺﾄｺ
******************************************************/
namespace Customize\Converter;
use Customize\Service\DynamicConverterService;
use Customize\Service\ItemSashikomiImageService;
use Customize\Service\LmAbstractService;
use Lm\Engine\EC\Entity\CategoryWithRelated;
use Lm\Entity\Goods;
use Lm\Service\Cache\CacheService;
use Lm\Service\Db\DbManager;
use Lm\Service\Db\SqlService;
use Customize\Service\LmHelper;
use Eccube\Common\EccubeConfig;
use Eccube\Service\TaxRuleService;
class DynamicConverter extends LmAbstractService
{
const TYPE_BASE_01 = 'base_01'; // 基本01-イメージ部分
const TYPE_WEARING_IMAGE_01 = 'wearing_image_01'; // 着用イメージ01
const TYPE_WEARING_IMAGE_02 = 'wearing_image_02'; // 着用イメージ02
const TYPE_WEARING_IMAGE_03 = 'wearing_image_03'; // 着用イメージ03
const TYPE_WEARING_IMAGE_04 = 'wearing_image_04'; // 着用イメージ04
const TYPE_MODEL_MENS = 'model_mens'; // 10-モデル雰囲気・モデル前後（メンズ）
const TYPE_MODEL_LADIES = 'model_ladies'; // 10-モデル雰囲気・モデル前後（レディース）
const TYPE_HANGER_ILLUST = 'hanger_illust'; // 11-ハンガーイラスト前後
const TYPE_FABRIC = 'fabric'; // 素材・機能について（複数）
const TYPE_UPPER = 'upper'; // アッパー
const TYPE_MID_SOLE = 'mid_sole'; // ミッドソール
const TYPE_OUT_SOLE = 'out_sole'; // アウトソール
const TYPE_DETAIL_FRONT_BACK = 'detail_front_back'; // こだわりPOINT(商品の平置き写真-前後)
const TYPE_DETAIL_COLUMN = 'detail_column'; // こだわりPOINT(詳細写真２カラム・３カラム)（複数）
const TYPE_BASE_03 = 'base_03'; // 15-基本03-色展開
const TYPE_COLOR_VARIATION = 'color_variation'; // 色展開（個別）
const TYPE_COLORUP = 'colorup'; // カラーアップ
const TYPE_SIZE_SILHOUETTE = 'size_silhouette'; // サイズシルエット
const TYPE_POCKET_SIZE_AREA = 'pocket_size_area'; // ポケットサイズエリア
const TYPE_PRINT_AREA = 'print_area'; // プリントエリア
const TYPE_SIZE_GUIDE = 'size_guide'; // サイズガイド
const WEARING_IMAGE_MAX_COUNT = 4; // 着用イメージの最大画像数
const DB_STATUS_TRUE = 1;
const DB_STATUS_FALSE = 0;
const CAMPAIGN_CATEGORY_LIST = [
'kucho-fuku',
];
// Follow Up Model
const REPLY_OPTION_NO_REPLIED = '0'; // 未返信
const REPLY_OPTION_WAIT_ENTIRE = '1'; // Ａ）全て待ちます
const REPLY_OPTION_WAIT_SEQUENTIAL = '2'; // Ｂ）揃ったものから順次発送してください
const REPLY_OPTION_CANCEL_DELAYED = '3'; // Ｃ）遅れた分だけキャンセルします
const REPLY_OPTION_CANCEL_ALL = '4'; // Ｃ）遅れた分だけキャンセルします
const REPLY_OPTION_ALTERNATIVE_DESIRED = '5';
const REPLY_OPTION_LIST = [
self::REPLY_OPTION_NO_REPLIED => [
'id' => self::REPLY_OPTION_NO_REPLIED,
'text' => '未回答',
'value' => 'count_reply_no_replied',
'enabled' => false,
'linked' => false,
],
self::REPLY_OPTION_WAIT_ENTIRE => [
'id' => self::REPLY_OPTION_WAIT_ENTIRE,
'text' => 'Ａ）全て待ちます',
'value' => 'count_reply_wait_entire',
'enabled' => false,
'linked' => true,
],
self::REPLY_OPTION_WAIT_SEQUENTIAL => [
'id' => self::REPLY_OPTION_WAIT_SEQUENTIAL,
'text' => 'Ａ）在庫有り分のみ発送してください',
'value' => 'count_reply_wait_sequential',
'enabled' => true,
'linked' => true,
],
self::REPLY_OPTION_CANCEL_DELAYED => [
'id' => self::REPLY_OPTION_CANCEL_DELAYED,
'text' => 'Ｃ）遅れた分だけキャンセルします',
'value' => 'count_reply_cancel_delayed',
'enabled' => false,
'linked' => true,
],
self::REPLY_OPTION_CANCEL_ALL => [
'id' => self::REPLY_OPTION_CANCEL_ALL,
'text' => 'Ｂ）全てキャンセル',
'value' => 'count_reply_cancel_all',
'enabled' => true,
'linked' => true,
],
self::REPLY_OPTION_ALTERNATIVE_DESIRED => [
'id' => self::REPLY_OPTION_ALTERNATIVE_DESIRED,
'text' => 'Ｃ）代替え商品（※色・サイズ希望をご返信下さい）',
'value' => 'count_reply_alternative_desired',
'enabled' => true,
'linked' => false,
],
];
const MEMO_FORMAT = '%s【納期遅れ対応】%s';
private static $GROUP_CONCAT_SEPARATOR = "%%__\t__%%"; // 画像説明やaltのテキストとして使用されてなさそうであればなんでもよい
public static $IMAGE_INFO_MAP = array(
// 基本01-イメージ部分
self::TYPE_BASE_01 => array(
'server_dir_name' => 'images',
'image_name' => '%s-images',
),
// 着用イメージ部分1
self::TYPE_WEARING_IMAGE_01 => array(
'server_dir_name' => 'first-images',
'image_name' => '%s-first-images',
),
// 着用イメージ部分2
self::TYPE_WEARING_IMAGE_02 => array(
'server_dir_name' => 'second-images',
'image_name' => '%s-second-images',
),
// 着用イメージ部分3
self::TYPE_WEARING_IMAGE_03 => array(
'server_dir_name' => 'third-images',
'image_name' => '%s-third-images',
),
// 着用イメージ部分4
self::TYPE_WEARING_IMAGE_04 => array(
'server_dir_name' => 'fourth-images',
'image_name' => '%s-fourth-images',
),
// 10-モデル雰囲気・モデル前後（メンズ）
self::TYPE_MODEL_MENS => array(
'server_dir_name' => 'front-back',
'image_name' => '%s-mens-front-back',
),
// 10-モデル雰囲気・モデル前後（レディース）
self::TYPE_MODEL_LADIES => array(
'server_dir_name' => 'front-back',
'image_name' => '%s-ladies-front-back',
),
// 11-ハンガーイラスト前後
self::TYPE_HANGER_ILLUST => array(
'server_dir_name' => 'hanger-illust',
'image_name' => '%s-hanger-illust',
),
// 素材・機能（複数枚）
self::TYPE_FABRIC => array(
'server_dir_name' => 'fabric',
'image_name' => '%s-fabric-%s',
),
// アッパー
self::TYPE_UPPER => array(
'server_dir_name' => 'shoes-upper',
'image_name' => '%s-upper',
),
// ミッドソール
self::TYPE_MID_SOLE => array(
'server_dir_name' => 'shoes-mid-sole',
'image_name' => '%s-mid-sole',
),
// アウトソール
self::TYPE_OUT_SOLE => array(
'server_dir_name' => 'shoes-out-sole',
'image_name' => '%s-sole',
),
// こだわりPOINT(商品の平置き写真-前後)
self::TYPE_DETAIL_FRONT_BACK => array(
'server_dir_name' => 'detail-front-back',
'image_name' => '%s-detail-front-back',
),
// こだわりPOINT(詳細写真２カラム・３カラム) （複数枚）
self::TYPE_DETAIL_COLUMN => array(
'server_dir_name' => 'detail-column',
'image_name' => '%s-detail-column-%s',
'default_column_num' => 3
),
// 15-基本03-色展開
self::TYPE_BASE_03 => array(
'server_dir_name' => 'color',
'image_name' => '%s-color',
),
// 色展開（個別）
self::TYPE_COLOR_VARIATION => array(
'server_dir_name' => 'color-kobetsu',
'image_name' => '%s-color-%s',
'default_column_num' => 3
),
// カラーUP
self::TYPE_COLORUP => array(
'server_dir_name' => 'colorup',
'image_name' => '%s-colorup',
),
// サイズシルエット
self::TYPE_SIZE_SILHOUETTE => array(
'server_dir_name' => 'size-silhouette',
'image_name' => '%s-ladies-silhouette',
),
// ポケットサイズエリア
self::TYPE_POCKET_SIZE_AREA => array(
'server_dir_name' => 'pocket-size',
'image_name' => '%s-pocket-size',
),
// プリントエリア
self::TYPE_PRINT_AREA => array(
'server_dir_name' => 'printarea',
'image_name' => '%s-printarea',
),
// サイズガイド
self::TYPE_SIZE_GUIDE => array(
'server_dir_name' => 'size-guide',
'image_name' => '%s-size-guide',
),
);
/**
* @var SqlService $Sql
*/
protected $Sql;
/**
* @var LmHelper
*/
protected $lmHelper;
/**
* @var EccubeConfig
*/
protected $eccubeConfig;
/**
* @var ProductConverter
*/
private $ProductConverter;
/**
* @var DynamicConverterService
*/
private $dynamicConverterService;
public function __construct(LmHelper $lmHelper,
EccubeConfig $eccubeConfig,
ProductConverter $productConverter,
DynamicConverterService $dynamicConverterService)
{
//
parent::__construct(new CacheService());
//
$this->lmHelper = $lmHelper;
$this->eccubeConfig = $eccubeConfig;
$this->ProductConverter = $productConverter;
$this->dynamicConverterService = $dynamicConverterService;
}
/**
* FIXME: DELETE ME.
* @param int $goodsId
* @return array|null
*/
public function getGoodsById($goodsId)
{
//
$result = (new SqlService())
->Table('goods_table')
->Set('goods_id', $goodsId)
->Find();
//
return $result;
}
public function getRankingList()
{
$result = (new SqlService())
->Table('daily_ranking_table')
->Join('goods_table', 'T.goods_id = T1.goods_id', 'inner')
->Select('T1.goods_id, T1.goods_name, ROUND(goods_price/ 1.08, 0) AS goods_price, ranking_no, T.goods_genre_text, create_date, update_date, COALESCE(T1.goods_canonical_hinban, T1.goods_id) AS item_id')
->FindAll();
return $result;
}
public function getFbnrs($params = [])
{
//
if (!isset($params['type'])) {
$params = $this->lmHelper->getPageType();
}
//
$siteType = $this->eccubeConfig['LM_SITE_ID'];
$webName = $params['value'];
//
return $this->dynamicConverterService->getFbnrs($params, $siteType, $webName);
}
public function getMessageCountByCategory()
{
$result = (new SqlService())
->Table('customer_message_category_table')
->Set('cmc_id', 28, '!=')
->Set('delete', 0)
->Order('cmc_count', 'DESC')
->Limit(5)
->FindAll();
return $result;
}
public function getCommonContentsMap($site_type, $device_type, $is_not_super_category_id)
{
$result = (new SqlService())
->Table('common_contents_table')
->Join('main_category_table', 'T.super_category_id = T1.main_category_id', 'LEFT')
->Set('site_type', $site_type)
->Set('device_type', $device_type)
->Select('T.*, T1.main_category_webname')
->Limit(5)
->FindAll();
$map = array();
if (isset($result)) {
foreach ($result as $row) {
if ((int)$row['super_category_id'] === $is_not_super_category_id) {
$map[$row['page_type']][$row['position']] = $row['contents'];
} else {
$map[$row['page_type']][$row['position']][$row['main_category_webname']] = $row['contents'];
}
}
}
return $map;
}
public function getVisitSubCategoryList()
{
$result = (new SqlService())
->Table('category_table')
->Join('main_category_table', 'T.category_main_category = T1.main_category_id')
->Set('T1.main_category_webname', 'visit')
->Set('T.category_status', 1)
->Order('T.category_webname', 'DESC')
->Limit(5)
->FindAll();
return $result;
}
public function getGoodsVendingStatusList()
{
$result = (new SqlService())
->Table('goods_table')
->Set('goods_ddate', null)
->Select('goods_id, goods_vending_status')
->FindAll();
return $result;
}
public function getMessageList($select = '*', $limit = null, $file = null)
{
$query = (new SqlService())
->Table('customer_message_table')
->Select($select)
->Set('cm_id', 0, '>')
->Set('display', 1)
->Set('approval', 1)
->Order('cm_id', 'desc');
if (isset($file)) $query->Set('file', 'NOT NULL');
if (isset($limit)) $query->Limit($limit);
return $query->FindAll();
}
public function getTopicData()
{
$result = (new SqlService())
->Table('topix_table')
->Select("*, date_format( topix_cdate, '%Y/%m/%d' ) as cdate")
->Order('topix_cdate', 'DESC')
->Order('topix_display')
->Limit(6)
->FindAll();
return $result;
}
public function LM_get_koe_count()
{
$result = (new SqlService())
->Table('customer_message_table')
->Set('cm_id', 0, '>')
->Set('display', 1)
->Set('approval', 1)
->Select('COUNT(*) as CNT')
->FindAll();
if (sizeof($result) > 0) return $result[0]['CNT'];
return null;
}
public function getHolidayInfo($year, $month, $day, $week, $count)
{
$result = (new SqlService())
->Table('holiday_calendar_table')
->Where("holiday_year = 0 or holiday_year = {$year}")
->Where("holiday_month = 0 or holiday_month = {$month}")
->Where("holiday_day = 0 or holiday_day = {$day}")
->Where("holiday_week = 0 or holiday_week = {$week}")
->Where("holiday_count = 0 or holiday_count = {$count}")
->Where("not ( holiday_year = 0 and holiday_month = 0 and holiday_day = 0 and holiday_count = 0 and holiday_week = 0 )")
->Find();
return $result;
}
public function getMenuGroup($menu_group, $bool_true = 1)
{
$result = (new SqlService())
->Table('sp_customized_menu_table')
->Set('menu_group_name', $menu_group)
->Set('is_active', $bool_true)
->Where('main_category_id IS NULL')
->Order('menu_id')
->Limit(5)
->FindAll();
if (empty($result)) return [];
return $this->buildMenu($result);
}
public function getMainCategoryMenu($main_category_webname)
{
$items = (new SqlService())
->Sql('
SELECT sp_customized_menu_table.*
FROM sp_customized_menu_table
JOIN main_category_table ON main_category_table.main_category_id = sp_customized_menu_table.main_category_id
WHERE is_active = 1 AND menu_group_name = :menu_group_name AND main_category_webname = :main_category_webname
ORDER BY menu_id
')
->Params([
'menu_group_name' => $this->eccubeConfig['EditSpMenu']['MENU_NAME_CATEGORY'],
'main_category_webname' => $main_category_webname
])
->FetchAll();
if (empty($items)) {
return [];
}
return $this->buildMenu($items);
}
public function getSearchWordGroup($word_group, $bool_true = 1)
{
$result = (new SqlService())
->Table('sp_customized_frequently_searched_word_table')
->Set('word_group_name', $word_group)
->Where('main_category_id IS NULL')
->Set('is_active', $bool_true)
->Order('word_id')
->Limit(5)
->FindAll();
if (empty($result)) return [];
return $result;
}
private static function buildMenu($rows)
{
$menu = (object)[
'id' => 0,
'children' => []
];
$parent_ref = &$menu;
$menu_map = [0 => $menu];
$parent_id = 0;
$current_indent = 0;
foreach ($rows as &$row) {
$id = (int)$row['menu_id'];
$indent = (int)$row['indent'];
$menu_data = (object)[
'id' => $id,
'label' => $row['label'],
'url' => $row['url'],
'children' => [],
'parent_id' => $parent_id
];
$menu_map[$id] = $menu_data;
// 階層落ちる
if ($indent > $current_indent) {
$parent_ref = &$menu_map[$parent_id];
$parent_ref->children[] = &$menu_map[$id];
$parent_id = $id;
} // 階層上がる
else if ($indent < $current_indent) {
// 3階層から1階層に上がることもあるので、ループで処理
for ($i = 0; $i <= $current_indent - $indent; $i++) {
$parent_id = $menu_map[$parent_id]->parent_id;
}
$parent_ref = &$menu_map[$parent_id];
$menu_map[$id]->parent_id = $parent_ref->id;
$parent_ref->children[] = &$menu_map[$id];
$parent_id = $id;
} // 階層変わらず
else {
// 階層落ちの段階でparent_idが同階層のメニューになっているので1つ戻す
// &$menu_map[$id]->parent_id = $menu_map[$parent_id]->parent_id;
$menu_map[$id]->parent_id = $menu_map[$parent_id]->parent_id;
$parent_ref = &$menu_map[$menu_map[$parent_id]->parent_id];
$parent_ref->children[] = &$menu_map[$id];
$parent_id = $id;
}
$current_indent = $indent;
}
return $menu->children;
}
public function getPageContent($deviceName, $pageName, $contentName)
{
$result = (new SqlService())
->Table('page_content_table')
->Set('pc_site_type', 1)
->Set('pc_device_name', $deviceName)
->Set('pc_page_name', $pageName)
->Set('pc_content_name', $contentName)
->Find();
return $result;
}
public function getUpperColumnByCategory($siteType = null, $webName = null)
{
//
if ($siteType === null) {
$siteType = $this->eccubeConfig['LM_SITE_ID'];
}
//
$query = (new SqlService())
->Table('upper_column_table')
->Set('site_type', $siteType);
if ($webName) {
$query->Join('main_category_table', 'T.target_category_id = T1.main_category_id', 'Left')
->Set('T1.main_category_has_main_categories', 1)
->Set('T1.main_category_webname', $webName)
->Set('T1.main_category_status', 1);
} else {
$query
->Set('target_category_id', null);
}
return $query->Select('upper_one_column_content')->Find();
}
public function getReview($id, $isSecret = true, $main_category_id = null, $category_id = null, $limit = '', $offset = 0)
{
$SELECT = !empty($limit) ? "SELECT SQL_CALC_FOUND_ROWS" : "SELECT";
$sql = "$SELECT
DISTINCT
cr_oh_id
,cr_goods_id
,cr_customer_id
,cr_name
,cr_points
,cr_title
,cr_comment
,cr_staff_comment
,CASE cr_age
WHEN '1' THEN '10代'
WHEN '2' THEN '20代'
WHEN '3' THEN '30代'
WHEN '4' THEN '40代'
WHEN '5' THEN '50代'
WHEN '6' THEN '60代'
ELSE '秘密'
END cr_age
,CASE cr_sex
WHEN '0' THEN '男性'
WHEN '1' THEN '女性'
ELSE '秘密'
END cr_sex
,cr_approval
,app_datetime
,upd_datetime
,add_datetime
,goods_name
,(SELECT min(gp_kataban) FROM goods_price_table where cr_goods_id = gp_goods GROUP BY gp_goods) as gp_kataban
FROM customer_review_table
LEFT JOIN goods_table ON goods_id = cr_goods_id
WHERE cr_approval = '1' AND cr_site_id = 0";
if (!empty($id) && empty($main_category_id)) {
// 商品ページのみに表示
$sql .= " AND cr_goods_id = {$id}
AND cr_main_category_id = 0
AND EXISTS (
SELECT * FROM goods_table WHERE goods_id = {$id} AND goods_ddate IS NULL";
if (!$isSecret) {
$sql .= " AND goods_status = 1 ";
}
$sql .= " )";
} else if (!empty($main_category_id) && empty($category_id)) {
// 親カテページのみに表示
$sql .= " AND cr_main_category_id = {$main_category_id}
AND cr_category_id = 0";
} else if (!empty($main_category_id) && !empty($category_id)) {
// 子カテページのみに表示
$sql .= " AND cr_main_category_id = {$main_category_id}
AND cr_category_id = '{$category_id}'";
}
$sql .= " ORDER BY `customer_review_table`.`app_datetime` desc";
if (!empty($limit)) {
// 商品ページのみに表示
$sql .= " LIMIT {$offset}, {$limit}";
}
return (new SqlService())->Sql($sql)->FetchAll();
}
public function LM_displayteika_new($id, $getOnlyTeika = false)
{
$rs = $this->getGoodsPriceSelect($id) ?? [];
$tmp_row = 0;
$ma = 0;
$mi = 100;
$ma_kakaku = 0;
$mi_kakaku = 0;
$max_teika = 0;
$min_teika = 0;
$max_teika2 = 0;
$min_teika2 = 0;
$viewStr = array('teika' => null, 'off' => null);
foreach ($rs as $row) {
$tmp_row++;
if ($tmp_row == 1) {
$ma_kakaku = $row['gp_price'];
$mi_kakaku = $row['gp_price'];
} else {
if ($ma_kakaku < $row['gp_price'])
$ma_kakaku = $row['gp_price'];
if ($mi_kakaku > $row['gp_price'])
$mi_kakaku = $row['gp_price'];
}
$gp_teika = round(intval($row['gp_teika']) / 1.10);
if ($max_teika2 < $gp_teika) {
$max_teika2 = $gp_teika;
$max_teika = intval($row['gp_teika']);
}
if ($min_teika2 == 0 or $min_teika2 > $gp_teika) {
$min_teika2 = $gp_teika;
$min_teika = intval($row['gp_teika']);
}
if (intval($row['gp_teika']) > 0 and intval($row['gp_price']) > 0) {
$hikaku = round((1 - $row['gp_price'] / $row['gp_teika']) * 100);
if ($ma < $hikaku) $ma = $hikaku;
if ($mi == 0 or $mi > $hikaku) $mi = $hikaku;
}
$sagaku[] = intval($row['gp_teika']) - $row['gp_price'];
}
if ($tmp_row > 0) {
$teika = "";
if ($min_teika != $max_teika and $max_teika > 0 and $min_teika > 0) {
$teika2 = number_format($min_teika2) . "～" . number_format($max_teika2);
$teika = number_format($min_teika) . "～" . number_format($max_teika);
$sagakuMsg = "" . number_format(max($sagaku));
} elseif ($min_teika == $max_teika) {
$teika2 = number_format($min_teika2);
$teika = number_format($min_teika);
$sagakuMsg = number_format(max($sagaku));
}
$per = "";
if ($ma == $mi and $ma != 0) {
$per = $ma . "%";
} elseif ($mi != 0 and $ma != 0) {
// $per = $mi . "～" . $ma . "%";
$per = "" . $ma . "%";
}
if (!empty($teika)) {
if ($getOnlyTeika) {
return $teika;
}
$viewStr['teika'] = '' . $teika . '円 (税抜価格' . $teika2 . '円) ↓ ';
}
if ($teika != "" and $per != "") {
$viewStr['off'] = $per . "OFF";
}
}
return $viewStr;
}
public function getGoodsPriceJoinedByGoodsIdList($goodsIdList)
{
// just accept integer for id in $goodsIdList
$goodsIdList = (array)$goodsIdList;
foreach ($goodsIdList as $key => &$id) {
if ($id) {
$id = intval($id);
} else {
// remove $id not avaiable like null and empty ''
unset($goodsIdList[$key]);
}
}
// re - index array
$goodsIdList = array_values($goodsIdList);
//
$goodsIdIn = implode(',', (array)$goodsIdList);
//
$sql = "SELECT
goods_id
, gp_kataban
, gp_id
, SUM(ROUND(`gp_teika` / 1.10)) AS `sum_teika_ex_tax`
, SUM(`gp_teika`) AS `sum_teika_in_tax`
, SUM(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN (truncate((cp_price/1.10) + .5,0)) ELSE (gp_price2) END) AS `sum_price_ex_tax`
, SUM(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN (cp_price) ELSE (ROUND(gp_price2 * (1+ 10/100))) END) AS `sum_price_in_tax`
, (NOW() BETWEEN MIN(`cp_start_datetime`) AND MAX(`cp_end_datetime`)) AS is_campaign
, MIN(`cp_start_date`) AS `cp_start_date`
, MAX(`cp_end_date`) AS `cp_end_date`
, MIN(`cp_start_datetime`) AS `cp_start_datetime`
, MAX(`cp_end_datetime`) AS `cp_end_datetime`
FROM (
SELECT
IfNull(`parent_goods_id`, goods_id) AS `goods_id`
, IfNull(`parent_goods_main_kataban`, gp_kataban) AS `gp_kataban`
, gp_id
, COALESCE(`gsp_gp_teika`, gp_teika) AS `gp_teika`
, COALESCE(`gsp_gp_price2`, gp_price2) AS `gp_price2`
, COALESCE(`gsp_cp_price`, cp_price) AS `cp_price`
, COALESCE(`gsp_cp_start_date`, cp_start_date) AS `cp_start_date`
, COALESCE(`gsp_cp_end_date`, cp_end_date) AS `cp_end_date`
, COALESCE(`gsp_cp_start_datetime`, cp_start_datetime) AS `cp_start_datetime`
, COALESCE(`gsp_cp_end_datetime`, cp_end_datetime) AS `cp_end_datetime`
FROM (
SELECT
gp_goods AS `goods_id`
, gp_kataban
, gp_id
, gp_teika
, gp_price2
, cp_price
, cp_start_date
, cp_end_date
, cp_start_datetime
, cp_end_datetime
FROM goods_price_table
LEFT JOIN campaign_price_table ON cp_gp = gp_id AND `cp_end_datetime` >= NOW() and `cp_start_datetime` <= NOW() and cp_del_flg = 0
WHERE (gp_display != 99 OR gp_display IS NULL) AND gp_goods IN ({$goodsIdIn})
) AS `main_gp`
LEFT JOIN (
SELECT
`parent`.goods_id AS `parent_goods_id`
, `parent`.goods_main_kataban AS `parent_goods_main_kataban`
, `parent_gp`.gp_id
, `child_gp`.gp_teika AS `gsp_gp_teika`
, `child_gp`.gp_price2 AS `gsp_gp_price2`
, `child_cp`.cp_price AS `gsp_cp_price`
, `child_cp`.cp_start_date AS `gsp_cp_start_date`
, `child_cp`.cp_end_date AS `gsp_cp_end_date`
, `child_cp`.cp_start_datetime AS `gsp_cp_start_datetime`
, `child_cp`.cp_end_datetime AS `gsp_cp_end_datetime`
FROM goods_table AS `parent`
INNER JOIN goods_price_table AS `parent_gp` ON `parent`.goods_id = `parent_gp`.gp_goods
INNER JOIN goods_set_purchase_table ON `parent`.goods_id = gsp_goods_parent
INNER JOIN gsp_gp_table ON `parent_gp`.gp_id = gsp_gp_parent
INNER JOIN goods_table AS `child` ON `child`.goods_id = gsp_goods_child
INNER JOIN goods_price_table AS `child_gp` ON `child`.goods_id = `child_gp`.gp_goods AND `child_gp`.gp_id IN ( gsp_gp_main, gsp_gp_sub, gsp_gp_other )
LEFT JOIN campaign_price_table AS `child_cp` ON `child_gp`.gp_id = `child_cp`.cp_gp AND `child_cp`.`cp_end_datetime` >= NOW() and `child_cp`.`cp_start_datetime` <= NOW() and `child_cp`.`cp_del_flg` =0
WHERE `parent`.goods_id IN ({$goodsIdIn})
) AS `gsp_gp` USING (
gp_id
)
) AS `gp`
GROUP BY
`goods_id`
, gp_id";
//
return $sql;
}
public function LM_displayPriceList3($data)
{
// コーディネート用デザインで使用
$viewStr = '';
// 通常の場合のみ表示
if (!isset($data['tt_type']) or $data['tt_type'] == "1") {
$gp = $this->getGoodsPriceJoinedByGoodsIdList($data);
$strSql = "SELECT
goods_id
, CONCAT('https://www.l-m.co.jp/item/', `goods_id`, '.html') AS `URL`
, MIN(`sum_price_ex_tax`) AS `gp_price`
, MIN(`sum_price_in_tax`) AS `mi`
, MAX(`sum_price_in_tax`) AS `ma`
, MAX(`is_campaign`) AS `is_campaign`
, MIN(`cp_start_datetime`) AS `cp_start_datetime`
, MAX(`cp_end_datetime`) AS `cp_end_datetime`
FROM (
{$gp}
) AS `g`
GROUP BY
`goods_id`";
$row2 = (new SqlService())->Sql($strSql)->FetchAll();
if ($row2) {
if ($row2[0]['mi'] == $row2[0]['ma']) {
$viewStr .= number_format($row2[0]['mi']);
} else {
$viewStr .= number_format($row2[0]['mi']) . "～" . number_format($row2[0]['ma']);
}
}
}
//return $viewStr;
return $row2[0];
}
public function getGoodsPriceSelect($id)
{
$strSql = "SELECT gp_teika,
(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN truncate(cp_price + .5,0) ELSE ROUND(gp_price2 * (1+ 10/100)) END) as gp_price
FROM jancode_table
INNER JOIN goods_price_table ON jan_price = gp_id
INNER JOIN size_table ON gp_size_id = size_id
INNER JOIN goods_color_table ON jan_color = gcl_id
INNER JOIN color_table ON gcl_color_id = color_id
LEFT JOIN campaign_price_table ON cp_gp = gp_id AND `cp_end_datetime` >= NOW() and `cp_start_datetime` <= NOW() and cp_del_flg = 0
WHERE jan_goods = :goods_id
AND jan_ddate IS NULL
AND gp_price IS NOT NULL
AND gp_teika IS NOT NULL
AND (gp_display != 99 OR gp_display IS NULL)
GROUP BY cp_id,gp_id";
return (new SqlService())
->Sql($strSql)
->Params([
'goods_id' => $id,
])
->FetchAll();
}
public function getSearchCoordinate($id)
{
$result = (new SqlService())
->Table('goods_table')
->Set('goods_id', $id)
->Set('goods_design_coo', 'NOT NULL')
->Select('goods_design_coo')
->FindAll();
return $result;
}
public function getCoosData($goods_design_coo)
{
$result = (new SqlService())
->Table('goods_table')
->Set('goods_id', $goods_design_coo, 'IN')
->Set('goods_status', Goods::GOODS_STATUS_AVAIABLE)
->Set('goods_ddate', 'NULL')
->Select("goods_id, goods_canonical_hinban, goods_name")
->FindAll();
return $result;
}
public function getMainCategoryNameSelect($mcName)
{
$result = (new SqlService())
->Table('main_category_table')
->Join('category_group_table', 'T1.category_group_id = T.main_category_group', 'INNER')
->Join('category_table', 'T.main_category_id = T2.category_main_category', 'LEFT')
->Set('main_category_webname', $mcName)
->Select("main_category_webname AS mc, T2.category_webname AS ct, main_category_id, T2.category_id, T1.category_group_noshowdb, T1.category_group_only_main, main_category_name, T2.category_name")
->FindAll();
return $result;
}
public function getSubCategoryNameSelect($mcId, $ctName)
{
$result = (new SqlService())
->Table('main_category_table')
->Join('category_table', 'T.main_category_id = T1.category_main_category', 'INNER')
->Set('main_category_id', $mcId)
->Set('T1.category_webname', $ctName)
->Select("main_category_webname AS mc, T1.category_webname AS ct, main_category_id, T1.category_id")
->FindAll();
return $result;
}
public function getPopularityItemLIstByMainCategoryId2($mcId, $isSecret = false, $limit = 30, $offset = 0)
{
// DBからデータ取得
$sql = "select goods_id, goods_canonical_hinban, goods_recommend, goods_name, goods_sub_name, goods_caption, goods_display, goods_cdate, goods_udate, min( gp_price2 ) as price, min( gp_kataban ) as kataban, NULL AS category_id, COALESCE(cra_average_points,0) as average, cra_target_count as review_count
from goods_table
inner join goods_main_category_table on gmc_goods = goods_id
inner join main_category_table on main_category_id = gmc_main_category
inner join category_group_table on category_group_id = main_category_group AND category_group_only_main = 1
left join goods_price_table on goods_id = gp_goods
left join rank_table on goods_id = rank_goods
left join customer_review_average_table on cra_goods_id = goods_id
where goods_ddate is null AND main_category_id = {$mcId}";
if (!$isSecret) {
$sql .= " AND goods_status = 1 ";
}
$sql .= "
group by rank_money, goods_id, goods_recommend, goods_name, goods_caption, goods_display, goods_cdate, goods_udate
order by isnull( rank_money ), rank_money DESC, goods_recommend desc, price, isnull( goods_display ), goods_display, goods_udate desc, goods_cdate desc limit {$limit} offset {$offset}";
$tempRow = (new SqlService())->Sql($sql)->FetchAll();
$item = array();
if (isset($tempRow)) {
foreach ($tempRow as $row) {
$item[] = $row;
}
}
return $item;
}
public function getSearchAllCategory($mc)
{
return (new SqlService())->Sql("SELECT *, '全商品' AS category_name, 'allitem' AS category_id FROM front_main_category_table WHERE main_category_id = '{$mc}'")->FetchAll();
}
public function getSearchGoodsImage($mc, $ct)
{
$result = (new SqlService())
->Table('front_main_category_table')
->Join('front_category_table', 'T1.category_main_category = T.main_category_id')
->Set('main_category_id', $mc)
->Set('T1.category_id', $ct)
->FindAll();
return $result;
}
public function getSearchCategory($mc)
{
return (new SqlService())
->Table('front_main_category_table')
->Set('main_category_id', $mc)
->FindAll();
}
public function getGoodsKatabanSelect($id)
{
$query = "SELECT DISTINCT gp_kataban FROM goods_price_table WHERE gp_goods = :goods_id AND NOT(gp_display <=> 99) LIMIT 0,3";
$rows = (new SqlService())->Sql($query)
->Param('goods_id', $id)
->FetchAll();
return $rows;
}
public function getGoodsColorSelect($id)
{
$strSql = "SELECT
*
FROM (
SELECT
COALESCE(`gcl_parent`.gcl_id, `gcl_child`.gcl_id) AS `gcl_id`
, COALESCE(`gcl_parent`.gcl_display, `gcl_child`.gcl_display) AS `gcl_display`
, COALESCE(`color_parent`.color_id, `color_child`.color_id) AS `color_id`
, COALESCE(`color_parent`.color_display, `color_child`.color_display) AS `color_display`
, COALESCE(`color_parent`.color_name, `color_child`.color_name) AS `color_name`
, COALESCE(`color_parent`.color_rgb, `color_child`.color_rgb) AS `color_rgb`
FROM goods_table AS `parent`
LEFT JOIN goods_color_table AS `gcl_parent` ON `parent`.goods_id = `gcl_parent`.gcl_goods AND `gcl_parent`.gcl_display_status = 1
LEFT JOIN color_table AS `color_parent` ON `color_parent`.color_id = `gcl_parent`.gcl_color_id
LEFT JOIN goods_set_purchase_table ON `parent`.goods_id = gsp_goods_parent AND gsp_type = 0
LEFT JOIN goods_table AS `child` ON `child`.goods_id = gsp_goods_child
LEFT JOIN goods_color_table AS `gcl_child` ON `child`.goods_id = `gcl_child`.gcl_goods AND `gcl_child`.gcl_display_status = 1 and `gcl_child`.gcl_color_id = `gcl_parent`.gcl_color_id
LEFT JOIN color_table AS `color_child` ON `color_child`.color_id = `gcl_child`.gcl_color_id
WHERE `parent`.goods_id = {$id}
) AS `goods`
ORDER BY
gcl_display
, color_display
, CAST( color_name AS binary )";
return (new SqlService())->Sql($strSql)->FetchAll();
}
public function getStockList($id)
{
$sql = "SELECT gcl_id, gp_id, stock.stock, stock.jan_stock3, stock.jan_stock3_nyuka_date, stock.jan_stock4, stock.jan_stock5, stock.jan_stock6, stock.jan_stock7, stock.jan_stock8, stock.jan_stock9, stock.nodisp, stock.jan_color, stock.jan_price, ki_id IS NOT NULL AS keppin
FROM goods_color_table
INNER JOIN color_table ON gcl_color_id = color_id
INNER JOIN (
SELECT gp_goods, gp_id,
(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN truncate((cp_price/1.10) + .5,0) ELSE gp_price2 END) as gp_price
FROM goods_price_table
INNER JOIN size_table ON gp_size_id = size_id
LEFT JOIN campaign_price_table ON cp_gp = gp_id AND `cp_end_datetime` >= NOW() and `cp_start_datetime` <= NOW() and cp_del_flg =0
WHERE gp_goods = {$id}
AND ( gp_display IS NULL OR ( gp_display IS NOT NULL AND gp_display != 99 ) )
) AS size ON gcl_goods = gp_goods
INNER JOIN (
SELECT jan_goods, jan_color, jan_price, coalesce( jan_stock, '＊' ) AS stock, jan_stock3, jan_stock3_nyuka_date, jan_stock4, jan_stock5, jan_stock6, jan_stock7, jan_stock8, jan_stock9, jan_nodisplay AS nodisp
FROM jancode_table
WHERE jan_goods = {$id}
) AS stock ON jan_goods = gcl_goods AND jan_color = gcl_id AND jan_price = gp_id
LEFT JOIN (
SELECT ki_id, ki_goods, ki_gcl, ki_gp, MAX(ki_id) AS max_ki_id
FROM keppin_item_table AS ki
INNER JOIN (
SELECT MAX(ki_id) AS max_ki_id
FROM keppin_item_table
WHERE ki_goods = {$id} AND ki_date >= '" . date('Y-m-d 00:00:00') . "'
GROUP BY ki_goods, ki_gcl, ki_gp
) AS max_ki ON ki_id = max_ki_id
) AS max_ki ON ki_goods = gcl_goods AND (ki_gcl IS NULL OR ki_gcl = gcl_id) AND (ki_gp IS NULL AND ki_gp = gp_id)
WHERE gcl_goods = {$id}
AND gcl_display_status = 1
ORDER BY gcl_display, color_display, CAST( color_name AS binary )";
return (new SqlService())->Sql($sql)->FetchAll();
}
public function LM_chokusouCheck($goodsId, $gcl = "", $gp = "")
{
#2022/09/08 chkPass初期化
$chkPass = 0;
$data = $this->getShiiresakiGoodsData2($goodsId, $gcl, $gp);
$goods = $data[0] ?? null;
if (isset($goods) && ($goods['goods_shiiresaki'] == "" or $goods['gp_kataban'] == "" or $goods['jan_shiire_no'] == "")) {
//セット品番か確認
$data = $this->getShiiresakiGoodsData3($goodsId);
$goods = $data[0] ?? null;
if (isset($goods) && $goods['goods_shiiresaki'] != "") {
// 直送確認
$directData = $this->getShiiresakiDirectDeliveryData($goods['goods_shiiresaki']);
if ($directData[0]['dd_shiiresaki'] == $goods['goods_shiiresaki'] && $directData[0]['dd_hinban_chokusou'] == "" && $directData[0]['dd_hinban_exclude'] == "" && $directData[0]['dd_hinban_bichiku'] == "") {
return 1;
}
}
return 2;
}
// 直送確認
$directData = $this->getShiiresakiDirectDeliveryData($goods['goods_shiiresaki']);
if (empty($directData)) {
return 2;
}
// 仕入先品番
$shiireno = mb_ereg_replace('[^a-zA-Z]', '', $goods['jan_shiire_no']);
//直送除外品番（ＬＭ品番）
if ($directData[0]['dd_hinban_exclude'] != "") {
$chkKey1 = explode(",", $directData[0]['dd_hinban_exclude']);
for ($i = 0; $i < count($chkKey1); $i++) {
if ($goods['gp_kataban'] == $chkKey1[$i]) {
return 2;
}
}
}
$chkPass = "";
//備蓄対象品番（ＬＭ品番）
if ($directData[0]['dd_hinban_bichiku'] != "") {
$chkKey1 = explode(",", $directData[0]['dd_hinban_bichiku']);
$chkPass = 2;
for ($i = 0; $i < count($chkKey1); $i++) {
if ($goods['gp_kataban'] == $chkKey1[$i]) {
$chkPass = 1;
break;
}
}
}
//直送対象品番（該当アルファベット）
$soukoData = $this->getShiiresakiSoukoData($goods['goods_shiiresaki']);
if (isset($soukoData)) {
if ($chkPass != 1 && $soukoData[0]['shiiresaki_souko_hantei'] != "") {
$chkPass = 2;
$chkKey2 = explode(",", $soukoData[0]['shiiresaki_souko_hantei']);
for ($i = 0; $i < count($chkKey2); $i++) {
if ($shiireno == $chkKey2[$i]) {
$chkPass = 1;
break;
}
}
}
}
//直送対象品番（ＬＭ品番）
if ($chkPass != 1 && $directData[0]['dd_hinban_chokusou'] != "") {
$chkPass = 2;
$chkKey1 = explode(",", $directData[0]['dd_hinban_chokusou']);
for ($i = 0; $i < count($chkKey1); $i++) {
if ($goods['gp_kataban'] == $chkKey1[$i]) {
$chkPass = 1;
break;
}
}
}
//備蓄在庫数登録
if ($goods['jan_stock8_max'] > 0) {
$chkPass = 1;
} else if ($goods['jan_stock8_max'] !== null && !empty($goods['same_day_available'])) {
// 2020-04-06 worker_g: チトセより一部SKUの買取り在庫の対応。
// ・84-MZ0018
// ・84-MZ0019
// ・84-MZ0092
// ・84-MZ0093
$chkPass = 1;
}
//外部在庫数
if ($goods['jan_stock9_max'] > 0) {
$chkPass = 1;
}
if ($chkPass == "") {
$chkPass = 1;
}
// chkPass=1なら直送対象
return $chkPass;
}
public function getShiiresakiGoodsData2($goods, $gcl, $gp)
{
$strSql = "SELECT goods_shiiresaki, gp_kataban, jan_shiire_no
, MAX(jan_stock8) AS jan_stock8_max
, MAX(jan_stock9) AS jan_stock9_max
, MAX(jan_stock3 > 1 && IfNull(jan_stock3_nyuka_date, '0000-00-00') < NOW()) AS same_day_available
FROM jancode_table
INNER JOIN goods_table ON goods_id = jan_goods
INNER JOIN goods_price_table ON goods_id = gp_goods AND gp_id = jan_price
WHERE goods_id = {$goods} AND jan_ddate IS NULL";
if ($gcl != "" and $gp != "") {
$strSql .= " AND jan_color = " . $gcl . " AND jan_price = " . $gp . " ";
$strSql .= " GROUP BY gp_id";
} else {
$strSql .= " GROUP BY goods_id";
}
return (new SqlService())->Sql($strSql)->FetchAll();
}
public function getShiiresakiGoodsData3($id)
{
return (new SqlService())
->Table('goods_table')
->Set('goods_id', $id)
->Set('goods_set_purchase_flg', 1)
->Select('goods_shiiresaki')
->FindAll();
}
public function getShiiresakiDirectDeliveryData($shiiresaki)
{
return (new SqlService())
->Table('direct_delivery_table')
->Set('dd_shiiresaki', $shiiresaki)
->Where('dd_ddate IS NULL')
->FindAll();
}
public function getShiiresakiSoukoData($shiiresaki)
{
$strSql = "SELECT shiiresaki_souko_hantei,shiiresaki_souko_hinban,shiiresaki_souko2_hantei,shiiresaki_souko2_hinban,DATE_FORMAT( shiiresaki_shimekiri_time, '%H%i' ) AS stime FROM shiiresaki_table WHERE (shiiresaki_souko_hantei is not null OR shiiresaki_souko_hinban is not null OR shiiresaki_souko2_hantei is not null OR shiiresaki_souko2_hinban is not null ) AND shiiresaki_id = " . $shiiresaki;
return (new SqlService())->Sql($strSql)->FetchAll();
}
// 商品メイン画像
public function getGoodsImage($id, $title = null)
{
$dir = substr($id, -1);
$goodsDir = "/goods.img/{$dir}/{$id}/";
$image = [];
$tmpBigUrl = $goodsDir . "main.jpg";
$tmpMinUrl = $goodsDir . "main_45x45.jpg";
$tmpCom = "";
if (!empty($title)) {
$tmpCom = str_replace(array("\r\n", "\n", "\r"), '', strip_tags($title));
$tmpCom = mb_substr($tmpCom, 0, 20, "UTF-8");
}
$image = [$tmpBigUrl, $tmpMinUrl, $tmpCom];
return $image;
}
// 非営業日判定(Y-m-d形式)
public function getIsHoliday($date = null)
{
if (is_null($date)) $date = date('Y-m-d');
// DBには日曜が1、土曜が7として登録されているので1足す
$wday = (int)date('w', strtotime($date)) + 1;
list($year, $month, $day) = explode('-', $date);
$sql = "select
*
from
holiday_calendar_table
where
holiday_week = {$wday} or
(holiday_year = {$year} and holiday_month = {$month} and holiday_day = {$day})
";
return (new SqlService())->Sql($sql)->FetchAll();
}
public function sqlGspMain($parent_id)
{
$sql = "SELECT
gsp_goods_parent, gsp_type, gsp_name, gsp_show_flg, `child`.goods_id, `child`.goods_name, gp_child.gp_id, gp_child.gp_display,gp_child.gp_kataban, gp_child.gp_price, ROUND(MIN(cp_price)) AS cp_price, COALESCE(ROUND(MIN(cp_price)), gp_child.gp_price) AS price, size_id, size_name, size_display, gcl_id, gcl_display, color_id, color_display, color_name, color_rgb, jan_id, jan_shiire_no, jan_stock, jan_stock2, jan_stock3, jan_stock3_nyuka_date, jan_stock4, jan_stock5, IfNull(jan_stock, 0) + IfNull(jan_stock2, 0) + IfNull(jan_stock3, 0) + IfNull(jan_stock4, 0) + IfNull(jan_stock5, 0) as stock, jan_nodisplay, ki_comment, ki_date, zero_period, DATE_FORMAT(COALESCE(DATE_ADD(zero_period, INTERVAL 1 DAY), ki_date), '%c月%e日') AS zdate, COALESCE(ki_date, zero_period) AS nyuuka_yoteibi
, (
IfNull(MAX(jan_stock3), 0) > 0
AND IfNull(MAX(jan_stock3_nyuka_date), '0000-00-00') < NOW()
) AS `chokusou_flg`
FROM goods_table AS `parent`
INNER JOIN goods_set_purchase_table ON `parent`.goods_id = gsp_goods_parent
INNER JOIN goods_table AS `child` ON `child`.goods_id = gsp_goods_child
INNER JOIN goods_price_table AS gp_child ON `child`.goods_id = gp_child.gp_goods AND gp_child.gp_display != 99
INNER JOIN size_table ON size_id = gp_child.gp_size_id
INNER JOIN goods_color_table ON `child`.goods_id = gcl_goods AND gcl_display_status = 1
INNER JOIN color_table ON color_id = gcl_color_id
INNER JOIN jancode_table ON `child`.goods_id = jan_goods AND gcl_id = jan_color AND gp_child.gp_id = jan_price
LEFT JOIN campaign_price_table ON gp_child.gp_id = cp_gp AND cp_end_datetime >= NOW() and cp_start_datetime <= NOW() AND cp_price > 0
LEFT JOIN keppin_item_table ON `child`.goods_id = ki_goods AND gcl_id = ki_gcl AND gp_id = ki_gp AND ki_date > NOW()
LEFT JOIN zero_stock_table ON gcl_id = zero_gcl AND gp_id = zero_gp AND zero_period > NOW()
WHERE `parent`.goods_id = {$parent_id}
AND gsp_type = 0
GROUP BY gsp_goods_parent, gsp_type, `child`.goods_id, size_id, color_id
ORDER BY gsp_goods_parent, gsp_type, `child`.goods_id, size_id, color_id";
return $sql;
}
public function sqlGspSub($parent_id)
{
$sql = "SELECT
gsp_goods_parent, gsp_type, gsp_name, gsp_show_flg, `child`.goods_id, `child`.goods_name, gp_child.gp_id, gp_child.gp_display,gp_child.gp_kataban, gp_child.gp_price, ROUND(MIN(cp_price)) AS cp_price, COALESCE(ROUND(MIN(cp_price)), gp_child.gp_price) AS price, size_id, size_name, size_display, gcl_id, gcl_display, color_id, color_display, color_name, color_rgb, jan_id, jan_shiire_no, jan_stock, jan_stock2, jan_stock3, jan_stock3_nyuka_date, jan_stock4, jan_stock5, IfNull(jan_stock, 0) + IfNull(jan_stock2, 0) + IfNull(jan_stock3, 0) + IfNull(jan_stock4, 0) + IfNull(jan_stock5, 0) as stock, jan_nodisplay, ki_comment, ki_date, zero_period, DATE_FORMAT(COALESCE(DATE_ADD(zero_period, INTERVAL 1 DAY), ki_date), '%c月%e日') AS zdate, COALESCE(ki_date, zero_period) AS nyuuka_yoteibi
, (
IfNull(MAX(jan_stock3), 0) > 0
AND IfNull(MAX(jan_stock3_nyuka_date), '0000-00-00') < NOW()
) AS `chokusou_flg`
FROM goods_table AS `parent`
INNER JOIN goods_set_purchase_table ON `parent`.goods_id = gsp_goods_parent
INNER JOIN goods_table AS `child` ON `child`.goods_id = gsp_goods_child
INNER JOIN goods_price_table AS gp_child ON `child`.goods_id = gp_child.gp_goods AND gp_child.gp_display != 99
INNER JOIN size_table ON size_id = gp_child.gp_size_id
INNER JOIN goods_color_table ON `child`.goods_id = gcl_goods AND gcl_display_status = 1
INNER JOIN color_table ON color_id = gcl_color_id
INNER JOIN jancode_table ON `child`.goods_id = jan_goods AND gcl_id = jan_color AND gp_child.gp_id = jan_price
LEFT JOIN campaign_price_table ON gp_child.gp_id = cp_gp AND cp_end_datetime >= NOW() and cp_start_datetime <= NOW() AND cp_price > 0
LEFT JOIN keppin_item_table ON `child`.goods_id = ki_goods AND gcl_id = ki_gcl AND gp_id = ki_gp AND ki_date > NOW()
LEFT JOIN zero_stock_table ON gcl_id = zero_gcl AND gp_id = zero_gp AND zero_period > NOW()
WHERE `parent`.goods_id = {$parent_id}
AND gsp_type = 10
GROUP BY gsp_goods_parent, gsp_type, `child`.goods_id, size_id, color_id
ORDER BY gsp_goods_parent, gsp_type, `child`.goods_id, size_id, color_id";
return $sql;
}
public function sqlGspOther($parent_id)
{
$sql = "SELECT
gsp_goods_parent, gsp_type, gsp_name, gsp_show_flg, `child`.goods_id, `child`.goods_name, gp_child.gp_id, gp_child.gp_display,gp_child.gp_kataban, gp_child.gp_price, ROUND(MIN(cp_price)) AS cp_price, COALESCE(ROUND(MIN(cp_price)), gp_child.gp_price) AS price, size_id, size_name, size_display, gcl_id, gcl_display, color_id, color_display, color_name, color_rgb, jan_id, jan_shiire_no, jan_stock, jan_stock2, jan_stock3, jan_stock3_nyuka_date, jan_stock4, jan_stock5, IfNull(jan_stock, 0) + IfNull(jan_stock2, 0) + IfNull(jan_stock3, 0) + IfNull(jan_stock4, 0) + IfNull(jan_stock5, 0) as stock, jan_nodisplay, ki_comment, ki_date, zero_period, DATE_FORMAT(COALESCE(DATE_ADD(zero_period, INTERVAL 1 DAY), ki_date), '%c月%e日') AS zdate, COALESCE(ki_date, zero_period) AS nyuuka_yoteibi
, (
IfNull(MAX(jan_stock3), 0) > 0
AND IfNull(MAX(jan_stock3_nyuka_date), '0000-00-00') < NOW()
) AS `chokusou_flg`
FROM goods_table AS `parent`
INNER JOIN goods_set_purchase_table ON `parent`.goods_id = gsp_goods_parent
INNER JOIN goods_table AS `child` ON `child`.goods_id = gsp_goods_child
INNER JOIN goods_price_table AS gp_child ON `child`.goods_id = gp_child.gp_goods AND gp_child.gp_display != 99
INNER JOIN size_table ON size_id = gp_child.gp_size_id
INNER JOIN goods_color_table ON `child`.goods_id = gcl_goods AND gcl_display_status = 1
INNER JOIN color_table ON color_id = gcl_color_id
INNER JOIN jancode_table ON `child`.goods_id = jan_goods AND gcl_id = jan_color AND gp_child.gp_id = jan_price
LEFT JOIN campaign_price_table ON gp_child.gp_id = cp_gp AND cp_end_datetime >= NOW() and cp_start_datetime <= NOW() AND cp_price > 0
LEFT JOIN keppin_item_table ON `child`.goods_id = ki_goods AND gcl_id = ki_gcl AND gp_id = ki_gp AND ki_date > NOW()
LEFT JOIN zero_stock_table ON gcl_id = zero_gcl AND gp_id = zero_gp AND zero_period > NOW()
WHERE `parent`.goods_id = {$parent_id}
AND gsp_type NOT IN ( 0, 10 )
GROUP BY gsp_goods_parent, gsp_type, `child`.goods_id, size_id, color_id
ORDER BY gsp_goods_parent, gsp_type, `child`.goods_id, size_id, color_id";
return $sql;
}
public function sqlGsp($parent_id)
{
$sqlGspMain = $this->sqlGspMain($parent_id);
$sqlGspSub = $this->sqlGspSub($parent_id);
$sqlGspOther = $this->sqlGspOther($parent_id);
$sql = "SELECT
goods_parent.goods_id AS parent_goods_id
, goods_parent.goods_name AS parent_goods_name
, gp.gp_kataban AS parent_kataban
, gsp_main.gsp_type AS main_gsp_type
, gsp_main.gsp_name AS main_gsp_name
, gsp_main.gsp_show_flg AS main_gsp_show_flg
, gsp_main.goods_id AS main_goods_id
, gsp_main.goods_name AS main_goods_name
, gsp_main.gp_kataban AS main_kataban
, gsp_main.gp_price AS main_gp_price
, gsp_main.cp_price AS main_cp_price
, gsp_main.price AS main_price
, gsp_main.jan_id AS main_jan_id
, gsp_main.gcl_id AS main_gcl_id
, gsp_main.gcl_display AS main_gcl_display
, gsp_main.gp_id AS main_gp_id
, gsp_main.gp_display AS main_gp_display
, gsp_main.color_id AS main_color_id
, gsp_main.color_display AS main_color_display
, gsp_main.color_name AS main_color_name
, gsp_main.color_rgb AS main_color_rgb
, gsp_main.size_id AS main_size_id
, gsp_main.size_name AS main_size_name
, gsp_main.size_display AS main_size_display
, gsp_main.stock AS main_stock
, gsp_main.jan_stock3 AS main_stock3
, gsp_main.jan_stock3_nyuka_date AS main_stock3_nyuka_date
, gsp_main.`chokusou_flg` AS main_chokusou_flg
, gsp_main.jan_nodisplay AS main_nodisp
, gsp_main.ki_comment AS main_ki_comment
, gsp_main.ki_date AS main_ki_date
, gsp_main.zero_period AS main_zero_period
, gsp_main.zdate AS main_zdate
, gsp_main.nyuuka_yoteibi AS main_nyuuka_yoteibi
, gsp_sub.gsp_type AS sub_gsp_type
, gsp_sub.gsp_name AS sub_gsp_name
, gsp_sub.gsp_show_flg AS sub_gsp_show_flg
, gsp_sub.goods_id AS sub_goods_id
, gsp_sub.goods_name AS sub_goods_name
, gsp_sub.gp_kataban AS sub_kataban
, gsp_sub.gp_price AS sub_gp_price
, gsp_sub.cp_price AS sub_cp_price
, gsp_sub.price AS sub_price
, gsp_sub.jan_id AS sub_jan_id
, gsp_sub.gcl_id AS sub_gcl_id
, gsp_sub.gcl_display AS sub_gcl_display
, gsp_sub.gp_id AS sub_gp_id
, gsp_sub.gp_display AS sub_gp_display
, gsp_sub.color_id AS sub_color_id
, gsp_sub.color_display AS sub_color_display
, gsp_sub.color_name AS sub_color_name
, gsp_sub.color_rgb AS sub_color_rgb
, gsp_sub.size_id AS sub_size_id
, gsp_sub.size_name AS sub_size_name
, gsp_sub.size_display AS sub_size_display
, gsp_sub.stock AS sub_stock
, gsp_sub.jan_stock3 AS sub_stock3
, gsp_sub.jan_stock3_nyuka_date AS sub_stock3_nyuka_date
, gsp_sub.`chokusou_flg` AS sub_chokusou_flg
, gsp_sub.jan_nodisplay AS sub_nodisp
, gsp_sub.ki_comment AS sub_ki_comment
, gsp_sub.ki_date AS sub_ki_date
, gsp_sub.zero_period AS sub_zero_period
, gsp_sub.zdate AS sub_zdate
, gsp_sub.nyuuka_yoteibi AS sub_nyuuka_yoteibi
, gsp_other.gsp_type AS other_gsp_type
, gsp_other.gsp_name AS other_gsp_name
, gsp_other.gsp_show_flg AS other_gsp_show_flg
, gsp_other.goods_id AS other_goods_id
, gsp_other.goods_name AS other_goods_name
, gsp_other.gp_kataban AS other_kataban
, gsp_other.gp_price AS other_gp_price
, gsp_other.cp_price AS other_cp_price
, gsp_other.price AS other_price
, gsp_other.jan_id AS other_jan_id
, gsp_other.gcl_id AS other_gcl_id
, gsp_other.gcl_display AS other_gcl_display
, gsp_other.gp_id AS other_gp_id
, gsp_other.gp_display AS other_gp_display
, gsp_other.color_id AS other_color_id
, gsp_other.color_display AS other_color_display
, gsp_other.color_name AS other_color_name
, gsp_other.color_rgb AS other_color_rgb
, gsp_other.size_id AS other_size_id
, gsp_other.size_name AS other_size_name
, gsp_other.size_display AS other_size_display
, gsp_other.stock AS other_stock
, gsp_other.jan_stock3 AS other_stock3
, gsp_other.jan_stock3_nyuka_date AS other_stock3_nyuka_date
, gsp_other.`chokusou_flg` AS other_chokusou_flg
, gsp_other.jan_nodisplay AS other_nodisp
, gsp_other.ki_comment AS other_ki_comment
, gsp_other.ki_date AS other_ki_date
, gsp_other.zero_period AS other_zero_period
, gsp_other.zdate AS other_zdate
, gsp_other.nyuuka_yoteibi AS other_nyuuka_yoteibi
, CONCAT(gsp_main.jan_id, '-', gsp_sub.jan_id, '-', gsp_other.jan_id) AS jan_id_list
, COALESCE(ROUND(MIN(cp.cp_price)), gp.gp_price) AS price
, (
( ( `gsp_main`.goods_id IS NULL ) OR `gsp_main`.`chokusou_flg`)
AND ( ( `gsp_sub`.goods_id IS NULL ) OR `gsp_sub`.`chokusou_flg`)
AND ( ( `gsp_other`.goods_id IS NULL ) OR `gsp_other`.`chokusou_flg`)
) AS `chokusou_flg`
, gp.gp_id
, gp.gp_teika
, gp.gp_price
, gp.gp_price2
, gp.gp_genka
, size.size_id
, size.size_name
FROM ({$sqlGspMain}) AS `gsp_main`
INNER JOIN ({$sqlGspSub}) AS `gsp_sub` USING ( gsp_goods_parent )
LEFT JOIN ({$sqlGspOther}) AS `gsp_other` USING ( gsp_goods_parent )
INNER JOIN goods_table AS goods_parent ON goods_parent.goods_id = gsp_main.gsp_goods_parent
INNER JOIN gsp_gp_table AS gsp_sp ON gsp_gp_main = gsp_main.gp_id AND gsp_gp_sub = gsp_sub.gp_id AND IfNull(gsp_gp_other, 0) = IfNull(gsp_other.gp_id, 0)
INNER JOIN goods_price_table AS gp ON gp.gp_id = gsp_gp_parent
LEFT JOIN campaign_price_table AS cp ON gp.gp_id = cp.cp_gp AND cp.cp_end_datetime >= NOW() and cp.cp_start_datetime <= NOW() AND cp.cp_price > 0
LEFT JOIN size_table AS size ON size.size_id = gp.gp_size_id
GROUP BY
goods_parent.goods_id
, gsp_main.jan_id
, gsp_sub.jan_id
, gsp_other.jan_id
ORDER BY
parent_goods_id
, main_goods_id
, main_gcl_display
, main_color_display
, CAST( main_color_name AS binary )
, sub_goods_id
, sub_gcl_display
, sub_color_display
, CAST( sub_color_name AS binary )
, other_goods_id
, other_gcl_display
, other_color_display
, CAST( other_color_name AS binary )";
return $sql;
}
public function getGoodsSetPurchaseList($parent_id)
{
$sqlGsp = $this->sqlGsp($parent_id);
//
$strSql = "SELECT
parent_goods_id
, parent_goods_name
, parent_kataban
, main_goods_id
, main_goods_name
, main_kataban
, main_gsp_name
, main_gsp_show_flg
, main_jan_id
, main_gp_id
, main_gp_display
, main_size_name
, main_gcl_id
, main_gcl_display
, main_color_id
, main_color_name
, main_color_rgb
, main_stock
, main_stock3
, main_stock3_nyuka_date
, main_ki_comment
, main_ki_date
, main_zero_period
, main_zdate
, main_nyuuka_yoteibi
, sub_goods_id
, sub_goods_name
, sub_kataban
, sub_gsp_name
, sub_gsp_show_flg
, sub_jan_id
, sub_gp_id
, sub_gp_display
, sub_size_name
, sub_gcl_id
, sub_gcl_display
, sub_color_id
, sub_color_name
, sub_color_rgb
, sub_stock
, sub_stock3
, sub_stock3_nyuka_date
, sub_ki_comment
, sub_ki_date
, sub_zero_period
, sub_zdate
, sub_nyuuka_yoteibi
, other_goods_id
, other_goods_name
, other_kataban
, other_gsp_type
, other_gsp_name
, other_gsp_show_flg
, other_jan_id
, other_gp_id
, other_gp_display
, other_size_name
, other_gcl_id
, other_gcl_display
, other_color_id
, other_color_name
, other_color_rgb
, other_stock
, other_stock3
, other_stock3_nyuka_date
, other_ki_comment
, other_ki_date
, other_zero_period
, other_zdate
, other_nyuuka_yoteibi
, LEAST(IfNull(main_stock, 99999), IfNull(sub_stock, 99999), IfNull(other_stock, 99999)) AS stock
, LEAST(
CASE main_goods_id
WHEN NULL THEN 99999
ELSE IfNull(main_stock3, 0)
END
, CASE sub_goods_id
WHEN NULL THEN 99999
ELSE IfNull(sub_stock3, 0)
END
, CASE other_goods_id
WHEN NULL THEN 99999
ELSE IfNull(other_stock3, 0)
END
) AS stock3
, COALESCE(main_ki_comment, sub_ki_comment, other_ki_comment) AS comment
, COALESCE(main_zdate, sub_zdate, other_zdate) AS zdate
, COALESCE(main_nyuuka_yoteibi, sub_nyuuka_yoteibi, other_nyuuka_yoteibi) AS nyuuka_yoteibi
-- , GREATEST(IfNull(main_stock, -1), IfNull(sub_stock, -1), IfNull(other_stock, -1)) AS stock
, `chokusou_flg`
, price
, gp_id
, gp_teika
, gp_price
, gp_price2
, gp_genka
, size_id
, size_name
FROM
({$sqlGsp}) AS `gsp`
ORDER BY
other_gsp_type
, main_gcl_display
, sub_gcl_display
, other_gcl_display
, main_gp_display
, sub_gp_display
, other_gp_display";
return (new SqlService())->Sql($strSql)->FetchAll();
}
public function getJanShireColorData($dataId)
{
$sql = "SELECT DISTINCT `jan_goods`,`jan_color`,IFNULL(`jan_shiire_color`,`jan_uniform1_color_number`) AS jan_shiire_color
FROM jancode_table
INNER JOIN goods_table ON jan_goods = goods_id
WHERE jan_goods = :goods_id
AND jan_ddate IS NULL";
return (new SqlService())->Sql($sql)
->Param('goods_id', $dataId)
->FetchAll();
}
public function convertGoodsSetPurchaseListToMap($set_purchase_list)
{
$map = ['main' => [], 'sub' => [], 'other' => []];
$filter = ['color' => [], 'size' => []];
if (isset($set_purchase_list)) {
foreach ($set_purchase_list as $sku) {
foreach (array_keys($map) as $prefix) {
$data = &$map[$prefix];
if (empty($sku["{$prefix}_goods_id"])) continue;
$data['goods_id'] = $sku["{$prefix}_goods_id"];
$data['label'] = $sku["{$prefix}_gsp_name"];
$data['nodisp'] = (int)$sku["{$prefix}_gsp_show_flg"] === 0;
$data['color_list'] = isset($data['color_list']) ? $data['color_list'] : [];
$color_id = $sku["{$prefix}_gcl_id"];
if (!isset($filter['color'][$color_id])) {
$data['color_list'][] = [
'color_id' => $color_id,
'color_name' => $sku["{$prefix}_color_name"],
'color_rgb' => $sku["{$prefix}_color_rgb"]
];
$filter['color'][$color_id] = true;
}
$data['size_list'] = isset($data['size_list']) ? $data['size_list'] : [];
$gp_id = $sku["{$prefix}_gp_id"];
if (!isset($filter['size'][$gp_id])) {
$data['size_list'][] = [
'gp_id' => $gp_id,
'size_name' => $sku["{$prefix}_size_name"]
];
$filter['size'][$gp_id] = true;
}
$data['jan_id_map'][$color_id][$gp_id] = $sku["{$prefix}_jan_id"];
}
}
// 仕入先色番号セット
foreach ($map as $key => &$goods) {
if (empty($goods)) {
unset($map[$key]);
continue;
}
$color_num_map = $this->getShiireColorNumberMap($goods['goods_id']);
foreach ($goods['color_list'] as &$color) {
if (isset($color_num_map[$color['color_id']])) $color['color_num'] = $color_num_map[$color['color_id']];
}
}
}
return $map;
}
public function getShiireColorNumberMap($goods_id)
{
$rows = $this->getJanShireColorData($goods_id);
$map = [];
if (isset($rows)) {
foreach ($rows as $row) {
$map[$row['jan_color']] = is_null($row['jan_shiire_color']) ? '' : $row['jan_shiire_color'];
}
}
return $map;
}
public function getGoodsLinkSelect($data, $is_secret = true, $count = false)
{
if (is_array($data)) {
$whereGoods = "target_goods_id IN (" . implode(",", $data) . ")";
} else {
$whereGoods = "target_goods_id = " . $data;
}
$strSql = "
SELECT DISTINCT gl.goods_id, g.goods_canonical_hinban, gl.goods_name, gl.goods_count, min_gp_price, count_gp_price
FROM goods_link_table AS gl
INNER JOIN goods_table AS g ON g.goods_id = gl.goods_id AND g.goods_ddate IS NULL";
if (!$is_secret) {
$strSql .= " AND g.goods_status = 1 ";
}
$strSql .= "
WHERE {$whereGoods}
ORDER BY goods_count DESC
";
if ($count > 0) {
$strSql .= " LIMIT 0, " . $count;
} else {
$strSql .= " LIMIT 0, 16";
}
return (new SqlService())->Sql($strSql)->FetchAll();
}
// 口コミ平均評価取得
public function getReviewAverageData($goods_id = null)
{
$query = (new SqlService())
->Table('customer_review_average_table')
->Set('cra_target_count', 1, '>=')
->Select('cra_goods_id, cra_average_points, cra_target_count');
if ($goods_id) {
$query->Set('cra_goods_id', $goods_id);
}
return $query->FindAll();
}
public function getReviewAverage($goods_id)
{
$temp = $this->getReviewAverageData($goods_id);
$average = array();
if (isset($temp)) {
foreach ((array)$temp as $data) {
$average[$data['cra_goods_id']] = $data['cra_average_points'];
}
}
return $average;
}
public function getSashikomiImageInfoMap($goods_id)
{
/**
* 差し込みタイプ情報マップ
* 差し込みタイプは差し込みの表示・非表示や、表示するさいのカラム数などを持っている。
* 「作成しない」になっている画像はここではじかれる。
*/
$sashikomi_info_map = [];
$rows = (new SqlService())
->Table('sashikomi_image_table')
->Set('goods_id', $goods_id)
->FindAll();
if (isset($rows)) {
foreach ($rows as $row) {
$sashikomi_info_map[$row['sashikomi_type']] = $row;
}
}
// sashikomi_image_tableにデータが登録されていない差し込みはデフォルト値で生成する。
// 「そのまま表示」「作成しない」のリリース前から一度も保存されていない商品など。
foreach (array_keys(self::$IMAGE_INFO_MAP) as $sashikomi_type) {
if (isset($sashikomi_info_map[$sashikomi_type])) {
// 「作成しない」はここで削除
if ((int)$sashikomi_info_map[$sashikomi_type]['has_image'] === self::DB_STATUS_FALSE) {
unset($sashikomi_info_map[$sashikomi_type]);
}
continue;
}
$column_num = isset(self::$IMAGE_INFO_MAP[$sashikomi_type]['default_column_num'])
? self::$IMAGE_INFO_MAP[$sashikomi_type]['default_column_num']
: 1;
//$sashikomi_info_map[$sashikomi_type] = (Object)[
$sashikomi_info_map[$sashikomi_type] = [
'goods_id' => $goods_id,
'sashikomi_type' => $sashikomi_type,
'has_image' => self::DB_STATUS_TRUE,
'column_num' => $column_num
];
}
$rows = (new SqlService())
->Sql("
SELECT
goods_main_kataban,
shiiresaki_url,
s.*
FROM
goods_table g
JOIN
shiiresaki_table
ON
goods_shiiresaki = shiiresaki_id
JOIN(
SELECT
goods_id,
sashikomi_type,
GROUP_CONCAT(option_key order by option_key SEPARATOR :seperator) AS option_keys,
GROUP_CONCAT( IFNULL(option_value, '') order by option_key SEPARATOR :seperator) AS option_values
FROM
sashikomi_image_option_table
WHERE
goods_id = :goods_id
GROUP BY
goods_id, sashikomi_type
)s
USING(goods_id)")
->Param('goods_id', $goods_id)
->Param('seperator', self::$GROUP_CONCAT_SEPARATOR)
->FetchAll();
if (isset($rows)) {
foreach ($rows as $row) {
unset($sashikomi_info_ref);
foreach (array_keys($sashikomi_info_map) as $sashikomi_type) {
if (preg_match('/^' . preg_quote($sashikomi_type) . '(?:_(.+))?$/', $row['sashikomi_type'], $m)) {
$sashikomi_info_ref = &$sashikomi_info_map[$sashikomi_type];
break;
}
}
if (!isset($sashikomi_info_ref)) continue;
$sashikomi_image = array_combine(
explode(self::$GROUP_CONCAT_SEPARATOR, $row['option_keys']),
explode(self::$GROUP_CONCAT_SEPARATOR, $row['option_values'])
);
// 「非表示」に設定されている画像はとばす
if ((int)$sashikomi_image['status'] === self::DB_STATUS_FALSE) continue;
if (!isset($sashikomi_info_ref['image_list'])) $sashikomi_info_ref['image_list'] = [];
// 画像番号（複数枚画像の場合に$mに入っている）
$image_num = isset($m[1]) ? $m[1] : null;
$sashikomi_image['image_num'] = $image_num;
$sashikomi_image['path'] = ItemSashikomiImageService::getImagePath(
$sashikomi_type,
$row['goods_main_kataban'],
$row['shiiresaki_url'],
$image_num,
$sashikomi_image['ext']
);
$sashikomi_info_ref['image_list'][] = (object)$sashikomi_image;
}
}
$sashikomi = $this->adjustInfoMap($sashikomi_info_map, $goods_id);
if (!empty($sashikomi['color_variation']['image_list'])) {
$goodsDirectDeliveries = $this->getGoodsDirectDeliveries($goods_id);
$sashikomi['color_variation']['image_list'] = array_map(function ($colorVariation) use ($goodsDirectDeliveries) {
$colorVariation->is_direct_delivery = !empty($goodsDirectDeliveries[$colorVariation->image_num]);
return $colorVariation;
}, $sashikomi['color_variation']['image_list']);
}
return $sashikomi;
}
private function getGoodsDirectDeliveries($goodsId)
{
$goods = (new SqlService())
->Sql('
SELECT
goods_id,
goods_main_kataban AS kataban,
goods_shiiresaki AS shiiresaki_id,
goods_set_purchase_flg AS is_set_item
FROM goods_table
WHERE goods_id = :goods_id AND goods_ddate IS NULL
')
->Param('goods_id', $goodsId)
->Fetch();
if (empty($goods)) {
return [];
}
// セット品番
if ($goods['is_set_item'] == 1) {
return $this->getSetDirectDeliveries($goodsId);
}
$directDeliveryProvider = $this->getDirectDeliveryProviderByShiiresakiId($goods['shiiresaki_id']);
// 直送対象メーカーでなければ在庫状況（LM備蓄=stock3）からマップを生成
if (empty($directDeliveryProvider)) {
return $this->getGoodsDirectDeliveriesByInventory($goods);
} else { // メーカー直送設定と在庫状況からマップを生成
return $this->getGoodsDirectDeliveriesByProvider($directDeliveryProvider, $goods);
}
}
private function getSetDirectDeliveries($goodsId)
{
$goodsList = (new SqlService())
->Sql('
SELECT
gsp_goods_child AS goods_id,
CASE WHEN gsp_type = 0 THEN 1 ELSE 0 END AS is_main
FROM goods_set_purchase_table
WHERE gsp_goods_parent = :goods_id
')
->Param('goods_id', $goodsId)
->FetchAll();
$mainId = null;
$subIds = [];
foreach ($goodsList as $goods) {
if ((int)$goods['is_main'] === 1) {
$mainId = (int)$goods['goods_id'];
} else {
$subIds[] = (int)$goods['goods_id'];
}
}
$mainGoodsDirectDeliveries = $this->getGoodsDirectDeliveries($mainId);
$subGoodsHasNotDirectDelivery = false;
foreach ($subIds as $subId) {
$subGoodsDirectDeliveries = $this->getGoodsDirectDeliveries($subId);
// サブ品番の全カラーが在庫0だったら在庫なし
if (!in_array(true, array_values($subGoodsDirectDeliveries))) {
$subGoodsHasNotDirectDelivery = true;
break;
}
}
// サブ品番の在庫がなければ全カラー即日発送不可にする。
if ($subGoodsHasNotDirectDelivery) {
$mainGoodsDirectDeliveries = array_combine(
array_keys($mainGoodsDirectDeliveries),
array_pad([], count($mainGoodsDirectDeliveries), false)
);
}
return $mainGoodsDirectDeliveries;
}
private function getDirectDeliveryProviderByShiiresakiId($shiiresakiId)
{
$directDeliveryProviders = $this->getDirectDeliveryProviders();
return $directDeliveryProviders[$shiiresakiId] ?? null;
}
private function getDirectDeliveryProviders()
{
$directDeliveries = (new SqlService())
->Sql("
SELECT
shiiresaki_id,
IFNULL(shiiresaki_souko_hantei, '') AS chokusou_hinban_alphabet,
IFNULL(dd_hinban_chokusou, '') AS chokusou_hinban,
IFNULL(dd_hinban_exclude, '') AS exclude_hinban,
IFNULL(dd_hinban_bichiku, '') AS bichiku_hinban
FROM shiiresaki_table
JOIN direct_delivery_table ON shiiresaki_id = dd_shiiresaki
WHERE dd_ddate IS NULL
")
->FetchAll();
if (empty($directDeliveries)) {
return [];
}
$directDeliveryProviders = [];
foreach ($directDeliveries as $directDelivery) {
$directDeliveryProviders[(int)$directDelivery['shiiresaki_id']] = [
'shiiresaki_id' => $directDelivery['shiiresaki_id'],
'chokusou_alphabet_list' => $directDelivery['chokusou_hinban_alphabet'] === ''
? [] : explode(',', $directDelivery['chokusou_hinban_alphabet']),
'chokusou_hinban_list' => $directDelivery['chokusou_hinban'] === ''
? [] : explode(',', $directDelivery['chokusou_hinban']),
'exclude_hinban_list' => $directDelivery['exclude_hinban'] === ''
? [] : explode(',', $directDelivery['exclude_hinban']),
'bichiku_hinban_list' => $directDelivery['bichiku_hinban'] === ''
? [] : explode(',', $directDelivery['bichiku_hinban'])
];
}
return $directDeliveryProviders;
}
private function getGoodsDirectDeliveriesByInventory($goods_data)
{
$colorStockList = $this->getColorStockList($goods_data['goods_id']);
/**
* 在庫から判断するのはメーカー直送設定が行われていない仕入先なので、
* stock3以外は見る必要がない。
*/
$goodsDirectDeliveries = [];
foreach ($colorStockList as $colorStock) {
$goodsDirectDeliveries[$colorStock['color_num']] = $colorStock['has_lm_stock'];
}
return $goodsDirectDeliveries;
}
private function getGoodsDirectDeliveriesByProvider($directDeliveryProvider, $goods)
{
/**
* 直送対象であっても、在庫があることが前提となる。
*/
$colorStockList = $this->getColorStockList($goods['goods_id']);
$makerAlphabetList = $directDeliveryProvider['chokusou_alphabet_list'];
$chokusouHinbanList = $directDeliveryProvider['chokusou_hinban_list'];
$excludeHinbanList = $directDeliveryProvider['exclude_hinban_list'];
$bichikuHinbanList = $directDeliveryProvider['bichiku_hinban_list'];
/**
* メーカー直送品番（該当アルファベット）、メーカー直送品番（対象商品をLM品番で入力）、LM備蓄在庫（アシスト物流で在庫している）、除外品番が未設定であれば、
* 全カラー、サイズが直送対象。
*/
$allItemCanDelivery =
empty($makerAlphabetList) &&
empty($chokusouHinbanList) &&
empty($excludeHinbanList) &&
empty($bichikuHinbanList);
$goodsDirectDeliveries = [];
foreach ($colorStockList as $colorStock) {
// LM備蓄在庫があればどんな条件であっても（excludeに設定されていても）直送可能
if ($colorStock['has_lm_stock']) {
$goodsDirectDeliveries[$colorStock['color_num']] = true;
continue;
}
// デフォルトで通常在庫が直送在庫になる（LM備蓄がある場合は既に上の処理で直送可能判定済み）。
$hasStock = $colorStock['has_stock'];
// 直送可能在庫数の有無判定（メーカーごとに異なる）
switch ($directDeliveryProvider['shiiresaki_id']) {
// チトセ
case 84:
$hasStock = $hasStock || $colorStock['has_chitose_lm_bichiku_stock'] || $colorStock['has_chitose_gaibu_bichiku_stock'];
// 商品ID 8172（特殊）
if ((int)$goods['goods_id'] === 8172) {
$hasStock = $hasStock || $colorStock['has_chitose_lm_stock'] || $colorStock['has_chitose_kaitori_stock'];
}
break;
default:
break;
}
// 除外品番になっている、もしくは在庫がない場合は直送不可
if (in_array($goods['kataban'], $excludeHinbanList) || !$hasStock) {
$goodsDirectDeliveries[$colorStock['color_num']] = false;
continue;
}
if (
// 全て直送可能
$allItemCanDelivery ||
// メーカー直送品番（該当アルファベット）が指定されていてそれに含まれている
(
!empty($makerAlphabetList) &&
in_array($colorStock['hinban_alpabet'], $makerAlphabetList)
) ||
// メーカー直送品番（対象商品をLM品番で入力）が指定されていてそれに含まれている
(
!empty($chokusouHinbanList) &&
in_array($goods['kataban'], $chokusouHinbanList)
) ||
// LM備蓄在庫（アシスト物流で在庫している）している品番が指定されていてそれに含まれている
(
!empty($bichikuHinbanList) &&
in_array($goods['kataban'], $bichikuHinbanList) &&
$colorStock['has_lm_stock']
)
) {
$goodsDirectDeliveries[$colorStock['color_num']] = true;
continue;
}
$goodsDirectDeliveries[$colorStock['color_num']] = false;
}
return $goodsDirectDeliveries;
}
private function getColorStockList($goodsId)
{
/**
* 真偽値をMAXすることで在庫あり＝1をひっぱる
* 仕入先色番号がNULLのレコードがあるので（特にセットのサブ品番）、その場合はCOLOR_ID_をprefixにしたキーを使う。
*/
$colorStockList = (new SqlService())
->Sql("
SELECT
IFNULL(jan_shiire_color, CONCAT('COLOR_ID_', jan_color)) AS color_num,
jan_shiire_no AS shiire_no,
MAX( IFNULL(jan_stock, 0) > 1 ) AS has_stock,
MAX( IFNULL(jan_stock6, 0) > 1 ) AS has_chitose_lm_stock,
MAX( IFNULL(jan_stock7, 0) > 1 ) AS has_chitose_kaitori_stock,
MAX( IFNULL(jan_stock8, 0) > 1 ) AS has_chitose_lm_bichiku_stock,
MAX( IFNULL(jan_stock9, 0) > 1 ) AS has_chitose_gaibu_bichiku_stock,
MAX(
IFNULL(jan_stock3, 0) > 0 AND
IFNULL(jan_stock3_nyuka_date, '0000-00-00') < NOW()
) AS has_lm_stock
FROM (
SELECT jancode_table.*, keppin_id, zero_stock_id
FROM jancode_table
LEFT JOIN(
SELECT
ki_id AS keppin_id,
ki_goods AS goods_id,
ki_gcl AS gcl_id,
ki_gp AS gp_id
FROM keppin_item_table
WHERE ki_date IS NULL OR ki_date > NOW() AND ki_goods = :goods_id
) t1 ON jan_goods = t1.goods_id AND jan_color = t1.gcl_id AND jan_price = t1.gp_id
LEFT JOIN(
SELECT zero_id AS zero_stock_id, zero_goods AS goods_id, zero_gcl AS gcl_id, zero_gp AS gp_id
FROM zero_stock_table
WHERE zero_period > NOW() AND zero_goods = :goods_id
) t2 ON jan_goods = t2.goods_id AND jan_color = t2.gcl_id AND jan_price = t2.gp_id
WHERE jan_goods = :goods_id
) t
WHERE jan_ddate IS NULL AND jan_nodisplay IS NULL
GROUP BY jan_shiire_color, jan_shiire_no
")
->Param('goods_id', $goodsId)
->FetchAll();
return array_map(function ($colorStock) {
$colorStock['hinban_alpabet'] = preg_replace('/[^a-z]/i', '', $colorStock['shiire_no']);
$colorStock['has_stock'] = (int)$colorStock['has_stock'] === 1;
$colorStock['has_lm_stock'] = (int)$colorStock['has_lm_stock'] === 1;
$colorStock['has_chitose_lm_stock'] = (int)$colorStock['has_chitose_lm_stock'] === 1;
$colorStock['has_chitose_kaitori_stock'] = (int)$colorStock['has_chitose_kaitori_stock'] === 1;
$colorStock['has_chitose_lm_bichiku_stock'] = (int)$colorStock['has_chitose_lm_bichiku_stock'] === 1;
$colorStock['has_chitose_gaibu_bichiku_stock'] = (int)$colorStock['has_chitose_gaibu_bichiku_stock'] === 1;
return $colorStock;
}, $colorStockList);
}
private function adjustInfoMap($map, $goods_id)
{
$color_number_map = $this->getColorNumberMap($goods_id);
foreach ($map as $sashikomi_type => $data) {
if (empty($data['image_list'])) continue;
switch ($sashikomi_type) {
// 画像番号順にソート
case self::TYPE_FABRIC:
case self::TYPE_DETAIL_COLUMN:
$seek = array_map(function ($image) {
return $image->image_num;
}, $data['image_list']);
array_multisort(
$seek,
SORT_ASC,
SORT_STRING,
$map[$sashikomi_type]['image_list']
);
break;
// 色番号順にソートし、画像情報に色名を追加する
case self::TYPE_COLOR_VARIATION:
$order_map = $seek = [];
foreach (array_keys($color_number_map) as $idx => $color_num) {
$order_map[$color_num] = $idx;
}
foreach ($map[$sashikomi_type]['image_list'] as $idx => &$image) {
// 色名がない色は表示しない
if (!isset($color_number_map[$image->image_num])) {
unset($map[$sashikomi_type]['image_list'][$idx]);
continue;
}
$image->color_name = $color_number_map[$image->image_num];
$seek[] = isset($order_map[$image->image_num]) ? $order_map[$image->image_num] : 999; // 登録されていない色番号は最後に表示するよう999をセット
}
array_multisort(
$seek,
SORT_ASC,
SORT_NUMERIC,
$map[$sashikomi_type]['image_list']
);
break;
default:
break;
}
}
return $map;
}
private function getColorNumberMap($goods_id)
{
/**
* セット品番の場合はメインidに差し替える。
*/
$rows = (new SqlService())
->Table('goods_set_purchase_table')
->Set('gsp_goods_parent', $goods_id)
->Set('gsp_type', 0)
->FindAll();
$row = isset($rows) ? array_shift($rows) : null;
if (!is_null($row)) $goods_id = $row['gsp_goods_child'];
$map = [];
$query = '
SELECT
t3.*,
color_name,
gcl_display
FROM(
SELECT DISTINCT
*
FROM(
SELECT
goods_id
FROM
goods_table
WHERE
goods_ddate IS NULL AND
goods_id = :goods_id
)t1
JOIN(
SELECT
jan_goods AS goods_id,
jan_shiire_color,
jan_color
FROM
jancode_table
WHERE
jan_shiire_color IS NOT NULL
)t2
USING(goods_id)
)t3
JOIN
goods_color_table
ON
jan_color = gcl_id AND
gcl_display_status = 1
JOIN color_table ON color_id = gcl_color_id
ORDER BY
goods_id, gcl_display
';
$rows = (new SqlService())->Sql($query)
->Param('goods_id', $goods_id)
->FetchAll();
if (isset($rows)) {
foreach ($rows as $row) {
$map[$row['jan_shiire_color']] = $row['color_name'];
}
}
return $map;
}
public function getListByGoodsId($siteType, $goodsId)
{
$sql = "SELECT
nt.nt_id
, nt.nt_title
, nt.nt_url
FROM
new_tag_table AS nt, goods_new_tag_table AS gnt, goods_table AS goods
WHERE
goods_id = {$goodsId}
AND
nt_site_type = {$siteType}
AND
gnt_site_type = nt_site_type
AND
gnt_goods = goods_id
AND
gnt_new_tag = nt_id
ORDER BY
gnt_display
, nt_tag_number
, nt_display
, nt_title";
return (new SqlService())->Sql($sql)->FetchAll();
}
public function getLargeFooterListByGoodsId($siteType, $goodsId)
{
$SQL = new SqlService();
$result = $SQL->Select('T.footer_id, T.footer_title, T.footer_content_html')
->Table('footer_table')
->Join('goods_footer_table', 'T.footer_id = T1.gf_footer', 'INNER')
->Join('goods_table', 'T2.goods_id = T1.gf_goods', 'INNER')
->Set('footer_type', 2)
->Set('footer_site_type', $siteType)
->Set('T2.goods_id', $goodsId)
->OrderBy('T1.gf_display, T.footer_number, T.footer_display, T.footer_title')
->FindAll();
return $result;
}
public function LM_displaySizeMatrix_cross2($goodsId)
{
$viewStr = array();
$viewData = [];
// サイズを検索
$sql = "SELECT * FROM size_detail_table ORDER BY size_detail_display";
$sizeDetailList = (new SqlService())->Sql($sql)->FetchAll();
$gp_detail_list = array();
foreach ($sizeDetailList as $sizeDetail) {
$gp_detail_list[] = "MAX(CASE sd.size_detail_id WHEN {$sizeDetail['size_detail_id']} THEN gsd.gsd_value END) AS gp_detail{$sizeDetail['size_detail_id']}";
}
$gp_detail_select = implode(', ', $gp_detail_list);
$sql = "SELECT gp_id, gp_goods, gp_kataban, gp_size, gp_price, gp_comment, gp_display, gp_genka, gp_size_id, gp_teika, gp_price2,goods_set_purchase_flg, size.*, gsd.*
FROM goods_price_table AS gp
INNER JOIN goods_table AS goods ON goods_id = gp_goods
LEFT JOIN size_table AS size ON size_id = gp_size_id
LEFT JOIN (
SELECT gsd_goods_price AS gp_id, {$gp_detail_select}
FROM goods_size_detail_table AS gsd
INNER JOIN size_detail_table AS sd ON size_detail_id = gsd_size_detail
WHERE gsd_goods = {$goodsId}
GROUP BY gsd_goods_price
) AS gsd USING ( gp_id )
WHERE gp_goods = :goodsId AND IfNull(gp_display, 0) != 99
ORDER BY ISNULL( gp_display ), gp_display, gp_kataban, gp_price";
$rs = (new SqlService())->Sql($sql)
->Param('goodsId', $goodsId)
->FetchAll();
if (!empty($rs)) {
$result = array();
foreach ($rs as $rowNum => $row) {
$buf = array();
foreach ($row as $colName => $col) {
if (preg_match('/^gp_detail/', $colName)) {
$buf['gp_detail_list'][$colName] = $col;
} else {
$buf[$colName] = $col;
}
}
$result[] = $buf;
}
} else {
$result = $rs;
}
$goodsSizeDetaiList = $result;
$gp_size = array();
$gp_detail = array();
$gp_comment = array();
$gp_size_buf = "";
$tmp_row = count($goodsSizeDetaiList);
foreach ($goodsSizeDetaiList as $i => $goodsSizeDetail) {
$gp_size_list[$i] = $goodsSizeDetail['size_name'];
$gp_comment_list[$i] = $goodsSizeDetail['gp_comment'];
$gp_detail_list[$i] = $goodsSizeDetail["gp_detail_list"];
$purchase_flg = $goodsSizeDetail["goods_set_purchase_flg"];
}
// 見出しの書き込み
$gp_size_count_list = array();
foreach ($gp_size_list as $i => $gp_size) {
if ($gp_size_buf != $gp_size) {
$gp_size_buf = $gp_size;
$gp_size_count_list[$gp_size] = 1;
} else {
$gp_size_count_list[$gp_size]++;
}
}
foreach ($gp_size_count_list as $gp_size => $colspan) {
$viewStr['size'][] = $gp_size;
$viewStr['colspan'][] = $colspan;
}
$loop = 0;
foreach ($sizeDetailList as $i => $sizeDetail) {
$chk = false;
foreach ($gp_detail_list as $j => $gp_detail) {
if (!empty($gp_detail["gp_detail{$sizeDetail['size_detail_id']}"])) {
$chk = true;
break;
}
}
if ($chk) {
$tablet_ids = array(
22, // タブレットが入るポケットの深さ
23, // タブレットが入るポケット口
24, // タブレットが入るポケット口実寸
26 // タブレットが入るポケット
);
if (//isSizeHelpExists($sizeDetail['size_detail_id']) &&
!in_array((int)$sizeDetail['size_detail_id'], $tablet_ids) // タブレット系のサイズはリンクを表示しない
) {
$viewData[$loop]['link'] = $sizeDetail['size_detail_id'];
} else {
$viewData[$loop]['link'] = "";
}
$viewData[$loop]['position'] = $sizeDetail['size_detail_name'];
$count_td = array();
$last_value = null;
$gp_detail = array();
foreach ($gp_detail_list as $j => $gp_detail) {
//$td_value = "";
if (!empty($gp_detail["gp_detail{$sizeDetail['size_detail_id']}"])) {
$td_value = $gp_detail["gp_detail{$sizeDetail['size_detail_id']}"];
} else {
continue;
}
if ($last_value != $td_value) {
array_push($count_td, array('count' => 1, 'val' => $td_value));
} else {
$count_td[count($count_td) - 1]['count'] += 1;
}
$last_value = $td_value;
}
// 3つ以上同じサイズが続く場合、セルを結合,2つまでは独自セル
// ただし、全てが同じサイズであれば２つ以下でも結合
// 同じ値が２つの場合でも、文字列の長さが４文字以上（ないし５文字以上）の場合には結合
$uniqueCont = count($count_td);
$loop2 = 0;
foreach ($count_td as $count_val) {
$viewData[$loop]['count'][$loop2] = $count_val['count'];
$viewData[$loop]['val'][$loop2] = $count_val['val'];
/*
if (empty($data['goods_set_purchase_flg']) && $count_val['count'] == 2 && mb_strlen($count_val['val']) >= 4) {
$viewData[$loop]['count'][$loop2] = $count_val['count'];
$viewData[$loop]['val'][$loop2] = $count_val['val'];
}elseif((empty($purchase_flg) && $count_val['count'] <= 2 && $uniqueCont != 1) || $count_val['val'] == '') {
//セル数が2以下の場合且つ、サイズ種類が２つ以上ある場合独立セル
for($j=1; $j<=$count_val['count']; $j++) {
$viewData[$loop]['count'][$loop2] = "";
$viewData[$loop]['val'][$loop2] = $count_val['val'];
}
} else {
//結合セル
$viewData[$loop]['count'][$loop2] = $count_val['count'];
$viewData[$loop]['val'][$loop2] = $count_val['val'];
}
*/
$loop2++;
}
}
$loop++;
}
$chk = false;
for ($j = 0; $j < $tmp_row; $j++) {
if ($gp_comment_list[$j] != "") {
$chk = true;
break;
}
}
if ($chk) {
for ($j = 0; $j < $tmp_row; $j++) {
$viewStr['comment'][] = $gp_comment_list[$j];
}
}
return [$viewStr, $viewData];
}
public function getGoodsSeriesListByGoodsId($goodsId, $formatted = true)
{
$query = "SELECT
gs_id
, gs_column_number
, gs_title
, gs_sub_title
, goods_id
, goods_canonical_hinban
, COALESCE(goods_canonical_hinban, goods_id) AS item_id
, goods_name
, goods_new_name
, goods_caption
, goods_series_2column_caption
, MIN(gp_kataban) AS kataban
, COALESCE(color.color_id, color2.color_id) AS color_id
, COALESCE(color.color_name, color2.color_name) AS color_name
, COALESCE(color.color_rgb, color2.color_rgb) AS color_rgb
FROM goods_series_table AS gs
INNER JOIN goods_series_goods_table AS gsg ON gs_id = gsg_gs
INNER JOIN goods_table AS goods ON goods_id = gsg_goods
LEFT JOIN goods_price_table AS gp ON goods_id = gp_goods
LEFT JOIN goods_set_purchase_table ON goods_id = gsp_goods_parent AND gsp_type = 0
LEFT JOIN goods_color_table AS gcl ON goods_id = gcl.gcl_goods AND gcl.gcl_display_status = 1
LEFT JOIN color_table AS color ON color.color_id = gcl.gcl_color_id
-- gcl2, color2 --> セット品番対応。メイン品番のカラーを使用する。
LEFT JOIN goods_color_table AS gcl2 ON gsp_goods_child = gcl2.gcl_goods AND gcl2.gcl_display_status = 1
LEFT JOIN color_table AS color2 ON color2.color_id = gcl2.gcl_color_id
WHERE gs_goods = :gs_goods AND goods_status = 1 AND goods_ddate IS NULL AND gs_del_flg = 0
GROUP BY
gs_id
, gsg_id
, goods_id
, COALESCE(color.color_id, color2.color_id)
ORDER BY
gs_column_number
, gsg_id
, COALESCE(gcl.gcl_display, gcl2.gcl_display, 999)
, COALESCE(color.color_display, color2.color_display, 999)
, COALESCE(color.color_name, color2.color_name)";
$tmp = (new SqlService())->Sql($query)
->Param('gs_goods', $goodsId)
->FetchAll();
if ($formatted) {
// データ構造を階層型に整形
$result = array();
foreach ((array)$tmp as $row) {
//
$gsId = $row['gs_id'];
$columnNumber = $row['gs_column_number'];
$goodsId = $row['goods_id'];
$colorId = $row['color_id'];
//
$result[$columnNumber][$gsId]['gs_column_number'] = $row['gs_column_number'];
$result[$columnNumber][$gsId]['gs_title'] = $row['gs_title'];
$result[$columnNumber][$gsId]['gs_sub_title'] = $row['gs_sub_title'];
//
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['goods_id'] = $row['goods_id'];
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['goods_canonical_hinban'] = $row['goods_canonical_hinban'];
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['item_id'] = $row['item_id'];
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['goods_name'] = $row['goods_name'];
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['goods_new_name'] = $row['goods_new_name'];
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['goods_caption'] = $row['goods_caption'];
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['goods_series_2column_caption'] = $row['goods_series_2column_caption'];
$dir = substr($row['goods_id'], -1);
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['goods_image'] = "/goods.img/{$dir}/{$row['goods_id']}/main.jpg";
//
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['kataban'] = $row['kataban'];
//
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['color_list'][$colorId]['color_id'] = $row['color_id'];
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['color_list'][$colorId]['color_name'] = $row['color_name'];
$result[$columnNumber][$gsId]['goods_list'][$goodsId]['color_list'][$colorId]['color_rgb'] = $row['color_rgb'];
}
// 階層のために連想配列で組立てたデータを配列に変換
foreach ($result as &$gs2) {
foreach ($gs2 as &$gs) {
foreach ($gs['goods_list'] as &$goods) {
foreach ($goods['color_list'] as &$color) {
// no operation.
}
$goods['color_list'] = array_values($goods['color_list']);
}
$gs['goods_list'] = array_values($gs['goods_list']);
}
}
// 一番外側だけは連想配列（シリーズのカラム数をキーとする）のままとする
// // $result = array_values($result);
} else {
$result = $tmp;
}
return $result;
}
public function detectMainCategory($goodsId)
{
$campaignCategoryList = self::CAMPAIGN_CATEGORY_LIST;
$campaignCategoryListIn = implode(",", array_map(function ($item) {
return "'{$item}'";
}, $campaignCategoryList));
$query = "SELECT
`mc`.*
, main_category_webname IN (
{$campaignCategoryListIn}
) AS `is_campaign_category`
FROM (
SELECT
main_category_id
, main_category_webname
, COUNT(*) AS `CNT`
FROM
category_group_table
INNER JOIN
main_category_table
ON
category_group_id = main_category_group
INNER JOIN
category_table
ON
main_category_id = category_main_category
INNER JOIN
goods_category_table
ON
category_id = gc_category
AND
category_group_only_main = 0
INNER JOIN
goods_table
ON
goods_id = gc_goods
WHERE
goods_id = :goods_id
GROUP BY
main_category_webname
UNION ALL
SELECT
main_category_id
, main_category_webname
, COUNT(*) AS `CNT`
FROM
category_group_table
INNER JOIN
main_category_table
ON
category_group_id = main_category_group
INNER JOIN
goods_main_category_table
ON
main_category_id = gmc_main_category
AND
category_group_only_main = 1
INNER JOIN
goods_table
ON
goods_id = gmc_goods
WHERE
goods_id = :goods_id
GROUP BY
main_category_webname
) AS `mc`
ORDER BY
`is_campaign_category` DESC
, main_category_id ASC
, `CNT` DESC
LIMIT 1";
$rs = (new SqlService())->Sql($query)
->Param('goods_id', $goodsId)
->FetchAll();
if (!empty($rs)) {
$mct = $rs[0]['main_category_webname'];
} else {
$mct = null;
}
return $mct;
}
public function getDisplayCategorySql4($goodsId, $mct, $is_except_supersale = false)
{
$addWhereStr = '';
if ($is_except_supersale) {
$addWhereStr = " AND main_category_webname != 'super-sale'";
}
$query = "SELECT DISTINCT main_main_id, main_main_name, main_main_web_name, main_id, main_name, main_web_name, main_has_main_categories, cate_id, cate_name, cate_web_name, priority, cate_display
FROM (
-- 1
SELECT
main_category_main_category AS main_main_id,
main_category_main_category_name AS main_main_name,
main_category_main_category_webname AS main_main_web_name,
main_category_id AS main_id,
main_category_name AS main_name,
main_category_webname AS main_web_name,
main_category_has_main_categories AS main_has_main_categories,
NULL AS cate_id,
NULL AS cate_name,
NULL AS cate_web_name,
0 AS priority,
NULL AS cate_display
FROM
main_category_table
INNER JOIN
goods_main_category_table
ON
main_category_id = gmc_main_category
INNER JOIN
category_group_table
ON
category_group_id = main_category_group
WHERE
main_category_status = 1
AND
gmc_goods = :goodsId
AND
NOT EXISTS (
SELECT *
FROM category_table
INNER JOIN goods_category_table ON category_id = gc_category
INNER JOIN goods_table ON goods_id = gc_goods
WHERE goods_id = :goodsId AND category_main_category = main_category_id
)
{$addWhereStr}
-- LIMIT 0, 2
UNION ALL
-- 2
SELECT
main_category_main_category,
main_category_main_category_name,
main_category_main_category_webname,
main_category_id,
main_category_name,
main_category_webname,
main_category_has_main_categories,
NULL,
NULL,
NULL,
4 AS priority,
NULL
FROM
main_category_table
INNER JOIN
goods_main_category_table
ON
main_category_id = gmc_main_category
INNER JOIN
category_group_table
ON
category_group_id = main_category_group
WHERE
main_category_status = 1
AND
gmc_goods = :goodsId
AND
NOT EXISTS (
SELECT *
FROM category_table
INNER JOIN goods_category_table ON category_id = gc_category
INNER JOIN goods_table ON goods_id = gc_goods
WHERE goods_id = :goodsId AND category_main_category = main_category_id
)
{$addWhereStr}
-- LIMIT 2, 6
-- 3
UNION ALL
SELECT
main_category_main_category,
main_category_main_category_name,
main_category_main_category_webname,
main_category_id,
main_category_name,
main_category_webname,
main_category_has_main_categories,
category_id,
category_name,
category_webname,
1 AS priority,
category_display
FROM
main_category_table
INNER JOIN
category_table
ON
main_category_id = category_main_category
INNER JOIN
goods_category_table
ON
category_id = gc_category
INNER JOIN
category_group_table
ON
category_group_id = main_category_group
WHERE
main_category_status = 1
AND
main_category_webname = :mct
AND
category_status = 1
AND
gc_goods = :goodsId
AND
category_group_only_main = 0
{$addWhereStr}
-- LIMIT 0, 4
-- 4
UNION ALL
SELECT
main_category_main_category,
main_category_main_category_name,
main_category_main_category_webname,
main_category_id,
main_category_name,
main_category_webname,
main_category_has_main_categories,
category_id,
category_name,
category_webname,
2 AS priority,
category_display
FROM
main_category_table
INNER JOIN
category_table
ON
main_category_id = category_main_category
INNER JOIN
goods_category_table
ON
category_id = gc_category
INNER JOIN
category_group_table
ON
category_group_id = main_category_group
WHERE
main_category_status = 1
AND
main_category_webname <> :mct
AND
category_status = 1
AND
gc_goods = :goodsId
AND
category_group_only_main = 0
{$addWhereStr}
-- LIMIT 0, 6
-- 5
UNION ALL
SELECT
main_category_main_category,
main_category_main_category_name,
main_category_main_category_webname,
main_category_id,
main_category_name,
main_category_webname,
main_category_has_main_categories,
category_id,
category_name,
category_webname,
3 AS priority,
category_display
FROM
main_category_table
INNER JOIN
category_table
ON
main_category_id = category_main_category
INNER JOIN
goods_category_table
ON
category_id = gc_category
INNER JOIN
category_group_table
ON
category_group_id = main_category_group
WHERE
main_category_status = 1
AND
main_category_webname = :mct
AND
category_status = 1
AND
gc_goods = :goodsId
AND
category_group_only_main = 0
{$addWhereStr}
-- LIMIT 4, 2
-- dummy
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM main_category_table WHERE 1 != 1
) AS CATE
ORDER BY priority, ISNULL( cate_display ), cate_display, cate_id LIMIT 0, 6";
$rows = (new SqlService())->Sql($query)
->Param('goodsId', $goodsId)
->Param('mct', $mct)
->FetchAll();
return $rows;
}
public function getMainCategoryWebnameByMcId($mcId)
{
$query = "SELECT
main_category_webname
FROM
main_category_table
WHERE
main_category_id = :mc_id";
$rows = (new SqlService())->Sql($query)
->Param('mc_id', $mcId)
->FetchAll();
$row = array_shift($rows);
$result = $row['main_category_webname'];
return $result;
}
public function getGoodsCategories($goodsId)
{
$goodsCategories = (new SqlService())
->Sql('
SELECT
main_category_main_category AS main_main_id,
main_category_main_category_name AS main_main_name,
main_category_main_category_webname AS main_main_web_name,
main_category_id AS main_id,
main_category_name AS main_name,
main_category_webname AS main_web_name,
main_category_has_main_categories AS main_has_main_categories,
category_id AS cate_id,
category_name AS cate_name,
category_webname AS cate_web_name,
category_display AS cate_display
FROM goods_category_table
INNER JOIN category_table ON gc_category = category_id
INNER JOIN main_category_table ON category_main_category = main_category_id
INNER JOIN category_group_table ON category_group_id = main_category_group
WHERE category_table.category_status = 1 AND category_group_only_main = 0 AND gc_goods = :goods_id
UNION ALL
SELECT
main_category_main_category AS main_main_id,
main_category_main_category_name AS main_main_name,
main_category_main_category_webname AS main_main_web_name,
main_category_id AS main_id,
main_category_name AS main_name,
main_category_webname AS main_web_name,
main_category_has_main_categories AS main_has_main_categories,
NULL AS cate_id,
NULL AS cate_name,
NULL AS cate_web_name,
NULL AS cate_display
FROM main_category_table
INNER JOIN goods_main_category_table ON main_category_id = gmc_main_category
WHERE gmc_goods = :goods_id
')
->Param('goods_id', $goodsId)
->FetchAll();
if (empty($goodsCategories)) {
return [];
}
return $goodsCategories;
}
public function LM_displayCategory($id, $isExceptSupersale = false)
{
$goodsCategories = $this->getGoodsCategories($id);
$viewStr = '';
$viewStrReview = '';
$viewStrReviewList = array();
foreach ($goodsCategories as $goodsCategoryIndex => $goodsCategory) {
// 配下カテゴリ
$mcName = $goodsCategory['main_web_name'];
$mcMcName = $goodsCategory['main_main_web_name'];
$canonicalMcName = $mcName;
if (!empty($mcMcName)) {
$canonicalMcName = "{$mcMcName}/{$mcName}";
}
/**
* 800-491-7……アユダンテ提案……サーバーサイド側……フロントエンド側を一通り対応した結果、システム開発が必要となった4項目の対応
* ●項番２：”スーパーセール”カテゴリは除外する
*/
if ($isExceptSupersale && ($goodsCategory['main_main_web_name'] == 'super-sale')) {
continue;
}
if ($goodsCategoryIndex <= 5) {
$viewStr .= "";
if (!empty($goodsCategory['main_main_web_name']) && !empty($goodsCategory['main_main_name'])) {
$viewStr .= "<a href=\"/" . $goodsCategory['main_main_web_name'] . "/\">" . $goodsCategory['main_main_name'] . "</a> > ";
}
$viewStr .= "<a href=\"/" . $canonicalMcName . "/\">" . $goodsCategory['main_name'] . "</a>";
// 掲載カテゴリーURLが二重スラッシュ「//」となってしまうバグ修正
if (!empty($goodsCategory['cate_web_name']) && !empty($goodsCategory['cate_name'])) {
$viewStr .= " > <a href=\"/" . $canonicalMcName . "/" . $goodsCategory['cate_web_name'] . "/\">" . $goodsCategory['cate_name'] . "</a>";
}
$viewStr .= "\n";
$viewStrReview .= "<a href=\"/" . $canonicalMcName . "/\">" . $goodsCategory['main_name'] . "</a> >> <a href=\"/" . $canonicalMcName . "/" . $goodsCategory['cate_web_name'] . "/\">" . $goodsCategory['cate_name'] . "</a>\n";
$viewStrReviewList[] = "<a href=\"/" . $canonicalMcName . "/\">" . $goodsCategory['main_name'] . "</a>";
$viewStrReviewList[] = "<a href=\"/" . $canonicalMcName . "/" . $goodsCategory['cate_web_name'] . "/\">" . $goodsCategory['cate_name'] . "</a>";
}
$mainCategoryList[$goodsCategory['main_id']] = $goodsCategory;
$mainCategoryIdList[] = $goodsCategory['main_id'];
}
$categories = array_count_values($mainCategoryIdList);
$categoriesWithMostProducts = array_keys($categories, max($categories));
$canonicalPankuzuMainCategory = [$mainCategoryList[$categoriesWithMostProducts[0]]];
return array(
"viewStr" => $viewStr,
"viewStrReview" => $viewStrReview,
"viewStrReviewList" => $viewStrReviewList,
"canonicalPankuzuMainCategory" => $canonicalPankuzuMainCategory
);
}
public function LM_displayCategory4($goodsId, $mct = null, $additionalList = array(), $is_except_supersale = false)
{
return $this->getCached(function () use ($goodsId, $mct, $additionalList, $is_except_supersale) {
$viewStr = "";
$viewStrReview = "";
// パン屑用カテゴリの指定が無い場合、当該商品の掲載数の最も多い親カテを採用する。
if (empty($mct)) {
$mct = $this->detectMainCategory($goodsId);
}
$rs = self::getDisplayCategorySql4($goodsId, $mct, $is_except_supersale);
// 800-172-2……UTの商品ページの「当商品の掲載カテゴリー」……「作業服」のKW対策
if (!empty($additionalList)) {
$rs = array_merge($rs, $additionalList);
}
foreach ((array)$rs as $row) {
// 配下カテゴリ
$mcName = $row['main_web_name'];
$mcMcName = $row['main_main_web_name'];
$canonicalMcName = $mcName;
if (!empty($mcMcName)) {
$canonicalMcName = "{$mcMcName}/{$mcName}";
}
//
$viewStr .= "";
if (!empty($row['main_main_web_name']) && !empty($row['main_main_name'])) {
$viewStr .= "<a href=\"/" . $row['main_main_web_name'] . "/\">" . $row['main_main_name'] . "</a> > ";
//$viewStr[$loop][] = array($row['main_main_web_name'],$row['main_main_name']);
}
if (!empty($row['main_web_name']) && !empty($row['main_name'])) {
$viewStr .= "<a href=\"/" . $canonicalMcName . "/\">" . $row['main_name'] . "</a>";
//$viewStr[$loop][] = array($canonicalMcName,$row['main_name']);
}
if (!empty($row['cate_web_name']) && !empty($row['cate_name'])) {
$viewStr .= " > <a href=\"/" . $canonicalMcName . "/" . $row['cate_web_name'] . "/\">" . $row['cate_name'] . "</a>";
//$viewStr[$loop][] = array($canonicalMcName. "/" . $row['cate_web_name'],$row['cate_name']);
}
$viewStr .= "";
//$viewStrReview[$loop][] = array($canonicalMcName,$row['main_name'],$canonicalMcName . "/" . $row['cate_web_name'],$row['cate_name']);
$viewStrReview .= "<a href=\"/" . $canonicalMcName . "/\">" . $row['main_name'] . "</a> >> <a href=\"/" . $canonicalMcName . "/" . $row['cate_web_name'] . "/\">" . $row['cate_name'] . "</a>\n";
}
$query = "SELECT main_category_main_category AS main_main_id, main_category_main_category_name AS main_main_name, main_category_main_category_webname AS main_main_web_name, main_category_id AS main_id, main_category_name AS main_name, main_category_webname AS main_web_name, main_category_has_main_categories AS main_has_main_categories
, main_leftmenuup1
, main_leftmenuup2
, main_leftmenuup3
, main_leftmenudown1
, main_leftmenudown2
, main_leftmenudown3
, main_leftmenudown4
, main_leftmenudown5
, main_leftmenudown6
, main_leftmenudown7
FROM main_category_table
WHERE main_category_webname = :mc_id";
$rs = (new SqlService())->Sql($query)
->Param('mc_id', $mct)
->FetchAll();
$canonicalPankuzuMainCategory = $rs;
return array("viewStr" => $viewStr, "viewStrReview" => $viewStrReview, "canonicalPankuzuMainCategory" => $canonicalPankuzuMainCategory);
});
}
public function getMainCategorySqlFromGoodsId2($goodsId)
{
$query = "SELECT
main_category_main_category AS main_main_id
, main_category_main_category_name AS main_main_name
, main_category_main_category_webname AS main_main_web_name
, main_category_id AS main_id
, main_category_name AS main_name
, main_category_webname AS main_web_name
, main_category_has_main_categories AS main_has_main_categories
, category_id AS cate_id
, category_name AS cate_name
, category_webname AS cate_web_name
, Null AS priority
, Null AS cate_display
, main_leftmenuup1
, main_leftmenuup2
, main_leftmenuup3
, main_leftmenudown1
, main_leftmenudown2
, main_leftmenudown3
, main_leftmenudown4
, main_leftmenudown5
, main_leftmenudown6
, main_leftmenudown7
FROM
main_category_table
INNER JOIN
category_table
ON
main_category_id = category_main_category
INNER JOIN
goods_category_table
ON
category_id = gc_category
WHERE
gc_goods = :goods_id
AND
(
(main_category_webname, category_webname) IN (
('work', 'warm')
)
OR main_category_webname IN ('work-cool', 'work-bigsize', 'work-coolbiz')
)
ORDER BY
main_category_id";
$tmp = (new SqlService())->Sql($query)
->Param('goods_id', $goodsId)
->FetchAll();
return $tmp;
}
public function getGoodsDataForRecentviews2($goodsIdList)
{
// SQLインジェクション対策
$goodsIdList = $this->lmHelper->getIntegerArray($goodsIdList);
if (empty($goodsIdList)) {
return [];
}
$goodsId = implode(', ', $goodsIdList);
$query = "
SELECT
goods_id, goods_canonical_hinban, goods_name, MIN(ROUND(gp_price2 * (1+ 10/100))) AS min_gp_price, COUNT(DISTINCT(ROUND(gp_price2 * (1+ 10/100)))) AS count_gp_price,
(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN MIN(cp_price) ELSE MIN(ROUND(gp_price2 * (1+ 10/100))) END) as min_gp_price,
(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN COUNT(DISTINCT(cp_price)) ELSE COUNT(DISTINCT(ROUND(gp_price2 * (1+ 10/100)))) END) as count_gp_price,
IfNull(cra_average_points, 0) AS review_average, IfNull(cra_target_count, 0) AS review_count
FROM goods_table
LEFT JOIN jancode_table ON goods_id = jan_goods
LEFT JOIN goods_price_table ON goods_id = gp_goods
LEFT JOIN campaign_price_table ON cp_gp = gp_id AND `cp_end_datetime` >= NOW() and `cp_start_datetime` <= NOW()
LEFT JOIN customer_review_average_table ON goods_id = cra_goods_id AND cra_target_count >= 1
WHERE goods_id IN (" . $goodsId . ")
AND jan_ddate IS NULL AND gp_price IS NOT NULL
GROUP BY
goods_id
ORDER BY FIELD(goods_id, {$goodsId})";
$tmp = (new SqlService())->Sql($query)
->FetchAll();
return $tmp;
}
public function getCategoryData($mcId, $ctId = null)
{
if (empty($ctId)) {
$query = "SELECT * FROM main_category_table WHERE main_category_id = :mcId";
$tmp = (new SqlService())->Sql($query)
->Param('mcId', $mcId)
->Fetch();
} else {
$query = "SELECT * FROM main_category_table INNER JOIN category_table ON main_category_id = category_main_category WHERE main_category_id = :mcId AND category_id = :ctId";
$tmp = (new SqlService())->Sql($query)
->Param('mcId', $mcId)
->Param('ctId', $ctId)
->Fetch();
}
return $tmp;
}
public function getMainCategoryName($mcName)
{
return (new SqlService())
->Table('main_category_table')
->Join('category_group_table', 'T1.category_group_id = T.main_category_group', 'INNER')
->Join('category_table', 'T.main_category_id = T2.category_main_category', 'LEFT')
->Select("T.main_category_webname AS mc, T2.category_webname AS ct, T.main_category_id, T2.category_id
, T1.category_group_noshowdb, T1.category_group_only_main, T.main_category_group
, T.main_category_name, T2.category_name, T2.category_webname, T.main_bottomlink, T.main_pagebottom")
->Set('T.main_category_webname', $mcName)
->Find();
}
public function getSubCategoryName($mcId, $ctName)
{
return (new SqlService())
->Table('main_category_table')
->Join('category_table', 'T.main_category_id = T1.category_main_category', 'INNER')
->Select("T.main_category_webname AS mc, T1.category_webname AS ct, T.main_category_id, T1.category_id")
->Set('T.main_category_id', $mcId)
->Set('T1.category_webname', $ctName)
->Find();
}
public function getMainCategoryData($id)
{
return (new SqlService())
->Table('main_category_table')
->Set('main_category_id', $id)
->Find();
}
public function _getOriginalCategory_Main($id)
{
$rs = (array)(new SqlService())
->Table('category_table')
->Set('category_main_category', $id)
->Select("category_id, category_category, category_main")
->FindAll();
$all_category = "";
foreach ($rs as $row) {
if ($all_category != "") $all_category .= ",";
$all_category .= $this->_getOriginalCategory($row['category_id'], $row);
}
return $all_category;
}
public function _getOriginalCategory($id, $tmpRow = null)
{
if ($tmpRow === null) {
$row = (new SqlService())
->Table('category_table')
->Set('category_id', $id)
->Select("category_id, category_category, category_main")
->Find();
} else {
$row = $tmpRow;
}
if ($row) {
//カテゴリが共有されている場合の処理
if ($row['category_category'] != "") {
$all_category = $row['category_category'];
} elseif ($row['category_main'] != "") {
$strSQL = "select coalesce( category_category, category_id ) as cid, category_main
from category_table
where category_main_category = " . $row['category_main'] . " and category_main is null";
$tempRow = (new SqlService())->Sql($strSQL)->FetchAll();
foreach ($tempRow as $row) {
if ($all_category != "") $all_category .= ",";
$all_category .= $row['cid'];
}
} else {
$all_category = $id;
}
} else {
$all_category = $id;
}
return $all_category;
}
public function useUnItemOrder($mcId = null, $ctId = null)
{
if (empty($mcId)) return false;
$category_id = $mcId;
$is_parent = 1;
// 子カテの指定がある場合
if (!empty($ctId) && strtolower($ctId) !== 'allitem') {
$category_id = $ctId;
$is_parent = 0;
}
$rows = (new SqlService())
->Table('uniform_next_category_map_table')
->Set('lm_category_id', $category_id)
->Set('is_parent', $is_parent)
->Set('use_un_order', 1)
->Finds();
return !empty($rows);
}
public function getDataBySql($sql)
{
return (new SqlService())->Sql($sql)->FetchAll();
}
public function getSubCategoryList($mainCategoryId)
{
//
return CategoryWithRelated::getSubCategoryListByMainCategoryId($mainCategoryId);
}
public function getByCategoryWebName($siteType, $webName = null)
{
// $db = $this->getAdapter();
// $sql = "SELECT upper_one_column_content FROM upper_column_table";
// if ($webName) {
// $sql .= " LEFT JOIN main_category_table ON upper_column_table.target_category_id = main_category_table.main_category_id ";
// $sql .= " AND site_type = {$siteType} ";
// $sql .= " WHERE main_category_has_main_categories = 1 AND main_category_webname = '{$webName}' AND main_category_status = 1";
// }
// $result = $db->fetchRow($sql);
// return $result;
$query = (new SqlService())
->Table('upper_column_table')
->Select('upper_one_column_content');
if ($webName) {
$query->Join('main_category_table', "T.target_category_id = T1.main_category_id AND site_type = {$siteType}", 'LEFT')
->Set('T1.main_category_has_main_categories', 1)
->Set('T1.main_category_webname', $webName)
->Set('T1.main_category_status', 1);
}
return $query->Fetch();
}
/**
* 商品のパンくずリストを取得する
* TODO: 相関エンティティに移植
* @param int $goodsId
* @return array
*/
public function getBreadCrumbsByGoodsId($goodsId)
{
//
$breadCrumb = array();
//
$goods = $this->ProductConverter->getGoodsById($goodsId);
//
$displayCategory = $this->LM_displayCategory4($goodsId);
$canonicalPankuzuMainCategory = $displayCategory['canonicalPankuzuMainCategory'];
//
if (!empty($goods['goods_breadcrumb_category_name2']) && !empty($goods['goods_breadcrumb_category_url2'])) {
// 800-375-3……UT商品にて、パンくずリストのすべての階層を登録できるようにする（ファイルのインポート・エクスポートに項目追加して編集できるようにする）
$breadCrumb[] = array(
'href' => preg_replace('@([^/]+)$@', '$1/', $goods['goods_breadcrumb_category_url2']),
'label' => $goods['goods_breadcrumb_category_name2']
);
} else if ($canonicalPankuzuMainCategory[0]['main_web_name'] != '') {
$breadcrumbCategory = '';
$tempRow = $this->getMainCategoryData($canonicalPankuzuMainCategory[0]['main_web_name']);
if ($tempRow) {
if ($tempRow[0]['main_breadcrumb_category_name'] != '' && $tempRow[0]['main_breadcrumb_category_url'] != '') {
$breadCrumb[] = array(
'href' => preg_replace('@([^/]+)$@', '$1/', $tempRow[0]['main_breadcrumb_category_url']),
'label' => $tempRow[0]['main_breadcrumb_category_name']
);
}
}
}
if (!empty($goods['goods_breadcrumb_category_name3']) && !empty($goods['goods_breadcrumb_category_url3'])) {
// 800-375-3……UT商品にて、パンくずリストのすべての階層を登録できるようにする（ファイルのインポート・エクスポートに項目追加して編集できるようにする）
$breadCrumb[] = array(
'href' => preg_replace('@([^/]+)$@', '$1/', $goods['goods_breadcrumb_category_url3']),
'label' => $goods['goods_breadcrumb_category_name3']
);
} else if (empty($goods['goods_breadcrumb_category_name2']) || empty($goods['goods_breadcrumb_category_url2'])) {
// 配下カテゴリ
if (!empty($canonicalPankuzuMainCategory[0]['main_main_id'])) {
$breadCrumb[] = array(
'href' => "/{$canonicalPankuzuMainCategory[0]['main_main_web_name']}/",
'label' => $canonicalPankuzuMainCategory[0]['main_main_name']
);
// 商品詳細の「子カテゴリ名1」が空の場合のみ、「商品が最も多く属する親カテ」をパンくずに表示。
$breadCrumb[] = array(
'href' => "/{$canonicalPankuzuMainCategory[0]['main_main_web_name']}/{$canonicalPankuzuMainCategory[0]['main_web_name']}/",
'label' => $canonicalPankuzuMainCategory[0]['main_name']
);
} else {
// 商品詳細の「子カテゴリ名1」が空の場合のみ、「商品が最も多く属する親カテ」をパンくずに表示。
$breadCrumb[] = array(
'href' => "/{$canonicalPankuzuMainCategory[0]['main_web_name']}/",
'label' => $canonicalPankuzuMainCategory[0]['main_name']
);
}
}
if ($goods['goods_breadcrumb_category_name'] != '' && $goods['goods_breadcrumb_category_url'] != '') {
$breadCrumb[] = array(
'href' => preg_replace('@([^/]+)$@', '$1/', $goods['goods_breadcrumb_category_url']),
'label' => $goods['goods_breadcrumb_category_name']
);
}
if (!empty($goods['goods_breadcrumb_category_name4']) && !empty($goods['goods_breadcrumb_category_url4'])) {
// 800-375-4……UT商品パンくず……「子カテゴリ名4」の追加
$breadCrumb[] = array(
'href' => preg_replace('@([^/]+)$@', '$1/', $goods['goods_breadcrumb_category_url4']),
'label' => $goods['goods_breadcrumb_category_name4']
);
}
$itemId = $goods['goods_canonical_hinban'] ?: $goods['goods_id'];
$breadCrumb[] = array(
'href' => "/item/{$itemId}.html",
'label' => $goods['goods_name'],
);
return $breadCrumb;
}
public function getFollowUpByHashKey($hashKey)
{
$query = "SELECT * FROM follow_up_list_table
INNER JOIN customer_table USING ( customer_id )
WHERE ful_hash_key = :hashKey";
$result = (new SqlService())->Sql($query)
->Param('hashKey', $hashKey)
->Fetch();
return $result;
}
public function saveReply($ful_id, $replyOption)
{
$result = (new SqlService())
->Table('follow_up_list_table')
->Values([
'ful_reply_status' => 1,
'ful_reply_option' => $replyOption,
'ful_reply_date' => date('Y-m-d H:i:s'),
'ful_modified_at' => date('Y-m-d H:i:s'),
])
->Set('ful_id', $ful_id)
->Update();;
return $result;
}
// 受注情報（お届け予定日、黄色の付箋）の更新
public function updateOrder($ful_id, $replyOption)
{
$result = false;
if (!is_numeric($ful_id)) {
return false;
}
if (in_array($replyOption, [
self::REPLY_OPTION_WAIT_ENTIRE,
self::REPLY_OPTION_WAIT_SEQUENTIAL,
self::REPLY_OPTION_CANCEL_DELAYED,
self::REPLY_OPTION_CANCEL_ALL,
])) {
$date = date('Y-m-d');
$comment = "お客様返信済み";
$ohMemo = sprintf(self::MEMO_FORMAT, date('n/j', strtotime($date)), $comment);
$dbManager = new DbManager();
$g_db = $dbManager->get();
$sql = "UPDATE follow_up_list_table AS `ful`
INNER JOIN follow_up_detail_table AS `fud` USING ( ful_id )
INNER JOIN follow_up_summary_table AS `fus` USING ( fus_id )
INNER JOIN order_header_table AS `oh` USING ( oh_id )
SET
oh_send_schedule = DATE_FORMAT(fus_alternative_schedule, '%Y/%m/%d'),
oh_memo = CONCAT('" . $ohMemo . "', '\n\n', oh_memo)
WHERE
ful_id = " . $ful_id;
$g_db->exec($sql);
$result = true;
}
return $result;
}
public function getFollowUpDetailByFulId($fulId)
{
$query = "SELECT oh_shubetsu, oh_id FROM follow_up_list_table
INNER JOIN follow_up_detail_table USING (ful_id)
INNER JOIN order_header_table USING (oh_id)
WHERE ful_id = :fulId";
$result = (new SqlService())->Sql($query)
->Param('fulId', $fulId)
->FetchAll();
return $result;
}
}

app/Customize/Converter/DynamicConverter.php line 1989