Symfony Profiler

WITH base AS (
  
  /* 本販売店のみ */
  SELECT 
    c.id, 
    company_name, 
    stock_number, 
    ct.name AS contract_type_name, 
    contract_type_id, 
    margin_not_included, 
    contract_begin_ymd 
  FROM 
    dtb_chain_store c 
    INNER JOIN mtb_contract_type ct ON c.contract_type_id = ct.id 
  WHERE 
    c.chain_store_status_id = 2 
  UNION 
  SELECT 
    'SYS', 
    '未登録', 
    NULL, 
    NULL, 
    NULL, 
    false, 
    DATE(NOW() - INTERVAL 90 DAY)
), 
coupon AS (
  
  /* 販売店契約と販売店契約（応援プログラム適用）のみ */
  SELECT 
    IFNULL(
      sum_coupon.chain_store_id, 'SYS'
    ) AS chain_store_id, 
    sum_coupon.contract_type_id, 
    SUM(
      CASE WHEN sum_coupon.contract_type_id = 1 
      OR sum_coupon.contract_type_id = 2 
      OR sum_coupon.contract_type_id IS NULL THEN sum_coupon.coupon_total ELSE 0 END
    ) AS coupon_total 
  FROM 
    base 
    LEFT JOIN (
      SELECT 
        c.chain_store_id, 
        cs.contract_type_id, 
        FLOOR(
          SUM(
            (o.subtotal)
          )/ COUNT(o.id)
        ) coupon_total 
      FROM 
        `dtb_shipping` s 
        INNER JOIN (
          SELECT 
            oo.id, 
            oo.customer_id, 
            oo.order_status_id, 
            SUM(oi.price * oi.quantity) subtotal, 
            SUM(oi.tax) taxtotal 
          FROM 
            dtb_order oo 
            LEFT JOIN dtb_order_item oi ON oo.id = oi.order_id 
            LEFT JOIN dtb_product p ON oi.product_id = p.id 
          WHERE 
            oi.order_item_type_id = 1 
            AND p.option_margin_activate = 1 
          GROUP BY 
            oi.order_id, 
            oo.customer_id, 
            oo.order_status_id
        ) o ON s.order_id = o.id 
        LEFT JOIN plg_coupon_order co ON o.id = co.order_id 
        LEFT JOIN plg_coupon c ON co.coupon_id = c.coupon_id 
        LEFT JOIN dtb_chain_store cs ON c.chain_store_id = cs.id 
      WHERE 
        o.order_status_id = 5 
        AND DATE_FORMAT(
          DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), 
          '%Y-%m'
        ) = ? 
      GROUP BY 
        c.chain_store_id, 
        cs.contract_type_id, 
        o.id
    ) sum_coupon ON base.id = sum_coupon.chain_store_id 
    OR sum_coupon.chain_store_id IS NULL 
  GROUP BY 
    sum_coupon.chain_store_id, 
    sum_coupon.contract_type_id
), 
chain AS (
  SELECT 
    c.chain_store_id, 
    cs.contract_type_id, 
    FLOOR(
      SUM(o.payment_total)
    ) AS self_total 
  FROM 
    `dtb_shipping` s 
    INNER JOIN dtb_order o ON s.order_id = o.id 
    INNER JOIN dtb_customer c ON o.customer_id = c.id 
    INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id 
  WHERE 
    o.order_status_id = 5 
    AND DATE_FORMAT(
      DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), 
      '%Y-%m'
    ) = ? 
  GROUP BY 
    c.chain_store_id
), 
chain_notax AS (
  SELECT 
    c.chain_store_id, 
    cs.contract_type_id, 
    FLOOR(
      SUM(o.subtotal)
    ) AS self_total 
  FROM 
    `dtb_shipping` s 
    INNER JOIN (
      SELECT 
        oo.id, 
        oo.customer_id, 
        oo.order_status_id, 
        SUM(oi.price * oi.quantity) subtotal, 
        SUM(oi.tax) taxtotal 
      FROM 
        dtb_order oo 
        LEFT JOIN dtb_order_item oi ON oo.id = oi.order_id 
        LEFT JOIN dtb_product p ON oi.product_id = p.id 
      WHERE 
        oi.order_item_type_id = 1 
        AND p.option_margin_activate = 1 
      GROUP BY 
        oi.order_id, 
        oo.customer_id, 
        oo.order_status_id
    ) o ON s.order_id = o.id 
    INNER JOIN dtb_customer c ON o.customer_id = c.id 
    INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id 
  WHERE 
    o.order_status_id = 5 
    AND DATE_FORMAT(
      DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), 
      '%Y-%m'
    ) = ? 
  GROUP BY 
    c.chain_store_id
), 
chain_total AS (
  
  /* 小売店契約のみ */
  SELECT 
    SUM(self_total) total 
  FROM 
    chain 
  WHERE 
    contract_type_id = 3
), 
chain_total_notax AS (
  
  /* 小売店契約のみ */
  SELECT 
    SUM(self_total) total 
  FROM 
    chain_notax 
  WHERE 
    contract_type_id = 3
), 
support_cnt AS (
  SELECT 
    COUNT(id) cnt 
  FROM 
    base 
  WHERE 
    contract_type_id = 2
), 
upload_cashback AS (
  SELECT 
    chain_store_id, 
    FLOOR(
      SUM(adj_amount)
    ) uploadAmount 
  FROM 
    dtb_upload_cashback_data 
  WHERE 
    reference_ym = ? 
  GROUP BY 
    chain_store_id
), 
summary_memo AS (
  SELECT 
    chain_store_id, 
    FLOOR(
      SUM(adj_amount)
    ) memoAmount 
  FROM 
    dtb_cashback_summary_memo 
  WHERE 
    reference_ym = ? 
  GROUP BY 
    chain_store_id
), 
result AS (
  SELECT 
    base.*, 
    SUM(coupon.coupon_total) AS coupon_total, 
    CASE WHEN base.id = 'SYS' THEN 0 ELSE FLOOR(
      SUM(coupon.coupon_total) * 0.1
    ) END AS coupon_margin, 
    CASE WHEN base.contract_type_id = 3 THEN SUM(chain.self_total) ELSE 0 END AS kouri_self_total, 
    CASE WHEN base.contract_type_id = 2 THEN SUM(chain.self_total) ELSE 0 END AS oen_self_total, 
    CASE WHEN base.contract_type_id = 1 THEN SUM(chain.self_total) ELSE 0 END AS self_total, 
    CASE WHEN base.contract_type_id = 2 THEN 
    /* マージン対象外 */
    CASE WHEN base.margin_not_included 
    OR DATE_FORMAT(
      base.contract_begin_ymd, '%Y-%m'
    ) > ? THEN 0 ELSE 
    /* 最低保証額に満たない場合は必ず「15,000円」をマージンとする。 */
    CASE WHEN FLOOR(
      SUM(
        (chain_total_notax.total * 0.03) / support_cnt.cnt
      )
    ) >= 15000 THEN FLOOR(
      SUM(
        (chain_total_notax.total * 0.03) / support_cnt.cnt
      )
    ) ELSE 15000 END END ELSE 0 END AS support_total, 
    IFNULL(
      SUM(upload_cashback.uploadAmount), 
      0
    ) * 0.1 AS upload_amount 
  FROM 
    base 
    LEFT JOIN coupon ON base.id = coupon.chain_store_id 
    LEFT JOIN chain ON base.id = chain.chain_store_id 
    LEFT JOIN upload_cashback ON base.id = upload_cashback.chain_store_id 
    LEFT JOIN summary_memo ON base.id = summary_memo.chain_store_id 
    LEFT JOIN chain_total ON 1 = 1 
    LEFT JOIN chain_total_notax ON 1 = 1 
    LEFT JOIN support_cnt ON 1 = 1 
  WHERE 
    (
      (
        coupon_total != 0 
        OR self_total != 0 
        OR upload_cashback.uploadAmount != 0 
        OR memoAmount != 0
      ) 
      OR base.contract_type_id = 2
    ) 
  GROUP BY 
    base.id, 
    base.company_name, 
    base.stock_number, 
    base.contract_type_name 
  ORDER BY 
    base.contract_type_name, 
    base.company_name
) 
SELECT 
  result.*, 
  (
    IFNULL(result.coupon_margin, 0) + IFNULL(result.support_total, 0)
  ) AS margin_total 
FROM 
  result

WITH base AS (
                    /* 本販売店のみ */
                    SELECT c.id, company_name, stock_number, ct.name AS contract_type_name, contract_type_id, margin_not_included, contract_begin_ymd
                    FROM   dtb_chain_store c INNER JOIN mtb_contract_type ct ON c.contract_type_id = ct.id
                    WHERE  c.chain_store_status_id = 2
                    UNION
                    SELECT 'SYS', '未登録', NULL, NULL, NULL, false, DATE(NOW()-INTERVAL 90 DAY)
                ), coupon AS (
                    /* 販売店契約と販売店契約（応援プログラム適用）のみ */
                    SELECT IFNULL(sum_coupon.chain_store_id, 'SYS') AS chain_store_id,
                            sum_coupon.contract_type_id,
                            SUM(CASE WHEN sum_coupon.contract_type_id = 1 OR sum_coupon.contract_type_id = 2 OR sum_coupon.contract_type_id IS NULL THEN
                                sum_coupon.coupon_total
                            ELSE
                                0
                            END)  AS coupon_total
                    FROM   base LEFT JOIN (
                        SELECT c.chain_store_id
                                ,cs.contract_type_id
                                ,FLOOR(SUM((o.subtotal))/COUNT(o.id)) coupon_total
                        FROM `dtb_shipping` s 
                                INNER JOIN (
                                        SELECT oo.id, oo.customer_id, oo.order_status_id, SUM(oi.price * oi.quantity) subtotal, SUM(oi.tax) taxtotal
                                        FROM dtb_order oo LEFT JOIN dtb_order_item oi ON oo.id = oi.order_id
                                                LEFT JOIN dtb_product p ON oi.product_id = p.id
                                        WHERE oi.order_item_type_id = 1
                                          AND p.option_margin_activate = 1
                                    GROUP BY oi.order_id, oo.customer_id, oo.order_status_id
                                    ) o ON s.order_id = o.id
                                LEFT JOIN plg_coupon_order co ON o.id = co.order_id
                                LEFT JOIN plg_coupon c ON co.coupon_id = c.coupon_id
                                LEFT JOIN dtb_chain_store cs ON c.chain_store_id = cs.id
                        WHERE o.order_status_id = 5
                          AND DATE_FORMAT(DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), '%Y-%m') = '2024-09' 
                        GROUP BY c.chain_store_id, cs.contract_type_id, o.id
                    ) sum_coupon ON base.id = sum_coupon.chain_store_id OR sum_coupon.chain_store_id IS NULL
                    GROUP BY sum_coupon.chain_store_id, sum_coupon.contract_type_id
                ), chain AS (
                    SELECT c.chain_store_id
                            , cs.contract_type_id
                            ,FLOOR(SUM(o.payment_total)) AS self_total
                    FROM `dtb_shipping` s 
                            INNER JOIN dtb_order o ON s.order_id = o.id
                            INNER JOIN dtb_customer c ON o.customer_id = c.id
                            INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id
                    WHERE o.order_status_id = 5
                    AND DATE_FORMAT(DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), '%Y-%m') = '2024-09'
                    GROUP BY c.chain_store_id
                ), chain_notax AS (
                    SELECT c.chain_store_id
                            , cs.contract_type_id
                            ,FLOOR(SUM(o.subtotal)) AS self_total
                    FROM `dtb_shipping` s 
                            INNER JOIN (
                                    SELECT oo.id, oo.customer_id, oo.order_status_id, SUM(oi.price * oi.quantity) subtotal, SUM(oi.tax) taxtotal
                                    FROM dtb_order oo LEFT JOIN dtb_order_item oi ON oo.id = oi.order_id
                                            LEFT JOIN dtb_product p ON oi.product_id = p.id
                                    WHERE oi.order_item_type_id = 1
                                        AND p.option_margin_activate = 1
                                    GROUP BY oi.order_id, oo.customer_id, oo.order_status_id
                                ) o ON s.order_id = o.id
                            INNER JOIN dtb_customer c ON o.customer_id = c.id
                            INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id
                    WHERE o.order_status_id = 5
                    AND DATE_FORMAT(DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), '%Y-%m') = '2024-09'
                    GROUP BY c.chain_store_id
                ), chain_total AS (
                    /* 小売店契約のみ */
                    SELECT SUM(self_total) total
                    FROM   chain
                    WHERE  contract_type_id = 3
                ), chain_total_notax AS (
                    /* 小売店契約のみ */
                    SELECT SUM(self_total) total
                    FROM   chain_notax
                    WHERE  contract_type_id = 3
                ), support_cnt AS (
                    SELECT COUNT(id) cnt
                    FROM   base
                    WHERE  contract_type_id = 2
                ), upload_cashback AS (
                    SELECT chain_store_id,
                            FLOOR(SUM(adj_amount)) uploadAmount
                      FROM dtb_upload_cashback_data
                     WHERE reference_ym = '2024-09'
                     GROUP BY chain_store_id
                ), summary_memo AS (
                    SELECT chain_store_id,
                            FLOOR(SUM(adj_amount)) memoAmount
                      FROM dtb_cashback_summary_memo
                     WHERE reference_ym = '2024-09'
                     GROUP BY chain_store_id
                ), result AS (
                    SELECT base.*,
                        SUM(coupon.coupon_total) AS coupon_total,
                            CASE WHEN base.id = 'SYS' THEN
                                0
                            ELSE
                                FLOOR(SUM(coupon.coupon_total) * 0.1)
                            END AS coupon_margin,
                            CASE WHEN base.contract_type_id = 3 THEN
                                SUM(chain.self_total) 
                            ELSE
                                0
                            END AS kouri_self_total,
                            CASE WHEN base.contract_type_id = 2 THEN
                                SUM(chain.self_total)
                            ELSE
                                0
                            END AS oen_self_total,                            
                            CASE WHEN base.contract_type_id = 1 THEN
                                SUM(chain.self_total) 
                            ELSE
                                0
                            END AS self_total,
                            CASE WHEN base.contract_type_id = 2 THEN
                                /* マージン対象外 */
                                CASE WHEN base.margin_not_included OR DATE_FORMAT(base.contract_begin_ymd, '%Y-%m') > '2024-09' THEN
                                    0
                                ELSE
                                    /* 最低保証額に満たない場合は必ず「15,000円」をマージンとする。 */
                                    CASE WHEN FLOOR(SUM((chain_total_notax.total * 0.03) / support_cnt.cnt)) >= 15000 THEN
                                        FLOOR(SUM((chain_total_notax.total * 0.03) / support_cnt.cnt))
                                    ELSE
                                        15000
                                    END
                                END
                            ELSE
                                0
                            END AS support_total,
                            IFNULL(SUM(upload_cashback.uploadAmount),0) * 0.1  AS upload_amount
                    FROM   base
                            LEFT JOIN coupon ON base.id = coupon.chain_store_id
                            LEFT JOIN chain ON base.id = chain.chain_store_id
                            LEFT JOIN upload_cashback ON base.id = upload_cashback.chain_store_id
                            LEFT JOIN summary_memo ON base.id = summary_memo.chain_store_id
                            LEFT JOIN chain_total ON 1 = 1
                            LEFT JOIN chain_total_notax ON 1 = 1
                            LEFT JOIN support_cnt ON 1 = 1
                    WHERE ((coupon_total != 0
                            OR self_total != 0
                            OR upload_cashback.uploadAmount != 0
                            OR memoAmount != 0)
                        OR base.contract_type_id = 2)
                    GROUP BY base.id, base.company_name, base.stock_number, base.contract_type_name
                    ORDER BY base.contract_type_name, base.company_name
                )

                SELECT result.*,
                        (IFNULL(result.coupon_margin,0) + IFNULL(result.support_total,0)) AS margin_total
                FROM   result
         ;

WITH base AS (
  
  /* 本販売店のみ */
  SELECT 
    c.id, 
    c.dealer_code, 
    c.company_name, 
    c.stock_number, 
    ct.name AS contract_type_name, 
    contract_type_id, 
    contract_begin_ymd 
  FROM 
    dtb_chain_store c 
    INNER JOIN mtb_contract_type ct ON c.contract_type_id = ct.id 
  WHERE 
    c.chain_store_status_id = 2 
  UNION 
  SELECT 
    'SYS', 
    NULL, 
    '未登録', 
    NULL, 
    NULL, 
    NULL, 
    DATE(NOW() - INTERVAL 90 DAY)
), 
sales AS (
  
  /* 販売店契約と販売店契約（応援プログラム適用）のみ */
  SELECT 
    IFNULL(sum_sales.chain_store_id, 'SYS') AS chain_store_id, 
    sum_sales.contract_type_id, 
    SUM(
      CASE WHEN contract_type_id = 1 
      OR contract_type_id = 2 
      OR contract_type_id IS NULL THEN sum_sales.sales_total ELSE 0 END
    ) AS sales_total 
  FROM 
    (
      SELECT 
        c.chain_store_id, 
        cs.contract_type_id, 
        FLOOR(
          SUM(
            (o.subtotal)
          )/ COUNT(o.id)
        ) sales_total 
      FROM 
        `dtb_shipping` s 
        INNER JOIN (
          SELECT 
            oo.id, 
            oo.customer_id, 
            oo.order_status_id, 
            SUM(oi.price * oi.quantity) subtotal, 
            SUM(oi.tax) taxtotal 
          FROM 
            dtb_order oo 
            LEFT JOIN dtb_order_item oi ON oo.id = oi.order_id 
            LEFT JOIN dtb_product p ON oi.product_id = p.id 
          WHERE 
            oi.order_item_type_id = 1 
            AND p.option_margin_activate = 1 
          GROUP BY 
            oi.order_id, 
            oo.customer_id, 
            oo.order_status_id
        ) o ON s.order_id = o.id 
        LEFT JOIN plg_coupon_order co ON o.id = co.order_id 
        LEFT JOIN plg_coupon c ON co.coupon_id = c.coupon_id 
        LEFT JOIN dtb_chain_store cs ON c.chain_store_id = cs.id 
      WHERE 
        o.order_status_id = 5 
        AND DATE_FORMAT(
          DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), 
          '%Y-%m'
        ) = ? 
      GROUP BY 
        c.chain_store_id, 
        cs.contract_type_id, 
        o.id
    ) sum_sales 
  GROUP BY 
    sum_sales.chain_store_id, 
    sum_sales.contract_type_id
), 
chain AS (
  SELECT 
    c.chain_store_id, 
    cs.contract_type_id, 
    FLOOR(
      SUM(o.payment_total)
    ) AS self_total 
  FROM 
    `dtb_shipping` s 
    INNER JOIN dtb_order o ON s.order_id = o.id 
    INNER JOIN dtb_customer c ON o.customer_id = c.id 
    INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id 
  WHERE 
    o.order_status_id = 5 
    AND DATE_FORMAT(
      DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), 
      '%Y-%m'
    ) = ? 
  GROUP BY 
    c.chain_store_id
), 
chain_notax AS (
  SELECT 
    c.chain_store_id, 
    cs.contract_type_id, 
    FLOOR(
      SUM(o.subtotal)
    ) AS self_total 
  FROM 
    `dtb_shipping` s 
    INNER JOIN (
      SELECT 
        oo.id, 
        oo.customer_id, 
        oo.order_status_id, 
        SUM(oi.price * oi.quantity) subtotal, 
        SUM(oi.tax) taxtotal 
      FROM 
        dtb_order oo 
        LEFT JOIN dtb_order_item oi ON oo.id = oi.order_id 
        LEFT JOIN dtb_product p ON oi.product_id = p.id 
      WHERE 
        oi.order_item_type_id = 1 
        AND p.option_margin_activate = 1 
      GROUP BY 
        oi.order_id, 
        oo.customer_id, 
        oo.order_status_id
    ) o ON s.order_id = o.id 
    INNER JOIN dtb_customer c ON o.customer_id = c.id 
    INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id 
  WHERE 
    o.order_status_id = 5 
    AND DATE_FORMAT(
      DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), 
      '%Y-%m'
    ) = ? 
  GROUP BY 
    c.chain_store_id
), 
upload_cashback AS (
  SELECT 
    chain_store_id, 
    FLOOR(
      SUM(adj_amount)
    ) uploadAmount 
  FROM 
    dtb_upload_cashback_data 
  WHERE 
    reference_ym = ? 
  GROUP BY 
    chain_store_id
), 
result AS (
  SELECT 
    base.*, 
    SUM(sales.sales_total) AS sales_total, 
    CASE WHEN base.id = 'SYS' THEN 0 ELSE FLOOR(
      (
        IFNULL(
          SUM(sales.sales_total), 
          0
        ) + IFNULL(
          SUM(upload_cashback.uploadAmount), 
          0
        )
      ) * 0.03
    ) END AS sales_margin, 
    CASE WHEN base.contract_type_id = 3 THEN SUM(chain_notax.self_total) ELSE 0 END AS kouri_self_total, 
    CASE WHEN base.contract_type_id = 2 THEN 0 
    /* SUM(chain.self_total)  */
    ELSE 0 END AS oen_self_total, 
    CASE WHEN base.contract_type_id = 1 THEN SUM(chain_notax.self_total) ELSE 0 END AS self_total, 
    CASE WHEN base.contract_type_id = 1 THEN FLOOR(
      SUM(chain_notax.self_total) * 0.03
    ) ELSE 0 END AS chain_total 
  FROM 
    base 
    LEFT JOIN sales ON base.id = sales.chain_store_id 
    LEFT JOIN chain ON base.id = chain.chain_store_id 
    LEFT JOIN chain_notax ON base.id = chain_notax.chain_store_id 
    LEFT JOIN upload_cashback ON base.id = upload_cashback.chain_store_id 
  WHERE 
    (
      sales_total != 0 
      OR chain.self_total != 0 
      OR upload_cashback.uploadAmount != 0
    ) 
  GROUP BY 
    base.id, 
    base.dealer_code, 
    base.company_name, 
    base.stock_number, 
    base.contract_type_name 
  ORDER BY 
    base.contract_type_name, 
    base.company_name
) 
SELECT 
  result.*, 
  (
    IFNULL(result.sales_margin, 0) + IFNULL(result.chain_total, 0)
  ) AS margin_total 
FROM 
  result

WITH base AS (
                    /* 本販売店のみ */
                    SELECT c.id, c.dealer_code, c.company_name, c.stock_number, ct.name AS contract_type_name, contract_type_id, contract_begin_ymd
                    FROM   dtb_chain_store c INNER JOIN mtb_contract_type ct ON c.contract_type_id = ct.id
                    WHERE  c.chain_store_status_id = 2
                    UNION
                    SELECT 'SYS', NULL, '未登録', NULL, NULL, NULL, DATE(NOW()-INTERVAL 90 DAY)
                ), sales AS (
                    /* 販売店契約と販売店契約（応援プログラム適用）のみ */
                    SELECT IFNULL(sum_sales.chain_store_id, 'SYS') AS chain_store_id,
                            sum_sales.contract_type_id,
                            SUM(CASE WHEN contract_type_id = 1 OR contract_type_id = 2 OR contract_type_id IS NULL THEN
                            sum_sales.sales_total
                            ELSE
                                0
                            END)  AS sales_total
                    FROM   (
                        SELECT c.chain_store_id
                                ,cs.contract_type_id
                                ,FLOOR(SUM((o.subtotal))/COUNT(o.id)) sales_total
                        FROM `dtb_shipping` s 
                                INNER JOIN (
                                        SELECT oo.id, oo.customer_id, oo.order_status_id, SUM(oi.price * oi.quantity) subtotal, SUM(oi.tax) taxtotal
                                        FROM dtb_order oo LEFT JOIN dtb_order_item oi ON oo.id = oi.order_id
                                                        LEFT JOIN dtb_product p ON oi.product_id = p.id
                                        WHERE oi.order_item_type_id = 1
                                          AND p.option_margin_activate = 1
                                    GROUP BY oi.order_id, oo.customer_id, oo.order_status_id
                                    ) o ON s.order_id = o.id
                                LEFT JOIN plg_coupon_order co ON o.id = co.order_id
                                LEFT JOIN plg_coupon c ON co.coupon_id = c.coupon_id
                                LEFT JOIN dtb_chain_store cs ON c.chain_store_id = cs.id
                        WHERE o.order_status_id = 5
                        AND DATE_FORMAT(DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), '%Y-%m') = '2024-09'
                        GROUP BY c.chain_store_id, cs.contract_type_id, o.id
                    ) sum_sales
                    GROUP BY sum_sales.chain_store_id, sum_sales.contract_type_id
                ), chain AS (
                    SELECT c.chain_store_id
                            , cs.contract_type_id
                            ,FLOOR(SUM(o.payment_total)) AS self_total
                    FROM `dtb_shipping` s 
                            INNER JOIN dtb_order o ON s.order_id = o.id
                            INNER JOIN dtb_customer c ON o.customer_id = c.id
                            INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id
                    WHERE o.order_status_id = 5
                    AND DATE_FORMAT(DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), '%Y-%m') = '2024-09'
                    GROUP BY c.chain_store_id
                ), chain_notax AS (
                    SELECT c.chain_store_id
                            , cs.contract_type_id
                            ,FLOOR(SUM(o.subtotal)) AS self_total
                    FROM `dtb_shipping` s 
                            INNER JOIN (
                                      SELECT oo.id, oo.customer_id, oo.order_status_id, SUM(oi.price * oi.quantity) subtotal, SUM(oi.tax) taxtotal
                                        FROM dtb_order oo LEFT JOIN dtb_order_item oi ON oo.id = oi.order_id
                                                        LEFT JOIN dtb_product p ON oi.product_id = p.id
                                        WHERE oi.order_item_type_id = 1
                                          AND p.option_margin_activate = 1
                                     GROUP BY oi.order_id, oo.customer_id, oo.order_status_id
                                ) o ON s.order_id = o.id
                            INNER JOIN dtb_customer c ON o.customer_id = c.id
                            INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id
                    WHERE o.order_status_id = 5
                    AND DATE_FORMAT(DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), '%Y-%m') = '2024-09'
                    GROUP BY c.chain_store_id
                ), upload_cashback AS (
                    SELECT chain_store_id,
                            FLOOR(SUM(adj_amount)) uploadAmount
                      FROM dtb_upload_cashback_data
                     WHERE reference_ym = '2024-09'
                     GROUP BY chain_store_id
                ), result AS (
                    SELECT base.*,
                        SUM(sales.sales_total) AS sales_total,
                            CASE WHEN base.id = 'SYS' THEN
                                0
                            ELSE
                                FLOOR((IFNULL(SUM(sales.sales_total),0) + IFNULL(SUM(upload_cashback.uploadAmount),0)) * 0.03)
                            END AS sales_margin,
                            CASE WHEN base.contract_type_id = 3 THEN
                                SUM(chain_notax.self_total) 
                            ELSE
                                0
                            END AS kouri_self_total,
                            CASE WHEN base.contract_type_id = 2 THEN
                                0 /* SUM(chain.self_total)  */
                            ELSE
                                0
                            END AS oen_self_total,
                            CASE WHEN base.contract_type_id = 1 THEN
                                SUM(chain_notax.self_total) 
                            ELSE
                                0
                            END AS self_total,
                            CASE WHEN base.contract_type_id = 1 THEN
                                FLOOR(SUM(chain_notax.self_total) * 0.03)
                            ELSE
                                0
                            END AS chain_total
                    FROM   base
                            LEFT JOIN sales ON base.id = sales.chain_store_id
                            LEFT JOIN chain ON base.id = chain.chain_store_id
                            LEFT JOIN chain_notax ON base.id = chain_notax.chain_store_id
                            LEFT JOIN upload_cashback ON base.id = upload_cashback.chain_store_id
                    WHERE (sales_total != 0
                    OR chain.self_total != 0
                    OR upload_cashback.uploadAmount != 0)
                    GROUP BY base.id, base.dealer_code, base.company_name, base.stock_number, base.contract_type_name
                    ORDER BY base.contract_type_name, base.company_name
                )

                SELECT result.*,
                        (IFNULL(result.sales_margin,0) + IFNULL(result.chain_total, 0)) AS margin_total
                FROM   result
        ;

WITH base AS (
  SELECT 
    c.chain_store_id, 
    cs.contract_type_id, 
    o.category_id, 
    SUM(o.quantity) quantity, 
    SUM(o.subtotal) subtotal 
  FROM 
    dtb_shipping s 
    INNER JOIN (
      SELECT 
        oo.id, 
        oo.customer_id, 
        pt.category_id, 
        SUM(
          CASE WHEN oi.order_item_type_id = 7 THEN oi.quantity ELSE oi.quantity * IFNULL(pc.deliveryplus_size, 1) END
        ) quantity, 
        SUM(
          CASE WHEN oi.order_item_type_id = 7 THEN pc.price02 * oi.quantity ELSE oi.price * oi.quantity END
        ) subtotal 
      FROM 
        dtb_order oo 
        INNER JOIN dtb_order_item oi ON oo.id = oi.order_id 
        INNER JOIN dtb_product p ON oi.product_id = p.id 
        INNER JOIN dtb_product_class pc ON pc.id = oi.product_class_id 
        INNER JOIN dtb_product_category pt ON oi.product_id = pt.product_id 
      WHERE 
        oi.order_item_type_id IN (1, 7) 
        AND oo.order_status_id = 5 
        AND oi.product_id NOT IN (
          SELECT 
            product_id 
          FROM 
            plg_ks_product
        ) 
      GROUP BY 
        oi.order_id, 
        oo.customer_id, 
        pt.category_id
    ) o ON s.order_id = o.id 
    INNER JOIN dtb_customer c ON o.customer_id = c.id 
    INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id 
  WHERE 
    DATE_FORMAT(
      DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), 
      '%Y-%m'
    ) = ? 
    AND cs.contract_type_id = 1 
    AND cs.chain_store_status_id = 2 
    AND o.category_id IN (1, 5) 
  GROUP BY 
    c.chain_store_id, 
    cs.contract_type_id, 
    o.category_id
), 
result_price AS (
  SELECT 
    base.*, 
    (subtotal / 0.6) list_price, 
    CASE WHEN category_id = 1 THEN CASE WHEN quantity >= 4020 THEN 'net50' ELSE CASE WHEN quantity >= 820 THEN 'net55' ELSE 'net60' END END ELSE CASE WHEN quantity >= 1506 THEN 'net50' ELSE CASE WHEN quantity >= 306 THEN 'net55' ELSE 'net60' END END END percentage 
  FROM 
    base
) 
SELECT 
  result_price.*, 
  '' category_name, 
  0 diff_price, 
  '' memo 
FROM 
  result_price

WITH base AS (
                    SELECT c.chain_store_id
                            ,cs.contract_type_id
                            ,o.category_id
                            ,SUM(o.quantity) quantity
                            ,SUM(o.subtotal) subtotal
                    FROM    dtb_shipping s 
                            INNER JOIN (

                                SELECT oo.id, oo.customer_id, pt.category_id,
                                            SUM(CASE WHEN oi.order_item_type_id  = 7 THEN
                                                    oi.quantity
                                                ELSE
                                                    oi.quantity * IFNULL(pc.deliveryplus_size,1)
                                                END)  quantity,
                                            SUM(CASE WHEN oi.order_item_type_id  = 7 THEN
                                                    pc.price02 * oi.quantity
                                                ELSE
                                                    oi.price * oi.quantity
                                                END) subtotal
                                    FROM dtb_order oo 
                                                INNER JOIN dtb_order_item oi ON oo.id = oi.order_id
                                                INNER JOIN dtb_product p ON oi.product_id = p.id
                                                INNER JOIN dtb_product_class pc ON pc.id = oi.product_class_id
                                                INNER JOIN dtb_product_category pt ON oi.product_id = pt.product_id
                                    WHERE oi.order_item_type_id IN (1,7)
                                    AND oo.order_status_id = 5 
                                    AND oi.product_id NOT IN (SELECT product_id FROM plg_ks_product) 
                                GROUP BY oi.order_id, oo.customer_id, pt.category_id

                                ) o ON s.order_id = o.id
                            INNER JOIN dtb_customer c ON o.customer_id = c.id
                            INNER JOIN dtb_chain_store cs ON c.chain_store_id = cs.id
                    WHERE DATE_FORMAT(DATE_ADD(s.shipping_date, INTERVAL 9 HOUR), '%Y-%m') = '2024-08'
                    AND cs.contract_type_id = 1
                    AND cs.chain_store_status_id = 2
                    AND o.category_id IN (1,5)
                    GROUP BY c.chain_store_id, cs.contract_type_id, o.category_id
                ), result_price AS (
                    SELECT base.*
                        ,(subtotal / 0.6) list_price
                        ,CASE WHEN category_id = 1 THEN
                            CASE WHEN quantity >= 4020 THEN
                                'net50'
                            ELSE
                                CASE WHEN quantity >= 820 THEN
                                    'net55'
                                ELSE
                                    'net60'
                                END
                            END
                        ELSE
                            CASE WHEN quantity >= 1506 THEN
                                'net50'
                            ELSE
                                CASE WHEN quantity >= 306 THEN
                                    'net55'
                                ELSE
                                    'net60'
                                END
                            END
                        END percentage
                    FROM   base
                )

                SELECT result_price.*, '' category_name, 0 diff_price, '' memo
                FROM   result_price
        ;

SELECT 
  t0.id AS id_1, 
  t0.corporate_number AS corporate_number_2, 
  t0.stock_number AS stock_number_3, 
  t0.timestamp AS timestamp_4, 
  t0.contract_begin_ymd AS contract_begin_ymd_5, 
  t0.auto_usage_fee_begin_ymd AS auto_usage_fee_begin_ymd_6, 
  t0.auto_usage_fee_end_ymd AS auto_usage_fee_end_ymd_7, 
  t0.company_name AS company_name_8, 
  t0.company_name_kana AS company_name_kana_9, 
  t0.torokubango AS torokubango_10, 
  t0.begin_day AS begin_day_11, 
  t0.chainstore_postal_code AS chainstore_postal_code_12, 
  t0.chainstore_address_full AS chainstore_address_full_13, 
  t0.chainstore_address01 AS chainstore_address01_14, 
  t0.chainstore_address02 AS chainstore_address02_15, 
  t0.addr01_ka AS addr01_ka_16, 
  t0.full_name AS full_name_17, 
  t0.name01 AS name01_18, 
  t0.name02 AS name02_19, 
  t0.kana01 AS kana01_20, 
  t0.kana02 AS kana02_21, 
  t0.contact_phone_number AS contact_phone_number_22, 
  t0.cellphone_number AS cellphone_number_23, 
  t0.mediator_company_name AS mediator_company_name_24, 
  t0.mediator_company_name_kana AS mediator_company_name_kana_25, 
  t0.mediator_fullname AS mediator_fullname_26, 
  t0.mediator_name01 AS mediator_name01_27, 
  t0.mediator_name02 AS mediator_name02_28, 
  t0.mediator_kana01 AS mediator_kana01_29, 
  t0.mediator_kana02 AS mediator_kana02_30, 
  t0.mediator_address01 AS mediator_address01_31, 
  t0.dealer_code AS dealer_code_32, 
  t0.mediator_phone_number AS mediator_phone_number_33, 
  t0.bank_branch_id AS bank_branch_id_34, 
  t0.bank_account AS bank_account_35, 
  t0.code_number AS code_number_36, 
  t0.account_number AS account_number_37, 
  t0.bank_holder_name AS bank_holder_name_38, 
  t0.bank_holder AS bank_holder_39, 
  t0.chainstore_business_other_type AS chainstore_business_other_type_40, 
  t0.chainstore_name AS chainstore_name_41, 
  t0.chainstore_name_kana AS chainstore_name_kana_42, 
  t0.operating_name AS operating_name_43, 
  t0.operating_kana AS operating_kana_44, 
  t0.postal_code AS postal_code_45, 
  t0.addr02 AS addr02_46, 
  t0.addr03 AS addr03_47, 
  t0.main_name01 AS main_name01_48, 
  t0.main_name02 AS main_name02_49, 
  t0.main_kana01 AS main_kana01_50, 
  t0.main_kana02 AS main_kana02_51, 
  t0.phone_number AS phone_number_52, 
  t0.chainstore_email AS chainstore_email_53, 
  t0.option_webshop AS option_webshop_54, 
  t0.webshop_name AS webshop_name_55, 
  t0.webshop_url AS webshop_url_56, 
  t0.chainstore_webshop_opening_other_type AS chainstore_webshop_opening_other_type_57, 
  t0.webshop_main_operation_name AS webshop_main_operation_name_58, 
  t0.chainstore_webshop_phone_other_type AS chainstore_webshop_phone_other_type_59, 
  t0.chainstore_webshop_mail_other_type AS chainstore_webshop_mail_other_type_60, 
  t0.option_partner AS option_partner_61, 
  t0.partner_company_name AS partner_company_name_62, 
  t0.partner_company_name_kana AS partner_company_name_kana_63, 
  t0.partner_name01 AS partner_name01_64, 
  t0.partner_name02 AS partner_name02_65, 
  t0.partner_kana01 AS partner_kana01_66, 
  t0.partner_kana02 AS partner_kana02_67, 
  t0.partner_phone_number AS partner_phone_number_68, 
  t0.shop_day_off AS shop_day_off_69, 
  t0.shop_opening_time AS shop_opening_time_70, 
  t0.shop_website_url AS shop_website_url_71, 
  t0.shop_website_isopen AS shop_website_isopen_72, 
  t0.negotiate_start_date AS negotiate_start_date_73, 
  t0.memo AS memo_74, 
  t0.shop_image AS shop_image_75, 
  t0.shop_shikaku AS shop_shikaku_76, 
  t0.shop_todokede AS shop_todokede_77, 
  t0.shop_kyoka AS shop_kyoka_78, 
  t0.coupon_name_option AS coupon_name_option_79, 
  t0.note AS note_80, 
  t0.create_date AS create_date_81, 
  t0.update_date AS update_date_82, 
  t0.option_order_limit AS option_order_limit_83, 
  t0.order_limit_text AS order_limit_text_84, 
  t0.margin_price AS margin_price_85, 
  t0.margin_not_included AS margin_not_included_86, 
  t0.margin_not_included_update_time AS margin_not_included_update_time_87, 
  t0.purchasing_limit_price AS purchasing_limit_price_88, 
  t0.delivery_registrations AS delivery_registrations_89, 
  t0.sort_no AS sort_no_90, 
  t0.special AS special_91, 
  t0.owner_same_representative AS owner_same_representative_92, 
  t0.owner_name01 AS owner_name01_93, 
  t0.owner_name02 AS owner_name02_94, 
  t0.owner_kana01 AS owner_kana01_95, 
  t0.owner_kana02 AS owner_kana02_96, 
  t0.owner_postal_code AS owner_postal_code_97, 
  t0.owner_address01 AS owner_address01_98, 
  t0.owner_address02 AS owner_address02_99, 
  t0.owner_addr01_ka AS owner_addr01_ka_100, 
  t0.owner_department_name AS owner_department_name_101, 
  t0.owner_phone_number AS owner_phone_number_102, 
  t0.owner_email AS owner_email_103, 
  t0.direct_contract AS direct_contract_104, 
  t0.pre_chainstore_id AS pre_chainstore_id_105, 
  t0.contract_type_id AS contract_type_id_106, 
  t0.applicant_contract_type_id AS applicant_contract_type_id_107, 
  t0.auto_usage_fee_payment_id AS auto_usage_fee_payment_id_108, 
  t0.pref_id AS pref_id_109, 
  t0.chainstore_trading_account_type_id AS chainstore_trading_account_type_id_110, 
  t0.bank_id AS bank_id_111, 
  t0.bank_account_type_id AS bank_account_type_id_112, 
  t0.related_chainstore_type_id AS related_chainstore_type_id_113, 
  t0.chainstore_business_type_id AS chainstore_business_type_id_114, 
  t0.chainstore_provide_type_id AS chainstore_provide_type_id_115, 
  t0.chainstore_provide_style_type_id AS chainstore_provide_style_type_id_116, 
  t0.addr01_id AS addr01_id_117, 
  t0.chainstore_webshop_opening_type_id AS chainstore_webshop_opening_type_id_118, 
  t0.chainstore_webshop_owner_type_id AS chainstore_webshop_owner_type_id_119, 
  t0.chainstore_webshop_phone_type_id AS chainstore_webshop_phone_type_id_120, 
  t0.chainstore_webshop_mail_type_id AS chainstore_webshop_mail_type_id_121, 
  t0.chain_store_make_contract_type_id AS chain_store_make_contract_type_id_122, 
  t0.chain_store_make_contract_type2_id AS chain_store_make_contract_type2_id_123, 
  t0.chain_store_status_id AS chain_store_status_id_124, 
  t0.owner_pref_id AS owner_pref_id_125, 
  t0.discriminator_type 
FROM 
  dtb_chain_store t0 
WHERE 
  t0.id = ? 
  AND t0.discriminator_type IN ('chainstore') 
LIMIT 
  1