pnp 22

berikut data ParameterKelompokDistribusiRepository dan ParameterScoringRepository yang berkaitan dengan member tersebut:

1. ParameterKelompokDistribusiRepository.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;

public class KelompokDistribusiRepository {
    private string connString = ConfigurationManager.ConnectionStrings["iSee"].ConnectionString;
	
	private string NormalizeText(string value) {
		return (value ?? string.Empty).Trim();
	}

	private string NormalizeInitial(string value) {
		return (value ?? string.Empty).Trim().ToUpper();
	}

    // ==================== GET DATA ====================
    public List<KelompokDistribusiModel> GetAllDistribusi() {
        var list = new List<KelompokDistribusiModel>();
        using (var conn = new SqlConnection(connString)) {
            var cmd = new SqlCommand("SELECT ID, NamaKelompok FROM ISEE_PNP_KelompokDistribusi ORDER BY NamaKelompok", conn);
            conn.Open();
            using (var reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    list.Add(new KelompokDistribusiModel {
                        ID = reader.GetInt32(0),
                        NamaKelompok = reader.IsDBNull(1) ? "" : reader.GetString(1)
                    });
                }
            }
        }
        return list;
    }

    public List<string> GetPositions() {
        var list = new List<string>();
        using (var conn = new SqlConnection(connString)) {
            var cmd = new SqlCommand("SELECT [Item] FROM [ISEE_STAFF_PARAM] WHERE [Type] = 'Position' ORDER BY [Item] ASC", conn);
            conn.Open();
            using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { list.Add(reader.GetString(0)); } }
        }
        return list;
    }

    public List<string> GetJobTitles() {
        var list = new List<string>();
        using (var conn = new SqlConnection(connString)) {
            var cmd = new SqlCommand("SELECT [Item] FROM [ISEE_STAFF_PARAM] WHERE [Type] = 'Job Title' ORDER BY [Item] ASC", conn);
            conn.Open();
            using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { list.Add(reader.GetString(0)); } }
        }
        return list;
    }

    public List<MemberDistribusiModel> GetMembers(int distribusiId, string search = "") {
        var list = new List<MemberDistribusiModel>();
        using (var conn = new SqlConnection(connString)) {
            string query = "SELECT ID, KelompokDistribusiID, Skill, Position FROM ISEE_PNP_MemberDistribusi WHERE KelompokDistribusiID = @id";
            if (!string.IsNullOrWhiteSpace(search)) query += " AND (Skill LIKE @search OR Position LIKE @search)";
            var cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@id", distribusiId);
            if (!string.IsNullOrWhiteSpace(search)) cmd.Parameters.AddWithValue("@search", "%" + search + "%");
            conn.Open();
            using (var reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    list.Add(new MemberDistribusiModel {
                        ID = reader.GetInt32(0), KelompokDistribusiID = reader.GetInt32(1),
                        Skill = reader.IsDBNull(2) ? "" : reader.GetString(2), 
                        Position = reader.IsDBNull(3) ? "" : reader.GetString(3)
                    });
                }
            }
        }
        return list;
    }

    public List<DistribusiJenisSoalModel> GetJenisSoalByDistribusi(int distribusiId, string search = "") {
        var list = new List<DistribusiJenisSoalModel>();
        using (var conn = new SqlConnection(connString)) {
            string query = @"SELECT a.ID, a.KelompokDistribusiID, a.JenisSoalID, b.NamaJenisSoal, ISNULL(b.Initial1, '') as Initial1, ISNULL(b.Initial2, '') as Initial2
                FROM ISEE_PNP_DistribusiJenisSoal a JOIN ISEE_PNP_JenisSoal b ON a.JenisSoalID = b.ID WHERE a.KelompokDistribusiID = @id";
            if (!string.IsNullOrWhiteSpace(search)) query += " AND (b.NamaJenisSoal LIKE @search OR b.Initial1 LIKE @search OR b.Initial2 LIKE @search)";
            var cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@id", distribusiId);
            if (!string.IsNullOrWhiteSpace(search)) cmd.Parameters.AddWithValue("@search", "%" + search + "%");
            conn.Open();
            using (var reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    list.Add(new DistribusiJenisSoalModel {
                        ID = reader.GetInt32(0), KelompokDistribusiID = reader.GetInt32(1), JenisSoalID = reader.GetInt32(2),
                        NamaJenisSoal = reader.IsDBNull(3) ? "" : reader.GetString(3),
                        Initial1 = reader.IsDBNull(4) ? "" : reader.GetString(4).Trim(),
                        Initial2 = reader.IsDBNull(5) ? "" : reader.GetString(5).Trim()
                    });
                }
            }
        }
        return list;
    }

    public List<JenisSoalModel> GetAllJenisSoal() {
        var list = new List<JenisSoalModel>();
        using (var conn = new SqlConnection(connString)) {
            var cmd = new SqlCommand("SELECT ID, NamaJenisSoal, ISNULL(Initial1, '') as Initial1, ISNULL(Initial2, '') as Initial2 FROM ISEE_PNP_JenisSoal ORDER BY NamaJenisSoal", conn);
            conn.Open();
            using (var reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    list.Add(new JenisSoalModel {
                        ID = reader.GetInt32(0), NamaJenisSoal = reader.GetString(1),
                        Initial1 = reader.IsDBNull(2) ? "" : reader.GetString(2).Trim(),
                        Initial2 = reader.IsDBNull(3) ? "" : reader.GetString(3).Trim()
                    });
                }
            }
        }
        return list;
    }

    // ==================== HISTORY LOG ====================
    public List<HistoryLogModel> GetLogDistribusi(DateTime? dateFrom, DateTime? dateTo) {
        if (dateFrom.HasValue && dateTo.HasValue && dateFrom.Value > dateTo.Value) {
            throw new ArgumentException("Tanggal awal tidak boleh lebih besar dari tanggal akhir.");
        }

        var list = new List<HistoryLogModel>();
        using (var conn = new SqlConnection(connString)) {
            string query = @"SELECT l.ID, l.CreatedAt, l.DomainID, ISNULL(u.Full_Name, l.DomainID) as UserName, l.Action, l.EntityType, l.EntityID
                FROM ISEE_PNP_HistoryLog l LEFT JOIN ISEE_STAFF_DATA u ON l.DomainID = u.Domain_ID
                WHERE l.EntityType IN ('KelompokDistribusi', 'MemberDistribusi', 'JenisSoalDistribusi')";
            
            if (dateFrom.HasValue) query += " AND l.CreatedAt >= @dateFrom";
            if (dateTo.HasValue) query += " AND l.CreatedAt < @dateTo"; 
            query += " ORDER BY l.CreatedAt DESC";
            
            var cmd = new SqlCommand(query, conn);
            if (dateFrom.HasValue) cmd.Parameters.AddWithValue("@dateFrom", dateFrom.Value);
            if (dateTo.HasValue) cmd.Parameters.AddWithValue("@dateTo", dateTo.Value.Date.AddDays(1));
            
            conn.Open();
            using (var reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    list.Add(new HistoryLogModel {
                        ID = reader.GetInt32(0), CreatedAt = reader.GetDateTime(1),
                        DomainID = reader.IsDBNull(2) ? "" : reader.GetString(2),
                        UserName = reader.IsDBNull(3) ? (reader.IsDBNull(2) ? "" : reader.GetString(2)) : reader.GetString(3),
                        Action = reader.IsDBNull(4) ? "" : reader.GetString(4), 
                        EntityType = reader.IsDBNull(5) ? "" : reader.GetString(5), 
                        EntityID = reader.IsDBNull(6) ? "" : reader.GetString(6)
                    });
                }
            }
        }
        return list;
    }

    public List<HistoryLogModel> GetLogJenisSoal(DateTime? dateFrom, DateTime? dateTo) {
		if (dateFrom.HasValue && dateTo.HasValue && dateFrom.Value > dateTo.Value) {
            throw new ArgumentException("Tanggal awal tidak boleh lebih besar dari tanggal akhir.");
        }
		
        var list = new List<HistoryLogModel>();
        using (var conn = new SqlConnection(connString)) {
            string query = @"SELECT l.ID, l.CreatedAt, l.DomainID, ISNULL(u.Full_Name, l.DomainID) as UserName, l.Action, l.EntityType, l.EntityID
                FROM ISEE_PNP_HistoryLog l LEFT JOIN ISEE_STAFF_DATA u ON l.DomainID = u.Domain_ID WHERE l.EntityType = 'JenisSoal'";
			
			if (dateFrom.HasValue) query += " AND l.CreatedAt >= @dateFrom";
            if (dateTo.HasValue) query += " AND l.CreatedAt < @dateTo"; 
            query += " ORDER BY l.CreatedAt DESC";
			
            var cmd = new SqlCommand(query, conn);
            if (dateFrom.HasValue) cmd.Parameters.AddWithValue("@dateFrom", dateFrom.Value);
            if (dateTo.HasValue) cmd.Parameters.AddWithValue("@dateTo", dateTo.Value.AddDays(1).AddSeconds(-1));
            conn.Open();
            using (var reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
					list.Add(new HistoryLogModel {
                        ID = reader.GetInt32(0), CreatedAt = reader.GetDateTime(1),
                        DomainID = reader.IsDBNull(2) ? "" : reader.GetString(2),
                        UserName = reader.IsDBNull(3) ? (reader.IsDBNull(2) ? "" : reader.GetString(2)) : reader.GetString(3),
                        Action = reader.IsDBNull(4) ? "" : reader.GetString(4), 
                        EntityType = reader.IsDBNull(5) ? "" : reader.GetString(5), 
                        EntityID = reader.IsDBNull(6) ? "" : reader.GetString(6)
                    });
                }
            }
        }
        return list;
    }

    private void InsertHistoryLogInTransaction(
		SqlConnection conn,
		SqlTransaction trans,
		string domainID,
		string action,
		string entityType,
		string entityID
	) {
		string query = @"
			INSERT INTO ISEE_PNP_HistoryLog (
				DomainID,
				Action,
				EntityType,
				EntityID,
				CreatedAt
			)
			VALUES (@domainID, @action, @entityType, @entityID, GETDATE())";

		var cmd = new SqlCommand(query, conn, trans);
		cmd.Parameters.AddWithValue("@domainID", domainID);
		cmd.Parameters.AddWithValue("@action", action);
		cmd.Parameters.AddWithValue("@entityType", entityType);
		cmd.Parameters.AddWithValue("@entityID", entityID);
		cmd.ExecuteNonQuery();
	}

    // ==================== SAVE METHODS (FIXED NULL REFERENCE & LOGGING) ====================
    public void SaveDistribusi(List<KelompokDistribusiModel> data, List<int> deletedIds, string userId) {
		data = data ?? new List<KelompokDistribusiModel>();
		deletedIds = deletedIds ?? new List<int>();

		using (var conn = new SqlConnection(connString)) {
			conn.Open();

			using (var trans = conn.BeginTransaction()) {
				try {
					var existingDistribusi = new Dictionary<int, string>();

					var idsToFetch = deletedIds
						.Concat(data.Where(d => d.ID > 0).Select(d => d.ID))
						.Distinct()
						.ToList();

					if (idsToFetch.Count > 0) {
						string idList = string.Join(",", idsToFetch);

						var cmdFetch = new SqlCommand(
							"SELECT ID, NamaKelompok " +
							"FROM ISEE_PNP_KelompokDistribusi " +
							"WHERE ID IN (" + idList + ")",
							conn,
							trans
						);

						using (var reader = cmdFetch.ExecuteReader()) {
							while (reader.Read()) {
								existingDistribusi[reader.GetInt32(0)] = reader.IsDBNull(1)
									? ""
									: reader.GetString(1);
							}
						}
					}

					// DELETE
					if (deletedIds.Count > 0) {
						string ids = string.Join(",", deletedIds);
						
						string checkFkQuery =
							"IF EXISTS (SELECT 1 FROM ISEE_PNP_AgendaUjian WHERE KelompokDistribusiID IN (" + ids + ")) " +
							"THROW 50000, 'Kelompok Distribusi tidak dapat dihapus karena sedang digunakan pada modul Distribusi Soal.', 1; " +
							"IF EXISTS (SELECT 1 FROM ISEE_PNP_ParameterScoring WHERE KelompokDistribusiID IN (" + ids + ")) " +
							"THROW 50000, 'Kelompok Distribusi tidak dapat dihapus karena sedang digunakan pada modul Scoring Parameter.', 1;";
                        new SqlCommand(checkFkQuery, conn, trans).ExecuteNonQuery();

						new SqlCommand(
							"DELETE FROM ISEE_PNP_MemberDistribusi " +
							"WHERE KelompokDistribusiID IN (" + ids + ")",
							conn,
							trans
						).ExecuteNonQuery();


						new SqlCommand(
							"DELETE FROM ISEE_PNP_DistribusiJenisSoal " +
							"WHERE KelompokDistribusiID IN (" + ids + ")",
							conn,
							trans
						).ExecuteNonQuery();

						new SqlCommand(
							"DELETE FROM ISEE_PNP_KelompokDistribusi " +
							"WHERE ID IN (" + ids + ")",
							conn,
							trans
						).ExecuteNonQuery();

						foreach (var id in deletedIds) {
							string namaLama = existingDistribusi.ContainsKey(id)
								? NormalizeText(existingDistribusi[id])
								: "Unknown";

							string actionDesc = string.Format(
								"Menghapus Kelompok Distribusi '{0}'",
								namaLama
							);

							InsertHistoryLogInTransaction(
								conn,
								trans,
								userId,
								actionDesc,
								"KelompokDistribusi",
								id.ToString()
							);
						}
					}

					// CREATE / UPDATE
					foreach (var item in data) {
						string namaBaru = NormalizeText(item.NamaKelompok);
						if (string.IsNullOrEmpty(namaBaru)) continue;

                        string dupQuery = item.ID == 0 
                            ? "SELECT COUNT(1) FROM ISEE_PNP_KelompokDistribusi WHERE NamaKelompok = @nama"
                            : "SELECT COUNT(1) FROM ISEE_PNP_KelompokDistribusi WHERE NamaKelompok = @nama AND ID <> @id";
                        
                        var cmdDup = new SqlCommand(dupQuery, conn, trans);
                        cmdDup.Parameters.AddWithValue("@nama", namaBaru);
                        if (item.ID > 0) cmdDup.Parameters.AddWithValue("@id", item.ID);
                        
                        if (Convert.ToInt32(cmdDup.ExecuteScalar()) > 0) {
                            throw new Exception("Nama Kelompok '" + namaBaru + "' sudah digunakan.");
                        }

						// CREATE
						if (item.ID == 0) {
							string queryIns = @"
								INSERT INTO ISEE_PNP_KelompokDistribusi (NamaKelompok, CreatedBy, CreatedAt)
								OUTPUT INSERTED.ID
								VALUES (@nama, @uid, GETDATE())";

							var cmdIns = new SqlCommand(queryIns, conn, trans);
							cmdIns.Parameters.AddWithValue("@nama", namaBaru);
							cmdIns.Parameters.AddWithValue("@uid", userId);

							item.ID = Convert.ToInt32(cmdIns.ExecuteScalar());

							string actionDesc = string.Format(
								"Menambahkan Kelompok Distribusi baru '{0}'",
								namaBaru
							);

							InsertHistoryLogInTransaction(
								conn,
								trans,
								userId,
								actionDesc,
								"KelompokDistribusi",
								item.ID.ToString()
							);
						}
						// UPDATE
						else {
							if (!existingDistribusi.ContainsKey(item.ID)) {
								continue;
							}

							string namaLama = NormalizeText(existingDistribusi[item.ID]);

							// Jika tidak berubah, skip
							if (namaLama == namaBaru) {
								continue;
							}

							string queryUpd = @"
								UPDATE ISEE_PNP_KelompokDistribusi
								SET NamaKelompok = @nama,
									UpdatedBy = @uid,
									UpdatedAt = GETDATE()
								WHERE ID = @id";

							var cmdUpd = new SqlCommand(queryUpd, conn, trans);
							cmdUpd.Parameters.AddWithValue("@nama", namaBaru);
							cmdUpd.Parameters.AddWithValue("@uid", userId);
							cmdUpd.Parameters.AddWithValue("@id", item.ID);
							cmdUpd.ExecuteNonQuery();

							string actionDesc = string.Format(
								"Mengubah Kelompok Distribusi dari '{0}' menjadi '{1}'",
								namaLama,
								namaBaru
							);

							InsertHistoryLogInTransaction(
								conn,
								trans,
								userId,
								actionDesc,
								"KelompokDistribusi",
								item.ID.ToString()
							);
						}
					}

					trans.Commit();
				} catch {
					trans.Rollback();
					throw;
				}
			}
		}
	}

    public void SaveMembers(int distribusiId, List<MemberDistribusiModel> data, List<int> deletedIds, string userId) {
		data = data ?? new List<MemberDistribusiModel>();
		deletedIds = deletedIds ?? new List<int>();

		using (var conn = new SqlConnection(connString)) {
			conn.Open();

			using (var trans = conn.BeginTransaction()) {
				try {
					string kelompokNama = "";

					var cmdKelompok = new SqlCommand(
						"SELECT NamaKelompok FROM ISEE_PNP_KelompokDistribusi WHERE ID = @id",
						conn,
						trans
					);

					cmdKelompok.Parameters.AddWithValue("@id", distribusiId);

					var kelompokResult = cmdKelompok.ExecuteScalar();

					if (kelompokResult != null && kelompokResult != DBNull.Value) {
						kelompokNama = NormalizeText(kelompokResult.ToString());
					}

					var existingMembers = new Dictionary<int, MemberDistribusiModel>();

					var idsToFetch = deletedIds
						.Concat(data.Where(d => d.ID > 0).Select(d => d.ID))
						.Distinct()
						.ToList();

					if (idsToFetch.Count > 0) {
						string idList = string.Join(",", idsToFetch);

						var cmdFetch = new SqlCommand(
							"SELECT ID, KelompokDistribusiID, Skill, Position " +
							"FROM ISEE_PNP_MemberDistribusi " +
							"WHERE ID IN (" + idList + ")",
							conn,
							trans
						);

						using (var reader = cmdFetch.ExecuteReader()) {
							while (reader.Read()) {
								existingMembers[reader.GetInt32(0)] = new MemberDistribusiModel {
									ID = reader.GetInt32(0),
									KelompokDistribusiID = reader.GetInt32(1),
									Skill = reader.IsDBNull(2) ? "" : reader.GetString(2),
									Position = reader.IsDBNull(3) ? "" : reader.GetString(3)
								};
							}
						}
					}

					// DELETE
					if (deletedIds.Count > 0) {
						string ids = string.Join(",", deletedIds);

						new SqlCommand(
							"DELETE FROM ISEE_PNP_MemberDistribusi " +
							"WHERE ID IN (" + ids + ")",
							conn,
							trans
						).ExecuteNonQuery();

						foreach (var id in deletedIds) {
							MemberDistribusiModel oldMember;

							if (existingMembers.TryGetValue(id, out oldMember)) {
								string oldSkill = NormalizeText(oldMember.Skill);
								string oldPosition = NormalizeText(oldMember.Position);

								string actionDesc = string.Format(
									"Menghapus Member dengan Skill '{0}' dan Position '{1}' dari Kelompok Distribusi '{2}'",
									oldSkill,
									oldPosition,
									kelompokNama
								);

								InsertHistoryLogInTransaction(
									conn,
									trans,
									userId,
									actionDesc,
									"MemberDistribusi",
									id.ToString()
								);
							}
						}
					}

					// CREATE / UPDATE
					foreach (var item in data) {
						string skillBaru = NormalizeText(item.Skill);
						string positionBaru = NormalizeText(item.Position);
						if (string.IsNullOrEmpty(skillBaru) || string.IsNullOrEmpty(positionBaru)) continue;

                        string dupMemQuery = item.ID == 0
                            ? "SELECT COUNT(1) FROM ISEE_PNP_MemberDistribusi WHERE KelompokDistribusiID = @did AND Skill = @skill AND Position = @position"
                            : "SELECT COUNT(1) FROM ISEE_PNP_MemberDistribusi WHERE KelompokDistribusiID = @did AND Skill = @skill AND Position = @position AND ID <> @id";

                        var cmdDupMem = new SqlCommand(dupMemQuery, conn, trans);
                        cmdDupMem.Parameters.AddWithValue("@did", distribusiId);
                        cmdDupMem.Parameters.AddWithValue("@skill", skillBaru);
                        cmdDupMem.Parameters.AddWithValue("@position", positionBaru);
                        if (item.ID > 0) cmdDupMem.Parameters.AddWithValue("@id", item.ID);

                        if (Convert.ToInt32(cmdDupMem.ExecuteScalar()) > 0) {
                            throw new Exception("Kombinasi Skill '" + skillBaru + "' dan Position '" + positionBaru + "' sudah ada dalam kelompok ini.");
                        }

						// CREATE
						if (item.ID == 0) {
							string queryIns = @"
								INSERT INTO ISEE_PNP_MemberDistribusi (
									KelompokDistribusiID,
									Skill,
									Position,
									CreatedBy,
									CreatedAt
								)
								OUTPUT INSERTED.ID
								VALUES (@did, @skill, @position, @uid, GETDATE())";

							var cmdIns = new SqlCommand(queryIns, conn, trans);
							cmdIns.Parameters.AddWithValue("@did", distribusiId);
							cmdIns.Parameters.AddWithValue("@skill", skillBaru);
							cmdIns.Parameters.AddWithValue("@position", positionBaru);
							cmdIns.Parameters.AddWithValue("@uid", userId);

							item.ID = Convert.ToInt32(cmdIns.ExecuteScalar());

							string actionDesc = string.Format(
								"Menambahkan Member baru dengan Skill '{0}' dan Position '{1}' ke Kelompok Distribusi '{2}'",
								skillBaru,
								positionBaru,
								kelompokNama
							);

							InsertHistoryLogInTransaction(
								conn,
								trans,
								userId,
								actionDesc,
								"MemberDistribusi",
								item.ID.ToString()
							);
						}
						// UPDATE
						else {
							MemberDistribusiModel oldMember;

							if (!existingMembers.TryGetValue(item.ID, out oldMember)) {
								continue;
							}

							string skillLama = NormalizeText(oldMember.Skill);
							string positionLama = NormalizeText(oldMember.Position);

							// Jika tidak berubah, skip
							if (skillLama == skillBaru && positionLama == positionBaru) {
								continue;
							}

							string queryUpd = @"
								UPDATE ISEE_PNP_MemberDistribusi
								SET Skill = @skill,
									Position = @position,
									UpdatedBy = @uid,
									UpdatedAt = GETDATE()
								WHERE ID = @id";

							var cmdUpd = new SqlCommand(queryUpd, conn, trans);
							cmdUpd.Parameters.AddWithValue("@skill", skillBaru);
							cmdUpd.Parameters.AddWithValue("@position", positionBaru);
							cmdUpd.Parameters.AddWithValue("@uid", userId);
							cmdUpd.Parameters.AddWithValue("@id", item.ID);
							cmdUpd.ExecuteNonQuery();

							string actionDesc = string.Format(
								"Mengubah Member di Kelompok Distribusi '{0}' dari Skill '{1}' Position '{2}' menjadi Skill '{3}' Position '{4}'",
								kelompokNama,
								skillLama,
								positionLama,
								skillBaru,
								positionBaru
							);

							InsertHistoryLogInTransaction(
								conn,
								trans,
								userId,
								actionDesc,
								"MemberDistribusi",
								item.ID.ToString()
							);
						}
					}

					trans.Commit();
				} catch {
					trans.Rollback();
					throw;
				}
			}
		}
	}

    public void SaveDistribusiJenisSoal(int distribusiId, List<DistribusiJenisSoalModel> data, List<int> deletedIds, string userId) {
		data = data ?? new List<DistribusiJenisSoalModel>();
		deletedIds = deletedIds ?? new List<int>();

		using (var conn = new SqlConnection(connString)) {
			conn.Open();

			using (var trans = conn.BeginTransaction()) {
				try {
					string kelompokNama = "";

					var cmdKelompok = new SqlCommand(
						"SELECT NamaKelompok FROM ISEE_PNP_KelompokDistribusi WHERE ID = @id",
						conn,
						trans
					);

					cmdKelompok.Parameters.AddWithValue("@id", distribusiId);

					var kelompokResult = cmdKelompok.ExecuteScalar();

					if (kelompokResult != null && kelompokResult != DBNull.Value) {
						kelompokNama = NormalizeText(kelompokResult.ToString());
					}

					var existingRelation = new Dictionary<int, int>();

					var relationIdsToFetch = deletedIds
						.Concat(
							data
								.Where(d => d.ID > 0)
								.Select(d => d.ID)
						)
						.Distinct()
						.ToList();

					if (relationIdsToFetch.Count > 0) {
						string idList = string.Join(",", relationIdsToFetch);

						var cmdFetch = new SqlCommand(
							"SELECT ID, JenisSoalID " +
							"FROM ISEE_PNP_DistribusiJenisSoal " +
							"WHERE KelompokDistribusiID = @did " +
							"AND ID IN (" + idList + ")",
							conn,
							trans
						);

						cmdFetch.Parameters.AddWithValue("@did", distribusiId);

						using (var reader = cmdFetch.ExecuteReader()) {
							while (reader.Read()) {
								existingRelation[reader.GetInt32(0)] = reader.GetInt32(1);
							}
						}
					}

					var jenisSoalIds = existingRelation.Values
						.Concat(
							data
								.Where(d => d.JenisSoalID > 0)
								.Select(d => d.JenisSoalID)
						)
						.Distinct()
						.ToList();

					var jenisSoalNames = new Dictionary<int, string>();

					if (jenisSoalIds.Count > 0) {
						string jsIdList = string.Join(",", jenisSoalIds);

						var cmdJS = new SqlCommand(
							"SELECT ID, NamaJenisSoal " +
							"FROM ISEE_PNP_JenisSoal " +
							"WHERE ID IN (" + jsIdList + ")",
							conn,
							trans
						);

						using (var reader = cmdJS.ExecuteReader()) {
							while (reader.Read()) {
								jenisSoalNames[reader.GetInt32(0)] = reader.IsDBNull(1)
									? ""
									: reader.GetString(1);
							}
						}
					}

					// ================================
					// DELETE
					// ================================
					if (deletedIds.Count > 0) {
						string ids = string.Join(",", deletedIds);

						new SqlCommand(
							"DELETE FROM ISEE_PNP_DistribusiJenisSoal " +
							"WHERE ID IN (" + ids + ")",
							conn,
							trans
						).ExecuteNonQuery();

						foreach (var id in deletedIds) {
							int jenisSoalId;

							if (existingRelation.TryGetValue(id, out jenisSoalId)) {
								string jenisSoalNama = jenisSoalNames.ContainsKey(jenisSoalId)
									? jenisSoalNames[jenisSoalId]
									: "Unknown";

								string actionDesc = string.Format(
									"Menghapus Jenis Soal '{0}' dari Kelompok Distribusi '{1}'",
									jenisSoalNama,
									kelompokNama
								);

								InsertHistoryLogInTransaction(
									conn,
									trans,
									userId,
									actionDesc,
									"JenisSoalDistribusi",
									id.ToString()
								);
							}
						}
					}

					// ================================
					// CREATE / UPDATE
					// ================================
					foreach (var item in data) {
						if (item.JenisSoalID <= 0) {
							continue;
						}

						// ================================
						// CREATE
						// ================================
						if (item.ID == 0) {
							var cmdCheck = new SqlCommand(@"
								SELECT COUNT(*)
								FROM ISEE_PNP_DistribusiJenisSoal
								WHERE KelompokDistribusiID = @did
								  AND JenisSoalID = @jid",
								conn,
								trans
							);

							cmdCheck.Parameters.AddWithValue("@did", distribusiId);
							cmdCheck.Parameters.AddWithValue("@jid", item.JenisSoalID);

							int count = Convert.ToInt32(cmdCheck.ExecuteScalar());

							if (count > 0) {
								continue;
							}

							string queryIns = @"
								INSERT INTO ISEE_PNP_DistribusiJenisSoal (
									KelompokDistribusiID,
									JenisSoalID,
									CreatedBy,
									CreatedAt
								)
								OUTPUT INSERTED.ID
								VALUES (
									@did,
									@jid,
									@uid,
									GETDATE()
								)";

							var cmdIns = new SqlCommand(queryIns, conn, trans);

							cmdIns.Parameters.AddWithValue("@did", distribusiId);
							cmdIns.Parameters.AddWithValue("@jid", item.JenisSoalID);
							cmdIns.Parameters.AddWithValue("@uid", userId);

							item.ID = Convert.ToInt32(cmdIns.ExecuteScalar());

							string jenisSoalNama = jenisSoalNames.ContainsKey(item.JenisSoalID)
								? jenisSoalNames[item.JenisSoalID]
								: "Unknown";

							string actionDesc = string.Format(
								"Menambahkan Jenis Soal '{0}' ke Kelompok Distribusi '{1}'",
								jenisSoalNama,
								kelompokNama
							);

							InsertHistoryLogInTransaction(
								conn,
								trans,
								userId,
								actionDesc,
								"JenisSoalDistribusi",
								item.ID.ToString()
							);
						}

						// ================================
						// UPDATE
						// ================================
						else {
							int jenisSoalLamaId;

							if (!existingRelation.TryGetValue(item.ID, out jenisSoalLamaId)) {
								continue;
							}

							if (jenisSoalLamaId == item.JenisSoalID) {
								continue;
							}

							var cmdCheck = new SqlCommand(@"
								SELECT COUNT(*)
								FROM ISEE_PNP_DistribusiJenisSoal
								WHERE KelompokDistribusiID = @did
								  AND JenisSoalID = @jid
								  AND ID <> @id",
								conn,
								trans
							);

							cmdCheck.Parameters.AddWithValue("@did", distribusiId);
							cmdCheck.Parameters.AddWithValue("@jid", item.JenisSoalID);
							cmdCheck.Parameters.AddWithValue("@id", item.ID);

							int count = Convert.ToInt32(cmdCheck.ExecuteScalar());

							if (count > 0) {
								continue;
							}

							string queryUpd = @"
								UPDATE ISEE_PNP_DistribusiJenisSoal
								SET JenisSoalID = @jid,
									UpdatedBy = @uid,
									UpdatedAt = GETDATE()
								WHERE ID = @id
								  AND KelompokDistribusiID = @did";

							var cmdUpd = new SqlCommand(queryUpd, conn, trans);

							cmdUpd.Parameters.AddWithValue("@jid", item.JenisSoalID);
							cmdUpd.Parameters.AddWithValue("@uid", userId);
							cmdUpd.Parameters.AddWithValue("@id", item.ID);
							cmdUpd.Parameters.AddWithValue("@did", distribusiId);

							int affectedRows = cmdUpd.ExecuteNonQuery();

							if (affectedRows <= 0) {
								continue;
							}

							string jenisSoalLamaNama =
								jenisSoalNames.ContainsKey(jenisSoalLamaId)
									? jenisSoalNames[jenisSoalLamaId]
									: "Unknown";

							string jenisSoalBaruNama =
								jenisSoalNames.ContainsKey(item.JenisSoalID)
									? jenisSoalNames[item.JenisSoalID]
									: "Unknown";

							string actionDesc = string.Format(
								"Mengubah Jenis Soal pada Kelompok Distribusi '{0}' dari '{1}' menjadi '{2}'",
								kelompokNama,
								jenisSoalLamaNama,
								jenisSoalBaruNama
							);

							InsertHistoryLogInTransaction(
								conn,
								trans,
								userId,
								actionDesc,
								"JenisSoalDistribusi",
								item.ID.ToString()
							);
						}
					}

					trans.Commit();
				} catch {
					trans.Rollback();
					throw;
				}
			}
		}
	}
}

1. ParameterScoringRepository.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;

public class ParameterScoringRepository
{
    private string connString = ConfigurationManager.ConnectionStrings["iSee"].ConnectionString;

    private string NormalizeText(string value)
    {
        return (value ?? string.Empty).Trim();
    }

    // ==================== GET DATA ====================
    public List<ParameterScoringModel> GetScoring(List<int> kelompokIds, List<string> skills, string search = "")
    {
        var list = new List<ParameterScoringModel>();

        using (var conn = new SqlConnection(connString))
        {
            string query = @"
                SELECT a.ID, a.KelompokDistribusiID, b.NamaKelompok, a.Skill, a.Level, a.NilaiTarget
                FROM ISEE_PNP_ParameterScoring a
                JOIN ISEE_PNP_KelompokDistribusi b ON a.KelompokDistribusiID = b.ID
                WHERE 1 = 1";

            var cmd = new SqlCommand();

            if (kelompokIds != null && kelompokIds.Count > 0)
            {
                var idParams = new List<string>();

                for (int i = 0; i < kelompokIds.Count; i++)
                {
                    string pName = "@kid" + i;
                    idParams.Add(pName);
                    cmd.Parameters.AddWithValue(pName, kelompokIds[i]);
                }

                query += " AND a.KelompokDistribusiID IN (" + string.Join(",", idParams) + ")";
            }

            if (skills != null && skills.Count > 0)
            {
                var skillParams = new List<string>();

                for (int i = 0; i < skills.Count; i++)
                {
                    string pName = "@skill" + i;
                    skillParams.Add(pName);
                    cmd.Parameters.AddWithValue(pName, skills[i]);
                }

                query += " AND a.Skill IN (" + string.Join(",", skillParams) + ")";
            }

            if (!string.IsNullOrWhiteSpace(search))
            {
                query += @" AND (b.NamaKelompok LIKE @search
                              OR a.Skill LIKE @search
                              OR a.Level LIKE @search
                              OR CONVERT(nvarchar(50), a.NilaiTarget) LIKE @search)";

                cmd.Parameters.AddWithValue("@search", "%" + search + "%");
            }

            query += " ORDER BY b.NamaKelompok, a.Skill, a.Level";

            cmd.CommandText = query;
            cmd.Connection = conn;

            conn.Open();

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add(new ParameterScoringModel
                    {
                        ID = reader.GetInt32(0),
                        KelompokDistribusiID = reader.GetInt32(1),
                        NamaKelompok = reader.IsDBNull(2) ? "" : reader.GetString(2),
                        Skill = reader.IsDBNull(3) ? "" : reader.GetString(3),
                        Level = reader.IsDBNull(4) ? "" : reader.GetString(4),
                        NilaiTarget = reader.GetDecimal(5)
                    });
                }
            }
        }

        return list;
    }

    // ==================== PARENT-CHILD: SKILL BERDASARKAN KELOMPOK DISTRIBUSI ====================
    public List<string> GetSkillsByKelompok(int kelompokId)
    {
        var list = new List<string>();

        if (kelompokId <= 0) return list;

        using (var conn = new SqlConnection(connString))
        {
            string query = @"
                SELECT DISTINCT m.Skill
                FROM ISEE_PNP_MemberDistribusi m
                WHERE m.KelompokDistribusiID = @kid
                  AND m.Skill IS NOT NULL
                  AND LTRIM(RTRIM(m.Skill)) <> ''
                ORDER BY m.Skill ASC";

            var cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@kid", kelompokId);

            conn.Open();

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add(reader.GetString(0));
                }
            }
        }

        return list;
    }

    // ==================== LEVEL ====================
    public List<ParameterLevel> GetAllLevel()
    {
        var list = new List<ParameterLevel>();

        using (var conn = new SqlConnection(connString))
        {
            var cmd = new SqlCommand(@"
                SELECT DISTINCT SDM_LEVEL
                FROM ISEE_STAFF_DATA
                WHERE SDM_LEVEL IS NOT NULL
                ORDER BY SDM_LEVEL ASC", conn);

            conn.Open();

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add(new ParameterLevel
                    {
                        SDM_LEVEL = reader.GetString(0)
                    });
                }
            }
        }

        return list;
    }

    // ==================== HISTORY LOG ====================
    public List<HistoryLogModel> GetLogScoring(DateTime? dateFrom, DateTime? dateTo)
    {
        var list = new List<HistoryLogModel>();

        using (var conn = new SqlConnection(connString))
        {
            string query = @"
                SELECT l.ID,
                       l.CreatedAt,
                       l.DomainID,
                       ISNULL(u.Full_Name, l.DomainID) AS UserName,
                       l.Action,
                       l.EntityType,
                       l.EntityID
                FROM ISEE_PNP_HistoryLog l
                LEFT JOIN ISEE_STAFF_DATA u ON l.DomainID = u.Domain_ID
                WHERE l.EntityType = 'ParameterScoring'";

            if (dateFrom.HasValue)
            {
                query += " AND l.CreatedAt >= @dateFrom";
            }

            if (dateTo.HasValue)
            {
                query += " AND l.CreatedAt <= @dateTo";
            }

            query += " ORDER BY l.CreatedAt DESC";

            var cmd = new SqlCommand(query, conn);

            if (dateFrom.HasValue)
            {
                cmd.Parameters.AddWithValue("@dateFrom", dateFrom.Value);
            }

            if (dateTo.HasValue)
            {
                cmd.Parameters.AddWithValue("@dateTo", dateTo.Value.AddDays(1).AddSeconds(-1));
            }

            conn.Open();

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add(new HistoryLogModel
                    {
                        ID = reader.GetInt32(0),
                        CreatedAt = reader.GetDateTime(1),
                        DomainID = reader.IsDBNull(2) ? "" : reader.GetString(2),
                        UserName = reader.IsDBNull(3) ? "" : reader.GetString(3),
                        Action = reader.IsDBNull(4) ? "" : reader.GetString(4),
                        EntityType = reader.IsDBNull(5) ? "" : reader.GetString(5),
                        EntityID = reader.IsDBNull(6) ? "" : reader.GetString(6)
                    });
                }
            }
        }

        return list;
    }

    private void InsertHistoryLogInTransaction(
        SqlConnection conn,
        SqlTransaction trans,
        string domainID,
        string action,
        string entityType,
        string entityID)
    {
        string query = @"
            INSERT INTO ISEE_PNP_HistoryLog (
                DomainID,
                Action,
                EntityType,
                EntityID,
                CreatedAt
            )
            VALUES (@domainID, @action, @entityType, @entityID, GETDATE())";

        var cmd = new SqlCommand(query, conn, trans);

        cmd.Parameters.AddWithValue("@domainID", domainID);
        cmd.Parameters.AddWithValue("@action", action);
        cmd.Parameters.AddWithValue("@entityType", entityType);
        cmd.Parameters.AddWithValue("@entityID", entityID);

        cmd.ExecuteNonQuery();
    }

    // ==================== BULK UPSERT + HARD DELETE + LOGGING ====================
    public void SaveScoring(List<ParameterScoringModel> data, List<int> deletedIds, string userId)
    {
        data = data ?? new List<ParameterScoringModel>();
        deletedIds = deletedIds ?? new List<int>();

        using (var conn = new SqlConnection(connString))
        {
            conn.Open();

            using (var trans = conn.BeginTransaction())
            {
                try
                {
                    // ==================================================
                    // 1. Ambil data existing untuk kebutuhan log & compare
                    // ==================================================
                    var existingScoring = new Dictionary<int, ParameterScoringModel>();

                    var idsToFetch = deletedIds
                        .Concat(data.Where(d => d.ID > 0).Select(d => d.ID))
                        .Distinct()
                        .ToList();

                    if (idsToFetch.Count > 0)
                    {
                        string idList = string.Join(",", idsToFetch);

                        var cmdFetch = new SqlCommand(@"
                            SELECT a.ID,
                                   a.KelompokDistribusiID,
                                   b.NamaKelompok,
                                   a.Skill,
                                   a.Level,
                                   a.NilaiTarget
                            FROM ISEE_PNP_ParameterScoring a
                            JOIN ISEE_PNP_KelompokDistribusi b ON a.KelompokDistribusiID = b.ID
                            WHERE a.ID IN (" + idList + ")", conn, trans);

                        using (var reader = cmdFetch.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                existingScoring[reader.GetInt32(0)] = new ParameterScoringModel
                                {
                                    ID = reader.GetInt32(0),
                                    KelompokDistribusiID = reader.GetInt32(1),
                                    NamaKelompok = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                    Skill = reader.IsDBNull(3) ? "" : reader.GetString(3),
                                    Level = reader.IsDBNull(4) ? "" : reader.GetString(4),
                                    NilaiTarget = reader.GetDecimal(5)
                                };
                            }
                        }
                    }

                    // ==================================================
                    // 2. Ambil nama kelompok distribusi untuk log
                    // ==================================================
                    var kelompokIds = data
                        .Where(d => d.KelompokDistribusiID > 0)
                        .Select(d => d.KelompokDistribusiID)
                        .Concat(existingScoring.Values.Select(x => x.KelompokDistribusiID))
                        .Distinct()
                        .ToList();

                    var kelompokNames = new Dictionary<int, string>();

                    if (kelompokIds.Count > 0)
                    {
                        string kelompokIdList = string.Join(",", kelompokIds);

                        var cmdKelompok = new SqlCommand(@"
                            SELECT ID, NamaKelompok
                            FROM ISEE_PNP_KelompokDistribusi
                            WHERE ID IN (" + kelompokIdList + ")", conn, trans);

                        using (var reader = cmdKelompok.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                kelompokNames[reader.GetInt32(0)] = reader.IsDBNull(1) ? "" : reader.GetString(1);
                            }
                        }
                    }

                    // ==================================================
                    // 3. DELETE
                    // ==================================================
                    if (deletedIds.Count > 0)
                    {
                        string ids = string.Join(",", deletedIds);

                        new SqlCommand(
                            string.Format("DELETE FROM ISEE_PNP_ParameterScoring WHERE ID IN ({0})", ids),
                            conn,
                            trans
                        ).ExecuteNonQuery();

                        foreach (var id in deletedIds)
                        {
                            ParameterScoringModel old;

                            if (existingScoring.TryGetValue(id, out old))
                            {
                                string namaKelompokLama = NormalizeText(old.NamaKelompok);

                                if (string.IsNullOrEmpty(namaKelompokLama))
                                {
                                    string fallbackNama;
                                    if (kelompokNames.TryGetValue(old.KelompokDistribusiID, out fallbackNama))
                                    {
                                        namaKelompokLama = fallbackNama;
                                    }
                                    else
                                    {
                                        namaKelompokLama = "Unknown";
                                    }
                                }

                                string actionDesc = string.Format(
                                    "Menghapus Parameter Scoring pada Kelompok '{0}', Skill '{1}', Level '{2}' dengan Nilai Target '{3}'",
                                    namaKelompokLama,
                                    NormalizeText(old.Skill),
                                    NormalizeText(old.Level),
                                    old.NilaiTarget
                                );

                                InsertHistoryLogInTransaction(
                                    conn,
                                    trans,
                                    userId,
                                    actionDesc,
                                    "ParameterScoring",
                                    id.ToString()
                                );
                            }
                        }
                    }

                    // ==================================================
                    // 4. CREATE / UPDATE
                    // ==================================================
                    foreach (var item in data)
                    {
                        string skillBaru = NormalizeText(item.Skill);
                        string levelBaru = NormalizeText(item.Level);

                        if (item.KelompokDistribusiID <= 0 || string.IsNullOrEmpty(skillBaru) || string.IsNullOrEmpty(levelBaru))
                        {
                            continue;
                        }

                        string namaKelompokBaru;
                        if (!kelompokNames.TryGetValue(item.KelompokDistribusiID, out namaKelompokBaru))
                        {
                            namaKelompokBaru = "Unknown";
                        }

                        // ==================== CREATE ====================
                        if (item.ID == 0)
                        {
                            string queryIns = @"
                                INSERT INTO ISEE_PNP_ParameterScoring
                                (
                                    KelompokDistribusiID,
                                    Skill,
                                    Level,
                                    NilaiTarget,
                                    CreatedBy,
                                    CreatedAt
                                )
                                OUTPUT INSERTED.ID
                                VALUES
                                (
                                    @did,
                                    @skill,
                                    @lvl,
                                    @nilai,
                                    @uid,
                                    GETDATE()
                                )";

                            var cmdIns = new SqlCommand(queryIns, conn, trans);

                            cmdIns.Parameters.AddWithValue("@did", item.KelompokDistribusiID);
                            cmdIns.Parameters.AddWithValue("@skill", skillBaru);
                            cmdIns.Parameters.AddWithValue("@lvl", levelBaru);
                            cmdIns.Parameters.AddWithValue("@nilai", item.NilaiTarget);
                            cmdIns.Parameters.AddWithValue("@uid", userId);

                            item.ID = Convert.ToInt32(cmdIns.ExecuteScalar());

                            string actionDesc = string.Format(
                                "Menambahkan Parameter Scoring baru pada Kelompok '{0}', Skill '{1}', Level '{2}' dengan Nilai Target '{3}'",
                                namaKelompokBaru,
                                skillBaru,
                                levelBaru,
                                item.NilaiTarget
                            );

                            InsertHistoryLogInTransaction(
                                conn,
                                trans,
                                userId,
                                actionDesc,
                                "ParameterScoring",
                                item.ID.ToString()
                            );
                        }
                        // ==================== UPDATE ====================
                        else
                        {
                            ParameterScoringModel old;

                            if (!existingScoring.TryGetValue(item.ID, out old))
                            {
                                continue;
                            }

                            string namaKelompokLama = NormalizeText(old.NamaKelompok);

                            if (string.IsNullOrEmpty(namaKelompokLama))
                            {
                                string fallbackNama;
                                if (kelompokNames.TryGetValue(old.KelompokDistribusiID, out fallbackNama))
                                {
                                    namaKelompokLama = fallbackNama;
                                }
                                else
                                {
                                    namaKelompokLama = "Unknown";
                                }
                            }

                            string skillLama = NormalizeText(old.Skill);
                            string levelLama = NormalizeText(old.Level);

                            // Jika tidak ada perubahan, skip
                            if (
                                old.KelompokDistribusiID == item.KelompokDistribusiID &&
                                skillLama == skillBaru &&
                                levelLama == levelBaru &&
                                old.NilaiTarget == item.NilaiTarget
                            )
                            {
                                continue;
                            }

                            string queryUpd = @"
                                UPDATE ISEE_PNP_ParameterScoring
                                SET KelompokDistribusiID = @did,
                                    Skill = @skill,
                                    Level = @lvl,
                                    NilaiTarget = @nilai,
                                    UpdatedBy = @uid,
                                    UpdatedAt = GETDATE()
                                WHERE ID = @id";

                            var cmdUpd = new SqlCommand(queryUpd, conn, trans);

                            cmdUpd.Parameters.AddWithValue("@did", item.KelompokDistribusiID);
                            cmdUpd.Parameters.AddWithValue("@skill", skillBaru);
                            cmdUpd.Parameters.AddWithValue("@lvl", levelBaru);
                            cmdUpd.Parameters.AddWithValue("@nilai", item.NilaiTarget);
                            cmdUpd.Parameters.AddWithValue("@uid", userId);
                            cmdUpd.Parameters.AddWithValue("@id", item.ID);

                            cmdUpd.ExecuteNonQuery();

                            string actionDesc = string.Format(
                                "Mengubah Parameter Scoring dari Kelompok '{0}', Skill '{1}', Level '{2}', Nilai Target '{3}' menjadi Kelompok '{4}', Skill '{5}', Level '{6}', Nilai Target '{7}'",
                                namaKelompokLama,
                                skillLama,
                                levelLama,
                                old.NilaiTarget,
                                namaKelompokBaru,
                                skillBaru,
                                levelBaru,
                                item.NilaiTarget
                            );

                            InsertHistoryLogInTransaction(
                                conn,
                                trans,
                                userId,
                                actionDesc,
                                "ParameterScoring",
                                item.ID.ToString()
                            );
                        }
                    }

                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
        }
    }
}

Atau bisa dilihat yang ada pada Dashboard yang telah berhasil menghitung dengan sesuai:

using Models.pnp.dashboard;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Repositories.pnp.dashboard
{
    public class DashboardRepository
    {
        private readonly string _connStr =
            ConfigurationManager.ConnectionStrings["iSee"].ConnectionString;

        private const string FamCte = @"
			;WITH Fam AS (
				SELECT a.ID AS AgendaID, a.ID AS RootID
				FROM ISEE_PNP_AgendaUjian a
				WHERE a.ParentAgendaID IS NULL
				UNION ALL
				SELECT c.ID, f.RootID
				FROM ISEE_PNP_AgendaUjian c
				INNER JOIN Fam f ON c.ParentAgendaID = f.AgendaID
			)";

        private const string RecursionHint = " OPTION (MAXRECURSION 50)";

        private static string PeriodeExpr(string alias)
        {
            return "ISNULL(NULLIF(LTRIM(RTRIM(" + alias + ".PeriodeBerjalan)), ''), CONVERT(char(7), " + alias + ".TanggalMulai, 120))";
        }

        private const string DurasiApply = @"
			CROSS APPLY (
				SELECT
					CASE WHEN up.MulaiUjian IS NULL OR up.SelesaiUjian IS NULL OR up.SelesaiUjian < up.MulaiUjian
						 THEN NULL
						 ELSE DATEDIFF(SECOND, up.MulaiUjian, up.SelesaiUjian) END AS Wall,
					CASE WHEN up.SisaWaktuDetik IS NOT NULL AND a.DurasiMenit > 0
						 THEN a.DurasiMenit * 60 - up.SisaWaktuDetik
						 ELSE NULL END AS ByRemaining
			) tw
			CROSS APPLY (
				SELECT CASE
					WHEN tw.Wall IS NULL THEN NULL
					WHEN tw.ByRemaining IS NOT NULL AND tw.ByRemaining > 0 AND tw.ByRemaining <= tw.Wall THEN tw.ByRemaining
					WHEN a.DurasiMenit > 0 AND tw.Wall > a.DurasiMenit * 60 THEN a.DurasiMenit * 60
					ELSE tw.Wall END AS DurasiDetik
			) td";

        private const string PesertaCtes = @",
			Peserta AS (
				SELECT r.ID AS RootID, UPPER(LTRIM(RTRIM(s.Domain_ID))) AS DomainID
				FROM Roots r
				INNER JOIN ISEE_PNP_MemberDistribusi md ON md.KelompokDistribusiID = r.KelompokDistribusiID
				INNER JOIN ISEE_STAFF_DATA s
					ON LTRIM(RTRIM(s.Position)) = LTRIM(RTRIM(md.Position))
				   AND LTRIM(RTRIM(s.Job_Title)) = LTRIM(RTRIM(md.Skill))
				WHERE NULLIF(LTRIM(RTRIM(s.Domain_ID)), '') IS NOT NULL
				UNION
				SELECT f.RootID, UPPER(LTRIM(RTRIM(i.DomainID)))
				FROM ISEE_PNP_InvitePeserta i
				INNER JOIN Fam f ON f.AgendaID = i.AgendaUjianID
				INNER JOIN Roots r ON r.ID = f.RootID
				UNION
				SELECT f.RootID, UPPER(LTRIM(RTRIM(u.DomainID)))
				FROM ISEE_PNP_UjianPeserta u
				INNER JOIN Fam f ON f.AgendaID = u.AgendaUjianID
				INNER JOIN Roots r ON r.ID = f.RootID
			),
			TotalPeserta AS (
				SELECT RootID, COUNT(*) AS Total FROM Peserta GROUP BY RootID
			)";

        // Peserta yang sudah selesai (DomainID unik, termasuk agenda susulan). Butuh CTE "Roots".
        private const string SelesaiCte = @",
			Selesai AS (
				SELECT f.RootID, COUNT(DISTINCT UPPER(LTRIM(RTRIM(u.DomainID)))) AS Total
				FROM ISEE_PNP_UjianPeserta u
				INNER JOIN Fam f ON f.AgendaID = u.AgendaUjianID
				INNER JOIN Roots r ON r.ID = f.RootID
				WHERE u.SelesaiUjian IS NOT NULL
				GROUP BY f.RootID
			)";

        private const string JawabanAggCtes = @",
			Jawab AS (
				SELECT jp.BankSoalID,
					   CASE WHEN c.OpsiID IS NULL THEN 0 ELSE 1 END AS IsBenar
				FROM ISEE_PNP_JawabanPeserta jp
				INNER JOIN ISEE_PNP_UjianPeserta up ON up.ID = jp.UjianPesertaID
				INNER JOIN Fam f ON f.AgendaID = up.AgendaUjianID
				OUTER APPLY (
					SELECT TOP 1 o.ID AS OpsiID
					FROM ISEE_PNP_SoalPesertaOpsi o
					WHERE o.UjianPesertaID = jp.UjianPesertaID
					  AND o.BankSoalID = jp.BankSoalID
					  AND o.IsCorrect = 1
					  AND o.AssignedLabel = jp.JawabanDipilih
				) c
				WHERE f.RootID = @agendaId
				  AND jp.JawabanDipilih IS NOT NULL
			),
			JawabAgg AS (
				SELECT BankSoalID,
					   SUM(IsBenar) AS Benar,
					   SUM(1 - IsBenar) AS Salah
				FROM Jawab
				GROUP BY BankSoalID
			),
			Dist AS (
				SELECT ds.BankSoalID, COUNT(*) AS Cnt
				FROM ISEE_PNP_DistribusiSoal ds
				INNER JOIN Fam f ON f.AgendaID = ds.AgendaUjianID
				WHERE f.RootID = @agendaId
				GROUP BY ds.BankSoalID
			)";

        // ======================================================================
        // SHARED DROPDOWN OPTIONS
        // ======================================================================

        public List<OptionModel> GetAgendaOptions()
        {
            var list = new List<OptionModel>();

            using (var conn = new SqlConnection(_connStr))
            {
                string q = @"
					SELECT ID, NamaAgenda, FORMAT(TanggalMulai, 'yyyy-MM') AS Periode
					FROM ISEE_PNP_AgendaUjian
					WHERE ParentAgendaID IS NULL
					ORDER BY TanggalMulai DESC";

                using (var cmd = new SqlCommand(q, conn))
                {
                    conn.Open();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(new OptionModel
                            {
                                ID = reader.GetInt32(0),
                                NamaAgenda = reader.IsDBNull(1) ? "" : reader.GetString(1),
                                Periode = reader.IsDBNull(2) ? "" : reader.GetString(2)
                            });
                        }
                    }
                }
            }

            return list;
        }

        public List<SimpleOptionModel> GetKelompokList()
        {
            var list = new List<SimpleOptionModel>();

            using (var conn = new SqlConnection(_connStr))
            {
                var cmd = new SqlCommand("SELECT ID, NamaKelompok FROM ISEE_PNP_KelompokDistribusi ORDER BY NamaKelompok", conn);
                conn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        list.Add(new SimpleOptionModel { ID = reader.GetInt32(0), NamaKelompok = reader.IsDBNull(1) ? "" : reader.GetString(1) });
                }
            }

            return list;
        }

        public List<JenisSoalOptionModel> GetJenisSoalList()
        {
            var list = new List<JenisSoalOptionModel>();

            using (var conn = new SqlConnection(_connStr))
            {
                var cmd = new SqlCommand("SELECT ID, NamaJenisSoal FROM ISEE_PNP_JenisSoal ORDER BY NamaJenisSoal", conn);
                conn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        list.Add(new JenisSoalOptionModel { ID = reader.GetInt32(0), NamaJenisSoal = reader.IsDBNull(1) ? "" : reader.GetString(1) });
                }
            }

            return list;
        }

        // ======================================================================
        // 1. PASS GRADE (agenda induk + agenda susulan digabung)
        // ======================================================================

        public List<PassGradeModel> GetPassGrade(string periode, string nama, string status)
        {
            var list = new List<PassGradeModel>();
            var pars = new List<SqlParameter>();
            string filters = "";

            if (!string.IsNullOrWhiteSpace(periode))
            {
                filters += " AND Periode = @periode";
                pars.Add(new SqlParameter("@periode", SqlDbType.VarChar, 7) { Value = periode.Trim() });
            }

            if (!string.IsNullOrWhiteSpace(nama))
            {
                filters += " AND NamaAgenda LIKE @nama";
                pars.Add(new SqlParameter("@nama", SqlDbType.NVarChar, 255) { Value = "%" + nama.Trim() + "%" });
            }

            if (!string.IsNullOrWhiteSpace(status))
            {
                filters += " AND Status = @status";
                pars.Add(new SqlParameter("@status", SqlDbType.VarChar, 50) { Value = status.Trim() });
            }

            string q = FamCte + @",
				RootsRaw AS (
					SELECT a.ID, a.NamaAgenda, a.KelompokDistribusiID, a.TanggalMulai,
						   " + PeriodeExpr("a") + @" AS Periode,
						   CASE WHEN EXISTS (
									SELECT 1
									FROM Fam x
									INNER JOIN ISEE_PNP_AgendaUjian xa ON xa.ID = x.AgendaID
									WHERE x.RootID = a.ID AND xa.Status = 'Aktif')
								THEN 'Aktif' ELSE a.Status END AS Status,
						   (SELECT COUNT(*) FROM Fam x WHERE x.RootID = a.ID AND x.AgendaID <> a.ID) AS JumlahSusulan
					FROM ISEE_PNP_AgendaUjian a
					WHERE a.ParentAgendaID IS NULL
					  AND EXISTS (   -- sama seperti sebelumnya: hanya agenda yang punya parameter scoring
							SELECT 1
							FROM ISEE_PNP_ParameterScoring ps
							INNER JOIN ISEE_PNP_MemberDistribusi md
								ON md.KelompokDistribusiID = ps.KelompokDistribusiID
							   AND LTRIM(RTRIM(md.Skill)) = LTRIM(RTRIM(ps.Skill))
							WHERE ps.KelompokDistribusiID = a.KelompokDistribusiID)
				),
				Roots AS (
					SELECT * FROM RootsRaw WHERE 1 = 1" + filters + @"
				)" + PesertaCtes + SelesaiCte + @"
				SELECT r.ID, r.NamaAgenda, r.Periode,
					   ISNULL(tp.Total, 0) AS TotalPeserta,
					   ISNULL(sl.Total, 0) AS Selesai,
					   r.Status, r.JumlahSusulan
				FROM Roots r
				LEFT JOIN TotalPeserta tp ON tp.RootID = r.ID
				LEFT JOIN Selesai sl ON sl.RootID = r.ID
				ORDER BY r.TanggalMulai DESC, r.ID DESC" + RecursionHint;

            using (var conn = new SqlConnection(_connStr))
            using (var cmd = new SqlCommand(q, conn))
            {
                cmd.Parameters.AddRange(pars.ToArray());
                conn.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int total = reader.GetInt32(3);
                        int selesai = reader.GetInt32(4);

                        list.Add(new PassGradeModel
                        {
                            ID = reader.GetInt32(0),
                            NamaAgenda = reader.IsDBNull(1) ? "" : reader.GetString(1),
                            Periode = reader.IsDBNull(2) ? "" : reader.GetString(2),
                            TotalPeserta = total,
                            Selesai = selesai,
                            Belum = Math.Max(total - selesai, 0),
                            Status = reader.IsDBNull(5) ? "" : reader.GetString(5),
                            JumlahSusulan = reader.GetInt32(6)
                        });
                    }
                }
            }

            return list;
        }

        // ======================================================================
        // 2. LEADERBOARD (TOP 10 PER AGENDA, SUSULAN IKUT)
        // ======================================================================

        public List<LeaderboardRowModel> GetLeaderboard(int agendaId, string periode)
        {
            var list = new List<LeaderboardRowModel>();
            var pars = new List<SqlParameter> { new SqlParameter("@agendaId", agendaId) };
            string periodeFilter = "";

            if (!string.IsNullOrEmpty(periode) && periode.Length == 7)
            {
                periodeFilter = @"
      AND EXISTS (SELECT 1 FROM ISEE_PNP_AgendaUjian ra
                  WHERE ra.ID = @agendaId AND " + PeriodeExpr("ra") + " = @periode)";
                pars.Add(new SqlParameter("@periode", SqlDbType.VarChar, 7) { Value = periode });
            }

            string q = FamCte + @",
				Ranked AS (
					SELECT up.DomainID, up.Nilai, up.MulaiUjian, up.SelesaiUjian, td.DurasiDetik,
						   ROW_NUMBER() OVER (
							   PARTITION BY UPPER(up.DomainID)
							   ORDER BY up.Nilai DESC, ISNULL(td.DurasiDetik, 2147483647) ASC, up.SelesaiUjian ASC) AS rn
					FROM ISEE_PNP_UjianPeserta up
					INNER JOIN Fam f ON f.AgendaID = up.AgendaUjianID
					INNER JOIN ISEE_PNP_AgendaUjian a ON a.ID = up.AgendaUjianID
					" + DurasiApply + @"
					WHERE f.RootID = @agendaId
					  AND up.Nilai IS NOT NULL" + periodeFilter + @"
				),
				Best AS (
					SELECT rk.*,
						   COUNT(*) OVER () AS TotalDinilai,
						   AVG(CAST(rk.Nilai AS decimal(18, 4))) OVER () AS RataRata
					FROM Ranked rk
					WHERE rk.rn = 1
				)
				SELECT TOP 10
					   ISNULL(staff.Full_Name, b.DomainID) AS NamaPeserta,
					   b.DomainID, staff.Site, b.Nilai, b.MulaiUjian, b.SelesaiUjian, b.DurasiDetik,
					   b.TotalDinilai, b.RataRata
				FROM Best b
				LEFT JOIN v_ISEE_STAFF_DATA staff ON staff.Domain_ID = b.DomainID
				ORDER BY b.Nilai DESC, ISNULL(b.DurasiDetik, 2147483647) ASC, b.SelesaiUjian ASC" + RecursionHint;

            using (var conn = new SqlConnection(_connStr))
            using (var cmd = new SqlCommand(q, conn))
            {
                cmd.Parameters.AddRange(pars.ToArray());
                conn.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(new LeaderboardRowModel
                        {
                            NamaPeserta = reader.IsDBNull(0) ? "-" : reader.GetString(0),
                            DomainID = reader.IsDBNull(1) ? "" : reader.GetString(1),
                            Site = reader.IsDBNull(2) ? "" : reader.GetString(2),
                            Nilai = reader.IsDBNull(3) ? (decimal?)null : reader.GetDecimal(3),
                            MulaiUjian = reader.IsDBNull(4) ? (DateTime?)null : reader.GetDateTime(4),
                            SelesaiUjian = reader.IsDBNull(5) ? (DateTime?)null : reader.GetDateTime(5),
                            DurasiDetik = reader.IsDBNull(6) ? (int?)null : Convert.ToInt32(reader.GetValue(6)),
                            TotalDinilai = reader.IsDBNull(7) ? 0 : Convert.ToInt32(reader.GetValue(7)),
                            RataRata = reader.IsDBNull(8) ? (decimal?)null : Convert.ToDecimal(reader.GetValue(8))
                        });
                    }
                }
            }

            return list;
        }

        // ======================================================================
        // 3. PENGERJAAN AGENDA AKTIF (aktif bila induk ATAU susulan masih aktif)
        // ======================================================================

        public List<PengerjaanAktifModel> GetPengerjaanAktif(int? kelompokId, string nama)
        {
            var list = new List<PengerjaanAktifModel>();
            var pars = new List<SqlParameter>();
            string filters = "";

            if (kelompokId.HasValue && kelompokId.Value > 0)
            {
                filters += " AND a.KelompokDistribusiID = @kelompokId";
                pars.Add(new SqlParameter("@kelompokId", kelompokId.Value));
            }

            if (!string.IsNullOrWhiteSpace(nama))
            {
                filters += " AND a.NamaAgenda LIKE @nama";
                pars.Add(new SqlParameter("@nama", SqlDbType.NVarChar, 255) { Value = "%" + nama.Trim() + "%" });
            }

            string q = FamCte + @",
				Aktif AS (
					SELECT f.RootID, MAX(fa.TanggalSelesai) AS TanggalSelesai
					FROM Fam f
					INNER JOIN ISEE_PNP_AgendaUjian fa ON fa.ID = f.AgendaID
					WHERE fa.Status = 'Aktif'
					  AND fa.TanggalSelesai >= CAST(GETDATE() AS DATE)
					GROUP BY f.RootID
				),
				Roots AS (
					SELECT a.ID, a.NamaAgenda, a.KelompokDistribusiID, a.TanggalMulai,
						   " + PeriodeExpr("a") + @" AS Periode,
						   ak.TanggalSelesai,
						   (SELECT COUNT(*) FROM Fam x WHERE x.RootID = a.ID AND x.AgendaID <> a.ID) AS JumlahSusulan
					FROM ISEE_PNP_AgendaUjian a
					INNER JOIN Aktif ak ON ak.RootID = a.ID
					WHERE a.ParentAgendaID IS NULL" + filters + @"
				)" + PesertaCtes + SelesaiCte + @"
				SELECT r.ID, r.NamaAgenda, r.Periode, kd.NamaKelompok, r.TanggalSelesai,
					   DATEDIFF(DAY, CAST(GETDATE() AS DATE), r.TanggalSelesai) AS SisaHari,
					   ISNULL(tp.Total, 0) AS TotalPeserta,
					   ISNULL(sl.Total, 0) AS Selesai,
					   r.JumlahSusulan
				FROM Roots r
				LEFT JOIN ISEE_PNP_KelompokDistribusi kd ON kd.ID = r.KelompokDistribusiID
				LEFT JOIN TotalPeserta tp ON tp.RootID = r.ID
				LEFT JOIN Selesai sl ON sl.RootID = r.ID
				ORDER BY r.TanggalSelesai ASC, r.ID" + RecursionHint;

            using (var conn = new SqlConnection(_connStr))
            using (var cmd = new SqlCommand(q, conn))
            {
                cmd.Parameters.AddRange(pars.ToArray());
                conn.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int total = reader.GetInt32(6);
                        int selesai = reader.GetInt32(7);

                        list.Add(new PengerjaanAktifModel
                        {
                            ID = reader.GetInt32(0),
                            NamaAgenda = reader.IsDBNull(1) ? "" : reader.GetString(1),
                            Periode = reader.IsDBNull(2) ? "" : reader.GetString(2),
                            NamaKelompokDistribusi = reader.IsDBNull(3) ? "" : reader.GetString(3),
                            TanggalSelesai = reader.GetDateTime(4),
                            SisaHari = Math.Max(reader.GetInt32(5), 0),
                            TotalPeserta = total,
                            Selesai = selesai,
                            Belum = Math.Max(total - selesai, 0),
                            JumlahSusulan = reader.GetInt32(8)
                        });
                    }
                }
            }

            return list;
        }

        // ======================================================================
        // 4. HASIL JAWABAN AGENDA DISTRIBUSI (susulan ikut)
        // ======================================================================

        public List<HasilJawabanModel> GetHasilJawaban(int agendaId, int? jenisSoalId)
        {
            var list = new List<HasilJawabanModel>();
            var pars = new List<SqlParameter> { new SqlParameter("@agendaId", agendaId) };
            string jenisFilter = "";

            if (jenisSoalId.HasValue && jenisSoalId.Value > 0)
            {
                jenisFilter = " AND bs.JenisSoalID = @jenisSoalId";
                pars.Add(new SqlParameter("@jenisSoalId", jenisSoalId.Value));
            }

            string q = FamCte + JawabanAggCtes + @"
				SELECT js.ID AS JenisSoalID,
					   js.NamaJenisSoal,
					   bs.KategoriSoal,
					   ISNULL(SUM(d.Cnt), 0) AS JumlahDistribusi,
					   ISNULL(SUM(ja.Benar), 0) AS Benar,
					   ISNULL(SUM(ja.Salah), 0) AS Salah
				FROM Dist d
				INNER JOIN ISEE_PNP_BankSoal bs ON bs.ID = d.BankSoalID
				INNER JOIN ISEE_PNP_JenisSoal js ON js.ID = bs.JenisSoalID
				LEFT JOIN JawabAgg ja ON ja.BankSoalID = bs.ID
				WHERE 1 = 1" + jenisFilter + @"
				GROUP BY js.ID, js.NamaJenisSoal, bs.KategoriSoal
				ORDER BY js.NamaJenisSoal, bs.KategoriSoal" + RecursionHint;

            using (var conn = new SqlConnection(_connStr))
            using (var cmd = new SqlCommand(q, conn))
            {
                cmd.Parameters.AddRange(pars.ToArray());
                conn.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(new HasilJawabanModel
                        {
                            JenisSoalID = reader.GetInt32(0),
                            NamaJenisSoal = reader.IsDBNull(1) ? "" : reader.GetString(1),
                            KategoriSoal = reader.IsDBNull(2) ? "" : reader.GetString(2),
                            JumlahDistribusi = Convert.ToInt32(reader.GetValue(3)),
                            Benar = Convert.ToInt32(reader.GetValue(4)),
                            Salah = Convert.ToInt32(reader.GetValue(5))
                        });
                    }
                }
            }

            return list;
        }

        public List<HasilJawabanDetailModel> GetHasilJawabanDetail(int agendaId, string kategoriSoal, int? jenisSoalId)
        {
            var list = new List<HasilJawabanDetailModel>();
            var pars = new List<SqlParameter>
            {
                new SqlParameter("@agendaId", agendaId),
                new SqlParameter("@kategoriSoal", SqlDbType.VarChar, 100) { Value = kategoriSoal ?? "" }
            };
            string jenisFilter = "";

            if (jenisSoalId.HasValue && jenisSoalId.Value > 0)
            {
                jenisFilter = " AND bs.JenisSoalID = @jenisSoalId";
                pars.Add(new SqlParameter("@jenisSoalId", jenisSoalId.Value));
            }

            string q = FamCte + JawabanAggCtes + @"
				SELECT bs.ID, bs.Pertanyaan, bs.KunciJawabanLabel,
					   d.Cnt AS JumlahDistribusi,
					   ISNULL(ja.Benar, 0) AS Benar,
					   ISNULL(ja.Salah, 0) AS Salah
				FROM Dist d
				INNER JOIN ISEE_PNP_BankSoal bs ON bs.ID = d.BankSoalID
				LEFT JOIN JawabAgg ja ON ja.BankSoalID = bs.ID
				WHERE bs.KategoriSoal = @kategoriSoal" + jenisFilter + @"
				ORDER BY bs.ID" + RecursionHint;

            using (var conn = new SqlConnection(_connStr))
            using (var cmd = new SqlCommand(q, conn))
            {
                cmd.Parameters.AddRange(pars.ToArray());
                conn.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(new HasilJawabanDetailModel
                        {
                            BankSoalID = reader.GetInt32(0),
                            Pertanyaan = reader.IsDBNull(1) ? "" : reader.GetString(1),
                            KunciJawabanLabel = reader.IsDBNull(2) ? "" : reader.GetString(2),
                            JumlahDistribusi = Convert.ToInt32(reader.GetValue(3)),
                            Benar = Convert.ToInt32(reader.GetValue(4)),
                            Salah = Convert.ToInt32(reader.GetValue(5))
                        });
                    }
                }
            }

            return list;
        }

        // ======================================================================
        // 5. MONITORING REALTIME
        // ======================================================================

        public MonitoringPageModel GetMonitoring(int? agendaId, int? kelompokId, string search, int page, int pageSize)
        {
            var result = new MonitoringPageModel();
            page = page < 1 ? 1 : page;
            pageSize = pageSize < 1 ? 15 : pageSize;

            string whereSql = " WHERE 1 = 1";
            var pars = new List<SqlParameter>();

            if (agendaId.HasValue && agendaId.Value > 0)
            {
                // agenda induk -> ikut seluruh peserta agenda susulannya
                whereSql += " AND up.AgendaUjianID IN (SELECT AgendaID FROM Fam WHERE RootID = @agendaId)";
                pars.Add(new SqlParameter("@agendaId", agendaId.Value));
            }

            if (kelompokId.HasValue && kelompokId.Value > 0)
            {
                whereSql += " AND a.KelompokDistribusiID = @kelompokId";
                pars.Add(new SqlParameter("@kelompokId", kelompokId.Value));
            }

            if (!string.IsNullOrWhiteSpace(search))
            {
                whereSql += " AND (staff.Full_Name LIKE @search OR up.DomainID LIKE @search)";
                pars.Add(new SqlParameter("@search", SqlDbType.NVarChar, 200) { Value = "%" + search.Trim() + "%" });
            }

            const string fromSql = @"
				FROM ISEE_PNP_UjianPeserta up
				INNER JOIN ISEE_PNP_AgendaUjian a ON a.ID = up.AgendaUjianID
				LEFT JOIN ISEE_PNP_KelompokDistribusi kd ON kd.ID = a.KelompokDistribusiID
				LEFT JOIN v_ISEE_STAFF_DATA staff ON staff.Domain_ID = up.DomainID";

            using (var conn = new SqlConnection(_connStr))
            {
                conn.Open();

                using (var cmd = new SqlCommand(FamCte + " SELECT COUNT(*) " + fromSql + whereSql + RecursionHint, conn))
                {
                    cmd.Parameters.AddRange(pars.Select(p => Clone(p)).ToArray());
                    result.Total = Convert.ToInt32(cmd.ExecuteScalar());
                }

                string q = FamCte + @"
					SELECT up.ID,
						   ISNULL(staff.Full_Name, up.DomainID) AS NamaPeserta,
						   up.DomainID,
						   a.NamaAgenda + CASE WHEN a.ParentAgendaID IS NOT NULL THEN ' (Susulan)' ELSE '' END AS NamaAgenda,
						   " + PeriodeExpr("a") + @" AS Periode,
						   kd.NamaKelompok,
						   up.MulaiUjian,
						   up.SelesaiUjian,
						   td.DurasiDetik
					" + fromSql + DurasiApply + whereSql + @"
					ORDER BY up.MulaiUjian DESC, up.ID DESC
					OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY" + RecursionHint;

                using (var cmd = new SqlCommand(q, conn))
                {
                    cmd.Parameters.AddRange(pars.Select(p => Clone(p)).ToArray());
                    cmd.Parameters.AddWithValue("@offset", (page - 1) * pageSize);
                    cmd.Parameters.AddWithValue("@pageSize", pageSize);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result.Items.Add(new MonitoringRowModel
                            {
                                UjianPesertaID = reader.GetInt32(0),
                                NamaPeserta = reader.IsDBNull(1) ? "-" : reader.GetString(1),
                                DomainID = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                NamaAgenda = reader.IsDBNull(3) ? "" : reader.GetString(3),
                                Periode = reader.IsDBNull(4) ? "" : reader.GetString(4),
                                NamaKelompokDistribusi = reader.IsDBNull(5) ? "" : reader.GetString(5),
                                MulaiUjian = reader.IsDBNull(6) ? (DateTime?)null : reader.GetDateTime(6),
                                SelesaiUjian = reader.IsDBNull(7) ? (DateTime?)null : reader.GetDateTime(7),
                                DurasiDetik = reader.IsDBNull(8) ? (int?)null : Convert.ToInt32(reader.GetValue(8))
                            });
                        }
                    }
                }
            }

            return result;
        }

        public MonitoringDetailModel GetMonitoringDetail(int ujianPesertaId)
        {
            var model = new MonitoringDetailModel();

            string q = @"
				SELECT ISNULL(staff.Full_Name, up.DomainID),
					   up.DomainID,
					   a.NamaAgenda + CASE WHEN a.ParentAgendaID IS NOT NULL THEN ' (Susulan)' ELSE '' END,
					   up.Status,
					   up.Nilai,
					   up.MulaiUjian,
					   up.SelesaiUjian,
					   td.DurasiDetik
				FROM ISEE_PNP_UjianPeserta up
				INNER JOIN ISEE_PNP_AgendaUjian a ON a.ID = up.AgendaUjianID
				LEFT JOIN v_ISEE_STAFF_DATA staff ON staff.Domain_ID = up.DomainID
				" + DurasiApply + @"
				WHERE up.ID = @id;

				SELECT sp.BankSoalID, bs.Pertanyaan, bs.KunciJawabanLabel, jp.JawabanDipilih
				FROM ISEE_PNP_SoalPeserta sp
				INNER JOIN ISEE_PNP_BankSoal bs ON bs.ID = sp.BankSoalID
				LEFT JOIN ISEE_PNP_JawabanPeserta jp
					   ON jp.UjianPesertaID = sp.UjianPesertaID AND jp.BankSoalID = sp.BankSoalID
				WHERE sp.UjianPesertaID = @id
				ORDER BY sp.Urutan, sp.ID;

				SELECT so.BankSoalID AS BankSoalID, so.AssignedLabel AS Label, os.TeksOpsi AS TeksOpsi, so.IsCorrect AS IsCorrect
				FROM ISEE_PNP_SoalPesertaOpsi so
				INNER JOIN ISEE_PNP_OpsiSoal os ON os.ID = so.OpsiSoalID
				WHERE so.UjianPesertaID = @id
				UNION ALL
				SELECT os.BankSoalID, os.LabelOpsi, os.TeksOpsi,
					   CASE WHEN os.LabelOpsi = bs.KunciJawabanLabel THEN 1 ELSE 0 END
				FROM ISEE_PNP_SoalPeserta sp
				INNER JOIN ISEE_PNP_BankSoal bs ON bs.ID = sp.BankSoalID
				INNER JOIN ISEE_PNP_OpsiSoal os ON os.BankSoalID = sp.BankSoalID
				WHERE sp.UjianPesertaID = @id
				  AND NOT EXISTS (SELECT 1 FROM ISEE_PNP_SoalPesertaOpsi x
								  WHERE x.UjianPesertaID = @id AND x.BankSoalID = sp.BankSoalID)
				ORDER BY BankSoalID, Label;";

            var soalDict = new Dictionary<int, MonitoringSoalDetailModel>();
            var orderedBankIds = new List<int>();

            using (var conn = new SqlConnection(_connStr))
            using (var cmd = new SqlCommand(q, conn))
            {
                cmd.Parameters.AddWithValue("@id", ujianPesertaId);
                conn.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        model.Peserta = new MonitoringPesertaInfoModel
                        {
                            NamaPeserta = reader.IsDBNull(0) ? "-" : reader.GetString(0),
                            DomainID = reader.IsDBNull(1) ? "" : reader.GetString(1),
                            NamaAgenda = reader.IsDBNull(2) ? "" : reader.GetString(2),
                            Status = reader.IsDBNull(3) ? "" : reader.GetString(3),
                            Nilai = reader.IsDBNull(4) ? (decimal?)null : reader.GetDecimal(4),
                            MulaiUjian = reader.IsDBNull(5) ? (DateTime?)null : reader.GetDateTime(5),
                            SelesaiUjian = reader.IsDBNull(6) ? (DateTime?)null : reader.GetDateTime(6),
                            DurasiDetik = reader.IsDBNull(7) ? (int?)null : Convert.ToInt32(reader.GetValue(7))
                        };
                    }

                    reader.NextResult();
                    while (reader.Read())
                    {
                        int bankId = reader.GetInt32(0);
                        if (!soalDict.ContainsKey(bankId))
                        {
                            soalDict[bankId] = new MonitoringSoalDetailModel
                            {
                                Pertanyaan = reader.IsDBNull(1) ? "" : reader.GetString(1),
                                KunciJawabanLabel = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                JawabanDipilih = reader.IsDBNull(3) ? null : reader.GetString(3)
                            };
                            orderedBankIds.Add(bankId);
                        }
                    }

                    reader.NextResult();
                    while (reader.Read())
                    {
                        int bankId = reader.GetInt32(0);
                        MonitoringSoalDetailModel soal;
                        if (soalDict.TryGetValue(bankId, out soal))
                        {
                            soal.Opsi.Add(new OpsiSoalModel
                            {
                                LabelOpsi = reader.IsDBNull(1) ? "" : reader.GetString(1),
                                TeksOpsi = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                IsCorrect = !reader.IsDBNull(3) && Convert.ToInt32(reader.GetValue(3)) == 1
                            });
                        }
                    }
                }
            }

            model.SoalList = orderedBankIds.Select(id => soalDict[id]).ToList();

            // Kunci disamakan dengan label yang dilihat peserta.
            foreach (var soal in model.SoalList)
            {
                var kunci = soal.Opsi.FirstOrDefault(o => o.IsCorrect);
                if (kunci != null) soal.KunciJawabanLabel = kunci.LabelOpsi;
            }

            return model;
        }

        // ======================================================================
        // 6. SURVEY (PNP + NON PNP)
        // ======================================================================

        public List<SurveyRowModel> GetSurvey(string tipe, string periode)
        {
            var list = new List<SurveyRowModel>();

            bool includePnp = string.IsNullOrEmpty(tipe) || tipe == "PNP";
            bool includeNonPnp = string.IsNullOrEmpty(tipe) || tipe == "NonPNP";
            bool filterPeriode = !string.IsNullOrEmpty(periode) && periode.Length == 7;

            using (var conn = new SqlConnection(_connStr))
            {
                conn.Open();

                if (includePnp)
                {
                    string qPnp = FamCte + @",
						Roots AS (
							SELECT r.ID, r.NamaAgenda, r.KelompokDistribusiID
							FROM ISEE_PNP_AgendaUjian r
							WHERE r.ParentAgendaID IS NULL
							  AND EXISTS (SELECT 1
										  FROM ISEE_PNP_AgendaSurveyPNP s
										  INNER JOIN Fam f ON f.AgendaID = s.AgendaDistribusiID
										  WHERE f.RootID = r.ID)
						)" + PesertaCtes + @",
						SvPeriode AS (
							SELECT f.RootID, s.Periode
							FROM ISEE_PNP_AgendaSurveyPNP s
							INNER JOIN Fam f ON f.AgendaID = s.AgendaDistribusiID
							WHERE 1 = 1" + (filterPeriode ? " AND s.Periode = @periode" : "") + @"
							GROUP BY f.RootID, s.Periode
						),
						Resp AS (
							SELECT f.RootID,
								   COUNT(DISTINCT UPPER(LTRIM(RTRIM(COALESCE(NULLIF(jsp.DomainID, ''), up.DomainID))))) AS TotalResponden,
								   COUNT(jsp.ID) AS TotalJawaban
							FROM ISEE_PNP_JawabanSurveyPeserta jsp
							INNER JOIN ISEE_PNP_UjianPeserta up ON up.ID = jsp.UjianPesertaID
							INNER JOIN Fam f ON f.AgendaID = up.AgendaUjianID
							GROUP BY f.RootID
						)
						SELECT r.ID, r.NamaAgenda, sp.Periode,
							   ISNULL(tp.Total, 0) AS TotalDiundang,
							   ISNULL(rs.TotalResponden, 0) AS TotalResponden,
							   ISNULL(rs.TotalJawaban, 0) AS TotalJawaban
						FROM SvPeriode sp
						INNER JOIN Roots r ON r.ID = sp.RootID
						LEFT JOIN TotalPeserta tp ON tp.RootID = r.ID
						LEFT JOIN Resp rs ON rs.RootID = r.ID
						ORDER BY sp.Periode DESC, r.NamaAgenda" + RecursionHint;

                    using (var cmd = new SqlCommand(qPnp, conn))
                    {
                        if (filterPeriode)
                            cmd.Parameters.Add(new SqlParameter("@periode", SqlDbType.VarChar, 50) { Value = periode });

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(new SurveyRowModel
                                {
                                    AgendaID = reader.GetInt32(0),
                                    NamaAgenda = reader.IsDBNull(1) ? "" : reader.GetString(1),
                                    Tipe = "PNP",
                                    Periode = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                    TotalRespondenDiundang = reader.GetInt32(3),
                                    TotalResponden = reader.GetInt32(4),
                                    TotalJawaban = reader.GetInt32(5)
                                });
                            }
                        }
                    }
                }

                if (includeNonPnp)
                {
                    string qNonPnp = @"
						;WITH G AS (
							SELECT MIN(ID) AS AgendaID, NamaAgenda, StartDate, EndDate
							FROM ISEE_PNP_AgendaSurveyNonPNP
							GROUP BY NamaAgenda, StartDate, EndDate
						)
						SELECT g.AgendaID, g.NamaAgenda, CONVERT(char(7), g.StartDate, 120) AS Periode,
							   ISNULL(resp.TotalResponden, 0) AS TotalResponden,
							   ISNULL(resp.TotalJawaban, 0) AS TotalJawaban
						FROM G g
						OUTER APPLY (
							SELECT COUNT(DISTINCT UPPER(LTRIM(RTRIM(jsp.DomainID)))) AS TotalResponden,
								   COUNT(jsp.ID) AS TotalJawaban
							FROM ISEE_PNP_JawabanSurveyPeserta jsp
							WHERE jsp.AgendaNonPNPID IN (
								SELECT m.ID
								FROM ISEE_PNP_AgendaSurveyNonPNP m
								WHERE m.NamaAgenda = g.NamaAgenda AND m.StartDate = g.StartDate AND m.EndDate = g.EndDate)
						) resp
						WHERE 1 = 1" + (filterPeriode ? " AND CONVERT(char(7), g.StartDate, 120) = @periode" : "") + @"
						ORDER BY g.StartDate DESC, g.NamaAgenda;";

                    using (var cmd = new SqlCommand(qNonPnp, conn))
                    {
                        if (filterPeriode)
                            cmd.Parameters.Add(new SqlParameter("@periode", SqlDbType.VarChar, 7) { Value = periode });

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(new SurveyRowModel
                                {
                                    AgendaID = reader.GetInt32(0),
                                    NamaAgenda = reader.IsDBNull(1) ? "" : reader.GetString(1),
                                    Tipe = "NonPNP",
                                    Periode = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                    TotalRespondenDiundang = 0,
                                    TotalResponden = reader.GetInt32(3),
                                    TotalJawaban = reader.GetInt32(4)
                                });
                            }
                        }
                    }
                }
            }

            return list.OrderByDescending(r => r.Periode).ThenBy(r => r.NamaAgenda).ToList();
        }

        private const string SurveyDetailSelect = @"
			SELECT j.PertanyaanID,
				   COALESCE(MAX(pt.PertanyaanText),
					 MAX(ps.NamaPertanyaan),
					 N'Pertanyaan #' + CAST(j.PertanyaanID AS nvarchar(20))) AS NamaPertanyaan,
				   COUNT(DISTINCT j.K) AS JumlahDijawab,
				   t.Total AS TotalResponden,
				   AVG(CAST(j.Nilai AS float)) AS RataNilai
			FROM Jwb j
			CROSS JOIN TotalR t
			LEFT JOIN ISEE_PNP_PertanyaanSurvey ps
				   ON ps.ID = j.PertanyaanID
				  AND j.PertanyaanID IN (SELECT PertanyaanID FROM Grp)
			LEFT JOIN ISEE_PNP_PertanyaanTemplate pt ON pt.ID = j.PertanyaanID
			GROUP BY j.PertanyaanID, t.Total
			ORDER BY j.PertanyaanID;";

        public SurveyDetailModel GetSurveyDetail(int agendaId, string tipe)
        {
            var model = new SurveyDetailModel();
            bool nonPnp = (tipe == "NonPNP");

            string q = nonPnp
                ? @"
				DECLARE @nama nvarchar(500), @s date, @e date;
				SELECT @nama = NamaAgenda, @s = StartDate, @e = EndDate
				FROM ISEE_PNP_AgendaSurveyNonPNP WHERE ID = @id;

				SELECT @nama AS NamaAgenda;

				;WITH Grp AS (
					SELECT ID, PertanyaanID
					FROM ISEE_PNP_AgendaSurveyNonPNP
					WHERE NamaAgenda = @nama AND StartDate = @s AND EndDate = @e
				),
				Jwb AS (
					SELECT jsp.PertanyaanID, jsp.Nilai, UPPER(LTRIM(RTRIM(jsp.DomainID))) AS K
					FROM ISEE_PNP_JawabanSurveyPeserta jsp
					WHERE jsp.AgendaNonPNPID IN (SELECT ID FROM Grp)
				),
				TotalR AS (SELECT COUNT(DISTINCT K) AS Total FROM Jwb)"
								+ SurveyDetailSelect
								: @"
				SELECT NamaAgenda FROM ISEE_PNP_AgendaUjian WHERE ID = @id;
				"
								+ FamCte + @",
				Grp AS (
					SELECT s.PertanyaanID
					FROM ISEE_PNP_AgendaSurveyPNP s
					INNER JOIN Fam f ON f.AgendaID = s.AgendaDistribusiID
					WHERE f.RootID = @id
				),
				Jwb AS (
					SELECT jsp.PertanyaanID, jsp.Nilai,
						   UPPER(LTRIM(RTRIM(COALESCE(NULLIF(jsp.DomainID, ''), up.DomainID)))) AS K
					FROM ISEE_PNP_JawabanSurveyPeserta jsp
					INNER JOIN ISEE_PNP_UjianPeserta up ON up.ID = jsp.UjianPesertaID
					INNER JOIN Fam f ON f.AgendaID = up.AgendaUjianID
					WHERE f.RootID = @id
				),
				TotalR AS (SELECT COUNT(DISTINCT K) AS Total FROM Jwb)"
                + SurveyDetailSelect;

            using (var conn = new SqlConnection(_connStr))
            using (var cmd = new SqlCommand(q, conn))
            {
                cmd.Parameters.AddWithValue("@id", agendaId);
                conn.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    model.NamaAgenda = reader.Read() && !reader.IsDBNull(0) ? reader.GetString(0) : "";

                    reader.NextResult();
                    while (reader.Read())
                    {
                        int totalResponden = Convert.ToInt32(reader.GetValue(3));
                        model.TotalResponden = totalResponden;

                        model.Breakdown.Add(new SurveyBreakdownItemModel
                        {
                            NamaPertanyaan = reader.IsDBNull(1) ? "" : reader.GetString(1),
                            JumlahDijawab = Convert.ToInt32(reader.GetValue(2)),
                            TotalJawaban = totalResponden,
                            RataNilai = reader.IsDBNull(4) ? (double?)null : Convert.ToDouble(reader.GetValue(4))
                        });
                    }
                }
            }

            return model;
        }

        private static SqlParameter Clone(SqlParameter p)
        {
            var c = new SqlParameter(p.ParameterName, p.SqlDbType) { Value = p.Value };
            if (p.Size > 0) c.Size = p.Size;
            return c;
        }
    }
}