Life Collections

NTT Dataが提供するシステム共通基盤（PaaS）のIntra-mart。Intra-martのユーザ管理に利用できる便利なクエリを集めました。クエリを活用して、ユーザメンテナンスにお役立て下さい(｡･ω･｡)

Intra-mart管理に役立つ便利なクエリ集～ユーザ管理編～

0. 環境

• Package : WebPlatform /AppFramework Version7.2x
• DataBase : SQL Server

※WebPlatformとバージョンは昔のものですが、基本的なDB構造に違いはないと。。信じてます(ﾉд･｡)

1. Intra-martユーザ x 組織 x 役職情報一覧取得クエリ

Intra-martに登録しているユーザをベースに、そのユーザの組織・役職情報を一覧で取得します。削除ユーザ、廃止組織は含まず、有効なデータのみ取得しています。

SELECT
  a.user_cd 'ユーザコード'
  , a.user_name 'ユーザ名'
  , b.company_cd '会社コード'
  , d.department_name '会社名'
  , b.department_cd '組織コード'
  , c.department_name '組織名'
  , b.department_main '主管フラグ'
  , e.post_cd '役職コード'
  , f.post_name '役職名'
FROM
  imm_user a
  LEFT JOIN imm_department_ath b --# ユーザx組織紐付けテーブル
    ON
      b.user_cd = a.user_cd
      AND GETDATE() BETWEEN b.start_date AND b.end_date
      AND b.delete_flag = '0'
  LEFT JOIN imm_department c --# 組織テーブル（組織名取得用）
    ON
      c.company_cd = b.company_cd
      AND c.department_set_cd = b.department_set_cd
      AND c.department_cd = b.department_cd
      AND GETDATE() BETWEEN c.start_date AND c.end_date
      AND c.delete_flag = '0' 
  LEFT JOIN imm_department d --# 組織テーブル（会社名取得用）
    ON
      d.company_cd = b.company_cd
      AND d.department_set_cd = b.department_set_cd
      AND d.department_cd = b.company_cd --# 会社名取得用
      AND GETDATE() BETWEEN d.start_date AND d.end_date
      AND d.delete_flag = '0' 
  LEFT JOIN imm_department_post_ath e --# 組織x役職紐付けテーブル
    ON
      e.company_cd = b.company_cd
      AND e.department_set_cd = b.department_set_cd
      AND e.department_cd = b.department_cd
      AND e.user_cd = b.user_cd
      AND GETDATE() BETWEEN e.start_date AND e.end_date
      AND e.delete_flag = '0'
  LEFT JOIN imm_company_post f --# 会社x役職紐付けテーブル
    ON
      f.company_cd = e.company_cd
      AND f.department_set_cd = e.department_set_cd
      AND f.post_cd = e.post_cd
      AND GETDATE() BETWEEN f.start_date AND f.end_date
      AND f.delete_flag = '0' 
WHERE
  GETDATE() BETWEEN a.start_date AND a.end_date --# 現在有効期間内のユーザのみを対象とする
  AND a.delete_flag = '0' --# 削除されていないユーザのみを対象とする
ORDER BY
  a.user_cd ASC;

2. Intra-martユーザ x ロール紐付け一覧取得クエリ

Intra-martに登録されているユーザをベースに、各ユーザが保持するロールの一覧を取得します。

SELECT
	a.user_id
	, c.user_name AS user_name
	, a.role_id
	, b.display_name
FROM
	b_m_account_role_b a
	LEFT JOIN b_m_role_i b
		ON
			a.role_id = b.role_id --# ロールは期間化されないため時系列比較不要
			AND b.locale_id = 'ja'
	LEFT JOIN imm_user c
		ON
			c.user_cd = a.user_id
			AND GETDATE() BETWEEN c.start_date AND c.end_date
			AND c.delete_flag = '0'
WHERE
	CONVERT(NVARCHAR, GETDATE(), 111) + '|00:00:00' BETWEEN valid_start_date AND valid_end_date
	AND c.user_name IS NOT NULL --# 削除済みユーザは対象から除去

3. Intra-martユーザ x パブリックグループ紐付け一覧取得クエリ

Intra-martに登録されているユーザベースに、ユーザが保持するパブリックグループ・役割の一覧を取得します。

SELECT
	ipga.public_group_cd 'PGコード'
	, ipg.public_group_name 'PG名称'
	, ipgra.role_cd '役割コード'
	, ipgr.role_name '役割名称'
	, ipga.user_cd 'ユーザコード'
	, imu.user_name 'ユーザ名'
FROM
	imm_public_grp_ath ipga --# パブグル紐付け
	LEFT JOIN imm_public_grp ipg
		ON
			ipg.public_group_cd  = ipga.public_group_cd
			AND ipg.delete_flag = '0'
			AND GETDATE() BETWEEN ipg.start_date AND ipg.end_date
	LEFT JOIN imm_public_grp_role_ath ipgra
		ON
			ipgra.public_group_cd = ipg.public_group_cd
			AND ipgra.user_cd = ipga.user_cd
			AND ipgra.delete_flag = '0'
			AND GETDATE() BETWEEN ipgra.start_date AND ipgra.end_date
	LEFT JOIN imm_public_grp_role ipgr
		ON
			ipgr.role_cd = ipgra.role_cd
			AND ipgr.public_group_set_cd = ipgra.public_group_set_cd
			AND ipgr.delete_flag = '0'
			AND GETDATE() BETWEEN ipgr.start_date AND ipgr.end_date
	LEFT JOIN imm_user imu
		ON
			imu.user_cd = ipga.user_cd
			AND GETDATE() BETWEEN imu.start_date AND imu.end_date
			AND imu.delete_flag = '0'
WHERE
	ipga.delete_flag = '0'
	AND GETDATE() BETWEEN ipga.start_date AND ipga.end_date
	AND imu.user_name IS NOT NULL --# 削除済みユーザは対象から除去

4. Intra-martロール内包情報取得クエリ

Intra-martに登録されているロール情報を一覧で取得します。ロールは階層（2階層）を保持しているため、親階層、小階層を取得しています。

SELECT 
	a.role_id
	, b.child_role_id
	, a.category
FROM
	b_m_role_b a
	LEFT JOIN b_m_role_inclusion_b b
		ON
			b.parent_role_id = a.role_id

5. Intra-martパブリックグループと役割一覧取得クエリ

Intra-martに登録されているパブリックグループの情報を一覧で取得します。各パブリックグループには役割が登録されているため、パブリックグループと役割を一覧で取得します。

SELECT
	ipg.public_group_cd 'PGコード'
	, ipg.public_group_name 'PG名称'
	, ipgr.role_cd '役割コード'
	, ipgr.role_name '役割名称'
FROM
	imm_public_grp ipg
	LEFT JOIN imm_public_grp_role ipgr
		ON
			ipgr.public_group_set_cd = ipg.public_group_set_cd
			AND GETDATE() BETWEEN ipgr.start_date AND ipgr.end_date
WHERE
	GETDATE() BETWEEN ipg.start_date AND ipg.end_date;