# Certification CP
###### tags: `cert`
> [name=RoyHuang]
------------------------------------------------------------------------
## 網址
- 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 展場認證
### 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
- 公司認證
### 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
- 型錄認證
### 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
- 得獎資訊
### 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 展場資訊
### 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
------------------------------------------------------------------------
## 官方認證
### 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
Sign in with Wallet
Connect another wallet