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;
}
}
