/// <summary>
/// Loding data
/// </summary>
/// <returns>boolean</returns>
private bool Loaddb()
{
if (CbTahunAjaran.SelectedIndex >= 0 && CbKelas.SelectedIndex >= 0)
{
Dg.Columns.Clear();
Dg.Columns.Add("Column0", "NIS");
Dg.Columns.Add("Column1", "SISWA");
Dg.Columns.Add("Column2", "JK");
int a = Dg.Columns.Count;
string tempselelct = "";
A.SetSelect("SELECT `kode_pelajaran`, `namapelajaran` ");
A.SetFrom("FROM `tb_nilai` `N` INNER JOIN `tb_ruangan` `R` ON `R`.`kode_ruangan`=`N`.`kode_ruangan` INNER JOIN `tb_jadwal` `J` ON `J`.`kode_jadwal`=`N`.`kode_jadwal` INNER JOIN `r_walikelas` `W` ON `W`.`kode_kelas`=`R`.`kode_kelas` AND `W`.`tahunajaran`=`R`.`tahunajaran` LEFT JOIN `r_matapelajaran` `MP` ON `MP`.`kodepelajaran`=`J`.`kode_pelajaran` LEFT JOIN `m_siswa` `S` ON `S`.`kode_siswa`=`R`.`kode_siswa` ");
A.SetWhere("WHERE `N`.`kode_jenisnilai`='JN0000000000001' AND `W`.`kode_guru`='GR1501907290001' AND `J`.`tahunajaran`='2019/2020' AND `J`.`kode_kelas`='KL1011907230001' ");
A.SetGroupby("GROUP BY `kode_pelajaran` ");
A.SetOrderby("ORDER BY `namapelajaran` ");
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetGroupby() + A.GetOrderby() + ";");
foreach (DataRow b in A.GetQueri().GetData().Rows)
{
tempselelct += ", SUM(IF(`kode_pelajaran`= '" + b["kode_pelajaran"] + "', `nilai`, '-')) `" + b["namapelajaran"] + "`";
Dg.Columns.Add("Column1" + a, b["namapelajaran"].ToString());
a++;
}
A.SetSelect("SELECT `nis`, `namasiswa`, `jeniskelamin`" + tempselelct);
A.SetGroupby("GROUP BY `R`.`kode_siswa` ");
A.SetOrderby("ORDER BY `namasiswa` ");
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetGroupby() + A.GetOrderby() +
tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere(), A.GetGroupby()) + "");
A.SetLQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetOrderby());
Dg.QueriToDg();
}
return(true);
}
private void Bubah_Click(object sender, EventArgs e)
{
FKelolaUser f = new FKelolaUser(Dg.CurrentRow.Cells[Dg.GetColumnIndexByHeader("ID USER")].Value.ToString());
f.ShowDialog();
Loaddb();
}
private void Dg_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
if (e.ColumnIndex == Dg.GetColumnIndexByHeader("PILIH"))
{
if (Dg.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString().Equals("BELUM"))
{
Dictionary <string, string> data = new Dictionary <string, string>
{
{ "tahunajaran", CbTahunAjaran.Text },
{ "kodekelas", kodekelas[CbKelas.SelectedIndex] },
{ "kelas", CbKelas.Text },
{ "kodepelajaran", Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("KODE PELAJARAN")].Value.ToString() },
{ "kodemapel", Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("KODE MAPEL")].Value.ToString() },
{ "pelajaran", Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("PELAJARAN")].Value.ToString() },
};
FKelolaPengajaran f = new FKelolaPengajaran(data);
f.ShowDialog();
Loaddb();
}
else
{
FKelolaPengajaran f = new FKelolaPengajaran(Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("KODE JADWAL")].Value);
f.ShowDialog();
Dg.LoadIndex(Loaddb, e.ColumnIndex);
}
}
}
}
private void Dg_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
if (e.ColumnIndex == Dg.GetColumnIndexByHeader("PILIH"))
{
int index = Dg.CurrentRow.Index;
if (Dg.CurrentRow.Cells[Dg.GetColumnIndexByHeader("PILIH")].Value.ToString().Equals("SUDAH"))
{
if (MessageBox.Show("Hapus Kelas?", "Pertanyaan", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (A.ManipulasiData("DELETE FROM `tb_ruangan` " +
"WHERE `kode_ruangan` = '" + Dg.CurrentRow.Cells[Dg.GetColumnIndexByHeader("KODE RUANGAN")].Value.ToString() + "'"))
{
Dg.LoadIndex(loaddb, 2);
}
}
}
else
{
string value = "";
A.InputRichTextBox("Keterangan", "Input", ref value);
A.SetInsert("INSERT INTO `tb_ruangan` (`kode_ruangan`,`kode_kelas`,`kode_siswa`,`tahunajaran`,`keterangan`,`tanggal`,`id_user`) ");
A.SetValues("VALUES ('" + A.GenerateKode("RG", "tb_ruangan", "kode_ruangan") + "', '" + KodeKelas[CbKelas.SelectedIndex] + "', " +
"'" + Dg.Rows[e.RowIndex].Cells[0].Value.ToString() + "', '" + CbTahunAjaran.Text + "', '" + value + "', NOW(), '" + S.GetUserid() + "')");
A.SetQueri(A.GetInsert() + A.GetValues() + ";");
if (A.GetQueri().ManipulasiData())
{
Dg.LoadIndex(loaddb, 2);
}
}
}
}
}
//private void TextBox_TextChanged(object sender, TextChangedEventArgs e)
//{
// var text = sender as TextBox;
// if (text != null)
// {
// var tag = text.Tag as Filter;
// vm.Refresh(tag);
// dg.Items.Refresh();
// }
//}
private void TextBox_PreviewKeyDown(object sender, KeyEventArgs e)
{
if (e.Key == Key.Down)
{
Dg.SelectRowByIndex(dg, 0);
}
}
private void menuItem3_Click(object sender, System.EventArgs e)
{
Dg edu = new Dg("Initial Catalog=CodeGen;Data Source=localhost;Integrated Security=SSPI;");
DataTable dt;
dt = edu.GetSPList("Initial Catalog=CodeGen;Data Source=localhost;Integrated Security=SSPI;");
}
private void Dg_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
if (e.ColumnIndex == Dg.GetColumnIndexByHeader("UBAH"))
{
Dg.Enabled = false;
TbJenisNilai.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("JENIS NILAI")].Value.ToString();
TbKeterangan.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("KETERANGAN")].Value.ToString();
}
else if (e.ColumnIndex == Dg.GetColumnIndexByHeader("HAPUS"))
{
if (Dg.Rows[e.RowIndex].Cells[0].Value.ToString().Equals("JN0000000000001"))
{
MessageBox.Show("Tidak dapat menghapus Jenis nilai ini!!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (MessageBox.Show("Hapus mata pelajaran?", "Pertanyaan", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (A.DBHapus("UPDATE `r_jenisnilai` SET `hapus` = 'Y' WHERE `kode_jenisnilai` = '" + Dg.Rows[e.RowIndex].Cells[0].Value.ToString() + "';"))
{
Loaddb();
}
}
}
}
}
}
private void Dg_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (Dg.SelectedRows.Count > 0 && e.RowIndex >= 0)
{
tbket.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("KETERANGAN")].Value.ToString();
tbhakakses.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("HAK AKSES")].Value.ToString();
}
}
private void dataGrid1_DoubleClick(object sender, System.EventArgs e)
{
Dg myDG = new Dg(connectionstring);
SqlParameterCollection spc;
spc = myDG.getSPParameters(connectionstring, "listModels");
this.richTextBox1.AppendText(myDG.genSignature("listModels", spc));
this.richTextBox1.AppendText(myDG.genParmsCode(spc));
}
private void button1_Click(object sender, System.EventArgs e)
{
DataTable dt;
this.richTextBox1.Clear();
connectionstring = "Initial Catalog=CodeGen;Data Source=localhost;Integrated Security=SSPI;";
Dg myDG = new Dg(connectionstring);
dt = myDG.GetSPList(connectionstring);
this.dataGrid1.DataSource = dt;
}
private void BSimpan_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(TbKodeMapel.Text))
{
MessageBox.Show("Kode mapel kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (string.IsNullOrEmpty(TbMataPelajaran.Text))
{
MessageBox.Show("Mata Pelajaran kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (CbStatus.SelectedIndex < 0)
{
MessageBox.Show("Status kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (Dg.Enabled)
{
if (MessageBox.Show("Simpan pelajaran baru?", "Pertanyaan", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
A.SetInsert("INSERT INTO `r_matapelajaran` (`kodepelajaran`, `kodemapel`, `namapelajaran`, `status`)");
A.SetValues("VALUES('" + A.GenerateKode("MP", "r_matapelajaran", "kodepelajaran") + "', '" + TbKodeMapel.Text + "', " +
"'" + TbMataPelajaran.StrEscape() + "', '" + CbStatus.ToYN() + "')");
A.SetQueri(A.GetInsert() + A.GetValues() + ";");
if (A.GetQueri().ManipulasiData())
{
MessageBox.Show("Data telah ditambahkan!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information);
TbKodeMapel.Clear();
TbMataPelajaran.Clear();
CbStatus.SelectedIndex = -1;
Loaddb();
}
}
}
else
{
if (MessageBox.Show("Simpan perubahan pelajaran?", "Pertanyaan", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
A.SetUpdate("UPDATE `r_matapelajaran` ");
A.SetSet("SET `kodemapel` = '" + TbKodeMapel.Text + "', `namapelajaran` = '" + TbMataPelajaran.StrEscape() + "', `status` = '" + CbStatus.ToYN() + "' ");
A.SetWhere("WHERE `kodepelajaran` = '" + Dg.CurrentRow.Cells[0].Value.ToString() + "' ");
A.SetQueri(A.GetUpdate() + A.GetSet() + A.GetWhere() + ";");
if (A.GetQueri().ManipulasiData())
{
MessageBox.Show("Data telah diubah!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information);
TbKodeMapel.Clear();
TbMataPelajaran.Clear();
CbStatus.SelectedIndex = -1;
Dg.LoadIndex(Loaddb, 1);
}
}
}
}
}
private void BExport_Click(object sender, EventArgs e)
{
if (Dg.Rows.Count > 0)
{
string judul = "Data Pelajaran Tahun Ajaran " + CbTahunAjaran.Text + " " + DateTime.Now.ToString("dd/MM/yyyy");
Dg.ExportExcel(judul);
}
else
{
MessageBox.Show("Data kosong!!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private bool Loaddb()
{
A.SetSelect("SELECT `kodepelajaran`, `kodemapel`, `namapelajaran`, IF(`status`='Y','YA','TIDAK') `status` ");
A.SetFrom("FROM `r_matapelajaran` ");
A.SetWhere("WHERE `hapus`='N' ");
TbCari.GenerateQueriCari(new List <string>()
{
"kodemapel", "namapelajaran", "status",
});
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere()) + ";");
Dg.QueriToDg();
return(true);
}
private bool Loaddb()
{
A.SetSelect("SELECT `kode_jenisnilai`, `namajenisnilai`, `keterangan` ");
A.SetFrom("FROM `r_jenisnilai` ");
A.SetWhere("WHERE `hapus`='N' ");
TbCari.GenerateQueriCari(new List <string>()
{
"kode_jenisnilai", "namajenisnilai", "keterangan",
});
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere()) + ";");
Dg.QueriToDg();
return(true);
}
private void BSimpan_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(TbKelas.Text))
{
MessageBox.Show("Nama kelas kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (string.IsNullOrEmpty(TbUrutan.Text))
{
MessageBox.Show("Urutan kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (Dg.Enabled)
{
if (MessageBox.Show("Simpan kelas baru?", "Pertanyaan", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
A.SetInsert("INSERT INTO `r_kelas` (`kode_kelas`, `namakelas`, `urutan`, `keterangan`)");
A.SetValues("VALUES('" + A.GenerateKode("KL", "r_kelas", "kode_kelas") + "', '" + TbKelas.Text + "', '" + TbUrutan.Text + "', " +
"'" + TbKeterangan.StrEscape() + "')");
A.SetQueri(A.GetInsert() + A.GetValues() + ";");
if (A.GetQueri().ManipulasiData())
{
MessageBox.Show("Data telah ditambahkan!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information);
TbKelas.Clear();
TbUrutan.Clear();
TbKeterangan.Clear();
Loaddb();
}
}
}
else
{
if (MessageBox.Show("Simpan perubahan kelas?", "Pertanyaan", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
A.SetUpdate("UPDATE `r_kelas` ");
A.SetSet("SET `namakelas` = '" + TbKelas.Text + "', `keterangan` = '" + TbKeterangan.StrEscape() + "', '" + TbUrutan.Text + "' ");
A.SetWhere("WHERE `kode_kelas` = '" + Dg.CurrentRow.Cells[0].Value.ToString() + "' ");
A.SetQueri(A.GetUpdate() + A.GetSet() + A.GetWhere() + ";");
if (A.GetQueri().ManipulasiData())
{
MessageBox.Show("Data telah diubah!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information);
TbKelas.Clear();
TbUrutan.Clear();
TbKeterangan.Clear();
Dg.LoadIndex(Loaddb, 1);
}
}
}
}
}
private bool Loaddb()
{
A.SetSelect("SELECT `id_user`, `nama_akses`, `username` ");
A.SetFrom("FROM `m_user` `U` LEFT JOIN `m_akses` `A` ON `A`.`id_akses`=`U`.`id_akses` ");
A.SetWhere("WHERE `hapus`='N' ");
if (S.GetUseracces() != "1")
{
A.SetWhere(A.GetWhere() + "AND `id_user`='" + S.GetUserid() + "' ");
}
A.SetOrderby(" ORDER BY `id_user` ");
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetOrderby() + tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere()));
Dg.QueriToDg();
return(true);
}
private bool Loaddb()
{
if (CbKelas.SelectedIndex >= 0 && CbTahunAjaran.SelectedIndex >= 0)
{
A.SetSelect("SELECT `nis`, `namasiswa`, `jeniskelamin`, `angkatan` ");
A.SetFrom("FROM `tb_ruangan` `R` LEFT JOIN `m_siswa` `S` ON `S`.`kode_siswa`=`R`.`kode_siswa` ");
A.SetWhere("WHERE `tahunajaran`= '" + CbTahunAjaran.Text + "' AND `R`.`kode_kelas`= '" + kodekelas[CbKelas.SelectedIndex] + "' ");
A.SetOrderby("ORDER BY `namasiswa` ASC ");
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetOrderby() + tbhalaman.LimitQ(ldarihalaman, LJData, A.GetFrom(), A.GetWhere()) + ";");
A.SetLQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetGroupby() + A.GetOrderby());
Dg.QueriToDg();
}
return(true);
}
private bool Loaddb()
{
A.SetSelect("SELECT `kode_guru`, `nidn`, `nik`, `nosk`, CONCAT(IF(`gelardepan` IS NULL,'', CONCAT(`gelardepan`,' ')), `namaguru`, ' ', " +
"IF(`gelarbelakang` IS NULL, '', CONCAT(`gelarbelakang`,' '))) `namaguru`, `jeniskelamin`, CONCAT(IF(`tempatlahir`='','-', `tempatlahir`), ', ', DATE_FORMAT(`tgllahir`,'%d/%m/%Y')) `ttl`, " +
"`nohp`, `email`, `alamat`, `golongan`, `jabatanstruktural`, `jabatanfungsional`, DATE_FORMAT(`masuk`,'%d/%m/%Y') `masuk`, `status` ");
A.SetFrom("FROM `m_guru` ");
A.SetWhere("WHERE `hapus`='N' ");
TbCari.GenerateQueriCari(new List <string>()
{
"nidn", "nik", "nosk", "namaguru", "gelardepan", "gelarbelakang", "golongan", "nohp", "email",
});
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere()) + ";");
Dg.QueriToDg();
return(true);
}
private bool Loaddb()
{
A.SetSelect("SELECT `kode_siswa`, `nis`, `namasiswa`, `jeniskelamin`, CONCAT(IF(`tempatlahir`='','-', `tempatlahir`),', ', `tgllahir`) `tll`, `kontak`, `email`, " +
"`alamat`, `ayah`, `ibu`, DATE_FORMAT(`masuk`,'%d-%m-%Y') `masuk`,`angkatan`, `status`, `keterangan` ");
A.SetFrom("FROM `m_siswa` ");
A.SetWhere("WHERE `hapus`='N' ");
Dtp1.GenerateQueriDate(Dtp2, "masuk");
CbAngkatan.GenerateQueriValue("angkatan");
TbCari.GenerateQueriCari(new List <string>()
{
"nis", "namasiswa", "tempatlahir", "kontak", "email", "ayah", "ibu"
});
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere()) + ";");
Dg.QueriToDg();
return(true);
}
private void BUbah_Click(object sender, EventArgs e)
{
if (Dg.Rows.Count >= 0)
{
if (Dg.SelectedRows.Count > 0)
{
FInputSiswa f = new FInputSiswa(Dg.CurrentRow.Cells[Dg.GetColumnIndexByHeader("KODE SISWA")].Value.ToString());
f.ShowDialog();
Dg.LoadIndex(Loaddb, 1);
}
}
else
{
MessageBox.Show("Data Kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private bool Loaddb()
{
if (CbTahunAjaran.SelectedIndex >= 0)
{
A.SetSelect("SELECT `namakelas`, `kodemapel`, `namapelajaran`, `hari`, DATE_FORMAT(`waktu`, '%h:%i') `waktu`, `totaljam` ");
A.SetFrom("FROM `tb_jadwal` `J` LEFT JOIN `tb_waktupelajaran` `W` ON `W`.`kode_jadwal`=`J`.`kode_jadwal` " +
"LEFT JOIN `r_matapelajaran` `MP` ON `MP`.`kodepelajaran`=`J`.`kode_pelajaran` " +
"LEFT JOIN `r_kelas` `K` ON `K`.`kode_kelas`=`J`.`kode_kelas` ");
A.SetWhere("WHERE `J`.`kode_guru`='" + S.GetKodeGuru() + "' AND tahunajaran = '" + CbTahunAjaran.Text + "' ");
A.SetOrderby("ORDER BY `namakelas`, `namapelajaran` ASC ");
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetOrderby() + tbhalaman.LimitQ(ldarihalaman, LJData, A.GetFrom(), A.GetWhere()) + ";");
A.SetLQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetGroupby() + A.GetOrderby());
Dg.QueriToDg();
}
return(true);
}
private void searcher_PreviewKeyDown(object sender, KeyEventArgs e)
{
if (!Entrence.IsShowPopUp)
{
// e.Handled = true;
return;
}
if (e.Key == Key.Down)
{
Dg.SelectRowByIndex(dg, 0);
}
if (e.Key == Key.Up)
{
Dg.SelectRowByIndex(dg, dg.Items.Count - 1);
}
}
private void Bsimpan_Click(object sender, EventArgs e)
{
if (Dg.SelectedRows.Count > 0)
{
if (MessageBox.Show("Simpan?", "Pertanyaan", MessageBoxButtons.OK, MessageBoxIcon.Question) == DialogResult.Yes)
{
A.SetQueri("UPDATE `m_akses` SET `nama_akses`='" + tbhakakses.Text + "', `ket_akses`='" + tbket.Text + "' WHERE `id_akses`=" +
Dg.CurrentRow.Cells[Dg.GetColumnIndexByHeader("ID HAK AKSES")].Value.ToString());
if (A.GetQueri().ManipulasiData())
{
MessageBox.Show("Data berhasil di simpan!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information);
tbket.Clear();
tbhakakses.Clear();
Loaddb();
}
}
}
}
private bool loaddb()
{
if (CbKelas.SelectedIndex >= 0 && CbTahunAjaran.SelectedIndex >= 0 && CbMasuk.SelectedIndex >= 0)
{
A.SetSelect("SELECT `SW`.`kode_siswa`, `kode_ruangan`, IF(`kode_ruangan` IS NULL, 'BELUM', 'SUDAH') `pilih`, `namakelas`, `nis`, " +
"`namasiswa`,`jeniskelamin`,`masuk`, `R`.`keterangan` ");
A.SetFrom("FROM `m_siswa` `SW` LEFT JOIN (SELECT * FROM `tb_ruangan` `R` WHERE `tahunajaran`= '" + CbTahunAjaran.Text + "') `R` " +
"ON `R`.`kode_siswa` = `SW`.`kode_siswa` LEFT JOIN `r_kelas` `K` ON `K`.`kode_kelas`=`R`.`kode_kelas` ");
A.SetWhere("WHERE `SW`.`hapus` = 'N' AND `masuk` LIKE '" + CbMasuk.Text + "%' ");
TbCari.GenerateQueriCari(new List <string>()
{
"namakelas", "nis", "namasiswa"
});
A.SetOrderby("ORDER BY `SW`.`namasiswa` ASC ");
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere()) + ";");
Dg.QueriToDg();
}
return(true);
}
//Func<string,string,string,object[],out int> Fun=Reader;
/*_执行Sql命令_*/
public static T Exec <T>(string CmdStr, Dg <T> Ex, int Cls = 1, string ConStr = null, string CmdType = null, params object[] Ps)
{
T Tmp = default(T);
ConStr = string.IsNullOrEmpty(ConStr) ? Db.Ds : ConStr;
SqlConnection Con = new SqlConnection(ConStr);
SqlCommand Cmd = new SqlCommand(CmdStr, Con);
Cmd.CommandType = string.IsNullOrEmpty(CmdType) ? CommandType.StoredProcedure : CommandType.Text;
try
{
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
if (Ps.Length > 0)
{
Db.Ps(Cmd, Ps);
}
Tmp = Ex(Cmd);
if (Ps != null && Ps.Length > 1 && C.Lst(Ps).ToString().StartsWith("@"))
{
Ps.SetValue(Cmd.Parameters[C.Lst(Ps).ToString()].Value, Ps.Length - 1);
}
}
catch (SqlException e)
{
if (typeof(T).ToString() != "System.Object[]")
{
Tmp = (T)C.Cvt <string>(e.Message);
}
}
finally
{
if (Cls == 1)
{
Con.Close();
}
}
return(Tmp);
}
private void Dg_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
if (e.ColumnIndex == Dg.GetColumnIndexByHeader("UBAH"))
{
Dg.Enabled = false;
TbKodeMapel.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("KODE MAPEL")].Value.ToString();
TbMataPelajaran.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("MATA PELAJARAN")].Value.ToString();
CbStatus.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("AKTIF")].Value.ToString();
}
else if (e.ColumnIndex == Dg.GetColumnIndexByHeader("HAPUS"))
{
if (MessageBox.Show("Hapus mata pelajaran?", "Pertanyaan", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (A.DBHapus("UPDATE `r_matapelajaran` SET `hapus` = 'Y' WHERE `kodepelajaran` = '" + Dg.Rows[e.RowIndex].Cells[0].Value.ToString() + "';"))
{
Loaddb();
}
}
}
}
}
private bool Loaddb()
{
if (CbKelas.SelectedIndex >= 0 && CbJenisNilai.SelectedIndex >= 0 && CbTahunAjaran.SelectedIndex >= 0 && CbMataPelajaran.SelectedIndex >= 0)
{
A.SetSelect("SELECT IFNULL(`kode_nilai`, '0') `kode_nilai` , `R`.`kode_ruangan`, " +
"IF(`kode_nilai` IS NULL, 'INPUT', 'HAPUS') `input`, `nis`, `S`.`namasiswa`, `jeniskelamin`, `namakelas`, " +
"`N`.`tanggal`, IFNULL(`nilai`,'-') `nilai`, IFNULL(`N`.`keterangan`,'') `keterangan` ");
A.SetFrom("FROM `tb_ruangan` `R` LEFT JOIN `m_siswa` `S` ON `R`.`kode_siswa` = `S`.`kode_siswa` " +
"LEFT JOIN `r_kelas` `K` ON `K`.`kode_kelas`=`R`.`kode_kelas`LEFT JOIN (SELECT * FROM `tb_nilai` `N` " +
"WHERE `N`.`kode_jenisnilai`='" + KodeJenisNilai[CbJenisNilai.SelectedIndex] + "' " +
"AND `N`.`kode_jadwal`=(SELECT `kode_jadwal` FROM `tb_jadwal` WHERE `kode_guru`='" + S.GetKodeGuru() + "' " +
"AND `kode_kelas`='" + KodeKelas[CbKelas.SelectedIndex] + "' AND `kode_pelajaran`= '" + KodePelajaran[CbMataPelajaran.SelectedIndex] + "' " +
"AND `tahunajaran`= '" + CbTahunAjaran.Text + "')) `N` ON `N`.`kode_ruangan` = `R`.`kode_ruangan` ");
A.SetWhere("WHERE `R`.`kode_kelas` = '" + KodeKelas[CbKelas.SelectedIndex] + "' AND `R`.`tahunajaran` = '" + CbTahunAjaran.Text + "'");
TbCari.GenerateQueriCari(new List <string>()
{
"nis", "namasiswa", "namakelas", "`N`.`keterangan`"
});
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere()) + ";");
Dg.QueriToDg();
}
return(true);
}
private void Dg_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
if (e.ColumnIndex == Dg.GetColumnIndexByHeader("UBAH"))
{
Dg.Enabled = false;
TbKelas.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("KELAS")].Value.ToString();
TbUrutan.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("URUTAN")].Value.ToString();
TbKeterangan.Text = Dg.Rows[e.RowIndex].Cells[Dg.GetColumnIndexByHeader("KETERANGAN")].Value.ToString();
}
else if (e.ColumnIndex == Dg.GetColumnIndexByHeader("HAPUS"))
{
if (MessageBox.Show("Hapus mata pelajaran?", "Pertanyaan", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
if (A.DBHapus("UPDATE `r_kelas` SET `hapus` = 'Y' WHERE `kode_kelas` = '" + Dg.Rows[e.RowIndex].Cells[0].Value.ToString() + "';"))
{
Loaddb();
}
}
}
}
}
/// <summary>
/// Loding data
/// </summary>
/// <returns>boolean</returns>
private bool Loaddb()
{
if(CbTahunAjaran.SelectedIndex>=0 && CbKelas.SelectedIndex>=0 && CbMapel.SelectedIndex>=0)
{
Dg.Columns.Clear();
Dg.Columns.Add("Column0", "NIS");
Dg.Columns.Add("Column1", "SISWA");
Dg.Columns.Add("Column2", "JK");
int a = Dg.Columns.Count;
string tempselelct = "";
A.SetSelect("SELECT `N`.`kode_jenisnilai`, `namajenisnilai` ");
A.SetFrom("FROM `tb_nilai` `N` LEFT JOIN `r_jenisnilai` `JN` ON `JN`.`kode_jenisnilai`=`N`.`kode_jenisnilai` " +
"LEFT JOIN `tb_ruangan` `R` ON `R`.`kode_ruangan`=`N`.`kode_ruangan` LEFT JOIN `tb_jadwal` `J` ON `J`.`kode_jadwal`=`N`.`kode_jadwal` " +
"LEFT JOIN `r_kelas` `K` ON `K`.`kode_kelas`=`R`.`kode_kelas` LEFT JOIN `m_siswa` `S` ON `S`.`kode_siswa`=`R`.`kode_siswa` ");
A.SetWhere("WHERE `J`.`kode_guru`='"+S.GetKodeGuru()+"' AND `J`.`tahunajaran`='"+CbTahunAjaran.Text+ "' " +
"AND `J`.`kode_pelajaran`='"+KodePelajaran[CbMapel.SelectedIndex]+"' AND `J`.`kode_kelas`='"+KodeKelas[CbKelas.SelectedIndex]+"' ");
A.SetGroupby("GROUP BY `N`.`kode_jenisnilai` ");
A.SetOrderby("ORDER BY `JN`.`urutan` ");
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetGroupby() + A.GetOrderby() + ";");
foreach(DataRow b in A.GetQueri().GetData().Rows)
{
tempselelct += ", SUM(IF(`N`.`kode_jenisnilai`= '" + b["kode_jenisnilai"] + "', `nilai`, '-')) `" + b["namajenisnilai"] + "`";
Dg.Columns.Add("Column1"+a, b["namajenisnilai"].ToString());
a++;
}
A.SetSelect("SELECT `nis`, `namasiswa`, `jeniskelamin`" + tempselelct);
A.SetGroupby("GROUP BY `S`.`kode_siswa`");
A.SetOrderby("ORDER BY `namasiswa` ");
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + A.GetGroupby() + A.GetOrderby() +
tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere(), A.GetGroupby()) + "");
A.SetLQueri(A.GetSelect()+A.GetFrom()+A.GetWhere()+A.GetOrderby());
Dg.QueriToDg();
}
return true;
}
private bool Loaddb()
{
if (CbKelas.SelectedIndex >= 0 && CbTahunAjaran.SelectedIndex >= 0)
{
A.SetSelect("SELECT IFNULL(`J`.`kode_jadwal`,'0') `kodejadwal`, `kodepelajaran`, " +
"IF(`J`.`kode_jadwal` IS NULL, 'BELUM', 'SUDAH') `pilih`, `kodemapel`, `namapelajaran`, `namaguru`, `keterangan`, `jadwal` ");
A.SetFrom("FROM `r_matapelajaran` `MP` LEFT JOIN (SELECT `J`.`kode_jadwal`, `J`.`kode_pelajaran`, " +
"CONCAT(`nidn`,' (',IF(`gelardepan`='','',CONCAT(`gelardepan`,' ')), `namaguru`, " +
"IF(`gelarbelakang`='','',CONCAT(' ', `gelarbelakang`)),')') `namaguru`, `J`.`keterangan` FROM `tb_jadwal` `J` " +
"LEFT JOIN `m_guru` `G` ON `G`.`kode_guru`=`J`.`kode_guru`WHERE `kode_kelas`='" + kodekelas[CbKelas.SelectedIndex] + "' " +
"AND tahunajaran='" + CbTahunAjaran.Text + "') `J` ON `J`.`kode_pelajaran`=`MP`.`kodepelajaran` " +
"LEFT JOIN (SELECT`kode_jadwal`, GROUP_CONCAT(`hari`,',',`waktu`,',',`totaljam`,';' SEPARATOR '\n') `jadwal` " +
"FROM `tb_waktupelajaran`GROUP BY `kode_jadwal`) `W` ON `W`.`kode_jadwal`=`J`.`kode_jadwal`");
A.SetWhere("WHERE `status`='Y' AND `hapus`='N' ");
TbCari.GenerateQueriCari(new List <string>()
{
"kodemapel", "namapelajaran", "namaguru"
});
A.SetQueri(A.GetSelect() + A.GetFrom() + A.GetWhere() + tbhalaman.LimitQ(ldarihalaman, A.GetFrom(), A.GetWhere()) + ";");
Dg.QueriToDg();
return(true);
}
return(false);
}
