API - HackMD

# API ## 會員資料查詢 ### GET /api/v1/members :::spoiler request fields | 名稱 | API param | DB column | | -------- | -------- | -------- | | 身分證字號 | national_id | mm_person_id| | 手機號碼 | cellphone | mm_phone | | 姓名 | name | mm_real_name | | 會員 ID* | epayment_id | mm_login_id | | 儲值卡號 | TBD | OUTWARDNO | | 電票註冊日期 | TBD | reqDate | | 電支註冊日期 | registered_at | mm_creation_time* | | 查詢顯示筆數 | limit | -- | | 查詢上頁 cursor | before | -- | | 查詢下頁 cursor | after | -- | ::: ### 會員資料查詢 -> 儲值卡交易紀錄 ### GET /api/v1/members/{epayment_id}/transactions?type=card ::: spoiler request fields | 名稱 | API param | DB column | | -------- | -------- | -------- | | 儲值卡號 | Text | Text | | 類別 | Text | Text | | 交易場所 | Text | Text | | 交易時間 | Text | Text | ::: ### 會員資料查詢 -> 電支交易紀錄 ### GET /api/v1/members/{epayment_id}/transactions?type=epayment ::: spoiler request fields | 名稱 | API param | DB column | | -------- | -------- | -------- | | 交易時間 | Text | Text | ::: --- ## 廣告受眾設定 ### GET /api/v1/target-customers 列表 ::: spoiler request fields - id - 受眾代碼 - client_name - 客戶名稱 - client_type - created_at_period[] - 設定日期起迄 - limit - 查詢顯示筆數 - before - 查詢上頁 cursor - after - 查詢下頁 cursor ::: ::: spoiler response ```json { "data": [ { "id": "A123456", "client_name": "聯邦銀行", "client_type": "ipass", "*targeting_limit": 10000, "*conditions": { "age": { "from": 18, "to": 30 }, "gender": "M", "nationality": [], "location": "Taipei", "and so on...": "TBD", }, "operator": $operator_user_id, "created_at": "2023-03-08T18:47:19.000Z", "download_link": "/api/v1/target-customers/1/download" }, ], "total": 3, "limit": 10, "before": $cursor, "after": $cursor } // *前端用不到的欄位 ``` ::: ### POST /api/v1/target-customers 新增 ### PUT /api/v1/target-customers/{id} 更新 ::: spoiler request body ```json { "client_name": "聯邦銀行", "client_type": "ipass", "targeting_limit": 10000, "conditions": { "customer_profile": { "age_range": [18,24], "gender": "M", "nationality": [], "locations": [ "TPE", "KHH", ], "has_registered_card": true, "has_epayment_account": true, }, "epayment": { "consumption": [ { "city": "KHH", "frequency": 5, "date_range": ["2023-03-08T18:47:19.000Z","2023-03-08T18:47:19.000Z"], "store": $store_id, "minimum_spend": 300, }, ], "transfer": [ { "city": "KHH", "frequency": 5, "date_range": ["2023-03-08T18:47:19.000Z","2023-03-08T18:47:19.000Z"], "minimum_spend": 300, }, ], "payment": [ { "city": "KHH", "frequency": 5, "date_range": ["2023-03-08T18:47:19.000Z","2023-03-08T18:47:19.000Z"], "item": $item_id, "minimum_spend": 3000, } ], "red_envelope": [ { "frequency": -1, "date_range": ["2023-03-08T18:47:19.000Z","2023-03-08T18:47:19.000Z"], "minimum_spend": 300, }, ], "recharge": { "frequency": 5, "date_range": ["2023-03-08T18:47:19.000Z","2023-03-08T18:47:19.000Z"], "facility": $facility_id, "minimum_spend": 300, }, }, "card": { "consumption": [ { "city": "KHH", "frequency": 5, "date_range": ["2023-03-08T18:47:19.000Z","2023-03-08T18:47:19.000Z"], "store": $store_id, "minimum_spend": 300, }, ], "transportaion": [ { "vehicle": $vehicle_id, "frequency": 5, "date_range": ["2023-03-08T18:47:19.000Z","2023-03-08T18:47:19.000Z"], "minimum_spend": 30, }, ], }, }, } ``` ::: ### SQL query ::: spoiler sql query | conditions | query | | --- | --- | | `epayment.consumption` | `select mm.mm_person_id from iPASS_PAY.dbo.transaction_record tr inner join iPASS_PAY.dbo.member_master mm on tr.tr_buyer_member_master_id = mm.mm_id inner join iPASS_PAY.dbo.transaction_record_extention tre on tr.tr_id =tre.tre_tr_id inner join iPASS_PAY.dbo.special_store ss on tre.tre_merchant_id = ss.ss_merchant_id inner join iPASS_PAY.dbo.special_store_info ssi on ssi.ssi_merchant_id = ss.ss_merchant_id where ssi.ssi_office_address = 'city' and (tr.tr_creation_time BETWEEN 'started_at' and 'ended_at') and ss.ss_merchant_id = 'store' and tr.tr_amt >= 'minimum_spend' group by mm.person_idhaving count(*) = 'frequency'` | | `epayment.transfer` | `select mm.mm_person_id from iPASS_PAY.dbo.member_master mm inner join iPASS_PAY.dbo.transfer_cross tc on mm.mm_id = tc.tc_mmid where tc.tc_inout = 'O' and tc.tc_status ='50' and (tc.tc_creation_time BETWEEN 'started_at' and 'ended_at') and tc_txnAmount >= 'minimum_spend' having count(*) = 'frequency'` | | `epayment.payment` | `select mm.mm_person_id from iPASS_PAY.dbo.member_master mm inner join iPASS_PAY.dbo.transaction_record tr on mm.mm_id = tr.tr_buyer_member_master_id where tr.tr_type in ('31','32','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75') and (tr.tr_creation_time BETWEEN 'started_at' and 'ended_at') and tr.tr_type = 'item' and tr.tr_amt >= 'minimum_spend' having count(*) = 'frequency'` | | `epayment.red_envelope` | `select mm.mm_person_id from member_master mm inner join transaction_record tr on mm.mm_id = tr.tr_buyer_member_master_id inner join transfer_master tm on tr.tr_id = tm.tm_tr_id where tm.tm_type = '4' and (tr.tr_creation_time BETWEEN 'started_at' and 'ended_at') and tr.tr_amt >= 'minimum_spend' having count(*) = 'frequency'` | | `epayment.recharge` | `select mm.mm_person_id from iPASS_PAY.dbo.member_master mm inner join prepaid_record pr on mm.mm_id = pr.pr_member_master_id where pr.pr_creation_time BETWEEN ('started_at','ended_at') and pr.pr_bank_code = 'facility' and pr.pr_amount >= 'minimum_spend' having count(*) = 'frequency'` | | `card.consumption` | `query` | | `card.transportaion` | `query` | ::: ### POST /api/v1/target-customers/{id}/analytics 成效試算 ::: spoiler response ```json { "matched_customers": 1100000, "targeting_limit": 50000, } ``` ::: --- ## 營運規則設定 ### GET /api/v1/campaigns 列表 ::: spoiler request fields - id - name - target_customer_id - client_type={client, ipass} - client_name - started_at - ended_at - limit - 查詢顯示筆數 - before - 查詢上頁 cursor - after - 查詢下頁 cursor ::: ::: spoiler response ```json { "data": [ { "id": "A123456", "target_customer_id": "A123456", "name": "聯邦銀行 12 月回饋 100" "client_name": "聯邦銀行", "client_type": "ipass", "created_at": "2023-03-08T18:47:19.000Z", "campaign_period": ["2023-03-08T18:47:19.000Z", "2023-03-08T18:47:19.000Z"], "status": 0, } ], "total": 3, "limit": 10, "before": $cursor, "after": $cursor } ``` ::: ### POST /api/v1/campaigns 新增 ::: info 進階條件是供活動結束後，計算成效/產報表用；和受眾無關是否連結受眾代碼 <-> 投放人數上限 - 「是」帶入受眾包設定的人數上限 - 「否」投放人數上限為空 ::: ### PUT /api/v1/campaigns/{id} 更新 ::: spoiler request body ```json { "target_customer_id": 1, "name": "聯邦銀行 12 月回饋 100", "campaign_period": ["2023-03-08T18:47:19.000Z", "2023-03-08T18:47:20.000Z"], "reward_type": "recharge" "reward_amount": 50, "reward_percentage": 10, "has_registered_card": true, "has_epayment_account": true, "conditions": { "customer_profile": { "age_range": { "from": 18, "to": 18, }, "gender": "M", "nationality": [], "locations": [ "TPE", "KHH", ], }, }, "report_conditions": { "epayment": { "consumption": [ { "city": "KHH", "frequency": 5, "started_at": "2023-03-08T18:47:19.000Z", "ended_at": "2023-03-08T18:47:19.000Z", "store": $store_id, "minimum_spend": 300, } ], "transfer": [ { "city": "KHH", "frequency": 5, "started_at": "2023-03-08T18:47:19.000Z", "ended_at": "2023-03-08T18:47:19.000Z", "minimum_spend": 300, }, ], "payment": [ { "city": "KHH", "frequency": 5, "started_at": "2023-03-08T18:47:19.000Z", "ended_at": "2023-03-08T18:47:19.000Z", "item": $item_id, "minimum_spend": 3000, } ], "red_envelope": [ { "frequency": -1, "started_at": "2023-03-08T18:47:19.000Z", "ended_at": "2023-03-08T18:47:19.000Z", "minimum_spend": 300, }, ], "recharge": { "frequency": 5, "started_at": "2023-03-08T18:47:19.000Z", "ended_at": "2023-03-08T18:47:19.000Z", "facility": $facility_id, "minimum_spend": 300, }, }, "card": { "consumption": [ { "city": "KHH", "frequency": 5, "started_at": "2023-03-08T18:47:19.000Z", "ended_at": "2023-03-08T18:47:19.000Z", "store": $store_id, "minimum_spend": 300, }, ], "transportaion": [ { "vehicle": $vehicle_id, "frequency": 5, "started_at": "2023-03-08T18:47:19.000Z", "ended_at": "2023-03-08T18:47:19.000Z", "minimum_spend": 30, }, ], }, }, } ``` ::: ### GET /api/v1/report/summary?campaign-id={id} 成效報表 ### 獲得差異份資料/分頁 e.g. 以下面的 GET API URL 來說，可以取得只有 `data.group_by_date` 的資料，並且以 `after` 為起始點拿到下個 pagination data `/api/v1/report?campaign-id={id}&data=group_by_date&after=2023-01-02 00:00:00` ::: spoiler response body ```json { "total_summary": { "amount": 500000, "count": 500, "average_count": 10, "external_link_click_count": 500, "growth_rate": 0.3, "new_customer_count": 0 }, "data": { "group_by_date": [ { "time": "2023-01-01 00:00:00", "amount": 562, "count": 13 }, { "time": "2023-01-02 00:00:00", "amount": 562, "count": 13 } ], "group_by_hour": [ { "time": "2023-01-01 01:00:00", "amount": 562, "count": 13 }, { "time": "2023-01-01 02:00:00", "amount": 562, "count": 13 }, { "time": "2023-01-01 03:00:00", "amount": 562, "count": 13 } ], "group_by_age_and_gender": [ { "age": 18, "amount": 5010, "count": 10, "gender": "male" }, { "age": 18, "amount": 5010, "count": 10, "gender": "female" }, { "age": 19, "amount": 5010, "count": 10, "gender": "male" }, { "age": 19, "amount": 5010, "count": 10, "gender": "female" }, { "age": 20, "amount": 5010, "count": 10, "gender": "male" } { "age": 20, "amount": 5010, "count": 10, "gender": "female" } ] } } ``` ::: Avaiable groups |data group name|after anchor| | -------- | -------- | |`group_by_date`|`time`| |`group_by_hour`|`time`| # Model ## Member 會員歸戶主表 - member_info [reference]: https:// "title" - epayment_id (電支的 login id) - epayment_master_id (電支的 member_master_id, mm_id) - national_id (會員身分證 id) - created_at (資料創建日) - registered_at (會員註冊日完成實名認證日而非創建日) ::: spoiler schema ```sql CREATE TABLE member ( id SERIAL PRIMARY KEY, --sonyflake unid national_id VARCHAR(10) NOT NULL, epayment_id varchar(120) NOT NULL, epayment_master_id bigint NOT NULL, created_at timestamp(0), registered_at timestamp(0) ); CREATE INDEX idx_member_national_id ON member (national_id); CREATE INDEX idx_member_epayment_id ON member (epayment_id); CREATE INDEX idx_member_created_at ON member (created_at); CREATE INDEX idx_member_registered_at ON member (registered_at); ``` ::: ## MemberInfo 會員歸戶明細 - member_id: (ref to member) --sonyflake id - national_id: (身分證 id) - source: {TBRT41, TBRT90} (歸戶資料表來源) - source_id: (歸戶資料表來源的 primary key: e.g. member_master.mm_id,TBRT90.CHIPNO) - name (客戶姓名) - gender (性別) - birth (生日) - contact_address (居住/通訊地址) - residence_address (戶籍地址) - phone (連絡電話) - cellphone (手機號碼) - email (電子信箱) - nationality: (國籍) - national_id_card_issued_at (身分證發卡日) - national_id_card_issued_location (身分證發證地代碼) - national_id_card_issued_type(初發/補發/換發) ::: spoiler schema ```sql CREATE TABLE member_info ( id SERIAL PRIMARY KEY, --sonyflake unid national_id VARCHAR(10) NOT NULL, source VARCHAR(30), COMMENT 'TBRT41,TBRT90,TBRT91,TBXBPERSONALDATA' name VARCHAR(50) NOT NULL, gender CHAR(1) NOT NULL, birth DATE NOT NULL, contact_address VARCHAR(120), residence_address VARCHAR(120), phone VARCHAR(20), cellphone VARCHAR(20), email VARCHAR(100), nationality VARCHAR(3) COMMENT '國籍代碼3碼', national_id_card_issued_at DATE COMMENT '身分證發卡日', national_id_card_issued_location char(1) COMMENT '身份證發證地代碼', national_id_card_issued_type char(2) COMMENT '初發,補發,換發' ); CREATE INDEX idx_member_info_name ON member_info (name); CREATE INDEX idx_member_info_email ON member_info (email); CREATE INDEX idx_member_info_cellphone ON member_info (cellphone); CREATE INDEX idx_member_info_national_id ON member_info (national_id); CREATE INDEX idx_member_info_registered_at ON member_info (registered_at); ``` ::: ## 廣告受眾 Model - 受眾代碼 KEY - 類別(客戶、一卡通) - 客戶名稱 - 人數上限 - ^年齡起 - ^年齡迄 - ^性別 - ^居住地: TPE, KHH - ^國籍: Thailand, Vietnam - ^註冊 iPass Money: 是/否 - ^持有記名卡: 是/否 ^ 存在 conditions json :::spoiler schema ```sql CREATE TABLE target_customer ( id BIGINT PRIMARY KEY, client_name VARCHAR(100) NOT NULL, client_type VARCHAR(20) NOT NULL, targeting_limit INTEGER NOT NULL, conditions JSONB NOT NULL, created_at timestamp(0), updated_at timestamp(0), deleted_at timestamp(0), ); CREATE INDEX idx_target_customer_id ON target_customer (id); CREATE INDEX idx_target_customer_client_name ON target_customer (client_name); CREATE INDEX idx_target_customer_client_type ON target_customer (client_type); ::: ## 廣告受眾名單 Model - 受眾代碼 KEY - 產出受眾結果位置 (S3 full path s3://path/to/the/file) :::spoiler schema ```sql CREATE TABLE target_customer_history ( target_customer_id BIGINT, result_path VARCHAR(100) NOT NULL, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, deleted_at TIMESTAMPTZ NULL ); CREATE UNIQUE INDEX target_customer_history ON public.target_customer_history (target_customer_id, created_at) WHERE delete_time IS NULL; ``` ::: ## 營運規則 Model - 營運規則 id, KEY - 受眾代碼 - 類別(客戶、一卡通) - 活動名稱 - 客戶名稱 - 人數上限 - ^年齡起 - ^年齡迄 - ^性別 - ^居住地: TPE, KHH - ^國籍: Thailand, Vietnam - ^註冊 iPass Money: 是/否 - ^持有記名卡: 是/否 ^ 存在 conditions，電支電票等條件存在 report_conditions :::spoiler schema ```sql CREATE TABLE campaign ( id BIGINT PRIMARY KEY, target_customer_id BIGINT, name VARCHAR(50) NOT NULL, client_name VARCHAR(100) NOT NULL, client_type VARCHAR(20) NOT NULL, targeting_limit INTEGER NOT NULL, conditions JSONB NOT NULL, report_conditions JSONB NOT NULL, created_at timestamp(0), updated_at timestamp(0), deleted_at timestamp(0), ); CREATE INDEX idx_target_customer_id ON target_customer (id); CREATE INDEX idx_target_customer_client_name ON target_customer (client_name); CREATE INDEX idx_target_customer_client_type ON target_customer (client_type); ``` ::: ### 電支消費縣市Model - epayment_id (會員帳號) - (消費地點) - (特店編號) - (消費金額) - 消費日期 ### 電支銀行轉帳Model - epayment_id (會員帳號) - (銀行機構3碼) - (轉帳金額) - 轉帳日期 ### 電支生活繳費Model - epayment_id (會員帳號) - source: {cabletv_bill, health_insurance_bill_paid} (生活繳費資料表來源) - (繳費金額) - 繳費日期 - (繳費縣市，這個欄位目前不好處理因為資料來自不同的表，有些表不一定有縣市) ### 電支LINE發紅包Model - epayment_id (會員帳號) - LINE PAY ID (LINE ID) - (發放紅包金額) - 發紅包日期 ### 電支儲值機構Model - epayment_id (會員帳號) - (銀行機構3碼) - (儲值金額) - 儲值日期 ### 電票消費縣市Model - epayment_id (會員帳號) - (票卡晶片號碼) - {台北市,高雄市}(消費地點) - (特店編號) - (消費金額) - 消費日期 ### 電票搭乘交通運具Model - epayment_id (會員帳號) - (票卡晶片號碼) - source {TBXTTXN, TBXRTCRTCTXN} (運具交易資料表來源) - (消費金額) - 消費日期 # 電支 Personal Data - 會員顯示名稱/商家名稱 - 身分證字號/公司統一編號 - 會員電話 - 聯絡人姓名 - 會員生日 - 會員email - 會員國籍 - 會員地址 - 護照號碼 - 會員實名(驗證用)/個人或負責人姓名 - 會員ID/公司負責人ID(20170303) - 負責人電話 - 負責人國籍 - 負責人聯絡地址 # 電票 Personal Data - 客戶ID(身分證字號) - 客戶姓名 - 性別(M-男、F-女) - 生日(YYYYMMDD) - 戶籍地址 - 居住地址 - 出生國 - 國籍 - 註冊國 - 電話 - 手機號碼 - email - 發證日 - 發證地代碼 - 補換發代碼 # 電票 Card Info (TBXBMAIN 聯名卡票卡主檔) - COMPID 運輸業者代碼(ipass) - OUTWARDNO 卡片外觀卡號(儲值卡號碼) - CHIPNO 晶片號碼 - STATUS 票卡狀態(00 正常 20 黑名單 30 鎖卡 40 退卡) - CARDTYPE 信用卡類別(C-Credit Card、D-Debit Card) - SPIDPROVIDER 特種票識別單位(桃園、宜蘭、OX大學...) - SPIDTYPE 特種票識別身份 - BANKNO 金融單位代碼3碼 - ISSUECODE 發卡單位 - AUTOLOADFLAG 自動加值Flag - AUTOLOADAMT 自動加值金額 - AUTOLOADSTOPDATE 自動加值停止日 - CARDLOSTFLAG 卡片掛失Flag - CARDLOSTDATE 卡片掛失日 - BLACKLISTDATE 黑名單登記日 - CARDLOCKDATE 鎖卡日 - CARDRETURNFLAG 卡片歸還Flag - CARDRETURNDATE 卡片歸還日 - CARDREFUNDDATE 卡片退款日 - EXPIRYDATE 個人身分有效期限 - PERSONALCARDTYPE 個人身分別分類(一般(成人)、兒童、學生、老人、身心障礙、員工) - BANKID 銀行代碼2碼(這似乎是公司自己定義的？再SURVEY)

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.