protected void PopulateMhsAktif(string semester) { try { string CS = ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString; using (SqlConnection con = new SqlConnection(CS)) { //------------------------------------------------------------------------------------ con.Open(); SqlCommand CmdJadwal = new SqlCommand("" + "SELECT A.id_prog_study, A.prog_study, A.jumlah_aktif, N.jumlah_nonaktif, C.jumlah_cuti, K.jumlah_keluar, L.jumlah_lulus FROM " + "( " + // -- ============== new perhitungan Aktif ================= -- "SELECT * FROM( " + "SELECT StatusMhs.id_prog_study, StatusMhs.prog_study, StatusMhs.semester, StatusMhs.setatus, COUNT(*) AS jumlah_aktif FROM " + "( " + //-- === Mhs KRS === -- "SELECT MhsKRS.id_prog_study, MhsKRS.nama, MhsKRS.npm, MhsKRS.prog_study, MhsKRS.semester, StatusBerjalan.status as setatus FROM( " + "SELECT bak_mahasiswa.npm, bak_mahasiswa.nama, bak_jadwal.semester, bak_prog_study.id_prog_study, bak_prog_study.prog_study " + "FROM bak_jadwal INNER JOIN " + "bak_krs ON bak_jadwal.no_jadwal = bak_krs.no_jadwal INNER JOIN " + "bak_mahasiswa ON bak_krs.npm = bak_mahasiswa.npm INNER JOIN " + "bak_prog_study ON bak_jadwal.id_prog_study = bak_prog_study.id_prog_study " + "WHERE (LEFT(bak_mahasiswa.thn_angkatan, 4) > 1990) AND bak_prog_study.jenjang IN('S2') AND semester = @SemNewHitungJumlah " + "GROUP BY bak_mahasiswa.npm, bak_mahasiswa.nama, bak_jadwal.semester, bak_prog_study.prog_study, bak_mahasiswa.status, bak_mahasiswa.thn_angkatan, bak_prog_study.id_prog_study " + "UNION ALL " + "SELECT bak_mahasiswa.npm, bak_mahasiswa.nama, bak_jadwal.semester, bak_prog_study.id_prog_study, bak_prog_study.prog_study " + "FROM bak_jadwal INNER JOIN " + "bak_krs ON bak_jadwal.no_jadwal = bak_krs.no_jadwal INNER JOIN " + "bak_mahasiswa ON bak_krs.npm = bak_mahasiswa.npm INNER JOIN " + "bak_prog_study ON bak_jadwal.id_prog_study = bak_prog_study.id_prog_study " + "WHERE(LEFT(bak_mahasiswa.thn_angkatan, 4) > 1990) AND bak_prog_study.jenjang NOT IN('S2') AND dbo.bak_jadwal.semester = @SemNewHitungJumlah " + "GROUP BY bak_mahasiswa.npm, bak_mahasiswa.nama, bak_jadwal.semester, bak_prog_study.prog_study, bak_mahasiswa.status, bak_mahasiswa.thn_angkatan, bak_prog_study.id_prog_study " + ") AS MhsKRS LEFT OUTER JOIN " + "( " + // -- == Status Berjalan == -- "SELECT npm, status FROM bak_cuti_nonaktif WHERE semester = @SemNewHitungJumlah " + ") AS StatusBerjalan on MhsKRS.npm = StatusBerjalan.npm " + ") AS StatusMhs " + "WHERE StatusMhs.setatus = 'A' or StatusMhs.setatus is NULL " + "GROUP BY StatusMhs.id_prog_study, StatusMhs.prog_study, StatusMhs.semester, StatusMhs.setatus " + ") as MhsAktifKRS" + ") AS A LEFT OUTER JOIN " + "( " + //-- ============== new perhitungan Non Aktif ================= -- "SELECT id_prog_study, prog_study, semester, setatus, COUNT(*) AS jumlah_nonaktif FROM " + "( " + "SELECT bak_cuti_nonaktif.npm, bak_mahasiswa.nama, bak_cuti_nonaktif.semester, bak_prog_study.id_prog_study, bak_prog_study.prog_study, bak_cuti_nonaktif.status AS setatus " + "FROM bak_cuti_nonaktif INNER JOIN " + "bak_mahasiswa ON bak_cuti_nonaktif.npm = bak_mahasiswa.npm INNER JOIN " + "bak_prog_study ON bak_mahasiswa.id_prog_study = bak_prog_study.id_prog_study AND bak_cuti_nonaktif.semester = @SemNewHitungJumlah AND dbo.bak_cuti_nonaktif.status = 'N' " + ") AS StatusMhs " + "GROUP BY StatusMhs.id_prog_study, prog_study, StatusMhs.semester, StatusMhs.setatus " + ") AS N ON N.id_prog_study = A.id_prog_study LEFT OUTER JOIN " + "( " + //-- ============== new perhitungan Keluar ================= -- "SELECT id_prog_study, prog_study, semester, setatus, COUNT(*) AS jumlah_keluar FROM " + "( " + "SELECT bak_cuti_nonaktif.npm, bak_mahasiswa.nama, bak_cuti_nonaktif.semester, bak_prog_study.id_prog_study, bak_prog_study.prog_study, bak_cuti_nonaktif.status AS setatus " + "FROM bak_cuti_nonaktif INNER JOIN " + "bak_mahasiswa ON bak_cuti_nonaktif.npm = bak_mahasiswa.npm INNER JOIN " + "bak_prog_study ON bak_mahasiswa.id_prog_study = bak_prog_study.id_prog_study AND bak_cuti_nonaktif.semester = @SemNewHitungJumlah AND dbo.bak_cuti_nonaktif.status = 'K' " + ") AS StatusMhs " + "GROUP BY StatusMhs.id_prog_study, prog_study, StatusMhs.semester, StatusMhs.setatus " + ") AS K ON K.id_prog_study = A.id_prog_study LEFT OUTER JOIN " + "( " + //-- ============== new perhitungan Cuti ================= -- "SELECT id_prog_study, prog_study, semester, setatus, COUNT(*) AS jumlah_cuti FROM " + "( " + "SELECT bak_cuti_nonaktif.npm, bak_mahasiswa.nama, bak_cuti_nonaktif.semester, bak_prog_study.id_prog_study, bak_prog_study.prog_study, bak_cuti_nonaktif.status AS setatus " + "FROM bak_cuti_nonaktif INNER JOIN " + "bak_mahasiswa ON bak_cuti_nonaktif.npm = bak_mahasiswa.npm INNER JOIN " + "bak_prog_study ON bak_mahasiswa.id_prog_study = bak_prog_study.id_prog_study AND bak_cuti_nonaktif.semester = @SemNewHitungJumlah AND dbo.bak_cuti_nonaktif.status = 'C' " + ") AS StatusMhs " + "GROUP BY StatusMhs.id_prog_study, prog_study, StatusMhs.semester, StatusMhs.setatus " + ") AS C ON C.id_prog_study = A.id_prog_study LEFT OUTER JOIN " + "( " + //-- =================== new perhitungan Lulus =================== "SELECT M.id_prog_study, prog_study, COUNT(*) AS jumlah_lulus FROM dbo.bak_mahasiswa AS M " + "INNER JOIN dbo.bak_prog_study AS P ON P.id_prog_study = M.id_prog_study " + "WHERE M.status = 'L' AND M.smster_lls = @SemNewHitungJumlah " + "GROUP BY M.id_prog_study, prog_study " + ") AS L ON L.id_prog_study = A.id_prog_study " + "", con); CmdJadwal.CommandType = System.Data.CommandType.Text; CmdJadwal.Parameters.AddWithValue("@SemNewHitungJumlah", semester); DataTable TableJadwal = new DataTable(); TableJadwal.Columns.Add("Program Studi"); TableJadwal.Columns.Add("Aktif"); TableJadwal.Columns.Add("Non Aktif"); TableJadwal.Columns.Add("Cuti"); TableJadwal.Columns.Add("Keluar"); TableJadwal.Columns.Add("Lulus"); using (SqlDataReader rdr = CmdJadwal.ExecuteReader()) { if (rdr.HasRows) { this.PanelRekapAktif.Enabled = true; this.PanelRekapAktif.Visible = true; while (rdr.Read()) { DataRow datarow = TableJadwal.NewRow(); datarow["Program Studi"] = rdr["prog_study"]; datarow["Aktif"] = rdr["jumlah_aktif"]; if (rdr["jumlah_nonaktif"] == DBNull.Value) { datarow["Non Aktif"] = 0; } else { datarow["Non Aktif"] = rdr["jumlah_nonaktif"]; } if (rdr["jumlah_cuti"] == DBNull.Value) { datarow["Cuti"] = 0; } else { datarow["Cuti"] = rdr["jumlah_cuti"]; } if (rdr["jumlah_keluar"] == DBNull.Value) { datarow["Keluar"] = 0; } else { datarow["Keluar"] = rdr["jumlah_keluar"]; } if (rdr["jumlah_lulus"] == DBNull.Value) { datarow["Lulus"] = 0; } else { datarow["Lulus"] = rdr["jumlah_lulus"]; } TableJadwal.Rows.Add(datarow); } //Fill Gridview this.GvMhsAktif.DataSource = TableJadwal; this.GvMhsAktif.DataBind(); } else { this.PanelRekapAktif.Enabled = false; this.PanelRekapAktif.Visible = false; //clear Gridview TableJadwal.Rows.Clear(); TableJadwal.Clear(); GvMhsAktif.DataSource = TableJadwal; GvMhsAktif.DataBind(); this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Data Tidak Ditemukan');", true); } } } } catch (Exception ex) { this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('" + ex.Message.ToString() + "');", true); return; } }
protected void PopulateMhsAktif(string TopSemester, string IdProdi) { try { string CS = ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString; using (SqlConnection con = new SqlConnection(CS)) { //------------------------------------------------------------------------------------ con.Open(); SqlCommand CmdJadwal = new SqlCommand(""+ " SELECT ProdiMhs.thn_angkatan, MhsA.jumlah_aktif, MhsNonAktif.jumlah_nonaktif, MhsCuti.jumlah_cuti, MhsK.jumlah_keluar, MhsLulus.jumlah_lulus from "+ " ( "+ //--Cuti NonAktif Keluar -- " SELECT bak_mahasiswa.thn_angkatan "+ " FROM bak_cuti_nonaktif INNER JOIN "+ " bak_mahasiswa ON bak_cuti_nonaktif.npm = bak_mahasiswa.npm INNER JOIN "+ " bak_prog_study ON bak_mahasiswa.id_prog_study = bak_prog_study.id_prog_study "+ " where bak_mahasiswa.id_prog_study = @IdMhsProdi and bak_cuti_nonaktif.semester = @SemNewHitungJumlah AND bak_cuti_nonaktif.status in ('N', 'C', 'K') "+ " GROUP BY bak_mahasiswa.thn_angkatan "+ " UNION "+ // -- Lulus -- " select thn_angkatan from bak_mahasiswa where status = 'L' and id_prog_study = @IdMhsProdi and smster_lls = @SemNewHitungJumlah "+ " group by thn_angkatan "+ " UNION "+ //-- Aktif -- " SELECT bak_mahasiswa.thn_angkatan "+ " FROM bak_jadwal INNER JOIN "+ " bak_krs ON bak_jadwal.no_jadwal = bak_krs.no_jadwal INNER JOIN "+ " bak_mahasiswa ON bak_krs.npm = bak_mahasiswa.npm INNER JOIN "+ " bak_prog_study ON bak_jadwal.id_prog_study = bak_prog_study.id_prog_study "+ " WHERE(dbo.bak_mahasiswa.status IN('A')) AND(LEFT(bak_mahasiswa.thn_angkatan, 4) > 1998) AND semester = @SemNewHitungJumlah AND bak_prog_study.id_prog_study = @IdMhsProdi "+ " GROUP BY bak_mahasiswa.thn_angkatan "+ " ) as ProdiMhs LEFT OUTER JOIN "+ " ( "+ " SELECT MahasiswaAktif.thn_angkatan, COUNT(*) as jumlah_aktif FROM "+ " ( "+ " SELECT bak_mahasiswa.thn_angkatan, bak_mahasiswa.npm "+ " FROM bak_jadwal INNER JOIN "+ " bak_krs ON bak_jadwal.no_jadwal = bak_krs.no_jadwal INNER JOIN "+ " bak_prog_study ON bak_jadwal.id_prog_study = bak_prog_study.id_prog_study INNER JOIN "+ " bak_mahasiswa ON bak_krs.npm = bak_mahasiswa.npm "+ " WHERE (bak_jadwal.semester = @SemNewHitungJumlah) AND (bak_prog_study.id_prog_study = @IdMhsProdi) AND(LEFT(bak_mahasiswa.thn_angkatan, 4) > 1998) AND(bak_mahasiswa.status = 'A') "+ " group by bak_mahasiswa.thn_angkatan, bak_mahasiswa.npm, bak_jadwal.semester, bak_prog_study.id_prog_study "+ " ) AS MahasiswaAktif "+ " group by MahasiswaAktif.thn_angkatan "+ " ) as MhsA on ProdiMhs.thn_angkatan = MhsA.thn_angkatan LEFT OUTER JOIN " + " ( "+ " SELECT bak_prog_study.id_prog_study, bak_prog_study.prog_study, bak_cuti_nonaktif.semester, bak_mahasiswa.thn_angkatan, COUNT(*) as jumlah_keluar "+ " FROM bak_cuti_nonaktif INNER JOIN "+ " bak_mahasiswa ON bak_cuti_nonaktif.npm = bak_mahasiswa.npm INNER JOIN "+ " bak_prog_study ON bak_mahasiswa.id_prog_study = bak_prog_study.id_prog_study "+ " WHERE semester = @SemNewHitungJumlah AND bak_mahasiswa.id_prog_study = @IdMhsProdi AND bak_cuti_nonaktif.status in ('K') "+ " GROUP BY bak_prog_study.id_prog_study, bak_prog_study.prog_study, bak_cuti_nonaktif.semester, bak_mahasiswa.thn_angkatan "+ " ) as MhsK on ProdiMhs.thn_angkatan = MhsK.thn_angkatan LEFT OUTER JOIN "+ " ( "+ " SELECT bak_prog_study.id_prog_study, bak_prog_study.prog_study, bak_cuti_nonaktif.semester, bak_mahasiswa.thn_angkatan, COUNT(*) as jumlah_nonaktif "+ " FROM bak_cuti_nonaktif INNER JOIN "+ " bak_mahasiswa ON bak_cuti_nonaktif.npm = bak_mahasiswa.npm INNER JOIN "+ " bak_prog_study ON bak_mahasiswa.id_prog_study = bak_prog_study.id_prog_study "+ " WHERE semester = @SemNewHitungJumlah AND bak_mahasiswa.id_prog_study = @IdMhsProdi AND bak_cuti_nonaktif.status in ('N') "+ " GROUP BY bak_prog_study.id_prog_study, bak_prog_study.prog_study, bak_cuti_nonaktif.semester, bak_mahasiswa.thn_angkatan "+ " ) as MhsNonAktif on ProdiMhs.thn_angkatan = MhsNonAktif.thn_angkatan LEFT OUTER JOIN "+ " ( "+ " SELECT bak_prog_study.id_prog_study, bak_prog_study.prog_study, bak_cuti_nonaktif.semester, bak_mahasiswa.thn_angkatan, COUNT(*) as jumlah_cuti "+ " FROM bak_cuti_nonaktif INNER JOIN "+ " bak_mahasiswa ON bak_cuti_nonaktif.npm = bak_mahasiswa.npm INNER JOIN "+ " bak_prog_study ON bak_mahasiswa.id_prog_study = bak_prog_study.id_prog_study "+ " WHERE semester = @SemNewHitungJumlah AND bak_mahasiswa.id_prog_study = @IdMhsProdi AND bak_cuti_nonaktif.status in ('C') "+ " GROUP BY bak_prog_study.id_prog_study, bak_prog_study.prog_study, bak_cuti_nonaktif.semester, bak_mahasiswa.thn_angkatan "+ " ) as MhsCuti on ProdiMhs.thn_angkatan = MhsCuti.thn_angkatan LEFT OUTER JOIN "+ " ( " + " select thn_angkatan, COUNT(*) as jumlah_lulus from bak_mahasiswa where status = 'L' AND smster_lls = @SemNewHitungJumlah AND id_prog_study = @IdMhsProdi " + " group by thn_angkatan "+ " ) as MhsLulus on ProdiMhs.thn_angkatan = MhsLulus.thn_angkatan "+ "", con); CmdJadwal.CommandType = System.Data.CommandType.Text; CmdJadwal.Parameters.AddWithValue("@SemNewHitungJumlah", TopSemester); CmdJadwal.Parameters.AddWithValue("@IdMhsProdi", IdProdi); DataTable TableJadwal = new DataTable(); TableJadwal.Columns.Add("Tahun Angkatan"); TableJadwal.Columns.Add("Aktif"); TableJadwal.Columns.Add("Non Aktif"); TableJadwal.Columns.Add("Cuti"); TableJadwal.Columns.Add("Keluar"); TableJadwal.Columns.Add("Lulus"); using (SqlDataReader rdr = CmdJadwal.ExecuteReader()) { if (rdr.HasRows) { this.PanelRekapAktif.Enabled = true; this.PanelRekapAktif.Visible = true; while (rdr.Read()) { DataRow datarow = TableJadwal.NewRow(); datarow["Tahun Angkatan"] = rdr["thn_angkatan"]; if (rdr["jumlah_aktif"] == DBNull.Value) { datarow["Aktif"] = 0; } else { datarow["Aktif"] = rdr["jumlah_aktif"]; } if (rdr["jumlah_nonaktif"] == DBNull.Value) { datarow["Non Aktif"] = 0; } else { datarow["Non Aktif"] = rdr["jumlah_nonaktif"]; } if (rdr["jumlah_cuti"] == DBNull.Value) { datarow["Cuti"] = 0; } else { datarow["Cuti"] = rdr["jumlah_cuti"]; } if (rdr["jumlah_keluar"] == DBNull.Value) { datarow["Keluar"] = 0; } else { datarow["Keluar"] = rdr["jumlah_keluar"]; } if (rdr["jumlah_lulus"] == DBNull.Value) { datarow["Lulus"] = 0; } else { datarow["Lulus"] = rdr["jumlah_lulus"]; } TableJadwal.Rows.Add(datarow); } //Fill Gridview this.GvMhsAktif.DataSource = TableJadwal; this.GvMhsAktif.DataBind(); } else { this.PanelRekapAktif.Enabled = false; this.PanelRekapAktif.Visible = false; //clear Gridview TableJadwal.Rows.Clear(); TableJadwal.Clear(); GvMhsAktif.DataSource = TableJadwal; GvMhsAktif.DataBind(); this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Data Tidak Ditemukan');", true); } } } } catch (Exception ex) { this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('" + ex.Message.ToString() + "');", true); return; } }