USE [GrpInsurance]

-- PORTAL MEMBER
-- 1 jumlah user terdaftar vs frekuensi login
SELECT
	terdaftar.No_Polis,
	terdaftar.Nama_Perusahaan,
	terdaftar.jml AS Jml_Terdaftar,
	COALESCE(pernah_login.jml,0) AS Jml_Login	-- maksudnya jumlah user yang pernah login
FROM
	(
		SELECT DISTINCT
			polis.No_Polis,
			polis.Nama_Perusahaan,
			COUNT(culc.id_user) AS jml
		FROM
			dbo.custport_user_login_client AS culc 
			INNER JOIN
			(
				SELECT DISTINCT
					pa.No_Polis,
					prs.Nama_Perusahaan
				FROM
					dbo.Polis_Asuransi AS pa 
					INNER JOIN
					dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
			) AS polis ON culc.no_polis = polis.No_Polis
		WHERE
			culc.no_polis NOT IN ('1992.0011.0')	-- Equity Life (polis ini digunakan untuk testing)
			AND COALESCE(culc.no_peserta,'') <> ''
		GROUP BY
			polis.No_Polis,
			polis.Nama_Perusahaan
	) AS terdaftar 
	LEFT JOIN
	(
		SELECT
			polis.No_Polis,
			polis.Nama_Perusahaan,
			COUNT(culc.id_user) AS jml
		FROM
			dbo.custport_user_login_client AS culc 
			INNER JOIN
			(
				SELECT
					[user_id],
					MAX(last_activity) AS last_activity
				FROM
					dbo.custport_svc_user_session_client
				GROUP BY
					[user_id]
			) AS ses ON culc.id_user = ses.[user_id] 
			INNER JOIN
			(
				SELECT DISTINCT
					pa.No_Polis,
					prs.Nama_Perusahaan
				FROM
					dbo.Polis_Asuransi AS pa 
					INNER JOIN
					dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
			) AS polis ON culc.no_polis = polis.No_Polis
		WHERE
			culc.no_polis NOT IN ('1992.0011.0')	-- Equity Life (polis ini digunakan untuk testing)
			AND COALESCE(culc.no_peserta,'') <> ''
		GROUP BY
			polis.No_Polis,
			polis.Nama_Perusahaan
	) AS pernah_login ON terdaftar.No_Polis = pernah_login.No_Polis
ORDER BY
	terdaftar.Nama_Perusahaan

--------------------------------------------------------------------------------------------------------------------------------------------
--2 jml klaim
-- all
SELECT
	semua.No_Polis,
	semua.Nama_Perusahaan,
	semua.jml AS Jml_Total_Klaim,
	COALESCE(portal.jml,0) AS Jml_Klaim_Portal
FROM
	(
		SELECT
			pa.No_Polis,
			prs.Nama_Perusahaan,
			COUNT(kp.No_Klaim) AS jml
		FROM
			(
				SELECT
					no_polis
				FROM
					dbo.custport_user_login_client
				WHERE
					no_polis NOT IN ('1992.0011.0')	-- Equity Life (polis ini digunakan untuk testing)
					AND COALESCE(no_peserta,'') <> ''
				GROUP BY
					no_polis
			) AS list_polis		-- sementara list polis portal menggunakan query ini, kedepannya akan menggunakan tabel [custport_polis_member]
			INNER JOIN
			dbo.Polis_Asuransi AS pa ON list_polis.no_polis = pa.No_Polis 
			INNER JOIN
			dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs 
			INNER JOIN
			dbo.Klaim_Peserta AS kp ON pa.No_Proposal = kp.No_Proposal
		WHERE
			kp.Tgl_Klaim >= '2017-01-01'
			AND kp.Status = 'I'
		GROUP BY
			pa.No_Polis,
			prs.Nama_Perusahaan
	) AS semua 
	LEFT JOIN
	(
		SELECT
			pa.No_Polis,
			prs.Nama_Perusahaan,
			COUNT(kp.No_Klaim) AS jml
		FROM
			(
				SELECT
					no_polis
				FROM
					dbo.custport_user_login_client
				WHERE
					no_polis NOT IN ('1992.0011.0')	-- Equity Life (polis ini digunakan untuk testing)
					AND COALESCE(no_peserta,'') <> ''
				GROUP BY
					no_polis
			) AS list_polis		-- sementara list polis portal menggunakan query ini, kedepannya akan menggunakan tabel [custport_polis_member]
			INNER JOIN
			dbo.Polis_Asuransi AS pa ON list_polis.no_polis = pa.No_Polis 
			INNER JOIN
			dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs 
			INNER JOIN
			dbo.Klaim_Peserta AS kp ON pa.No_Proposal = kp.No_Proposal 
			INNER JOIN
			(
				SELECT
					COALESCE(ckl.no_klaim,ck.core_no_klaim) AS No_Klaim
				FROM
					dbo.custport_klaim AS ck 
					LEFT JOIN
					dbo.custport_klaim_link AS ckl ON ck.id_klaim = ckl.id_klaim 
					INNER JOIN
					dbo.custport_user_login_client AS culc ON ck.create_by = culc.id_user
				WHERE
					culc.no_polis NOT IN ('1992.0011.0')	-- Equity Life (polis ini digunakan untuk testing)
					AND COALESCE(culc.no_peserta,'') <> ''
					AND COALESCE(ckl.no_klaim,ck.core_no_klaim) IS NOT NULL
					AND ck.create_by <> 0	-- bukan internal
			) AS portal ON kp.No_Klaim = portal.No_Klaim
		WHERE
			kp.Tgl_Klaim >= '2017-01-01'
			AND kp.Status = 'I'
		GROUP BY
			pa.No_Polis,
			prs.Nama_Perusahaan
	) AS portal ON semua.No_Polis = portal.No_Polis

---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------

-- PORTAL HRD
-- jumlah user terdaftar vs frekuensi login
SELECT
	terdaftar.No_Polis,
	terdaftar.Nama_Perusahaan,
	terdaftar.jml AS Jml_Terdaftar,
	COALESCE(pernah_login.jml,0) AS Jml_Login	-- maksudnya jumlah user yang pernah login
FROM
	(
		SELECT DISTINCT
			polis.No_Polis,
			polis.Nama_Perusahaan,
			COUNT(culh.id_user) AS jml
		FROM
			dbo.custport_user_login_hrd AS culh 
			INNER JOIN
			(
				SELECT DISTINCT
					pa.No_Polis,
					prs.Nama_Perusahaan
				FROM
					dbo.Polis_Asuransi AS pa 
					INNER JOIN
					dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
			) AS polis ON culh.no_polis = polis.No_Polis
		WHERE
			culh.no_polis NOT IN ('1992.0011.0')	-- Equity Life (polis ini digunakan untuk testing)
			AND COALESCE(culh.no_peserta,'') = ''
		GROUP BY
			polis.No_Polis,
			polis.Nama_Perusahaan
	) AS terdaftar 
	LEFT JOIN
	(
		SELECT
			polis.No_Polis,
			polis.Nama_Perusahaan,
			COUNT(culh.id_user) AS jml
		FROM
			dbo.custport_user_login_hrd AS culh 
			INNER JOIN
			(
				SELECT
					[user_id],
					MAX(last_activity) AS last_activity
				FROM
					dbo.custport_svc_user_session
				GROUP BY
					[user_id]
			) AS ses ON culh.id_user = ses.[user_id] 
			INNER JOIN
			(
				SELECT DISTINCT
					pa.No_Polis,
					prs.Nama_Perusahaan
				FROM
					dbo.Polis_Asuransi AS pa 
					INNER JOIN
					dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
			) AS polis ON culh.no_polis = polis.No_Polis
		WHERE
			culh.no_polis NOT IN ('1992.0011.0')	-- Equity Life (polis ini digunakan untuk testing)
			AND COALESCE(culh.no_peserta,'') = ''
		GROUP BY
			polis.No_Polis,
			polis.Nama_Perusahaan
	) AS pernah_login ON terdaftar.No_Polis = pernah_login.No_Polis
ORDER BY
	terdaftar.Nama_Perusahaan

SELECT
	terdaftar.No_Polis,
	terdaftar.Nama_Perusahaan,
	terdaftar.jml AS Jml_Terdaftar,
	COALESCE(pernah_login.jml,0) AS Jml_Login	-- maksudnya jumlah user yang pernah login
FROM
	(
		SELECT DISTINCT
			polis.No_Polis,
			polis.Nama_Perusahaan,
			COUNT(cul.id_user) AS jml
		FROM
			dbo.custport_user_login AS cul
			INNER JOIN
			(
				SELECT DISTINCT
					pa.No_Polis,
					prs.Nama_Perusahaan
				FROM
					dbo.Polis_Asuransi AS pa 
					INNER JOIN
					dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
			) AS polis ON cul.no_polis = polis.No_Polis
		WHERE
			cul.no_polis NOT IN ('1992.0011.0')	-- Equity Life (polis ini digunakan untuk testing)
			AND COALESCE(cul.no_peserta,'') = ''
		GROUP BY
			polis.No_Polis,
			polis.Nama_Perusahaan
	) AS terdaftar 
	LEFT JOIN
	(
		SELECT
			polis.No_Polis,
			polis.Nama_Perusahaan,
			COUNT(cul.id_user) AS jml
		FROM
			dbo.custport_user_login AS cul 
			INNER JOIN
			(
				SELECT
					[user_id],
					MAX(last_activity) AS last_activity
				FROM
					dbo.custport_svc_user_session
				GROUP BY
					[user_id]
			) AS ses ON cul.id_user = ses.[user_id] 
			INNER JOIN
			(
				SELECT DISTINCT
					pa.No_Polis,
					prs.Nama_Perusahaan
				FROM
					dbo.Polis_Asuransi AS pa 
					INNER JOIN
					dbo.Perusahaan AS prs ON pa.No_Prs = prs.No_Prs
			) AS polis ON cul.no_polis = polis.No_Polis
		WHERE
			cul.no_polis NOT IN ('1992.0011.0')	-- Equity Life (polis ini digunakan untuk testing)
			AND COALESCE(cul.no_peserta,'') = ''
		GROUP BY
			polis.No_Polis,
			polis.Nama_Perusahaan
	) AS pernah_login ON terdaftar.No_Polis = pernah_login.No_Polis
ORDER BY
	terdaftar.Nama_Perusahaan