---
# System prepended metadata

title: Certification CP
tags: [cert]

---

# Certification CP

###### tags: `cert`
> [name=RoyHuang]

![](https://i.imgur.com/8FK9D2t.png)


------------------------------------------------------------------------
## 網址
- https://www.ttstaging.com.tw/certification/${公司統編}
  - Ex: https://www.ttstaging.com.tw/certification/03702716

## 程式
- com.hyweb.tt.cms.cm.layoutdefine.consumer.CompanyCertificationConsumer


------------------------------------------------------------------------
## 頁面初始判斷



------------------------------------------------------------------------
## Certification
- 進階認證 + 第三方認證 + taiwan_excellence 認證 + TTS 展場認證
![](https://i.imgur.com/yzyBHKH.png)


### Rule
- 顯示時順序: 進階認證 > 第三方認證 > taiwan_excellence 認證 > TTS 展場認證 
- 進階認證
  - 只要有一個第三方認證，就要顯示進階認證圖片
  - 進階認證只有一張圖
- 第三方認證
  - 都來自系統認證
  - 如果使用使用語法，則套語法；反之，則套用圖片
  - 圖片舊版是抓 attach_picture.scaling_file_url，改抓新圖
    - pc img: 100 * 100
    - mobile img: 100 * 100
    - 圖片 title 放認證名稱
- taiwan_excellence 認證
  - pc img: 100 * 100
  - mobile img: 100 * 100
- TTS 展場認證 
  - 靜態圖片
  - pc img: 100 * 100
  - mobile img: 100 * 100
- 認證多語，預設為 42


### Path
- /front/cert-cp/mix-third
  - GET

### INPUT
Column     | Type     | Require | Desc
-----------|----------|---------|---------
companyId  | number   | Y       | 公司id
languageId | number   | Y       | 語系id


### OUTPUT
```=
{ 
  code      : {{ number }} , // 代碼，0：ok、 < 0: error 使用
  errorMsg  : {{ string }} , // 錯誤訊息
  // 進階認證
  advancedCert: {
    has_cert             : {{ boolean }} , // 是否顯示認證
    name                 : {{ string }}  , // 認證名稱
    img_url              : {{ string }}  , // 圖片url
  },
  // 第三方認證
  thirdRows：[
    {
      "certId"          : {{ number }}  , // 認證id
      //"serial"        : {{ string }}  , // 認證編號
      //"startTime"     : {{ string }}  , // 認證上架日期(起)，YYYY-MM-DD
      //"endTime"       : {{ string }}  , // 認證上架日期(迄)，YYYY-MM-DD
      "certName"        : {{ string }}  , // 認證名稱
      "certDesc"        : {{ string }}  , // 認證說明
      "imgId"           : {{ number }}  , // 圖片id
      "pcImgUrl"        : {{ string }}  , // pc圖片url
      "mobileImgUrl"    : {{ string }}  , // mobile圖片url
      "hasUseHtml"      : {{ boolean }} , // 是否使用語法
      "informationHtml" : {{ string }}  , // 語法
      "lightboxUrl"     : {{ string }}  , // 燈箱url
    }, ...
  ],
  // TTS
  ttsRows：[
    {
      "certId"         : {{ number }}  , // 認證id
      "certName"       : {{ string }}  , // 認證名稱
      "imgId"          : {{ number }}  , // 圖片id
      "pcImgUrl"       : {{ string }}  , // pc圖片url
      "mobileImgUrl"   : {{ string }}  , // mobile圖片url
    }, ...
  ],
}
```

### SQL-進階認證
- has_cert : 是否顯示認證，如果有一筆第三認證就為 true
- name     : "Taiwan TradeShow" (寫死)
- img_url  : "/site/tteng/style/images/public/advanced_cert100X100.png" (寫死)


### SQL-第三方認證
```=
WITH CORE AS (
SELECT cert.cid,
       :languageId AS lang_id, -- 替換語系代碼
       MAX(com_cert.ccid) AS ccid
  FROM company_certification com_cert 
  JOIN certification cert ON com_cert.certification_id = cert.cid
 WHERE com_cert.company_id = :companyId   -- 替換公司代碼
   AND com_cert.certification_type_id = 401
   AND com_cert.shelved = 1 -- 上架
   AND com_cert.verify_status = 1324 -- 審核通過
   AND cert.available = 1 -- 系統認證啟用
   AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(com_cert.start_time, SYSDATE)) AND TRUNC(NVL(com_cert.end_time, SYSDATE)) -- 期間內
       OR cert.expired_time_show_pic = 1 -- 期間外
       )
 GROUP BY cert.cid
)
SELECT x_com_cert.ccid AS cert_id, 
       --x_com_cert.serial,
       --TO_CHAR(x_com_cert.start_time, 'YYYY-MM-DD') AS start_time, 
       --TO_CHAR(x_com_cert.end_time, 'YYYY-MM-DD') AS end_time, 
       
       NVL(
         (SELECT property_value FROM multi_language_value 
           WHERE bean_name = 'Certification' AND property_name = 'name' AND row_id = x_cert.cid AND language_id = cr.lang_id),
         (SELECT property_value FROM multi_language_value 
           WHERE bean_name = 'Certification' AND property_name = 'name' AND row_id = x_cert.cid AND language_id = 42)
       ) AS cert_name,
       
       NVL(
         (SELECT property_value FROM multi_language_value 
           WHERE bean_name = 'Certification' AND property_name = 'description' AND row_id = x_cert.cid AND language_id = cr.lang_id),
         (SELECT property_value FROM multi_language_value 
           WHERE bean_name = 'Certification' AND property_name = 'description' AND row_id = x_cert.cid AND language_id = 42)
       ) AS cert_description,
       
       x_cert.attach_id AS img_id,
       img_pkg.fun_third_cert_img_url(x_cert.attach_id, 100, 100) AS pc_img_url,
       img_pkg.fun_third_cert_img_url(x_cert.attach_id, 100, 100) AS mobile_img_url,

       CASE 
         WHEN (x_cert.USE_HTML = 1 AND SYSDATE BETWEEN NVL(x_com_cert.start_time, SYSDATE) AND NVL(x_com_cert.end_time, SYSDATE)) 
         THEN 1 ELSE 0
       END AS has_use_html,
       
       CASE 
         WHEN (x_cert.use_html = 1 AND SYSDATE BETWEEN NVL(x_com_cert.start_time, SYSDATE) AND NVL(x_com_cert.end_time, SYSDATE)) THEN
           REPLACE(REPLACE(REPLACE(x_cert.information_html, '[ID]', REPLACE(x_com_cert.serial, '-', '')),'[CID]', x_com_cert.company_id), '"', '\"')
         ELSE NULL 
       END AS information_html,

       '/lightbox/certification/' || x_com_cert.ccid AS lightbox_url

  FROM CORE cr
  JOIN company_certification x_com_cert ON x_com_cert.ccid = cr.ccid
  JOIN certification x_cert ON x_cert.cid = x_com_cert.certification_id
 ORDER BY x_com_cert.sort NULLS LAST, x_com_cert.ccid DESC
       ;
```

### fix issue
- 第三方認證啟用時間會有區間，且會有多筆，要抓最大筆，舊版用 java 篩選，效能差
- company_certification_to_type 已廢除，不使用
- 圖片要以系統認證的圖片為主
- 第三方認證與型錄無關
- 因為綁定多語語系，會造成認證資料漏缺。如：中文有，英文沒，網頁撈英文，舊版 java 抓二次篩選，效能差

```=
SELECT company_cert.ccid,
       company_cert.certification_id,
       company_cert.company_id,
       system_cert.description
       AS system_cert_description,
       multi_lang_val.language_id,
       multi_lang_val.bean_name,
       multi_lang_val.property_name,
       multi_lang_val.property_value
       AS NAME,
       (SELECT multi_lang_val2.property_value
          FROM multi_language_value multi_lang_val2
         WHERE multi_lang_val2.row_id = company_cert.certification_id
           AND multi_lang_val2.row_id = multi_lang_val.row_id
           AND multi_lang_val2.language_id = multi_lang_val.language_id
           AND multi_lang_val2.property_name = 'description'
       ) AS description,
       cert_to_type.certification_type_id,
       base.belong_company_id
       AS product_company_id,
       att.original_file_url
       AS scaling_picture_url,
       picture_back_up.scaling_file_url
       AS scaling_picture_backup_url,
       company_cert.start_time,
       company_cert.end_time,
       system_cert.cid
       AS system_cert_cid,
       base.deleted_flag
       AS product_delete_flag,
       company_cert.verify_status,
       company_cert.shelved,
       system_cert.use_html,
       system_cert.information_html,
       company_cert.serial,
       company_cert.sort,
       system_cert.sort
       AS system_cert_sort,
       system_cert.expired_time_show_pic,
       multi_product.actived
       AS product_actived,
       multi_product.verify_status_cid
       AS product_verify_status,
       Decode(
          (SELECT 1
             FROM company_certification
            WHERE ccid = company_cert.ccid
             AND Trunc(sysdate) BETWEEN start_time AND end_time
          ), 1, 1, 0
       )AS date_in_range,

       multi_product.language_id
       AS product_language_id,
       system_cert.available
  FROM company_certification company_cert
  JOIN multi_language_value multi_lang_val 
    ON ( multi_lang_val.row_id = company_cert.certification_id OR multi_lang_val.row_id = company_cert.ccid )
  JOIN company_certification_to_type cert_to_type ON cert_to_type.company_certification_id = company_cert.ccid
  -- 型錄相關
  LEFT JOIN article_product product ON product.pid = company_cert.product_id
  LEFT JOIN multi_article_product multi_product ON multi_product.product_pid = product.pid
  LEFT JOIN article_base base ON product.pid = base.aid
  -- 圖片
  LEFT JOIN attach_picture_scaling s ON s.attach_picture_original_id = company_cert.attach_picture_id
        AND s.width = 100 AND s.height = 100
  LEFT JOIN attach att ON att.aid = s.aid
  LEFT JOIN certification system_cert ON system_cert.cid = company_cert.certification_id
  LEFT JOIN attach_picture picture_back_up ON picture_back_up.aid = system_cert.attach_id
 WHERE company_cert.company_id = 65
   AND company_cert.shelved = 1
   AND company_cert.verify_status = 1324
   AND cert_to_type.certification_type_id = 401
   AND multi_lang_val.language_id = 42
   AND multi_lang_val.property_name = 'name'
   AND ( base.deleted_flag IS NULL OR base.deleted_flag = 0 )
   AND ( multi_lang_val.bean_name = 'Certification' OR multi_lang_val.bean_name ='CompanyCertification' )
 ORDER BY company_cert.start_time DESC 
       ;
```


------------------------------------------------------------------------
### SQL-taiwan_excellence 認證 + TTS 展場認證
```=
WITH CORE AS (
SELECT cert.cid, cert.attach_id, cfa.name, NVL(cfa.display_count, 0), cfa.sort
  FROM COMPANY_CERTIFICATION com_cert
  JOIN CERTIFICATION cert ON cert.cid = com_cert.certification_id
  CROSS JOIN CERTIFICATION_FOCUS_AREA cfa 
 WHERE com_cert.company_id = :companyId -- 套用公司代碼
   AND com_cert.shelved = 1 -- 上架
   AND com_cert.verify_status = 1324 -- 審核通過
   AND certification_type_id = (SELECT cid FROM CODE_META WHERE code = 'taiwan_excellence')
   AND com_cert.certification_id NOT IN (201, 202) -- 排除進階認證
   AND cert.available = 1 -- 系統認證啟用
   AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(com_cert.start_time, SYSDATE)) AND TRUNC(NVL(com_cert.end_time, SYSDATE)) -- 期間內
        OR cert.expired_time_show_pic = 1 -- 期間外
       ) 
   AND cfa.setting_type = 'VERIFIED'
   AND cfa.language = -1 -- AND (cfa.LANGUAGE = -1 OR cfa.LANGUAGE =42)
   AND INSTR(cfa.finale_value||','  ,(',' || com_cert.certification_id || ',') ) > 0
 GROUP BY cert.cid, cert.attach_id, cfa.name, NVL(cfa.display_count, 0), cfa.sort
 HAVING NVL(cfa.display_count, 0) <= COUNT(1)
 ORDER BY cfa.sort NULLS LAST
)
SELECT ROWNUM ORD,
       cr.cid AS cert_id,
       cr.name AS cert_name,
       cr.attach_id AS img_id,
       NVL (
         CASE WHEN ap.scaling_file_url IS NOT NULL AND ap.scaling_file_url NOT LIKE 'http%' THEN 'https:' || ap.scaling_file_url ELSE ap.scaling_file_url END,
         att.original_file_url
       ) AS img_url,
       NVL (
         CASE WHEN ap.scaling_file_url IS NOT NULL AND ap.scaling_file_url NOT LIKE 'http%' THEN 'https:' || ap.scaling_file_url ELSE ap.scaling_file_url END,
         att.original_file_url
       ) AS mobile_img_url,
  FROM CORE cr
  JOIN ATTACH_PICTURE ap ON ap.aid = cr.attach_id
  JOIN ATTACH att ON att.aid = cr.attach_id
UNION ALL
SELECT 99999999999 AS ORD,
       -1 AS cert_id,
       NVL(cfa.NAME, 'Taiwan International Trade Shows'' Exhibitor') AS cert_name,
       -1 AS img_id,
       'https://cs01.' || edm.host_name || '/static/icon-TaiwanTradeShows-100X100.png' AS pc_img_url,
       'https://cs01.' || edm.host_name || '/static/icon-TaiwanTradeShows-100X100.png' AS mobile_img_url
  FROM CERTIFICATION_FOCUS_AREA cfa
  CROSS JOIN ENV_DOMAIN_MAPPING edm 
 WHERE cfa.setting_type = 'VERIFIED'
   AND cfa.LANGUAGE = -1 
   AND INSTR(cfa.finale_value ||','  ,(',TTS,') ) > 0
   AND edm.env = 'current_env'
   AND EXISTS(
         SELECT 1
           FROM TTS_EXHIBITOR e 
           JOIN TTS_SHOWS_YEAR s on s.sy_id = e.sy_id 
           JOIN TTS_MULTI_SHOW_NAME m on m.s_id = SUBSTR(e.sy_id, 1, 2) 
           JOIN COMPANY com on com.seq = e.co_tax_id 
          WHERE com.cid = :companyId           -- 套用公司代碼
            AND m.language_id = :languageId    -- 套用語系代碼 
       )
 ORDER BY ORD
       ;
```

### fix issue
- 展場資訊沒比對此公司的展場歷史資料
- 與 EP 差異
  - 多判斷，排除進階認證
  - CERTIFICATION_FOCUS_AREA.language 沒有特別指定語系
  - 沒判斷
    - com_cert.shelved = 1 -- 上架
    - com_cert.verify_status = 1324 -- 審核通過
    - cert.available = 1 -- 系統認證啟用
    - AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(com_cert.start_time, SYSDATE)) AND TRUNC(NVL(com_cert.end_time, SYSDATE)) -- 期間內
        OR cert.expired_time_show_pic = 1 -- 期間外
       ) 
    - 移除 su 判斷
    - 無排序
- 比對，舊版多個sql 整合如下
```=
WITH CORE AS (
SELECT cert.cid, cert.attach_id, cfa.name, NVL(cfa.display_count, 0), cfa.sort
  FROM COMPANY_CERTIFICATION com_cert
  JOIN CERTIFICATION cert ON cert.cid = com_cert.certification_id
  CROSS JOIN CERTIFICATION_FOCUS_AREA cfa 
 WHERE com_cert.company_id = :companyId -- 套用公司代碼
   AND com_cert.shelved = 1 -- 上架
   AND com_cert.verify_status = 1324 -- 審核通過
   AND certification_type_id = (SELECT cid FROM CODE_META WHERE code = 'taiwan_excellence')
   AND com_cert.certification_id NOT IN (201, 202) -- 排除進階認證
   AND cert.available = 1 -- 系統認證啟用
   AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(com_cert.start_time, SYSDATE)) AND TRUNC(NVL(com_cert.end_time, SYSDATE)) -- 期間內
        OR cert.expired_time_show_pic = 1 -- 期間外
       ) 
   AND cfa.setting_type = 'VERIFIED'
   AND cfa.language = -1 -- AND (cfa.LANGUAGE = -1 OR cfa.LANGUAGE =42)
   AND INSTR(cfa.finale_value||','  ,(',' || com_cert.certification_id || ',') ) > 0
 GROUP BY cert.cid, cert.attach_id, cfa.name, NVL(cfa.display_count, 0), cfa.sort
 HAVING NVL(cfa.display_count, 0) <= COUNT(1)
 ORDER BY cfa.sort NULLS LAST
)
SELECT ROWNUM ORD,
       cr.cid AS cert_id,
       cr.name AS cert_name,
       cr.attach_id AS img_id,
       NVL (
         CASE WHEN ap.scaling_file_url IS NOT NULL AND ap.scaling_file_url NOT LIKE 'http%' THEN 'https:' || ap.scaling_file_url ELSE ap.scaling_file_url END,
         att.original_file_url
       ) AS pc_img_url,
       NVL (
         CASE WHEN ap.scaling_file_url IS NOT NULL AND ap.scaling_file_url NOT LIKE 'http%' THEN 'https:' || ap.scaling_file_url ELSE ap.scaling_file_url END,
         att.original_file_url
       ) AS mobile_img_url,
  FROM CORE cr
  JOIN ATTACH_PICTURE ap ON ap.aid = cr.attach_id
  JOIN ATTACH att ON att.aid = cr.attach_id
UNION ALL
SELECT 99999999999 AS ORD,
       -1 AS cert_id,
       NVL(cfa.NAME, 'Taiwan International Trade Shows'' Exhibitor') AS cert_name,
       -1 AS img_id,
       'https://cs01.' || edm.host_name || '/static/icon-TaiwanTradeShows-100X100.png' AS pc_img_url,
       'https://cs01.' || edm.host_name || '/static/icon-TaiwanTradeShows-100X100.png' AS mobile_img_url
  FROM CERTIFICATION_FOCUS_AREA cfa
  CROSS JOIN ENV_DOMAIN_MAPPING edm 
 WHERE cfa.setting_type = 'VERIFIED'
   AND cfa.LANGUAGE = -1 
   AND INSTR(cfa.finale_value ||','  ,(',TTS,') ) > 0
   AND edm.env = 'current_env'
   AND EXISTS(
         SELECT 1
           FROM TTS_EXHIBITOR e 
           JOIN TTS_SHOWS_YEAR s on s.sy_id = e.sy_id 
           JOIN TTS_MULTI_SHOW_NAME m on m.s_id = SUBSTR(e.sy_id, 1, 2) 
           JOIN COMPANY com on com.seq = e.co_tax_id 
          WHERE com.cid = :companyId  -- 套用公司代碼
            AND m.language_id = :languageId    -- 套用語系代碼 -- AND (cfa.LANGUAGE = -1 OR cfa.LANGUAGE =42)
            --AND EXISTS (SELECT 1 FROM COMPANY_SU su WHERE su.CID = com.CID AND su.RIGHT_ID = 2 AND su.CLOSED = 0 AND su.REMOVED = 0)
       )
 ORDER BY ORD
       ;
```


------------------------------------------------------------------------
## Company Certification
- 公司認證
![](https://i.imgur.com/n5IweqW.png)


### Rule
- 分系統認證、自訂認證
- 圖片舊版是抓 attach_picture.scaling_file_url，改抓新圖
  - pc img: 100 * 100
  - mobile img: 100 * 100
- 圖片 title 放認證名稱
- 認證多語，預設為 42


### Path
- /front/cert-cp/company
  - GET

### INPUT
Column     | Type     | Require | Desc
-----------|----------|---------|---------
companyId  | number   | Y       | 公司id
languageId | number   | Y       | 語系id

### OUTPUT
```=json
{ 
  code      : {{ number }} , // 代碼，0：ok、 < 0: error 使用
  errorMsg  : {{ string }} , // 錯誤訊息
  total     : {{ number }} , // 總筆數
  rows：[
    {
      "certId"          : {{ number }}  , // 認證id
      //"serial"           : {{ string }}  , // 認證編號
      //"startTime"       : {{ string }}  , // 認證上架日期(起)
      //"endTime"         : {{ string }}  , // 認證上架日期(迄)
      "certName"        : {{ string }}  , // 認證名稱
      //"certDesc"        : {{ string }}  , // 認證說明
      "imgId"           : {{ number }}  , // 圖片id
      "pcImgUrl"        : {{ string }}  , // pc圖片url
      "mobileImgUrl"    : {{ string }}  , // mobile圖片url
      "lightboxUrl"     : {{ string }}  , // 燈箱url
    }, ...
  ],
}
```

### SQL
```=
WITH CORE AS (
SELECT com_cert.ccid, 
       com_cert.certification_id,
       NVL(cert.attach_id, com_cert.attach_picture_id) AS attach_id,
       42 AS lang_id  -- 替換變數 
  FROM company_certification com_cert 
  LEFT JOIN certification cert ON com_cert.certification_id = cert.cid AND cert.available = 1  -- 系統認證要啟用
 WHERE com_cert.company_id = 65   -- 替換公司代碼
   AND com_cert.certification_type_id = 372
   AND com_cert.shelved = 1 -- 上架
   AND com_cert.verify_status = 1324 -- 審核通過
   AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(com_cert.start_time, SYSDATE)) AND TRUNC(NVL(com_cert.end_time, SYSDATE)) -- 期間內
       OR (NVL(com_cert.certification_id, -1) != -1 AND cert.expired_time_show_pic = 1) -- 期間外
       )
)
SELECT x_com_cert.ccid AS cert_id, 
       --x_com_cert.serial,
       --TO_CHAR(x_com_cert.start_time, 'YYYY-MM-DD') AS start_time, 
       --TO_CHAR(x_com_cert.end_time, 'YYYY-MM-DD') AS end_time, 
       
       DECODE(
           cr.certification_id, NULL,
           NVL(
             (SELECT property_value FROM multi_language_value WHERE bean_name = 'CompanyCertification' AND property_name = 'name' AND row_id = cr.ccid AND language_id = cr.lang_id),
             (SELECT property_value FROM multi_language_value WHERE bean_name = 'CompanyCertification' AND property_name = 'name' AND row_id = cr.ccid AND language_id = 42 )
           ),
           NVL(
             (SELECT property_value FROM multi_language_value WHERE bean_name = 'Certification' AND property_name = 'name' AND row_id = cr.certification_id AND language_id = cr.lang_id),
             (SELECT property_value FROM multi_language_value WHERE bean_name = 'Certification' AND property_name = 'name' AND row_id = cr.certification_id AND language_id = 42 )
           )
       ) AS cert_name,
       
       cr.attach_id AS img_id,
       img_pkg.fun_third_cert_img_url(cr.attach_id, 100, 100) AS pc_img_url,
       img_pkg.fun_third_cert_img_url(cr.attach_id, 100, 100) AS mobile_img_url,

       '/lightbox/certification/' || x_com_cert.ccid AS lightbox_url
       
  FROM CORE cr
  JOIN company_certification x_com_cert ON x_com_cert.ccid = cr.ccid
 ORDER BY x_com_cert.sort NULLS LAST, x_com_cert.ccid DESC 
       ;
```

### fix issue
- company_certification_to_type 已廢除，不使用
- 圖片要以系統認證的圖片為主
- 公司認證與型錄無關
- 因為綁定多語語系，會造成認證資料漏缺。如：中文有，英文沒，網頁撈英文，舊版 java 抓二次篩選，效能差

```=
SELECT company_cert.ccid,
       company_cert.certification_id,
       company_cert.company_id,
       system_cert.description AS system_cert_description,
       multi_lang_val.language_id,
       multi_lang_val.bean_name,
       multi_lang_val.property_name,
       multi_lang_val.property_value AS NAME,
       (SELECT multi_lang_val2.property_value
          FROM multi_language_value multi_lang_val2
         WHERE multi_lang_val2.row_id = company_cert.certification_id
           AND multi_lang_val2.row_id = multi_lang_val.row_id
           AND multi_lang_val2.language_id = multi_lang_val.language_id
           AND multi_lang_val2.property_name = 'description'
       ) AS description,
       cert_to_type.certification_type_id,
       base.belong_company_idAS product_company_id,
       att.original_file_url AS scaling_picture_url,
       picture_back_up.scaling_file_url AS scaling_picture_backup_url,
       company_cert.start_time,
       company_cert.end_time,
       system_cert.cid AS system_cert_cid,
       base.deleted_flag AS product_delete_flag,
       company_cert.verify_status,
       company_cert.shelved,
       system_cert.use_html,
       system_cert.information_html,
       company_cert.serial,
       company_cert.sort,
       system_cert.sort AS system_cert_sort,
       system_cert.expired_time_show_pic,
       multi_product.actived
       AS product_actived,
       multi_product.verify_status_cid
       AS product_verify_status,
       Decode(
         (SELECT 1 FROM company_certification
           WHERE ccid = company_cert.ccid
             AND Trunc(sysdate) BETWEEN start_time AND end_time
         ), 1, 1, 0
       ) AS date_in_range,
       
       multi_product.language_id
       AS product_language_id,
       system_cert.available
  FROM company_certification company_cert
  JOIN multi_language_value multi_lang_val 
    ON ( multi_lang_val.row_id = company_cert.certification_id OR multi_lang_val.row_id = company_cert.ccid )
  JOIN company_certification_to_type cert_to_type ON cert_to_type.company_certification_id = company_cert.ccid
  -- 型錄相關
  LEFT JOIN article_product product ON product.pid = company_cert.product_id
  LEFT JOIN multi_article_product multi_product ON multi_product.product_pid = product.pid
  LEFT JOIN article_base base ON product.pid = base.aid
       
  LEFT JOIN attach_picture_scaling s ON s.attach_picture_original_id = company_cert.attach_picture_id
        AND s.width = 100 AND s.height = 100
  LEFT JOIN attach att ON att.aid = s.aid
  LEFT JOIN certification system_cert ON system_cert.cid = company_cert.certification_id
  LEFT JOIN attach_picture picture_back_up ON picture_back_up.aid = system_cert.attach_id
 WHERE company_cert.company_id = 65
   AND company_cert.shelved = 1
   AND company_cert.verify_status = 1324
   AND cert_to_type.certification_type_id = 372
   AND multi_lang_val.language_id = 42
   AND multi_lang_val.property_name = 'name'
   AND ( base.deleted_flag IS NULL OR base.deleted_flag = 0 )
   AND ( multi_lang_val.bean_name = 'Certification' OR multi_lang_val.bean_name ='CompanyCertification' )
 ORDER BY company_cert.sort ASC, company_cert.start_time DESC
       ;
```


------------------------------------------------------------------------
## Product Certification
- taiwan_excellence
- 型錄認證
![](https://i.imgur.com/NYGbuIo.png)


### Rule
- 型錄認證分系統認證、自訂認證
- 排序：taiwan_excellence 最前面，型錄認證在後
  - taiwan_excellence 都是屬於系統認證
- 目前是 call SP，圖片來源只有一種，若要拆pc、mobile，請抽出 sql 另處理
- 圖片舊版是抓 attach_picture.scaling_file_url，改抓新圖
  - pc img: 100 * 100
  - mobile img: 100 * 100
  - 圖片 title 放認證名稱
- 認證多語，預設為 42

### Path
- /front/cert-cp/product
  - GET

### INPUT
Column     | Type     | Require | Desc
-----------|----------|---------|---------
companyId  | number   | Y       | 公司id
languageId | number   | Y       | 語系id

### OUTPUT
```=json
{ 
  code      : {{ number }} , // 代碼，0：ok、 < 0: error 使用
  errorMsg  : {{ string }} , // 錯誤訊息
  total     : {{ number }} , // 總筆數
  rows：[
    {
      "cert_id"          : {{ number }}  , // 認證id
      //"serial"           : {{ string }}  , // 認證編號
      //"start_time"       : {{ string }}  , // 認證上架日期(起)
      //"end_time"         : {{ string }}  , // 認證上架日期(迄)
      "cert_name"        : {{ string }}  , // 認證名稱
      "cert_desc"        : {{ string }}  , // 認證說明
      "img_id"           : {{ number }}  , // 圖片id
      "pc_img_url"       : {{ string }}  , // pc圖片url
      "mobile_img_url"   : {{ string }}  , // mobile圖片url
      "lightboxUrl"      : {{ string }}  , // 燈箱url
    }, ...
  ],
}
```

### SQL
```=
WITH RX AS (
  SELECT ROWNUM AS SEQ, prod_data_type DATA_TYPE, PX.ccid, PX.certification_id, PX.attach_id, PX.company_id, 
         :languageId -- 語系代碼替換 LANGUAGE_ID   -- 語系代碼替換
    FROM (-- 台灣精品獎
          SELECT '1' AS prod_data_type, ROWNUM AS seq, x.ccid, x.certification_id, x.attach_id, x.company_id
            FROM (-- 目前只有系統認證
                  SELECT cert.cid AS certification_id, cert.attach_id, com_cert.company_id, MAX(com_cert.ccid) AS ccid
                    FROM company_certification com_cert
                    JOIN certification cert ON cert.cid = com_cert.certification_id
                   WHERE com_cert.company_id = :companyId  --- 公司代碼替換
                     AND com_cert.certification_type_id = (SELECT cid FROM code_meta WHERE code = 'taiwan_excellence')
                     AND com_cert.shelved = 1 -- 上架
                     AND com_cert.verify_status = 1324 -- 審核通過
                     AND cert.available = 1 -- 系統認證要啟用
                     AND NVL(com_cert.product_id, -1) <> -1
                     AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(com_cert.start_time, SYSDATE)) AND TRUNC(NVL(com_cert.end_time, SYSDATE))
                          OR cert.expired_time_show_pic = 1
                         )
                     -- 產品必要條件
                     AND EXISTS (
                           SELECT 1
                             FROM article_product ap 
                             JOIN article_base ab ON ab.aid = ap.pid 
                             JOIN multi_article_product map ON map.product_pid = ab.aid 
                            WHERE ap.pid = com_cert.product_id
                              AND ab.belong_company_id = com_cert.company_id
                              AND NVL(ab.DELETED_FLAG, 0) = 0
                              AND map.language_id = :languageId -- 語系代碼替換
                              AND map.actived = 1
                              AND map.verify_status_cid = 1324
                         )
                     AND EXISTS (
                           SELECT 1 
                           FROM COMPANY_SU su 
                           WHERE su.cid = com_cert.company_id
                             AND su.closed = 0
                             AND su.removed = 0
                             AND su.right_id IN (2, 3)
                         )
                   GROUP BY cert.cid, cert.attach_id, com_cert.company_id
                 ) x
           UNION ALL
          -- 產品
          SELECT '2' AS prod_data_type, ROWNUM AS seq, x.ccid, x.certification_id, x.attach_id, x.company_id
            FROM (-- 系統認證
                  SELECT cert.cid AS certification_id, cert.attach_id, com_cert.company_id, MAX(com_cert.ccid) AS ccid
                    FROM company_certification com_cert
                    JOIN certification cert ON cert.cid = com_cert.certification_id
                   WHERE com_cert.company_id = :companyId   -- 公司代碼換
                     AND com_cert.certification_type_id = 376 
                     AND com_cert.shelved = 1 -- 上架
                     AND com_cert.verify_status = 1324 -- 審核通過
                     AND cert.available = 1 -- 系統認證要啟用
                     AND NVL(com_cert.product_id, -1) <> -1
                     AND (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(com_cert.start_time, SYSDATE)) AND TRUNC(NVL(com_cert.end_time, SYSDATE))
                          OR cert.expired_time_show_pic = 1
                         )
                     -- 產品必要條件
                     AND EXISTS (
                           SELECT 1
                             FROM article_product ap 
                             JOIN article_base ab ON ab.aid = ap.pid 
                             JOIN multi_article_product map ON map.product_pid = ab.aid 
                            WHERE ap.pid = com_cert.product_id
                              AND ab.belong_company_id = com_cert.company_id
                              AND NVL(ab.DELETED_FLAG, 0) = 0
                              AND map.language_id = :languageId -- 語系代碼替換
                              AND map.actived = 1
                              AND map.verify_status_cid = 1324
                         )
                     AND EXISTS (
                           SELECT 1 
                           FROM COMPANY_SU su 
                           WHERE su.cid = com_cert.company_id
                             AND su.closed = 0
                             AND su.removed = 0
                             AND su.right_id IN (2, 3)
                         )
                   GROUP BY cert.cid, cert.attach_id, com_cert.company_id
                   UNION ALL
                   -- 自訂認證
                   SELECT com_cert.certification_id, com_cert.attach_picture_id, com_cert.company_id, com_cert.ccid AS ccid
                     FROM company_certification com_cert
                    WHERE com_cert.company_id = :companyId    -- 公司代碼替換
                      AND com_cert.certification_type_id = 376 
                      AND com_cert.shelved = 1 -- 上架
                      AND com_cert.verify_status = 1324 -- 審核通過
                      AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(com_cert.start_time, SYSDATE)) AND TRUNC(NVL(com_cert.end_time, SYSDATE)) -- 期間內
                      AND com_cert.certification_id IS NULL
                      -- 產品必要條件
                      AND EXISTS (
                            SELECT 1
                              FROM article_product ap 
                              JOIN article_base ab ON ab.aid = ap.pid 
                              JOIN multi_article_product map ON map.product_pid = ab.aid 
                             WHERE ap.pid = com_cert.product_id
                               AND ab.belong_company_id = com_cert.company_id
                               AND NVL(ab.DELETED_FLAG, 0) = 0
                               AND map.language_id = :languageId -- 語系代碼替換
                               AND map.actived = 1
                               AND map.verify_status_cid = 1324
                          )
                      AND EXISTS (
                             SELECT 1 
                             FROM COMPANY_SU su 
                             WHERE su.cid = com_cert.company_id
                               AND su.closed = 0
                               AND su.removed = 0
                               AND su.right_id IN (2, 3)
                           )
                     ORDER BY ccid DESC
                 ) x   
           ORDER BY prod_data_type, seq
         ) PX
)
SELECT RX.seq,
       RX.ccid AS cert_id,
       --x_com_cert.serial,
       --TO_CHAR(x_com_cert.start_time, 'YYYY-MM-DD') AS start_time, 
       --TO_CHAR(x_com_cert.end_time, 'YYYY-MM-DD') AS end_time, 

       DECODE(RX.certification_id, NULL,
         NVL((SELECT property_value FROM multi_language_value 
              WHERE bean_name = 'CompanyCertification' 
               AND property_name = 'name' AND row_id = RX.ccid AND language_id = rx.LANGUAGE_ID),
             (SELECT property_value FROM multi_language_value 
              WHERE bean_name = 'CompanyCertification' 
                AND property_name = 'name' AND row_id = RX.ccid AND language_id = 42)
         ),
         NVL((SELECT property_value FROM multi_language_value 
              WHERE bean_name = 'Certification' 
                AND property_name = 'name' AND row_id = RX.certification_id AND language_id = rx.LANGUAGE_ID),
             (SELECT property_value FROM multi_language_value 
              WHERE bean_name = 'Certification' 
                AND property_name = 'name' AND row_id = RX.certification_id AND language_id = 42 )
         )
       ) AS cert_name,
                   
       DECODE(RX.certification_id, NULL,
         NVL((SELECT property_value FROM multi_language_value 
              WHERE bean_name = 'CompanyCertification' 
               AND property_name = 'description' AND row_id = RX.ccid AND language_id = rx.LANGUAGE_ID),
             (SELECT property_value FROM multi_language_value 
              WHERE bean_name = 'CompanyCertification' 
                AND property_name = 'description' AND row_id = RX.ccid AND language_id = 42)
         ),
         NVL((SELECT property_value FROM multi_language_value 
              WHERE bean_name = 'Certification' 
                AND property_name = 'description' AND row_id = RX.certification_id AND language_id = rx.LANGUAGE_ID),
             (SELECT property_value FROM multi_language_value 
              WHERE bean_name = 'Certification' 
                AND property_name = 'description' AND row_id = RX.certification_id AND language_id = 42 )
         )
       ) AS cert_description,
               
       RX.attach_id AS img_id,
       img_pkg.fun_product_cert_img_url(RX.attach_id, 100, 100) AS pc_img_url,
       img_pkg.fun_product_cert_img_url(RX.attach_id, 100, 100) AS mobile_img_url,
       '/lightbox/certification/' || RX.ccid AS lightbox_url

  FROM RX 
  --JOIN company_certification x_com_cert ON x_com_cert.ccid = RX.ccid
 ORDER BY RX.SEQ
       ;
```

### fix issue
- 舊版是 call SP: EP_ABOUTUS_PKG.sp_cert_datas，沒 ep 則執行公司認證同一個sql，問題類似於公司認證


------------------------------------------------------------------------
## Award
- 得獎資訊
![](https://i.imgur.com/g5ZesEo.png)


### Path
- /front/cert-cp/award
  - GET
- 參考 EP API: https://ep-api.ttstaging.com.tw/about-us/cert/award/65/42


### INPUT
Column     | Type     | Require | Desc
-----------|----------|---------|---------
companyId  | number   | Y       | 公司id
languageId | number   | Y       | 語系id


### OUTPUT
```=json
{ 
  code      : {{ number }} , // 代碼，0：ok、 < 0: error 使用
  errorMsg  : {{ string }} , // 錯誤訊息
  rows：[
    {
      "certId"          : {{ number }}  , // 認證id
      "startYear"       : {{ string }}  , // 認證上架年份，YYYY
      "certName"        : {{ string }}  , // 認證名稱
      "certDesc"        : {{ string }}  , // 認證說明
      "imgId"           : {{ number }}  , // 圖片id
      "pcImgUrl"        : {{ string }}  , // pc圖片url
      "mobileImgUrl"    : {{ string }}  , // mobile圖片url
      "lightboxUrl"     : {{ string }}  , // 燈箱url
    }, ...
  ],
}
```

### SQL


------------------------------------------------------------------------
## TTS 展場資訊
![](https://i.imgur.com/zIQCqtr.png)


### Path
- /front/cert-cp/official-tts
  - GET
- 參考 EP API: https://ep-api.ttstaging.com.tw/json/aboutUs/officialRecords4TTSInfo/65_42_aboutUs.officialRecords4TTSInfo.json

### INPUT
Column     | Type     | Require | Desc
-----------|----------|---------|---------
companyId  | number   | Y       | 公司id
languageId | number   | Y       | 語系id


### OUTPUT
```=
{ 
  code      : {{ number }} , // 代碼，0：ok、 < 0: error 使用
  errorMsg  : {{ string }} , // 錯誤訊息
  rows：[
    {
      "certId"          : {{ number }}  , // 認證id
      "startYear"       : {{ string }}  , // 認證上架年份，YYYY
      "certName"        : {{ string }}  , // 認證名稱
      "certDesc"        : {{ string }}  , // 認證說明
      "imgId"           : {{ number }}  , // 圖片id
      "pcImgUrl"        : {{ string }}  , // pc圖片url
      "mobileImgUrl"    : {{ string }}  , // mobile圖片url
      "lightboxUrl"     : {{ string }}  , // 燈箱url

      "syId"            : {{ string }} , //  
      "coTaxId"         : {{ string }} , //  
      "displayTitle"    : {{ string }} , //  
      "showYearMonth"   : {{ string }} , // YYYY/MM
      "logoUrl"         : {{ string }} , // 
      "showBoots"       : {{ string }} , // 
      "syUrl"           : {{ string }} , // 
   }, ...
  ],
}
```
#### SQL


------------------------------------------------------------------------
## 官方認證
![](https://i.imgur.com/oR13LJk.png)


### Path
- /front/cert-cp/official
  - GET
- 參考 EP API: https://ep-api.ttstaging.com.tw/about-us/cert/official/65/42


### INPUT
Column     | Type     | Require | Desc
-----------|----------|---------|---------
companyId  | number   | Y       | 公司id
languageId | number   | Y       | 語系id


### OUTPUT
```=json
{ 
  code      : {{ number }} , // 代碼，0：ok、 < 0: error 使用
  errorMsg  : {{ string }} , // 錯誤訊息
  rows：[
    {
      "certId"          : {{ number }}  , // 認證id
      "startYear"       : {{ string }}  , // 認證上架年份，YYYY
      "certName"        : {{ string }}  , // 認證名稱
      "certDesc"        : {{ string }}  , // 認證說明
      "imgId"           : {{ number }}  , // 圖片id
      "pcImgUrl"        : {{ string }}  , // pc圖片url
      "mobileImgUrl"    : {{ string }}  , // mobile圖片url
      "lightboxUrl"     : {{ string }}  , // 燈箱url
    }, ...
  ],
}
```


### SQL


------------------------------------------------------------------------
## METADATA
None
