/// <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 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 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()
{
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 `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()
{
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()
{
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 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 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);
}
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);
}
/// <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);
}
public FKelolaPengajaran(object kodejadwal)
{
List <string> deletelist = new List <string>();
InitializeComponent();
this.SetControlFrom();
Loadawal();
string kodekelas = "", kodepelajaran = "";
foreach (DataRow b in A.GetData("SELECT `tahunajaran`, `namakelas`, `kode_guru`, `kodemapel`, `namapelajaran`, " +
"`J`.`keterangan`,`J`.`kode_kelas`, `kodepelajaran` FROM `tb_jadwal` `J` " +
"LEFT JOIN `r_kelas` `K` ON `K`.`kode_kelas`=`J`.`kode_kelas` " +
"LEFT JOIN `r_matapelajaran` `MP` ON `MP`.`kodepelajaran` =`J`.`kode_pelajaran` ").Rows)
{
TbTahunAjaran.Text = b["tahunajaran"].ToString();
TbKelas.Text = b["namakelas"].ToString();
CbGuru.SelectedIndex = KodeGuru.FindIndex(x => x.Equals(b["kode_guru"].ToString()));
TbKodeMapel.Text = b["kodemapel"].ToString();
TbPelajaran.Text = b["namapelajaran"].ToString();
TbKeterangan.Text = b["keterangan"].ToString();
kodekelas = b["kode_kelas"].ToString();
kodepelajaran = b["kodepelajaran"].ToString();
}
A.SetQueri("SELECT `idwaktupelajaran`, `hari`, `waktu`, `totaljam` FROM `tb_waktupelajaran` WHERE `kode_jadwal`='" + kodejadwal + "';");
Dg.QueriToDg();
BSimpan.Click += (sender, e) =>
{
if (string.IsNullOrEmpty(TbTahunAjaran.Text))
{
MessageBox.Show("Tahun ajaran kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (string.IsNullOrEmpty(TbKelas.Text))
{
MessageBox.Show("Kelas kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (string.IsNullOrEmpty(CbGuru.Text))
{
MessageBox.Show("Guru kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (string.IsNullOrEmpty(TbKodeMapel.Text))
{
MessageBox.Show("Kode mapel kosong!", "Peringatan", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
A.SetInsert("INSERT INTO `tb_jadwal` (`kode_jadwal`,`kode_guru`,`kode_kelas`,`kode_pelajaran`,`tahunajaran`,`keterangan`,`tanggal`,`id_user`) ");
A.SetValues("VALUES ('" + kodejadwal + "', '" + KodeGuru[CbGuru.SelectedIndex] + "', '" + kodekelas + "', '" + kodepelajaran + "', " +
"'" + TbTahunAjaran.Text + "', '" + TbKeterangan.StrEscape() + "', NOW(), '" + S.GetUserid() + "') ");
A.SetQueri(A.GetInsert() + A.GetValues() + ";");
foreach (DataGridViewRow b in Dg.Rows)
{
if (b.Cells[0].Value.ToString().Equals("0"))
{
A.SetInsert("INSERT INTO `tb_waktupelajaran` (`kode_jadwal`,`totaljam`,`hari`,`waktu`,`id_user`,`tanggal`) ");
A.SetValues("VALUES ('" + kodejadwal + "', '" + CbJumlahJam.Text + "', '" + CbHari.Text + "', " +
"'" + CbJam.Text + ":" + CbMenit.Text + ":00', '" + S.GetUserid() + "', NOW()) ");
A.SetQueri(A.GetQueri() + A.GetInsert() + A.GetValues() + ";");
}
else
{
A.SetUpdate("UPDATE `tb_waktupelajaran` ");
A.SetValues("SET `totaljam` = '" + CbJumlahJam.Text + "', `hari` = '" + CbHari.Text + "', " +
"`waktu` = '" + CbJam.Text + ":" + CbMenit.Text + ":00' ");
A.SetWhere("WHERE `idwaktupelajaran` = '" + b.Cells[0].Value.ToString() + "' ");
A.SetQueri(A.GetQueri() + A.GetInsert() + A.GetValues() + ";");
}
}
foreach (var b in deletelist)
{
A.SetQueri(A.GetQueri() + "DELETE FROM `tb_jadwal` WHERE `idwaktupelajaran` = '" + b + "';");
}
if (A.GetQueri().ManipulasiData())
{
MessageBox.Show("Jadwal telah diubah!!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
}
};
BHapus.Click += (sende, e) =>
{
if (MessageBox.Show("Menghapus seluruh data pengajaran??", "Pertanyaan", MessageBoxButtons.OK, MessageBoxIcon.Question) == DialogResult.Yes)
{
A.SetQueri("DELETE FROM `tb_waktupelajaran` WHERE `kode_jadwal` = '" + kodejadwal + "';");
A.SetQueri(A.GetQueri() + "DELETE FROM `tb_jadwal` WHERE `kode_jadwal` = '" + kodejadwal + "'; ");
A.GetQueri().DBHapus();
}
};
Dg.CellClick += (sender, e) =>
{
if (e.RowIndex >= 0)
{
if (e.ColumnIndex == Dg.GetColumnIndexByHeader("HAPUS"))
{
if (Dg.Rows[e.RowIndex].Cells["ID WAKTU"].Value.ToString() != "0")
{
deletelist.Add(Dg.Rows[e.RowIndex].Cells["ID WAKTU"].Value.ToString());
}
Dg.Rows.RemoveAt(e.RowIndex);
}
}
};
}
private bool Loaddb()
{
A.SetQueri("SELECT `id_akses`, `nama_akses`, `ket_akses` FROM `m_akses` ORDER BY `id_akses` ASC");
Dg.QueriToDg();
return(true);
}