-- 商品ランキングを作成する関数
-- DROP FUNCTION f_upd_ranking ();

CREATE OR REPLACE FUNCTION f_upd_ranking() RETURNS integer AS $$
DECLARE
    _rec RECORD;
    _rankingrate integer;
    _s varchar(4000);
    _rankingcd integer;
BEGIN

  -- 作品ランキング 100位まで
  -- ランク計算の比率は
  -- アクセス数 : 売上数 = 1 : 100
  _s := 'SELECT * FROM (
  SELECT i.itemcd, i.itemnm, COALESCE(o.count,0)+SUM(a.refercount)*100 AS refercount
    FROM t_item_basic_attr i
      INNER JOIN t_item_web_attr web
      ON i.itemcd=web.itemcd AND web.releasedate <= CURRENT_DATE
      INNER JOIN t_access a
      ON a.accessdate >= CURRENT_DATE-7 AND i.itemcd = a.itemcd
      LEFT JOIN
	  (SELECT itemcd, count(*) AS count FROM t_order_detail WHERE typediv = 1 GROUP BY itemcd) o
	  ON o.itemcd=a.itemcd
  WHERE
    i.delflg = FALSE
  GROUP BY i.itemcd, i.itemnm, o.count) t
  ORDER BY refercount desc, itemnm
  LIMIT 100;';

  _rankingrate := 1;
  FOR _rec IN EXECUTE _s LOOP

    SELECT rankingcd INTO _rankingcd FROM t_ranking WHERE rankingtype=2
      AND date=CURRENT_DATE AND datadiv=1 AND rankingrate=_rankingrate;
    IF FOUND THEN
      UPDATE t_ranking SET refercd=_rec.itemcd
      WHERE rankingtype=2 AND date=CURRENT_DATE AND datadiv=1 AND rankingrate=_rankingrate;
    ELSE
      INSERT INTO t_ranking (rankingtype, date, datadiv, rankingrate, refercd)
        VALUES (2, CURRENT_DATE, 1, _rankingrate, _rec.itemcd);
    END IF;

--    RAISE NOTICE 'sale ranking % : % ', _rankingrate, _rec.itemcd;
    _rankingrate := _rankingrate + 1;

  END LOOP;

  RETURN 1;
END;
$$ LANGUAGE plpgsql;