SELECT d.tax_code,
       SUBSTRING_INDEX(ds.query_url, ':', 1)  AS `protocol`,
       SUBSTRING_INDEX(ds.query_url, '/', -1) AS `gateway_host`,
       CASE
           WHEN ds.subject_type = '0' THEN '仁励家'
           WHEN ds.subject_type = '1' THEN '税邦云'
           END                                AS `subject_name`,
       ds.subject_type,
       CASE
           WHEN ds.subject_type = '0' THEN 'DEFAULT'
           WHEN ds.subject_type = '1' AND ds.subject_channel = '0' THEN 'CICC'
           WHEN ds.subject_type = '1' AND ds.subject_channel = '1' THEN 'PING_AN_BANK'
           END                                AS `subject_channel`,
       ds.app_id,
       ds.app_secret                          AS `merchant_private_key`,
       CASE
           WHEN ds.subject_type = '0' THEN ds.rsa_public_key
           WHEN ds.subject_type = '1' AND ds.subject_channel = '0' THEN '/serving/server/gulop/cert/publickey.zhongjin.pem'
           WHEN ds.subject_type = '1' AND ds.subject_channel = '1' THEN '/serving/server/gulop/cert/publickey.pingan.pem'
           END
                                              AS `public_key`,
       NULL                                   AS `location_id`,
       NULL                                   AS `sign_type`,
       1                                      AS `lock_flag`,
       NOW()                                  AS `created_time`,
       NOW()                                  AS `modified_time`
FROM sys_dept_sub ds
         LEFT JOIN sys_dept d ON d.dept_id = ds.dept_id
WHERE ds.enable_flag = 1
  AND ds.query_url IS NOT NULL
  AND ds.rsa_public_key IS NOT NULL
;