2dept_sub_init_query.sql 1.6 KB

1234567891011121314151617181920212223242526272829303132
  1. SELECT d.tax_code,
  2. SUBSTRING_INDEX(ds.query_url, ':', 1) AS `protocol`,
  3. SUBSTRING_INDEX(ds.query_url, '/', -1) AS `gateway_host`,
  4. CASE
  5. WHEN ds.subject_type = '0' THEN '仁励家'
  6. WHEN ds.subject_type = '1' THEN '税邦云'
  7. END AS `subject_name`,
  8. ds.subject_type,
  9. CASE
  10. WHEN ds.subject_type = '0' THEN 'DEFAULT'
  11. WHEN ds.subject_type = '1' AND ds.subject_channel = '0' THEN 'CICC'
  12. WHEN ds.subject_type = '1' AND ds.subject_channel = '1' THEN 'PING_AN_BANK'
  13. END AS `subject_channel`,
  14. ds.app_id,
  15. ds.app_secret AS `merchant_private_key`,
  16. CASE
  17. WHEN ds.subject_type = '0' THEN ds.rsa_public_key
  18. WHEN ds.subject_type = '1' AND ds.subject_channel = '0' THEN '/serving/server/gulop/cert/publickey.zhongjin.pem'
  19. WHEN ds.subject_type = '1' AND ds.subject_channel = '1' THEN '/serving/server/gulop/cert/publickey.pingan.pem'
  20. END
  21. AS `public_key`,
  22. NULL AS `location_id`,
  23. NULL AS `sign_type`,
  24. 1 AS `lock_flag`,
  25. NOW() AS `created_time`,
  26. NOW() AS `modified_time`
  27. FROM sys_dept_sub ds
  28. LEFT JOIN sys_dept d ON d.dept_id = ds.dept_id
  29. WHERE ds.enable_flag = 1
  30. AND ds.query_url IS NOT NULL
  31. AND ds.rsa_public_key IS NOT NULL
  32. ;