vendor/lm/engine-ec/src/Entity/GoodsWithRelated.php line 798

Open in your IDE?
  1. <?php
  3. namespace Lm\Engine\EC\Entity;
  5. use Lm\Engine\EC\Entity\Goods\ColorNumberLabel;
  6. use Lm\Engine\EC\Entity\Goods\Label;
  7. use Lm\Engine\EC\Entity\Item\Item;
  8. use Lm\Engine\EC\UT;
  9. use Lm\Engine\SokujitsuHassou\SokujitsuHassou;
  10. use Lm\Engine\Zaiko\Entity\SkuExtended;
  11. use Lm\Entity\Category;
  12. use Lm\Engine\EC\Entity\Goods\CategoryListIconLabel;
  13. use Lm\Engine\EC\Entity\Goods\TargetGenderLabel;
  14. use Lm\Engine\EC\Entity\Goods\VendingStatusLabel;
  15. use Lm\Entity\GoodsSetPurchase;
  16. use Lm\Service\Cache\CacheService;
  17. use Lm\Service\Db\SqlService;
  18. use Lm\Entity\Goods;
  20. class GoodsWithRelated extends GoodsExtended
  21. {
  23.     const REVIEW_GET_MAX = 50;
  25.     /**
  26.      * @var CategoryWithRelated
  27.      */
  28.     protected $mainCategory;
  30.     /**
  31.      * @var CategoryWithRelated
  32.      */
  33.     protected $canonicalCategory;
  35.     /**
  36.      * @var array
  37.      */
  38.     protected $schemaOrgProduct;
  40.     /**
  41.      * @var array
  42.      */
  43.     protected $schemaOrgAggregateOffer;
  45.     /**
  46.      * @var array
  47.      */
  48.     protected $schemaOrgAggregateRating;
  50.     /**
  51.      * @var array
  52.      */
  53.     protected $schemaOrgReviewList;
  55.     /**
  56.      * @var array
  57.      */
  58.     protected $reviewList;
  60.     /**
  61.      * @var array
  62.      */
  63.     protected $reviewSummary;
  65.     /**
  66.      * @var Item
  67.      */
  68.     protected $item;
  70.     /**
  71.      * @var TargetGenderLabel
  72.      */
  73.     protected $targetGenderLabel;
  75.     /**
  76.      * @var CategoryListIconLabel
  77.      */
  78.     protected $categoryListIconLabel;
  80.     /**
  81.      * @var VendingStatusLabel
  82.      */
  83.     protected $vendingStatusLabel;
  85.     /**
  86.      * @var ColorNumberLabel
  87.      */
  88.     protected $colorNumberLabel;
  90.     /**
  91.      * @var Label[]
  92.      */
  93.     protected $labelList;
  95.     /**
  96.      * @var bool
  97.      */
  98.     protected $isSokujitsuHassouAvailable;
  100.     /**
  101.      * @return string
  102.      *
  103.      */
  104.     public function getItemUrl()
  105.     {
  106.         //
  107.         $ut = UT::getBaseInfo();
  108.         //
  109.         return sprintf("%s/item/%s.html", $ut->getShopUrl(), $this->getItemId());
  110.     }
  112.     /**
  113.      * @return CategoryWithRelated|null
  114.      */
  115.     public function getMainCategory()
  116.     {
  117.         return self::compute($this->canonicalCategory, function () {
  118.             //
  119.             return self::getMainCategoryById($this->goodsId);
  120.         });
  121.     }
  123.     /**
  124.      * @param $goodsId
  125.      * @return CategoryWithRelated|null
  126.      */
  127.     public static function getMainCategoryById($goodsId)
  128.     {
  129.         //
  130.         $mainCategory = (new SqlService())
  131.             ->Sql("SELECT
  132.                             main_category_main_category_webname
  133.                         ,    main_category_webname
  134.                         ,    COUNT(DISTINCT category_id) AS `ctCount`
  135.                         FROM (
  136.                             SELECT     main_category_id
  137.                             ,    main_category_main_category_webname
  138.                             ,    main_category_webname
  139.                             ,    category_id
  140.                             FROM goods_table
  141.                             INNER JOIN goods_category_table ON goods_id = gc_goods
  142.                             INNER JOIN category_table ON category_id = gc_category AND category_status = :category_status
  143.                             INNER JOIN main_category_table ON main_category_id = category_main_category AND main_category_status = :main_category_status
  144.                             WHERE goods_id = :goods_id
  145.                             UNION ALL
  146.                             SELECT    main_category_id
  147.                             ,    main_category_main_category_webname
  148.                             ,    main_category_webname
  149.                             ,    0 AS category_id
  150.                             FROM goods_table
  151.                             INNER JOIN goods_main_category_table ON goods_id = gmc_goods
  152.                             INNER JOIN main_category_table ON main_category_id = gmc_main_category AND main_category_status = :main_category_status
  153.                             WHERE goods_id = :goods_id
  154.                         ) AS `mc`
  155.                         GROUP BY
  156.                             main_category_id
  157.                         ORDER BY
  158.                             `ctCount` DESC
  159.                         ,    main_category_webname
  160.                         LIMIT 1")
  161.             ->Params([
  162.                 'goods_id' => $goodsId,
  163.                 'category_status' => Category::STATUS_AVAILABLE,
  164.                 'main_category_status' => Category::STATUS_AVAILABLE,
  165.             ])
  166.             ->Fetch()
  167.         ;
  168.         //
  169.         if (!empty($mainCategory)) {
  170.             //
  171.             return new CategoryWithRelated($mainCategory['main_category_webname'], null, $mainCategory['main_category_main_category_webname']);
  172.         } else {
  173.             //
  174.             return null;
  175.         }
  176.     }
  178.     /**
  179.      * @return CategoryWithRelated|null
  180.      */
  181.     public function getCanonicalCategory()
  182.     {
  183.         return self::compute($this->canonicalCategory, function () {
  184.             //
  185.             if ($mainCategory = $this->getMainCategory()) {
  186.                 //
  187.                 if ($mainCategory->hasMainCategories()) {
  188.                     //
  189.                     return $mainCategory;
  190.                 }
  191.             }
  192.         });
  193.     }
  195.     /**
  196.      * @return array
  197.      * @throws \Exception
  198.      */
  199.     public function getStockListForMatrix() {
  200.         //
  201.         $result = [
  202.             'stockList' => [],
  203.             'direct' => '',
  204.             'janList' => [],
  205.         ];
  206.         //
  207.         $goodsId = $this->getGoodsId();
  208.         //
  209.         $skuList = SkuExtended::getInstanceList($goodsId);
  210.         //
  211.         foreach($skuList as $sku){
  212.             //
  213.             $gclId = $sku->getGclId();
  214.             $gpId = $sku->getGpId();
  215.             //
  216.             if (!isset($result['stockList'][$goodsId])) {
  217.                 //
  218.                 $result['stockList'][$goodsId] = [];
  219.             }
  220.             //
  221.             if (!isset($result['stockList'][$goodsId][$gclId])) {
  222.                 //
  223.                 $result['stockList'][$goodsId][$gclId] = [];
  224.             }
  225.             //
  226.             $nyukaDateComment = $sku->getNyukaDateComment();
  227.             $nyukaDate = $sku->getNyukaDate();
  228.             $comment = '';
  229.             if (strstr(trim($nyukaDateComment), "次回生産予定無し")) {
  230.                 $comment = '<span style="color: #138a19;">入荷予定日<br />未定</span>';
  231.             } elseif (strstr(trim($nyukaDateComment), "生産中止")) {
  232.                 $comment = '<span style="color: #696969;">生産中止</span>';
  233.             } elseif (strstr(trim($nyukaDateComment), "生産中")) {
  234.                 $comment = '<span style="color: #138a19;">生産中</span>';
  235.             } elseif ($nyukaDate != null) {
  236.                 $comment = '<span style="color: #138a19;">入荷予定日<br />' . date("n月j日", strtotime("{$nyukaDate}")) . '</span>';
  237.             }
  238.             //
  239.             $result['stockList'][$goodsId][$gclId][$gpId] = [
  240.                 'stock_other' => $sku->getStockOther(),
  241.                 'stock' => $sku->getStockTotal(),
  242.             ];
  243.             //
  244.             if ($stock3 = $sku->getStockBichiku()) {
  245.                 //
  246.                 if ($bichikuNyukaDate = $sku->getBichikuNyukaDate()) {
  247.                     $result['stockList'][$goodsId][$gclId][$gpId]['stock3'] = sprintf('<div class="nyuka-yotei" style="font-size:12px;">入荷予定日<br />%s</div>', date("n月j日", strtotime("{$bichikuNyukaDate} +1 day")));
  248.                 } else {
  249.                     $result['stockList'][$goodsId][$gclId][$gpId]['stock3'] = sprintf("<span style=\"color:red;\">(即日発送 %s)</span>", $stock3);
  250.                 }
  251.             }
  252.             //
  253.             if (true) {
  254.                 $result['stockList'][$goodsId][$gclId][$gpId]['nodisp'] = $sku->getNoDisp();
  255.             }
  256.             //
  257.             if (!empty($nyukaDate)) {
  258.                 $result['stockList'][$goodsId][$gclId][$gpId]['zdate'] = (new \DateTime($nyukaDate))->format('n月j日');
  259.                 $result['stockList'][$goodsId][$gclId][$gpId]['nyuuka_yoteibi'] = (new \DateTime($nyukaDate))->format('Y-m-d H:i:s');
  260.             }
  261.             //
  262.             if (!empty($comment)) {
  263.                 $result['stockList'][$goodsId][$gclId][$gpId]['comment'] = $comment;
  264.             }
  265.             //
  266.             if (!isset($result['janList'][$gclId])) {
  267.                 //
  268.                 $result['janList'][$gclId] = [];
  269.             }
  270.             $result['janList'][$gclId][$gpId] = $sku->getBichikuNyukaDate() ?: $sku->getNyukaDate();
  271.         }
  272.         //
  273.         return $result;
  274.     }
  276.     /**
  277.      * @param int $goodsId
  278.      * @return array
  279.      * @throws Exception
  280.      */
  281.     function LM_chokusouCheckAll2($goodsId) {
  282.         return (array)(new \Lm\Engine\SokujitsuHassou\SokujitsuHassou())
  283.             ->check($goodsId, true);
  284.     }
  286.     protected function getStockList() {
  287.         //
  288.         $result = array();
  289.         //
  290.         $goodsId = $this->getGoodsId();
  291.         // 84: チトセの場合は、在庫数 + 外部倉庫
  292.         // $is_chitose = false;
  293.         // $strSql = sprintf("SELECT DISTINCT gp_kataban FROM jancode_table INNER JOIN goods_price_table ON jan_price = gp_id WHERE jan_goods = %d", $goodsId);
  294.         // if ($res = $model->getDatas($db, $strSql)) {
  295.         //     $tmp = explode('-', $res[0]['gp_kataban']);
  296.         //     if ($tmp[0] == '84') {
  297.         //         $is_chitose = true;
  298.         //     }
  299.         // }
  300.         $is_chitose = $this->isChitose();
  301.         // 在庫マトリクス高速化対応
  302.         // TODO: 直送チェック用のリストを取得
  303.         $sokujitsuFlgList = $this->LM_chokusouCheckAll2($goodsId);
  304.         // 在庫情報を取得
  305.         // $strSql = $this->getCheckStockSelectByIdSql($goodsId);
  306.         // $stockList = $model->getDatas($db, $strSql);
  307.         $stockList = (array)$this->getCheckStockList();
  308.         foreach ($stockList as $stock) {
  309.             if (is_numeric($stock['stock'])) {
  310.                 $result[$goodsId][$stock['jan_color']][$stock['jan_price']]['stock_other'] = intval($stock['jan_stock4']) + intval($stock['jan_stock5']) + intval($stock['jan_stock6']) + intval($stock['jan_stock7']) + intval($stock['jan_stock8']) + intval($stock['jan_stock3']);
  311.                 $result[$goodsId][$stock['jan_color']][$stock['jan_price']]['stock'] = $stock['stock'] + $result[$goodsId][$stock['jan_color']][$stock['jan_price']]['stock_other'];
  312.             } else {
  313.                 $result[$goodsId][$stock['jan_color']][$stock['jan_price']]['stock'] = $stock['stock'];
  314.             }
  315.             if ($is_chitose && $stock['jan_stock9']) {
  316.                 $result[$goodsId][$stock['jan_color']][$stock['jan_price']]['stock'] += $stock['jan_stock9'];
  317.             }
  318.             if($stock['jan_stock3']!="" and $stock['jan_stock3']>0 and $stock['jan_stock3_nyuka_date']==""){
  319.                 $result[$goodsId][$stock['jan_color']][$stock['jan_price']]['stock3'] = "<span style='color:red;'>(即日発送 ".$stock['jan_stock3'].")</span>";
  320.             }elseif($stock['jan_stock3']!="" and $stock['jan_stock3']>0 and $stock['jan_stock3_nyuka_date']!=""){
  321.                 $result[$goodsId][$stock['jan_color']][$stock['jan_price']]['stock3'] = '<div class="nyuka-yotei" style="font-size:12px;">入荷予定日<br />' . date("n月j日", strtotime($stock['jan_stock3_nyuka_date']." +1 day")) . '</div>';
  322.             }elseif(isset($sokujitsuFlgList[$stock['jan_color']][$stock['jan_price']]) && $sokujitsuFlgList[$stock['jan_color']][$stock['jan_price']]){
  323.                 $result[$goodsId][$stock['jan_color']][$stock['jan_price']]['stock3'] = "<span style='color:red;'>(即日発送 ".$result[$goodsId][$stock['jan_color']][$stock['jan_price']]['stock'].")</span>";
  324.             }
  325.             $result[$goodsId][$stock['jan_color']][$stock['jan_price']]['nodisp'] = $stock['nodisp'];
  326.         }
  327.         // 入荷予定情報を取得
  328.         // $strSql = $model->getGoodsArrivalDateSelectSql($goodsId);
  329.         // $zeroList = $model->getDatas($db, $strSql);
  330.         $zeroList = (array)$this->getGoodsArrivalDate($goodsId);
  331.         foreach ($zeroList as $zero) {
  332.             // // $result[$goodsId][$zero['zero_gcl']][$zero['zero_gp']]['stock'] = 0;
  333.             if ($result[$goodsId][$zero['zero_gcl']][$zero['zero_gp']]['stock'] === '＊') {
  334.                 // 在庫「＊」の場合、「在庫0指定」（＝予約注文）を優先
  335.                 $result[$goodsId][$zero['zero_gcl']][$zero['zero_gp']]['stock'] = 0;
  336.             } else if (empty($result[$goodsId][$zero['zero_gcl']][$zero['zero_gp']]['stock_other'])) {
  337.                 // 仕入先在庫のみ存在するの場合、「在庫0指定」（＝予約注文）を優先
  338.                 $result[$goodsId][$zero['zero_gcl']][$zero['zero_gp']]['stock'] = 0;
  339.             }
  340.             //
  341.             $result[$goodsId][$zero['zero_gcl']][$zero['zero_gp']]['zdate'] = $zero['zdate'];
  342.             $result[$goodsId][$zero['zero_gcl']][$zero['zero_gp']]['nyuuka_yoteibi'] = $zero['nyuuka_yoteibi'];
  343.         }
  344.         // 欠品情報を取得(入荷未定、入荷予定日あり、生産中、生産中止)
  345.         // $strSql = $model->getGoodsStockoutSelectSql($goodsId);
  346.         // $keppinList = $model->getDatas($db, $strSql);
  347.         $keppinList = (array)$this->getGoodsStockout();
  348.         foreach ((array)$keppinList as $keppin) {
  349.             // // $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['stock'] = 0;
  350.             if ($result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['stock'] === '＊') {
  351.                 // 在庫「＊」の場合、「欠品登録」（＝予約注文）を優先
  352.                 $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['stock'] = 0;
  353.             } else if (empty($result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['stock_other'])) {
  354.                 // 仕入先在庫のみ存在するの場合、「欠品登録」（＝予約注文）を優先
  355.                 $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['stock'] = 0;
  356.             }
  357.             //
  358.             $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['zdate'] = date('n月j日', strtotime($keppin['ki_date']));
  359.             $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['nyuuka_yoteibi'] = $keppin['ki_date'];
  360.             if (strstr(trim($keppin['ki_comment']), "次回生産予定無し")) {
  361.                 $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['comment'] = '<span style="color: #138a19;">入荷予定日<br />未定</span>';
  362.             } elseif (strstr(trim($keppin['ki_comment']), "生産中止")) {
  363.                 $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['comment'] = '<span style="color: #696969;">生産中止</span>';
  364.             } elseif (strstr(trim($keppin['ki_comment']), "生産中")) {
  365.                 $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['comment'] = '<span style="color: #138a19;">生産中</span>';
  366.             } elseif ($keppin['ki_date'] != null) {
  367.                 $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['comment'] = '<span style="color: #138a19;">入荷予定日<br />' . date("n月j日", strtotime("{$keppin['ki_date']} +1 day")) . '</span>';
  368.             } else {
  369.                 $result[$goodsId][$keppin['ki_gcl']][$keppin['ki_gp']]['comment'] = "";
  370.             }
  371.         }
  372.         return $result;
  373.     }
  375.     /**
  376.      * @return array|null
  377.      */
  378.     public function getCheckStockList ()
  379.     {
  380.         return self::getCheckStockListById($this->getGoodsId());
  381.     }
  383.     /**
  384.      * @link https://bitbucket.org/lm91/front/src/e4bda25c7a8fce8f116c87adb8646ee8f9635f6f/app/html/application/modules/default/models/commons/BaseLibraryModel.php#lines-523:529
  385.      * @param $goodsId
  386.      * @return array|null
  387.      */
  388.     public static function getCheckStockListById($goodsId)
  389.     {
  390.         //
  391.         return (new SqlService())
  392.             ->Select("
  393.                     jan_color
  394.                 ,    jan_price
  395.                 ,    coalesce( jan_stock, '＊' ) AS stock
  396.                 ,    coalesce( jan_stock3, 0 ) AS jan_stock3
  397.                 ,    jan_stock3_nyuka_date
  398.                 ,    coalesce( jan_stock4, 0 ) AS jan_stock4
  399.                 ,    coalesce( jan_stock5, 0 ) AS jan_stock5
  400.                 ,    coalesce( jan_stock6, 0 ) AS jan_stock6
  401.                 ,    coalesce( jan_stock7, 0 ) AS jan_stock7
  402.                 ,    coalesce( jan_stock8, 0 ) AS jan_stock8
  403.                 ,    coalesce( jan_stock9, 0 ) AS jan_stock9
  404.                 ,    coalesce( jan_stock9, 0 ) AS jan_stock9
  405.                 ,    jan_nodisplay AS nodisp
  406.                 ,    MIN(COALESCE(jan_stock3_nyuka_date, T2.zero_period, T1.ki_date)) AS `nyuuka_yoteibi`
  407.             ")
  408.             ->Table("jancode_table")
  409.             ->Join("keppin_item_table", "T.jan_goods = T1.ki_goods AND T.jan_color = T1.ki_gcl AND T.jan_price = T1.ki_gp AND T1.ki_date > NOW()", "LEFT")
  410.             ->Join("zero_stock_table", "T.jan_goods = T2.zero_goods AND T.jan_color = T2.zero_gcl AND T.jan_price = T2.zero_gp AND T2.zero_period > NOW()", "LEFT")
  411.             ->Set("jan_goods", $goodsId)
  412.             ->GroupBy('T.jan_id')
  413.             ->FindAll()
  414.         ;
  415.     }
  417.     /**
  418.      * 在庫入荷予定日
  419.      * @return array|null
  420.      */
  421.     public function getGoodsArrivalDate ()
  422.     {
  423.         return self::getGoodsArrivalDateById($this->getGoodsId());
  424.     }
  426.     /**
  427.      * 在庫入荷予定日
  428.      * @link https://bitbucket.org/lm91/front/src/e4bda25c7a8fce8f116c87adb8646ee8f9635f6f/app/html/application/modules/default/models/commons/BaseLibraryModel.php#lines-1281:1304
  429.      * @param int $goodsId
  430.      * @return array|null
  431.      */
  432.     public static function getGoodsArrivalDateById ($goodsId)
  433.     {
  434.         return (new SqlService())
  435.             ->Sql("SELECT *, date_format( date_add( zero_period, interval 1 day ), '%c月%e日' ) AS zdate,(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN cp_price ELSE ROUND(gp_price2 * (1+ 10/100)) END) as gp_price, date_add( zero_period, interval 1 day ) AS nyuuka_yoteibi 
  436.                     FROM zero_stock_table
  437.                     INNER JOIN goods_table ON zero_goods = goods_id
  438.                     LEFT JOIN goods_price_table ON zero_gp = gp_id
  439.                     LEFT JOIN size_table ON gp_size_id = size_id
  440.                     LEFT JOIN goods_color_table ON zero_gcl = gcl_id
  441.                     LEFT JOIN color_table ON gcl_color_id = color_id
  442.                     LEFT JOIN campaign_price_table ON cp_gp = gp_id AND `cp_end_datetime` >= NOW() and `cp_start_datetime` <= NOW()
  443.                     LEFT JOIN jancode_table ON jan_goods = goods_id AND jan_color = gcl_id AND jan_price = gp_id
  444.                     WHERE zero_goods = :goods_id
  445.                     AND zero_period >= NOW()
  446.                     AND ( jan_stock5 IS NULL OR jan_stock5 = 0 )
  447.                     AND ( jan_stock6 IS NULL OR jan_stock6 = 0 )
  448.                     AND ( jan_stock7 IS NULL OR jan_stock7 = 0 )
  449.                     AND ( jan_stock8 IS NULL OR jan_stock8 = 0 )
  450.                     AND ( jan_stock9 IS NULL OR jan_stock9 = 0 )
  451.                     AND ( gp_display IS NULL OR ( gp_display IS NOT NULL AND gp_display != 99 ) )
  452.                     AND gcl_display_status = 1
  453.                     ORDER BY gcl_display, color_display,gp_display,size_display")
  454.             ->Params([
  455.                 'goods_id' => $goodsId,
  456.             ])
  457.             ->FetchAll()
  458.         ;
  459.     }
  461.     /**
  462.      * 欠品情報
  463.      * @param string|null $fromDatetime
  464.      * @return array|null
  465.      */
  466.     public function getGoodsStockout ($fromDatetime = null)
  467.     {
  468.         return self::getGoodsStockoutById($this->getGoodsId(), $fromDatetime);
  469.     }
  471.     //
  473.     /**
  474.      * 欠品情報
  475.      * @link https://bitbucket.org/lm91/front/src/e4bda25c7a8fce8f116c87adb8646ee8f9635f6f/app/html/application/modules/default/models/commons/BaseLibraryModel.php#lines-1306:1330
  476.      * @param int $goodsId
  477.      * @param string|null $fromDatetime
  478.      * @return array|null
  479.      */
  480.     public static function getGoodsStockoutById ($goodsId, $fromDatetime = null)
  481.     {
  482.         //
  483.         if ($fromDatetime === null) {
  484.             $fromDatetime = date('Y-m-d 00:00:00');
  485.         }
  486.         //
  487.         return (new SqlService())
  488.             ->Sql("SELECT *, date_format( ki_date, '%Y/%m/%d' ) AS kdate
  489.                     FROM keppin_item_table AS a
  490.                     INNER JOIN goods_table ON ki_goods = goods_id
  491.                     LEFT JOIN goods_price_table ON ki_gp = gp_id
  492.                     LEFT JOIN size_table ON gp_size_id = size_id
  493.                     LEFT JOIN goods_color_table ON ki_gcl = gcl_id
  494.                     LEFT JOIN color_table ON gcl_color_id = color_id
  495.                     WHERE NOT EXISTS (
  496.                         SELECT * FROM keppin_item_table AS b
  497.                         WHERE a.ki_id < b.ki_id
  498.                         AND a.ki_goods = b.ki_goods
  499.                         AND ( a.ki_gp = b.ki_gp OR b.ki_gp IS NULL )
  500.                         AND ( a.ki_gcl = b.ki_gcl OR b.ki_gcl IS NULL )
  501.                     )
  502.                     AND a.ki_goods = :goods_id
  503.                     AND ( a.ki_date >= :from_datetime OR a.ki_date IS NULL )
  504.                     AND a.ki_comment IS NOT NULL
  505.                     AND ( gp_display IS NULL OR ( gp_display IS NOT NULL AND gp_display != 99 ) )
  506.                     AND gcl_display_status = 1
  507.                     ORDER BY gcl_display, color_display,gp_display,size_display")
  508.             ->Params([
  509.                 'goods_id' => $goodsId,
  510.                 'from_datetime' => $fromDatetime,
  511.             ])
  512.             ->FetchAll()
  513.         ;
  514.     }
  516.     public function getSchemaOrgProduct()
  517.     {
  518.         return self::compute($this->schemaOrgProduct, function () {
  519.             //
  520.             if ($category = $this->getMainCategory()) {
  521.                 //
  522.                 $category = $category->getMainCategoryName();
  523.             }
  524.             //
  525.             $result = [
  526.                 '@context' => 'http://schema.org',
  527.                 '@type' => 'Product',
  528.                 'productID' => $this->getGoodsId(),
  529.                 'name' => $this->getGoodsName(),
  530.                 'sku' => $this->getGoodsMainKataban(),
  531.                 'mpn' => $this->getGoodsMainKataban(),
  532.                 'category' => $category,
  533.             ];
  534.             //
  535.             if ($aggregateRating = $this->getSchemaOrgAggregateRating()) {
  536.                 //
  537.                 $result['aggregateRating'] = $aggregateRating;
  538.             }
  539.             //
  540.             if ($review = $this->getSchemaOrgReviewList()) {
  541.                 //
  542.                 $result['review'] = $review;
  543.             }
  544.             //
  545.             if ($offers = $this->getSchemaOrgAggregateOffer()) {
  546.                 //
  547.                 $result['offers'] = $offers;
  548.             }
  549.             //
  550.             return $result;
  551.         });
  552.     }
  554.     public function getSchemaOrgAggregateOffer()
  555.     {
  556.         return self::compute($this->schemaOrgAggregateOffer, function () {
  557.             //
  558.             $result = [];
  559.             //
  560.             $goodsPrice = $this->getPrice();
  561.             $availability = $this->isStockAvailable() ? 'InStock' : 'OutOfStock';
  562.             //
  563.             $result = [
  564.                 '@type' => 'AggregateOffer',
  565.                 'availability' => "http://schema.org/{$availability}",
  566.                 'price' => $goodsPrice['min_price_in_tax'],
  567.                 'lowPrice' => $goodsPrice['min_price_in_tax'],
  568.                 'highPrice' => $goodsPrice['max_price_in_tax'],
  569.                 'priceCurrency' => 'JPY',
  570.                 'url' => $this->getItemUrl(),
  571.             ];
  572.             //
  573.             if ($priceValidUntil = $goodsPrice['max_cp_end_datetime']) {
  574.                 $result['priceValidUntil'] = date('Y-m-d', strtotime($priceValidUntil));
  575.             }
  576.             //
  577.             return $result;
  578.         });
  579.     }
  581.     public function getSchemaOrgAggregateRating()
  582.     {
  583.         return self::compute($this->schemaOrgAggregateRating, function () {
  584.             //
  585.             $result = [];
  586.             //
  587.             if ($reviewList = $this->getReviewList($reviewCount)) {
  588.                 $cr_points = array_map(function ($v) {
  589.                     return $v["cr_points"];
  590.                 }, $reviewList);
  591.                 $arg = empty($cr_points) ? 0 : (array_sum($cr_points) / count($cr_points));
  592.                 $ratingValue = (floor($arg * 10)) / 10;
  593.                 //
  594.                 $result = [
  595.                     '@type' => 'AggregateRating',
  596.                     'ratingValue' => $ratingValue,
  597.                     'reviewCount' => $reviewCount,
  598.                 ];
  599.             }
  600.             //
  601.             return $result;
  602.         });
  603.     }
  605.     public function getSchemaOrgReviewList()
  606.     {
  607.         return self::compute($this->schemaOrgReviewList, function () {
  608.             //
  609.             $reviewList = $this->getReviewList();
  610.             //
  611.             $result = [];
  612.             //
  613.             foreach ($reviewList as $review) {
  614.                 //
  615.                 $result[] = [
  616.                     '@type' => 'Review',
  617.                     'datePublished' => date('Y-m-d', strtotime($review['app_datetime'])),
  618.                     'reviewBody' => !empty($review['cr_staff_comment']) ? $review['cr_staff_comment'] : $review['cr_comment'],
  619.                     'author' => [
  620.                         '@type' => 'Person',
  621.                         'name' => empty($review['cr_name']) ? '匿名希望' : $review['cr_name'],
  622.                     ],
  623.                     'reviewRating' => [
  624.                         '@type' => 'Rating',
  625.                         'bestRating' => 5,
  626.                         'ratingValue' => $review['cr_points'],
  627.                         'worstRating' => 1,
  628.                     ],
  629.                 ];
  630.             }
  631.             //
  632.             return $result;
  633.         });
  634.     }
  636.     public function getReviewSummary()
  637.     {
  638.         return self::compute($this->reviewSummary, function () {
  639.             //
  640.             $result = [
  641.                 'ratingValue' => 0,
  642.                 'reviewCount' => 0,
  643.                 'imgStars' => '',
  644.             ];
  645.             $image_name = 0;
  646.             $arg = 0;
  647.             //
  648.             if ($reviewList = $this->getReviewList($reviewCount)) {
  649.                 $cr_points = array_map(function ($v) {
  650.                     return $v["cr_points"];
  651.                 }, $reviewList);
  652.                 $arg = empty($cr_points) ? 0 : (array_sum($cr_points) / count($cr_points));
  653.                 $ratingValue = (floor($arg * 10)) / 10;
  654.                 //
  655.                 $image_name = (int)($arg * 2) / 2 * 10;
  656.                 //
  657.                 $result['ratingValue'] = $ratingValue;
  658.                 $result['reviewCount'] = $reviewCount;
  659.             }
  660.             //
  661.             $result['imgStars'] = "<img class=\"history-average-img\" alt=\"お客様からの口コミレビュー評価の星の数{$arg}\" src=\"/images/review/star_m{$image_name}.gif\">";
  662.             //
  663.             return $result;
  664.         });
  665.     }
  667.     public function getReviewList(&$count = null, $limit = self::REVIEW_GET_MAX, $offset = 0)
  668.     {
  669.         return self::compute($this->reviewList, function () use ($limit, $offset, &$count) {
  670.             //
  671.             $id = $this->getGoodsId();
  672.             //
  673.             return self::getReviewListById($id, $limit, $offset, $count);
  674.         });
  675.     }
  677.     public static function getReviewListById($id, $limit = null, $offset = 0, &$count = null)
  678.     {
  679.         return CacheService::getCached(function () use ($id, $limit, $offset, &$count) {
  680.             $sql = (new SqlService())
  681.                 ->Table('customer_review_table')
  682.                 ->Set('cr_approval', 1)
  683.                 ->Set('cr_site_id', 0)
  684.                 ->Set('cr_main_category_id', 0)
  685.                 ->Set('cr_goods_id', $id)
  686.                 ->Order('app_datetime', 'DESC');
  687.             //
  688.             $count = call_user_func(function ($sql) {
  689.                 return (int)$sql->Count();
  690.             }, clone $sql);
  691.             //
  692.             if (!empty($limit)) $sql
  693.                 ->Limit($offset, $limit);
  694.             //
  695.             $result = (array)$sql
  696.                 ->FindAll();
  697.             //
  698.             return $result;
  699.         });
  700.     }
  702.     public function getPrice()
  703.     {
  704.         return self::getPriceByGoodsId($this->getGoodsId());
  705.     }
  707.     /**
  708.      * TODO: 疑似バッチ
  709.      * @param int[]|int $goodsId
  710.      * @param $datetime
  711.      * @return mixed
  712.      * @throws \ReflectionException
  713.      */
  714.     public static function getPriceByGoodsId($goodsId, $datetime = null)
  715.     {
  716.         //
  717.         if ($datetime === null) {
  718.             //
  719.             $datetime = date("Y-m-d H:i:s");
  720.         }
  721.         //
  722.         $hash = md5(serialize($goodsId));
  723.         $label = "goods-price-{$hash}-{$datetime}";
  724.         //
  725.         return CacheService::getCached(function () use ($goodsId, $datetime) {
  726.             //
  727.             $goodsIdList = [];
  728.             foreach ((array)$goodsId as $i => $_goodsId) {
  729.                 $goodsIdList["goodsId{$i}"] = $_goodsId;
  730.             }
  731.             $goodsIdListPlaceHolder = implode(',', array_map(function ($key) {
  732.                 return ":{$key}";
  733.             }, array_keys($goodsIdList)));
  734.             //
  735.             // TODO: 税率は動的に取得
  736.             $sql = "SELECT
  737.                         goods_id
  738.                     ,    MIN(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN (truncate((cp_price/1.10) + .5,0)) ELSE (gp_price2) END ) AS `min_price_ex_tax`
  739.                     ,    MIN(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN (cp_price) ELSE (ROUND(gp_price2 * (1+ 10/100))) END) AS `min_price_in_tax`
  740.                     ,    MAX(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN (truncate((cp_price/1.10) + .5,0)) ELSE (gp_price2) END ) AS `max_price_ex_tax`
  741.                     ,    MAX(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN (cp_price) ELSE (ROUND(gp_price2 * (1+ 10/100))) END) AS `max_price_in_tax`
  742.                     ,    MIN(`cp_start_datetime`) AS `min_cp_start_datetime`
  743.                     ,    MAX(`cp_end_datetime`) AS `max_cp_end_datetime`
  744.                     ,    (:datetime BETWEEN MIN(`cp_start_datetime`) AND MAX(`cp_end_datetime`)) AS is_campaign
  745.                     FROM
  746.                         goods_table
  747.                     INNER JOIN
  748.                         goods_price_table
  749.                     ON
  750.                         goods_id = gp_goods
  751.                     AND
  752.                         IfNull(gp_display, 0) != 99
  753.                     LEFT JOIN
  754.                         campaign_price_table
  755.                     ON
  756.                         gp_id = cp_gp
  757.                     AND
  758.                         cp_start_datetime <= :datetime
  759.                     AND
  760.                         cp_end_datetime >= :datetime
  761.                     AND
  762.                         cp_del_flg = 0
  763.                     WHERE
  764.                         goods_id IN ({$goodsIdListPlaceHolder})
  765.                     GROUP BY
  766.                         goods_id";
  767.             $params = array_merge([
  768.                 'datetime' => $datetime,
  769.             ], $goodsIdList);
  770.             //
  771.             $sql = (new SqlService())
  772.                 // ->Select('goods_id, MIN(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN (truncate((cp_price/1.10) + .5,0)) ELSE (gp_price2) END ), MAX(CASE WHEN cp_price IS NOT NULL AND cp_price > 0 THEN (cp_price) ELSE (ROUND(gp_price2 * (1+ 10/100))) END)')
  773.                 // ->Table('goods_table')
  774.                 // ->Join('goods_price_table', 'T.goods_id = T1.gp_goods', 'INNER')
  775.                 // ->Join('campaign_price_table', 'T.goods_id = T1.gp_goods AND cp_start_datetime <= :datetime AND cp_end_datetime >= :datetime AND cp_del_flg = 0', 'LEFT')
  776.                 // ->Set('goods_id', $goodsId)
  777.                 ->Sql($sql)
  778.                 ->Params($params)
  779.             ;
  780.             //
  781.             if (is_array($goodsId)) {
  782.                 //
  783.                 $result = $sql->FetchAll();
  784.             } else {
  785.                 //
  786.                 $result = $sql->Fetch();
  787.             }
  788.             //
  789.             return $result;
  790.         }, $label);
  791.     }
  793.     public function getStock()
  794.     {
  795.         return self::getStockById($this->getGoodsId(), true);
  796.     }
  798.     public function isStockAvailable()
  799.     {
  800.         return self::getStockById($this->getGoodsId());
  801.     }
  803.     /**
  804.      * @param int|NULL $goods_id
  805.      * @param bool $is_matrix
  806.      * @param int|NULL $jan_id
  807.      * @param int|NULL $gp_id
  808.      * @param int|NULL $gcl_id
  809.      * @param string|NULL $date
  810.      * @return mixed
  811.      * @throws \Exception
  812.      */
  813.     public static function getStockById($goods_id = NULL, $is_matrix = false, $jan_id = NULL, $gp_id = NULL, $gcl_id = NULL, $date = NULL)
  814.     {
  815.         return CacheService::getCached(function () use ($goods_id, $is_matrix, $jan_id, $gp_id, $gcl_id, $date) {
  816.             //
  817.             $skuList = SkuExtended::getInstanceList($goods_id, $jan_id, $gcl_id, $gp_id, $date);
  818.             $stock_total_summary = 0;
  819.             $stockList = [];
  820.             foreach ($skuList as $sku) {
  821.                 //
  822.                 $gclId = $sku->getGclId();
  823.                 $gpId = $sku->getGpId();
  824.                 //
  825.                 if (!isset($stockList[$gclId])) {
  826.                     //
  827.                     $stockList[$gclId] = [];
  828.                 }
  829.                 //
  830.                 if (!isset($stockList[$gclId][$gpId])) {
  831.                     //
  832.                     $stockList[$gclId][$gpId] = [
  833.                         'stock_total' => null,
  834.                         'stock_sokujitsu' => null,
  835.                     ];
  836.                 }
  837.                 //
  838.                 if (!$sku->isNoDisplay()) {
  839.                     //
  840.                     $stockTotal = $sku->getStockTotal();
  841.                     //
  842.                     $stockList[$gclId][$gpId] = [
  843.                         'stock_total' => $stockTotal,
  844.                         'stock_sokujitsu' => $sku->getStockSokujitsu(),
  845.                     ];
  846.                     //
  847.                     $stock_total_summary += (($stockTotal === '＊') ? 1 : $stockTotal);
  848.                 }
  849.             }
  850.             //
  851.             if ($is_matrix === false) {
  852.                 //
  853.                 if (!empty($jan_id) || (!empty($gcl_id) && !empty($gp_id))) {
  854.                     // is_matrix = false ・・・ 指定されたSKUの分のみ出力
  855.                     return $stockList[$gclId][$gpId];
  856.                 } else {
  857.                     // is_matrix = false ・・・ SKU指定が省略された場合、在庫の有／無を返す。
  858.                     return $stock_total_summary > 0;
  859.                 }
  860.             } else {
  861.                 //is_matrix = true ・・・ 全SKUの多段配列でSKU毎に出力
  862.                 return $stockList;
  863.             }
  864.         });
  865.     }
  867.     public function getAsGoodsSetPurchase($flattened = false)
  868.     {
  869.         return self::getAsGoodsSetPurchaseById($this->getGoodsId(), $flattened);
  870.     }
  872.     public static function getAsGoodsSetPurchaseById($goodsId, $flattened = false)
  873.     {
  874.         return CacheService::getCached(function () use ($goodsId, $flattened) {
  875.             //
  876.             $goodsSetPurchase = CacheService::getCached(function () use ($goodsId) {
  877.                 return (new SqlService())
  878.                     ->Select(implode(', ', [
  879.                         'T.gsp_type',
  880.                         'T.gsp_name',
  881.                         'T1.goods_id',
  882.                         'T1.goods_main_kataban',
  883.                         'T1.goods_name',
  884.                         'T3.color_name',
  885.                         'T3.color_rgb',
  886.                     ]))
  887.                     ->Table('goods_set_purchase_table')
  888.                     ->Join('goods_table', 'T1.goods_id = T.gsp_goods_child', 'INNER')
  889.                     ->Join('goods_color_table', 'T1.goods_id = T2.gcl_goods AND T.gsp_type > 0')    // メイン品番のカラーは取得しない
  890.                     ->Join('color_table', 'T3.color_id = T2.gcl_color_id AND T.gsp_type > 0')    // メイン品番のカラーは取得しない
  891.                     ->Set('gsp_goods_parent', $goodsId)
  892.                     ->OrderBy('T.gsp_type')
  893.                     ->FindAll()
  894.                     ;
  895.             });
  896.             //
  897.             if ($flattened) {
  898.                 //
  899.                 $result = [];
  900.                 //
  901.                 foreach ($goodsSetPurchase as $goods) {
  902.                     //
  903.                     if ($type = GoodsSetPurchase::TYPE_LIST[$goods['gsp_type']])
  904.                         //
  905.                         foreach ($goods as $key => $value) {
  906.                             //
  907.                             $result["{$type['name']}_{$key}"] = $value;
  908.                         }
  909.                 }
  910.             } else {
  911.                 //
  912.                 $result = $goodsSetPurchase;
  913.             }
  914.             //
  915.             return $result;
  916.         });
  917.     }
  919.     /**
  920.      * @return GoodsSetPurchaseExtended[]
  921.      */
  922.     public function getChildrenAsGoodsSetPurchase()
  923.     {
  924.         return GoodsSetPurchaseExtended::getListByParentGoodsId($this->getGoodsId());
  925.     }
  927.     public static function getFeedGoodsByIdForYDN($goodsId, $quantity = 1)
  928.     {
  929.         //
  930.         $feedGoods = self::getFeedGoodsById($goodsId);
  931.         //
  932.         return array(
  933.             'item_id' => $feedGoods['goods_id'],
  934.             'category_id' => $feedGoods['categoryId'],
  935.             'price' => ((int)($feedGoods['sale_price'])),
  936.             'quantity' => $quantity,
  937.         );
  938.     }
  940.     public static function getFeedGoodsByIdForGDN($goodsId, $google_business_vertical = 'retail')
  941.     {
  942.         //
  943.         $feedGoods = self::getFeedGoodsById($goodsId);
  944.         //
  945.         $gdn = array(
  946.             'id' => $feedGoods['goods_id'],
  947.         );
  948.         if (!empty($google_business_vertical)) {
  949.             $gdn['google_business_vertical'] = $google_business_vertical;
  950.         }
  951.         return $gdn;
  952.     }
  954.     /**
  955.      * @param $goodsId
  956.      * @return string
  957.      * @link
  958.      */
  959.     public static function getFeedGoodsById($goodsId)
  960.     {
  961.         //
  962.         $result = (new SqlService())
  963.             ->Select("
  964.                     T.goods_id
  965.                 ,    T3.jan_shiire_no_list
  966.                 ,    T2.price
  967.                 ,    T2.sale_price
  968.                 ,    T1.categoryId
  969.                 ,    T1.categoryName
  970.                 ,    T3.stock
  971.                 ,    T3.lm_stock
  972.                 ,    T3.ki_date
  973.                 ,    T3.zero_period
  974.                 ,    T4.rating
  975.                 ,    T4.reviews
  976.             ")
  977.             ->Table('goods_table')
  978.             ->Join('feed_goods_category', 'T.goods_id = T1.goods_id', 'INNER')
  979.             ->Join('feed_goods_price', 'T.goods_id = T2.goods_id', 'INNER')
  980.             ->Join('feed_goods_stock', 'T.goods_id = T3.goods_id', 'INNER')
  981.             ->Join('feed_goods_review', 'T.goods_id = T4.goods_id', 'LEFT')
  982.             ->Set('T.goods_id', $goodsId)
  983.             ->Find()
  984.         ;
  985.         //
  986.         return $result;
  987.     }
  989.     /**
  990.      * @return Item
  991.      */
  992.     public function getItem()
  993.     {
  994.         return self::compute($this->item, function () {
  995.             return new Item($this);
  996.         });
  997.     }
  999.     /**
  1000.      * @return TargetGenderLabel
  1001.      */
  1002.     public function getTargetGenderLabel()
  1003.     {
  1004.         return self::compute($this->targetGenderLabel, function () {
  1005.             try {
  1006.                 return TargetGenderLabel::getById($this->getGoodsTargetGender());
  1007.             } catch(\Exception $e) {
  1008.                 return null;
  1009.             }
  1010.         });
  1011.     }
  1013.     /**
  1014.      * @return CategoryListIconLabel
  1015.      */
  1016.     public function getCategoryListIconLabel()
  1017.     {
  1018.         return self::compute($this->categoryListIconLabel, function () {
  1019.             try {
  1020.                 return CategoryListIconLabel::getById($this->getGoodsCategoryListIconId());
  1021.             } catch(\Exception $e) {
  1022.                 return null;
  1023.             }
  1024.         });
  1025.     }
  1027.     /**
  1028.      * @return VendingStatusLabel
  1029.      */
  1030.     public function getVendingStatusLabel()
  1031.     {
  1032.         return self::compute($this->vendingStatusLabel, function () {
  1033.             try {
  1034.                 $status = $this->getGoodsVendingStatus();
  1035.                 if ($status === Goods::VENDING_STATUS_EXCLUDE_MANUFACTURER_STOCK) {
  1036.                     $status = Goods::VENDING_STATUS_VENDING;
  1037.                 }
  1038.                 return VendingStatusLabel::getById($status);
  1039.             } catch(\Exception $e) {
  1040.                 return null;
  1041.             }
  1042.         });
  1043.     }
  1045.     /**
  1046.      * @return ColorNumberLabel
  1047.      */
  1048.     public function getColorNumberLabel()
  1049.     {
  1050.         return self::compute($this->colorNumberLabel, function () {
  1051.             try {
  1052.                 return ColorNumberLabel::getById(count($this->getItem()->getColorList()));
  1053.             } catch(\Exception $e) {
  1054.                 return null;
  1055.             }
  1056.         });
  1057.     }
  1059.     /**
  1060.      * @return Label[]
  1061.      */
  1062.     public function getLabelList()
  1063.     {
  1064.         return self::compute($this->labelList, function () {
  1065.             return array_filter([
  1066.                 $this->getTargetGenderLabel(),
  1067.                 $this->getColorNumberLabel(),
  1068.                 $this->getCategoryListIconLabel(),
  1069.             ], function ($label) {
  1070.                 return $label !== null;
  1071.             });
  1072.         });
  1073.     }
  1075.     /**
  1076.      * @return mixed
  1077.      * @throws \Exception
  1078.      */
  1079.     public function isSokujitsuHassouAvailable()
  1080.     {
  1081.         //
  1082.         return self::compute($this->isSokujitsuHassouAvailable, function () {
  1083.             //
  1084.             return (new SokujitsuHassou())
  1085.                 ->check($this->getGoodsId(), false) > SokujitsuHassou::FLG_NONE
  1086.             ;
  1087.         });
  1088.     }
  1090. }