protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string CS = ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmdvw = new SqlCommand("SELECT fak_kode, fak_name FROM bak_fakultas", con);
cmdvw.CommandType = System.Data.CommandType.Text;
DataTable Table = new DataTable();
Table.Columns.Add("Kode Fakultas");
Table.Columns.Add("Nama Fakultas");
con.Open();
using (SqlDataReader rdr = cmdvw.ExecuteReader())
{
if (rdr.HasRows)
{
while (rdr.Read())
{
DataRow datarow = Table.NewRow();
datarow["Kode Fakultas"] = rdr["fak_kode"];
datarow["Nama Fakultas"] = rdr["fak_name"];
//DateTime DateAwal = Convert.ToDateTime(rdr["open_date"]);
//DateTime DateAkhir = Convert.ToDateTime(rdr["close_date"]);
//datarow["Tanggal Buka"] = DateAwal.ToString("dd MMMM, yyyy");
//datarow["Tanggal Tutup"] = DateAkhir.ToString("dd MMMM, yyyy");
Table.Rows.Add(datarow);
}
//Fill Gridview
this.GVFakultas.DataSource = Table;
this.GVFakultas.DataBind();
Table.Dispose();
}
else
{
//clear Gridview
Table.Rows.Clear();
Table.Clear();
GVFakultas.DataSource = Table;
GVFakultas.DataBind();
}
}
}
}
}
protected void BtnOK_Click(object sender, EventArgs e)
{
if (this.DLTahun.SelectedValue == "Tahun" || this.DLTahun.SelectedValue == "")
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Pilih Tahun Akademik');", true);
return;
}
if (this.DlSemester.SelectedValue == "Semester")
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Pilih Semester');", true);
return;
}
string CS = ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
con.Open();
SqlCommand CmdMakul = new SqlCommand(" " +
"SELECT bak_fakultas.kode, bak_fakultas.fak_name, bak_jadwal.semester " +
"INTO #TempFakultas "+
"FROM bak_jadwal INNER JOIN " +
"bak_makul ON bak_jadwal.kode_makul = bak_makul.kode_makul INNER JOIN " +
" bak_dosen ON bak_jadwal.nidn = bak_dosen.nidn INNER JOIN " +
"bak_prog_study ON bak_jadwal.id_prog_study = bak_prog_study.id_prog_study 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_fakultas ON bak_prog_study.id_fakultas = bak_fakultas.kode " +
"WHERE (bak_jadwal.semester = @Semester) " +
"GROUP BY bak_jadwal.no_jadwal, bak_jadwal.kode_makul, bak_makul.makul, bak_makul.sks, bak_dosen.nidn, bak_dosen.nama, bak_jadwal.kelas, bak_jadwal.jenis_kelas, bak_jadwal.semester, " +
"bak_prog_study.prog_study, bak_jadwal.id_prog_study, bak_prog_study.id_prog_study, bak_fakultas.kode, bak_fakultas.fak_name " +
"SELECT bak_jadwal.no_jadwal, bak_nilai.nilai, COUNT(*) AS jumlah_kurang, bak_jadwal.id_prog_study, bak_prog_study.prog_study, bak_fakultas.kode, bak_fakultas.fak_name, bak_jadwal.semester " +
"INTO #TempFakultasKurang " +
"FROM bak_jadwal INNER JOIN " +
"bak_krs ON bak_jadwal.no_jadwal = bak_krs.no_jadwal INNER JOIN " +
"bak_makul ON bak_jadwal.kode_makul = bak_makul.kode_makul INNER JOIN " +
"bak_mahasiswa ON bak_krs.npm = bak_mahasiswa.npm INNER JOIN " +
"bak_dosen ON bak_jadwal.nidn = bak_dosen.nidn INNER JOIN " +
"bak_prog_study ON bak_jadwal.id_prog_study = bak_prog_study.id_prog_study INNER JOIN " +
"bak_fakultas ON bak_prog_study.id_fakultas = bak_fakultas.kode LEFT OUTER JOIN " +
"bak_nilai ON bak_jadwal.kode_makul = bak_nilai.kode_makul AND bak_mahasiswa.npm = bak_nilai.npm AND bak_nilai.semester = bak_jadwal.semester " +
"WHERE (bak_nilai.nilai IS NULL) AND (bak_jadwal.semester = @semester) " +
"GROUP BY bak_jadwal.no_jadwal, bak_nilai.nilai, bak_prog_study.prog_study, bak_jadwal.semester, bak_jadwal.id_prog_study, bak_fakultas.kode, bak_fakultas.fak_name " +
"SELECT x.kode, x.fak_name, x.jumlah_rombel, y.jumlah_kurang, CAST((CAST(jumlah_kurang AS DECIMAL)/CAST(jumlah_rombel AS DECIMAL))*100 AS DECIMAL (4,2)) AS kurang, CAST(100 - ((CAST(jumlah_kurang AS DECIMAL)/CAST(jumlah_rombel AS DECIMAL))*100) AS DECIMAL (4,2)) AS lengkap, x.semester FROM " +
"( " +
"SELECT kode,fak_name,semester, COUNT(*) AS jumlah_rombel FROM #TempFakultas GROUP BY kode,fak_name,semester " +
") AS x " +
"LEFT OUTER JOIN " +
"( " +
"SELECT kode,fak_name, COUNT(*) jumlah_kurang FROM #TempFakultasKurang GROUP BY kode,fak_name " +
") AS y " +
"ON y.kode = x.kode " +
"DROP TABLE #TempFakultasKurang " +
"DROP TABLE #TempFakultas " +
"", con);
CmdMakul.CommandType = System.Data.CommandType.Text;
CmdMakul.Parameters.AddWithValue("@semester", this.DLTahun.SelectedItem.Text + this.DlSemester.SelectedItem.Text);
DataTable TableMakul = new DataTable();
TableMakul.Columns.Add("Kode");
TableMakul.Columns.Add("Fakultas");
TableMakul.Columns.Add("Jumlah Rombel");
TableMakul.Columns.Add("Rombel Kurang");
TableMakul.Columns.Add("Kurang (%)");
TableMakul.Columns.Add("Lengkap (%)");
using (SqlDataReader rdr = CmdMakul.ExecuteReader())
{
if (rdr.HasRows)
{
this.PanelFakultas.Enabled = true;
this.PanelFakultas.Visible = true;
this.PanelListNilai.Enabled = false;
this.PanelListNilai.Visible = false;
while (rdr.Read())
{
DataRow datarow = TableMakul.NewRow();
datarow["Kode"] = rdr["kode"].ToString();
datarow["Fakultas"] = rdr["fak_name"];
datarow["Jumlah Rombel"] = rdr["jumlah_rombel"];
datarow["Rombel Kurang"] = rdr["jumlah_kurang"];
datarow["Kurang (%)"] = rdr["kurang"];
datarow["Lengkap (%)"] = rdr["lengkap"];
TableMakul.Rows.Add(datarow);
}
//Fill Gridview
this.GVFakultas.DataSource = TableMakul;
this.GVFakultas.DataBind();
}
else
{
this.PanelFakultas.Enabled = false;
this.PanelFakultas.Visible = false;
this.PanelListNilai.Enabled = false;
this.PanelListNilai.Visible = false;
//clear Gridview
TableMakul.Rows.Clear();
TableMakul.Clear();
GVFakultas.DataSource = TableMakul;
GVFakultas.DataBind();
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "ex", "alert('Jawal Belum Ada');", true);
return;
}
}
}
}