# air-line-edi project

## CW1 Mark API
[swagger>/v1/job/inboundJson](https://data-pipeline-api-dev.morrison.express/swagger-ui.html#/Job%20API/createInboundJobForJsonUsingPOST)

* Token : test (dev)
* customer : cw1
* dag_id : cw1_airlineEDIUpdate
* subject : airline_edi
* metadata

"{
  ""station"":""LAX"",
  ""trans_mode"":""AIR"",
  ""mawb_no"":""123-098765"",
  ""msg_type"":""FWB"",
  ""log_time"":""2022-03-27 01:23:11""
}"

### view this api result log
https://airflow-dev.morrison.express/admin/airflow/tree?dag_id=cw1_airlineEDIUpdate

## 舊Java專案打包要點
另外下面是部署稍微需要注意的部分：

Build/Build Artifacts 打包完之後 jar 檔要改檔名 GenerateEDIProgram.jar / ReceiveFSUProgram.jar
打包完成後打開jar 檔 刪掉META-INF/FILETEST.SF
上傳ftp: ftp.morrisonexpress.com （帳密跟遠端桌面連線的一樣）
遠端進需要部署的遠端主機上下載下來到 C:/AIRLINE_EDI，舊的記得先改檔名備份。
新的 jar 下載下來後 .bat 檔用 cmd 跑一遍，觀察有沒有正常執行。

## 查 Job Info

SELECT
  DISTINCT JK_MasterBillNum,
	JK.JK_PK jk_pk,
	SUBSTRING ( JK.JK_MasterBillNum, 1, 3 ) + '-' + SUBSTRING ( JK.JK_MasterBillNum, 4, 8 ) mawb_no,
	OHSENDING.OH_Code station,
	SUBSTRING ( RouteLeg1.JW_VoyageFlight, 1, 2 ) destination,
	SUBSTRING ( JK.JK_MasterBillNum, 1, 3 ) airline
FROM
	JobShipment AS JS
	LEFT JOIN JobConShipLink AS JN ON JS.JS_PK = JN.JN_JS
	LEFT JOIN JobConsol AS JK ON JK.JK_PK = JN.JN_JK
	LEFT JOIN OrgAddress SENDING ON SENDING.OA_PK = JK.JK_OA_SendingForwarderAddress
	LEFT JOIN OrgAddress RECEIVING ON RECEIVING.OA_PK = JK.JK_OA_ReceivingForwarderAddress
	INNER JOIN OrgHeader OHSENDING ON OHSENDING.OH_PK = SENDING.OA_OH
	INNER JOIN OrgHeader OHRECEIVING ON OHRECEIVING.OH_PK = RECEIVING.OA_OH
	LEFT JOIN dbo.ProcessTasks AS P9 ON P9.P9_ParentID = JK.JK_PK
	AND P9_SE_NKMilestoneEvent = 'DDV'
	AND P9_Description LIKE '%Manifest Sent to Airline%'
	INNER JOIN JobConsolTransport AS RouteLeg1 ON ( JK.JK_PK = RouteLeg1.JW_ParentGUID AND RouteLeg1.JW_ParentType = 'CON' AND RouteLeg1.JW_LegOrder = 1 )
WHERE
JK_MasterBillNum in ('16036988803')
ORDER BY jk_pk OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

## Waiting JoB List

SELECT
	JK.JK_PK jk_pk,
	SUBSTRING ( JK.JK_MasterBillNum, 1, 3 ) + '-' + SUBSTRING ( JK.JK_MasterBillNum, 4, 8 ) mawb_no,
	OHSENDING.OH_Code station,
	SUBSTRING ( RouteLeg1.JW_VoyageFlight, 1, 2 ) destination,
	SUBSTRING ( JK.JK_MasterBillNum, 1, 3 ) airline
FROM
	JobShipment AS JS
	LEFT JOIN JobConShipLink AS JN ON JS.JS_PK = JN.JN_JS
	LEFT JOIN JobConsol AS JK ON JK.JK_PK = JN.JN_JK
	LEFT JOIN OrgAddress SENDING ON SENDING.OA_PK = JK.JK_OA_SendingForwarderAddress
	LEFT JOIN OrgAddress RECEIVING ON RECEIVING.OA_PK = JK.JK_OA_ReceivingForwarderAddress
	INNER JOIN OrgHeader OHSENDING ON OHSENDING.OH_PK = SENDING.OA_OH
	INNER JOIN OrgHeader OHRECEIVING ON OHRECEIVING.OH_PK = RECEIVING.OA_OH
	LEFT JOIN dbo.ProcessTasks AS P9 ON P9.P9_ParentID = JK.JK_PK
	AND P9_SE_NKMilestoneEvent = 'DDV'
	AND P9_Description LIKE '%Manifest Sent to Airline%'
	INNER JOIN JobConsolTransport AS RouteLeg1 ON ( JK.JK_PK = RouteLeg1.JW_ParentGUID AND RouteLeg1.JW_ParentType = 'CON' AND RouteLeg1.JW_LegOrder = 1 )
WHERE
	OHSENDING.OH_Code= 'M-ATL'
	AND SUBSTRING ( JK_MasterBillNum, 1, 3 ) IN ( '160','043','999','020','014','235','001','724','006','180','172','016','157','933','176','081','580','932','403','125','057','114','074','105','992','423','615' )
	AND CONVERT ( VARCHAR, P9_ActualDate, 112 ) >= CONVERT ( VARCHAR, GETDATE( ), 112 )
	AND CONVERT ( VARCHAR, P9_ActualDate, 112 ) >= CONVERT ( VARCHAR,20220824, 112 )

## 常用code 小記

### diff report for some mawb_no

    # (file_entity_list, section_entity_list) = _test_job_diff(job_info)
    # print(file_entity_list)
    # print(section_entity_list)
    # with database.session_scope() as session:
    #     _save_entity_list(session, file_entity_list, EDIDiffFileDao)
    #     _save_entity_list(session, section_entity_list, EDIDiffSectionDao)

###### tags: `morrison`