private void tambahJenisSurat()
{
JenisSurat j = new JenisSurat();
j.setJenis(textBoxJenis.Text);
string jenis = j.getJenis();
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "INSERT INTO jenis_surat VALUES(NULL, @jenis)";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@jenis", jenis);
int hasil = cmd.ExecuteNonQuery();
if (hasil > 0)
{
MessageBox.Show("Data berhasil ditambah", "Sukses");
frm1.getAllJenisSurat();
}
else
{
MessageBox.Show("Data gagal ditambah", "Gagal");
}
conn.Close();
}
private void buttonHapusLampiranSuratKeluar_Click(object sender, EventArgs e)
{
if (FormSuratKeluar.status == "Tambah")
{
list_lampiran.RemoveAt(index_lampiran);
tampil_lampiran();
}
else if (FormSuratKeluar.status == "Edit")
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
query = "DELETE FROM lampiran_surat_keluar WHERE nomor_surat_keluar = @nomor_surat AND nama_lampiran = @lampiran";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nomor_surat", FormSuratMasuk.nomor_surat);
cmd.Parameters.AddWithValue("@lampiran", textBoxLampiranSuratKeluar.Text);
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
getLampiran();
}
}
private void tambahSubBidang()
{
BidangBagian bdg = new BidangBagian();
bdg.setBidang(textBoxSubBidangTambah.Text);
string bidang = bdg.getBidang();
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "INSERT INTO sub_bagian_bidang VALUES(NULL, @id_bidang, @nama_sub_bagian_bidang)";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nama_sub_bagian_bidang", bidang);
cmd.Parameters.AddWithValue("@id_bidang", id_bagian);
int hasil = cmd.ExecuteNonQuery();
if (hasil > 0)
{
MessageBox.Show("Data berhasil ditambah", "Sukses");
frm1.getAllSubBidang();
}
else
{
MessageBox.Show("Data gagal ditambah", "Gagal");
}
conn.Close();
}
private void buttonHapusJenisSurat_Click(object sender, EventArgs e)
{
string title = "Konfirmasi Penghapusan Data";
string konten = "Apakah Anda yakin ingin menghapus data?";
DialogResult result = MessageBox.Show(konten, title, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == System.Windows.Forms.DialogResult.Yes)
{
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "DELETE FROM jenis_surat WHERE id_jenis = @id_jenis";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@id_jenis", id_jenis);
//MessageBox.Show(query);
int sukses = cmd.ExecuteNonQuery();
if (sukses > 0)
{
MessageBox.Show("Data berhasil diupdate", "Sukses");
getAllJenisSurat();
}
else
{
MessageBox.Show("Data gagal diupdate", "Gagal");
}
conn.Close();
}
}
private void buttonEditLampiranSuratKeluar_Click(object sender, EventArgs e)
{
if (FormSuratKeluar.status == "Tambah")
{
list_lampiran[index_lampiran] = textBoxLampiranSuratKeluar.Text;
tampil_lampiran();
}
else if (FormSuratKeluar.status == "Edit")
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
foreach (DataGridViewRow row in dataGridViewLampiranSuratKeluar.SelectedRows)
{
lampiran_sebelumnya = row.Cells[0].Value.ToString();
}
try
{
query = "UPDATE lampiran_surat_keluar SET nama_lampiran = @nama_lampiran " +
"WHERE nomor_surat_keluar = @nomor_surat AND nama_lampiran = @lampiran_sebelumnya";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nama_lampiran", textBoxLampiranSuratKeluar.Text);
cmd.Parameters.AddWithValue("@lampiran_sebelumnya", lampiran_sebelumnya);
cmd.Parameters.AddWithValue("@nomor_surat", FormSuratKeluar.nomor_surat);
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
getLampiran();
}
}
public void getTotalRekapDisposisi()
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
query = "SELECT count(nomor_surat) as total_surat from surat_disposisi WHERE YEAR(tanggal_terima)=" + tahun + "";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader();
//setDataTable(reader);
while (reader.Read())
{
totalSurat = reader["total_surat"].ToString();
}
textBoxDisposisi.Text = totalSurat.ToString();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
private void buttonX1_Click(object sender, EventArgs e)
{
SaveFileDialog sfBackup = new SaveFileDialog();
sfBackup.Filter = "sql files (*.sql)|*.sql|All files (*.*)|*.*";
sfBackup.RestoreDirectory = true;
//sfBackup.ShowDialog();
Database dbc = new Database();
string strconnect = dbc.getString();
MySqlConnection connect = new MySqlConnection(strconnect);
connect.Open();
if (sfBackup.ShowDialog() == DialogResult.OK)
{
string filename = "" + sfBackup.FileName + "";
using (MySqlConnection cn = new MySqlConnection(strconnect))
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlBackup mb = new MySqlBackup(cmd))
{
cmd.Connection = cn;
cn.Open();
mb.ExportToFile(filename);
cn.Close();
}
}
}
try
{
bool backupResult = true;
if (backupResult == true)
{
MessageBox.Show("Sukses Backup Database!", "Sukses", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Gagal Backup Database!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (SystemException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void getAllJenisSurat()
{
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "SELECT * FROM jenis_surat";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader();
setDataTable(reader);
conn.Close();
}
public void getJenisSurat()
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
query = "SELECT nama_jenis FROM jenis_surat";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
comboBoxJenisSuratMasuk.Items.Add(reader[0]);
}
conn.Close();
}
public void getAllUser()
{
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "SELECT id_user, username, nama FROM user";
//MessageBox.Show(query);
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader();
setDataTable(reader);
conn.Close();
}
public void getAllBidang()
{
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "SELECT * FROM bagian_bidang";
//MessageBox.Show(query);
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader();
setDataTable(reader);
conn.Close();
}
public void getAllSubBidang2(string id_bidang)
{
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "SELECT * FROM sub_bagian_bidang where id_bagian_bidang = @id_bidang";
//MessageBox.Show(query);
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@id_bagian_bidang", id_bidang);
MySqlDataReader reader = cmd.ExecuteReader();
setDataTable(reader);
conn.Close();
}
public void getBidang()
{
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "SELECT * FROM bagian_bidang";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
comboBoxBidang.SelectedValue = reader[0];
comboBoxBidang.Items.Add(reader[1]);
}
conn.Close();
}
public string getIdJenisSurat(string nama_jenis)
{
string id_jenis = "";
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
query = "SELECT id_jenis FROM jenis_surat WHERE nama_jenis = @nama_jenis";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nama_jenis", nama_jenis);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
id_jenis = reader[0].ToString();
}
conn.Close();
return id_jenis;
}
private void getDistribusi()
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
query = "SELECT nomor_surat_masuk, detail_bagian_bidang_surat_masuk.id_bagian_bidang, bagian_bidang.nama_bagian_bidang AS nama FROM detail_bagian_bidang_surat_masuk JOIN bagian_bidang USING(id_bagian_bidang) " +
"WHERE detail_bagian_bidang_surat_masuk.nomor_surat_masuk = @nomor_surat";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nomor_surat", nomor_surat);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader["nama"].ToString().Equals("Tata Usaha"))
{
checkBoxTataUsaha.Checked = true;
}
if (reader["nama"].ToString().Equals("Programa Siaran"))
{
checkBoxProgramaSiaran.Checked = true;
}
if (reader["nama"].ToString().Equals("Pemberitaan"))
{
checkBoxPemberitaan.Checked = true;
}
if (reader["nama"].ToString().Equals("Teknologi dan Media Baru"))
{
checkBoxTeknologi.Checked = true;
}
if (reader["nama"].ToString().Equals("Layanan dan Pengembangan"))
{
checkBoxLayanan.Checked = true;
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
public void getAllRekapDisposisi()
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
query = "SELECT MONTH(tanggal_terima) as BULAN, count(nomor_surat) from surat_disposisi WHERE YEAR(tanggal_terima)=" + tahun + " GROUP BY BULAN ASC";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader reader = cmd.ExecuteReader();
setDataTable(reader);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
public FormEditUser(string id, string name, string u_name, string psswrd, FormUser frm)
{
InitializeComponent();
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "SELECT password FROM user WHERE username = @username";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@username", u_name);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
password = reader["password"].ToString();
//MessageBox.Show(password);
conn.Close();
id_user = id;
username = u_name;
txtBoxUserEdit.Text = u_name;
nama = name;
txtBoxNamaLengkapUser.Text = name;
frm2 = frm;
}
private void buttonEditSubBidang_Click(object sender, EventArgs e)
{
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "UPDATE sub_bagian_bidang SET nama_sub_bagian_bidang = @nama_sub_bagian_bidang WHERE id_sub_bagian_bidang = @id_sub_bagian_bidang";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@id_sub_bagian_bidang", id_sub_bagian_bidang);
cmd.Parameters.AddWithValue("@nama_sub_bagian_bidang", textBoxEditSubBidang.Text);
//MessageBox.Show(query);
int sukses = cmd.ExecuteNonQuery();
if (sukses > 0)
{
MessageBox.Show("Data berhasil diupdate", "Sukses");
frm1.getAllSubBidang();
}
else
{
MessageBox.Show("Data gagal diupdate", "Gagal");
}
conn.Close();
}
private void ButtonUserEdit_Click(object sender, EventArgs e)
{
if (cekValid())
return;
else
{
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
string query = "UPDATE user SET username = @username, password = @password, nama = @nama WHERE id_user = @id_user";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@id_user", id_user);
cmd.Parameters.AddWithValue("@username", txtBoxUserEdit.Text);
cmd.Parameters.AddWithValue("@password", textBoxPasswordBaru.Text);
cmd.Parameters.AddWithValue("@nama", txtBoxNamaLengkapUser.Text);
//MessageBox.Show(query);
int sukses = cmd.ExecuteNonQuery();
if (sukses > 0)
{
MessageBox.Show("Data berhasil diupdate", "Sukses");
frm2.getAllUser();
}
else
{
MessageBox.Show("Data gagal diupdate", "Gagal");
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
}
private void cariJenisSurat()
{
JenisSurat jenis = new JenisSurat();
string cari = textBoxCari.Text;
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "SELECT * FROM jenis_surat WHERE nama_jenis LIKE '%"+cari+"%'";
//MessageBox.Show(query);
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@cari", cari);
MySqlDataReader reader = cmd.ExecuteReader();
setDataTable(reader);
conn.Close();
}
private void hapusDisposisiBidang(string nomor_surat)
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
query = "DELETE FROM disposisi_bagian WHERE nomor_surat = @nomor_surat";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nomor_surat", nomor_surat);
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
private void hapusSuratDisposisi(string nomor_surat)
{
DialogResult result = MessageBox.Show("Apakah Anda yakin ingin menghapus data?", "Konfirmasi Penghapusan Data", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == System.Windows.Forms.DialogResult.Yes)
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
hapusDisposisiBidang(nomor_surat);
query = "DELETE FROM surat_disposisi WHERE nomor_surat = @nomor_surat";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nomor_surat", nomor_surat);
cmd.ExecuteNonQuery();
MessageBox.Show("Data berhasil dihapus", "Sukses", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
}
private void cekLogin()
{
string username = textBoxUsername.Text;
string password = textBoxPassword.Text;
user u = new user();
u.setUsername(username);
u.setPassword(password);
username = u.getUsername();
password = u.getPassword();
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
try
{
conn.Open();
string query = "SELECT * FROM user WHERE username = @username AND password = @password";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
MySqlDataReader reader = cmd.ExecuteReader();
if (!reader.Read())
{
MessageBox.Show("Username atau Password salah. Silahkan ulangi kembali.", "Kesalahan");
textBoxUsername.Clear();
textBoxPassword.Clear();
textBoxUsername.Focus();
}
else
{
id_user = reader[0].ToString();
this.Hide();
FormMain main = new FormMain();
main.Show();
}
conn.Close();
}
catch (MySqlException)
{
MessageBox.Show("Mencari Database, Harap tunggu...", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information);
Database dbc = new Database();
string strconnect = db.getString2();
MySqlConnection connect = new MySqlConnection(strconnect);
connect.Open();
MySqlCommand cmd = new MySqlCommand("create database if not exists db_surat", connect);
MySqlCommand cmd2 = new MySqlCommand("use db_surat", connect);
MySqlCommand cmd1 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `user` (`id_user` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(19) NOT NULL,`password` varchar(10) NOT NULL,`nama` varchar(40) NOT NULL,PRIMARY KEY (`id_user`),UNIQUE KEY `username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd3 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `bagian_bidang` (`id_bagian_bidang` int(11) NOT NULL AUTO_INCREMENT,`nama_bagian_bidang` varchar(50) DEFAULT NULL, PRIMARY KEY (`id_bagian_bidang`)) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd4 = new MySqlCommand("insert into `user`(`id_user`,`username`,`password`,`nama`) values (NULL,'admin','admin','admin')", connect);
MySqlCommand cmd5 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `bagian_bidang` (`id_bagian_bidang` int(11) NOT NULL AUTO_INCREMENT,`nama_bagian_bidang` varchar(30) DEFAULT NULL,PRIMARY KEY (`id_bagian_bidang`)) ENGINE=InnoDB", connect);
MySqlCommand cmd6 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `jenis_surat` (`id_jenis` int(11) NOT NULL AUTO_INCREMENT,`nama_jenis` varchar(20) DEFAULT NULL,PRIMARY KEY (`id_jenis`)) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
//MySqlCommand cmd7 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `lampiran` (`id_lampiran` int(11) NOT NULL AUTO_INCREMENT,`nama_lampiran` varchar(40) DEFAULT NULL,PRIMARY KEY (`id_lampiran`)) ENGINE=InnoDB", connect);
MySqlCommand cmd8 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `surat_keluar` (`nomor_surat_keluar` varchar(60) NOT NULL,`perihal` varchar(100) DEFAULT NULL,`tanggal_surat` date DEFAULT NULL,`id_jenis` int(11) DEFAULT NULL,`sifat_surat` varchar(10) DEFAULT NULL, `penerima` varchar(50) DEFAULT NULL,`jumlah_lampiran` int(11) DEFAULT '0',`jabatan_tertanda` varchar(50) DEFAULT NULL,`tertanda` varchar(50) DEFAULT NULL,`keterangan` varchar(100) DEFAULT NULL,`distribusi_tanggal` date DEFAULT NULL,`isi_singkat` varchar(300) DEFAULT NULL,`gambar_surat` varchar(200) DEFAULT NULL,`id_user` int(11) DEFAULT NULL,`tanggal_update` datetime DEFAULT NULL, PRIMARY KEY (`nomor_surat_keluar`),KEY `surat_keluar_ibfk_1` (`id_jenis`),KEY `surat_keluar_ibfk_2` (`id_user`),CONSTRAINT `surat_keluar_ibfk_1` FOREIGN KEY (`id_jenis`) REFERENCES `jenis_surat` (`id_jenis`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `surat_keluar_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd9 = new MySqlCommand("CREATE TABLE `surat_masuk` ( `nomor_surat_masuk` varchar(60) NOT NULL,`perihal` varchar(100) DEFAULT NULL, `tanggal_surat` date DEFAULT NULL,`tanggal_terima` date DEFAULT NULL,`id_jenis` int(11) DEFAULT NULL,`sifat_surat` varchar(10) DEFAULT NULL, `pengirim` varchar(50) DEFAULT NULL,`alamat_pengirim` varchar(200) DEFAULT NULL,`penerima` varchar(50) DEFAULT NULL, `jumlah_lampiran` int(11) DEFAULT '0',`jabatan_tertanda` varchar(50) DEFAULT NULL,`tertanda` varchar(50) DEFAULT NULL,`distribusi_tanggal` date DEFAULT NULL,`isi_singkat` varchar(300) DEFAULT NULL,`keterangan` varchar(100) DEFAULT NULL, `gambar_surat` varchar(200) DEFAULT NULL,`id_user` int(11) DEFAULT NULL,`tanggal_update` date DEFAULT NULL,PRIMARY KEY (`nomor_surat_masuk`),KEY `surat_masuk_ibfk_1` (`id_jenis`),KEY `id_user` (`id_user`), CONSTRAINT `surat_masuk_ibfk_1` FOREIGN KEY (`id_jenis`) REFERENCES `jenis_surat` (`id_jenis`) ON DELETE SET NULL ON UPDATE CASCADE,CONSTRAINT `surat_masuk_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd10 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `surat_disposisi` (`nomor_surat` varchar(60) NOT NULL, `nomor_agenda` varchar(60) DEFAULT NULL, `tanggal_surat` date DEFAULT NULL, `tanggal_terima` date DEFAULT NULL, `tanggal_diteruskan` date DEFAULT NULL, `asal` varchar(40) DEFAULT NULL, `sifat` varchar(20) DEFAULT NULL , `perihal` varchar(100) DEFAULT NULL, `perintah` varchar(300) DEFAULT NULL, `disposisi_lain` varchar(300) DEFAULT NULL, `diproses_diselesaikan` char(2) DEFAULT 'T', `dilaksanakan` char(2) DEFAULT 'T', `ditanggapi` char(2) DEFAULT 'T', `diperhatikan` char(2) DEFAULT 'T', `dilaporkan` char(2) DEFAULT 'T', `diketahui` char(2) DEFAULT 'T', `diedarkan` char(2) DEFAULT 'T', `diperbanyak_dicopy` char(2) DEFAULT 'T', `ditampung` char(2) DEFAULT 'T', `dihadiri_diwakilkan` char(2) DEFAULT 'T', `dikonsepkan_jawaban` char(2) DEFAULT 'T', `dievaluasi_diteliti` char(2) DEFAULT 'T', `saran_pendapat` char(2) DEFAULT 'T', `disimpan_file_arsip` char(2) DEFAULT 'T', `gambar_surat` varchar(200) DEFAULT 'no_image.png', `id_user` int(11) DEFAULT NULL, `tanggal_update` datetime DEFAULT NULL, PRIMARY KEY (`nomor_surat`), UNIQUE KEY `nomor_agenda` (`nomor_agenda`), KEY `id_user` (`id_user`), CONSTRAINT `surat_disposisi_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
//MySqlCommand cmd11 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `sub_bagian_bidang` (`id_sub_bagian_bidang` int(11) NOT NULL AUTO_INCREMENT,`id_bagian_bidang` int(11) DEFAULT NULL,`nama_sub_bagian_bidang` varchar(30) DEFAULT NULL,PRIMARY KEY (`id_sub_bagian_bidang`),KEY `id_bagian_bidang` (`id_bagian_bidang`),CONSTRAINT `sub_bagian_bidang_ibfk_1` FOREIGN KEY (`id_bagian_bidang`) REFERENCES `bagian_bidang` (`id_bagian_bidang`) ON DELETE SET NULL ON UPDATE SET NULL) ENGINE=InnoDB", connect);
MySqlCommand cmd12 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `detail_bagian_bidang_surat_keluar` ( `nomor_surat_keluar` varchar(40) DEFAULT NULL, `id_bagian_bidang` int(11) DEFAULT NULL, KEY `nomor_surat_keluar` (`nomor_surat_keluar`), KEY `id_sub_bagian_bidang` (`id_bagian_bidang`), CONSTRAINT `detail_bagian_bidang_surat_keluar_ibfk_1` FOREIGN KEY (`nomor_surat_keluar`) REFERENCES `surat_keluar` (`nomor_surat_keluar`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `detail_bagian_bidang_surat_keluar_ibfk_2` FOREIGN KEY (`id_bagian_bidang`) REFERENCES `bagian_bidang` (`id_bagian_bidang`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd13 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `detail_bagian_bidang_surat_masuk` ( `nomor_surat_masuk` varchar(40) DEFAULT NULL, `id_bagian_bidang` int(11) DEFAULT NULL, KEY `detail_bagian_bidang_surat_masuk_ibfk_1` (`nomor_surat_masuk`), KEY `detail_bagian_bidang_surat_masuk_ibfk_2` (`id_bagian_bidang`), CONSTRAINT `detail_bagian_bidang_surat_masuk_ibfk_1` FOREIGN KEY (`nomor_surat_masuk`) REFERENCES `surat_masuk` (`nomor_surat_masuk`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `detail_bagian_bidang_surat_masuk_ibfk_2` FOREIGN KEY (`id_bagian_bidang`) REFERENCES `bagian_bidang` (`id_bagian_bidang`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd14 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `disposisi_bagian` ( `nomor_surat` varchar(40) DEFAULT NULL, `id_bagian_bidang` int(11) DEFAULT NULL, KEY `nomor_surat` (`nomor_surat`), KEY `id_bagian` (`id_bagian_bidang`), CONSTRAINT `disposisi_bagian_ibfk_1` FOREIGN KEY (`nomor_surat`) REFERENCES `surat_disposisi` (`nomor_surat`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `disposisi_bagian_ibfk_2` FOREIGN KEY (`id_bagian_bidang`) REFERENCES `bagian_bidang` (`id_bagian_bidang`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd15 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `lampiran_surat_keluar` ( `id_lampiran` int(11) NOT NULL AUTO_INCREMENT, `nama_lampiran` varchar(40) DEFAULT NULL, `nomor_surat_keluar` varchar(40) DEFAULT NULL, PRIMARY KEY (`id_lampiran`), KEY `lampiran_surat_keluar_ibfk_1` (`nomor_surat_keluar`), CONSTRAINT `lampiran_surat_keluar_ibfk_1` FOREIGN KEY (`nomor_surat_keluar`) REFERENCES `surat_keluar` (`nomor_surat_keluar`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd16 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `lampiran_surat_masuk` ( `id_lampiran` int(11) NOT NULL AUTO_INCREMENT, `nama_lampiran` varchar(40) DEFAULT NULL, `nomor_surat_masuk` varchar(40) DEFAULT NULL, PRIMARY KEY (`id_lampiran`), KEY `lampiran_surat_masuk_ibfk_1` (`nomor_surat_masuk`), CONSTRAINT `lampiran_surat_masuk_ibfk_1` FOREIGN KEY (`nomor_surat_masuk`) REFERENCES `surat_masuk` (`nomor_surat_masuk`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd17 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `tembusan_surat_keluar` ( `id_tembusan` int(11) DEFAULT NULL, `nama_tembusan` varchar(40) DEFAULT NULL, `nomor_surat_keluar` varchar(40) DEFAULT NULL, KEY `tembusan_surat_keluar_ibfk_1` (`nomor_surat_keluar`), CONSTRAINT `tembusan_surat_keluar_ibfk_1` FOREIGN KEY (`nomor_surat_keluar`) REFERENCES `surat_keluar` (`nomor_surat_keluar`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd18 = new MySqlCommand("CREATE TABLE IF NOT EXISTS `tembusan_surat_masuk` (`id_tembusan` int(11) NOT NULL AUTO_INCREMENT, `nama_tembusan` varchar(40) DEFAULT NULL, `nomor_surat_masuk` varchar(40) DEFAULT NULL, PRIMARY KEY (`id_tembusan`), KEY `tembusan_surat_masuk_ibfk_1` (`nomor_surat_masuk`), CONSTRAINT `tembusan_surat_masuk_ibfk_1` FOREIGN KEY (`nomor_surat_masuk`) REFERENCES `surat_masuk` (`nomor_surat_masuk`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1", connect);
MySqlCommand cmd19 = new MySqlCommand("insert into `jenis_surat`(`id_jenis`,`nama_jenis`) values (1,'Surat Dinas'),(2,'Nota Dinas'),(3,'Surat Undangan'),(4,'Lain-Lain')", connect);
MySqlCommand cmd20 = new MySqlCommand("insert into `bagian_bidang`(`id_bagian_bidang`,`nama_bagian_bidang`) values (1,'Tata Usaha'),(2,'Programa Siaran'),(3,'Pemberitaan'),(4,'Teknologi dan Media Baru'),(5,'Layanan dan Pengembangan')", connect);
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
cmd3.ExecuteNonQuery();
cmd4.ExecuteNonQuery();
cmd5.ExecuteNonQuery();
cmd6.ExecuteNonQuery();
//cmd7.ExecuteNonQuery();
cmd8.ExecuteNonQuery();
cmd9.ExecuteNonQuery();
cmd10.ExecuteNonQuery();
//cmd11.ExecuteNonQuery();
cmd12.ExecuteNonQuery();
cmd13.ExecuteNonQuery();
cmd14.ExecuteNonQuery();
cmd15.ExecuteNonQuery();
cmd16.ExecuteNonQuery();
cmd17.ExecuteNonQuery();
cmd18.ExecuteNonQuery();
cmd19.ExecuteNonQuery();
cmd20.ExecuteNonQuery();
//cmd21.ExecuteNonQuery();
connect.Close();
cekLogin();
}
}
private void getSifatSurat(string nomor_surat)
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
query = "SELECT sifat_surat FROM surat_masuk WHERE nomor_surat_masuk = @nomor_surat";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nomor_surat", nomor_surat);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
textBoxSifatSurat.Text = reader["sifat_surat"].ToString();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
private void getJenisSurat(string nomor_surat)
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
query = "SELECT nama_jenis FROM jenis_surat JOIN surat_masuk USING (id_jenis) WHERE nomor_surat_masuk = @nomor_surat";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nomor_surat", nomor_surat);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
jenis_surat = reader["nama_jenis"].ToString();
}
textBoxJenisSurat.Text = jenis_surat;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
public void tambahSuratMasuk()
{
string lokasi_tujuan;
nomor_surat = textBoxNomorSuratMasuk.Text;
tgl_surat = dateTimeInputTanggalSuratMasuk.Value.Date.ToString("dd-MM-yyyy");
tgl_terima = dateTimeInputTanggalTerimaSuratMasuk.Value.Date.ToString("dd-MM-yyyy");
jenis_surat = comboBoxJenisSuratMasuk.Text;
sifat_surat = comboBoxSifatSuratMasuk.Text;
perihal_surat = textBoxPerihalSuratMasuk.Text;
keterangan_surat = textBoxKeteranganSuratMasuk.Text;
isi_surat = textBoxIsiSuratMasuk.Text;
pengirim = textBoxInstansiPengirimSuratMasuk.Text;
alamat_pengirim = textBoxAlamatPengirimSuratMasuk.Text;
penerima = textBoxPenerimaSuratMasuk.Text;
jabatan_tertanda = textBoxJabatanTertandaSuratMasuk.Text;
tertanda = textBoxTertandaPengirimSuratMasuk.Text;
distribusi_tanggal = dateTimeInputTanggalDistribusiSuratMasuk.Value.Date.ToString("dd-MM-yyyy");
lokasi_tujuan = Application.StartupPath + "\\image_surat_masuk";
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
using (conn)
{
conn.Open();
if (!Directory.Exists(lokasi_tujuan))
{
Directory.CreateDirectory(lokasi_tujuan);
}
if (pictureBoxGambarSuratMasuk.Image != null)
{
File.Copy(lokasi_gambar, lokasi_tujuan + "\\" + nama_gambar, true);
}
else
{
nama_gambar = "no_image.png";
if(!File.Exists(lokasi_tujuan + "\\no_image.png"))
File.Copy(Application.StartupPath + "\\no_image.png", lokasi_tujuan + "\\no_image.png", true);
}
try
{
query = "INSERT INTO surat_masuk(nomor_surat_masuk, perihal, tanggal_surat, tanggal_terima, id_jenis, " +
"sifat_surat, pengirim, alamat_pengirim, penerima, jabatan_tertanda, tertanda, " +
"distribusi_tanggal, isi_singkat, keterangan, gambar_surat, id_user, tanggal_update) " +
"VALUES(@nomor_surat, @perihal_surat, STR_TO_DATE(@tanggal_surat, '%d-%m-%Y'), " +
"STR_TO_DATE(@tanggal_terima, '%d-%m-%Y'), @id_jenis, " +
"@sifat_surat, @pengirim, @alamat_pengirim, @penerima, @jabatan_tertanda, @tertanda, " +
"STR_TO_DATE(@distribusi_tanggal, '%d-%m-%Y'), @isi_singkat, @keterangan, @gambar_surat, @id_user, NOW())";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nomor_surat", nomor_surat);
cmd.Parameters.AddWithValue("@sifat_surat", sifat_surat);
cmd.Parameters.AddWithValue("@id_jenis", getIdJenisSurat(jenis_surat));
cmd.Parameters.AddWithValue("@gambar_surat", nama_gambar);
cmd.Parameters.AddWithValue("@perihal_surat", perihal_surat);
cmd.Parameters.AddWithValue("@tanggal_surat", tgl_surat);
cmd.Parameters.AddWithValue("@tanggal_terima", tgl_terima);
cmd.Parameters.AddWithValue("@pengirim", pengirim);
cmd.Parameters.AddWithValue("@alamat_pengirim", alamat_pengirim);
cmd.Parameters.AddWithValue("@penerima", penerima);
cmd.Parameters.AddWithValue("@jabatan_tertanda", jabatan_tertanda);
cmd.Parameters.AddWithValue("@tertanda", tertanda);
cmd.Parameters.AddWithValue("@distribusi_tanggal", distribusi_tanggal);
cmd.Parameters.AddWithValue("@isi_singkat", isi_surat);
cmd.Parameters.AddWithValue("@keterangan", keterangan_surat);
cmd.Parameters.AddWithValue("@id_user", FormMain.id_user);
cmd.ExecuteNonQuery();
MessageBox.Show("Data berhasil ditambah", "Sukses", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (MySqlException ex)
{
if (ex.Number == 1062)
{
MessageBox.Show("Nomor surat yang dimaksud telah ada dalam database. Silahkan ubah nomor surat yang ingin dimasukkan.", "Duplicate Entry", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show(ex.ToString());
}
}
conn.Close();
}
}
private void tambahBagianBidang()
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
foreach (var kabag in list_bagian)
{
query = "INSERT INTO detail_bagian_bidang_surat_masuk VALUES(@nomor_surat, (SELECT id_bagian_bidang FROM bagian_bidang WHERE nama_bagian_bidang = @bagian))";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nomor_surat", nomor_surat);
cmd.Parameters.AddWithValue("@bagian", kabag);
cmd.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
private void cariBidang()
{
BidangBagian namaBidang = new BidangBagian();
string cari = textBoxCariBidang.Text;
Database db = new Database();
string strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
string query = "SELECT * FROM bagian_bidang WHERE nama_bagian_bidang LIKE '%" + cari + "%'";
//MessageBox.Show(query);
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@cari", cari);
MySqlDataReader reader = cmd.ExecuteReader();
setDataTable(reader);
conn.Close();
}
private void tambahTembusan()
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
foreach (string tembusan in FormSuratMasukTembusan.list_tembusan)
{
query = "INSERT INTO tembusan_surat_masuk VALUES(NULL, @tembusan, @nomor_surat_masuk)";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@tembusan", tembusan);
cmd.Parameters.AddWithValue("@nomor_surat_masuk", nomor_surat);
cmd.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}
private void getSuratMasuk()
{
Database db = new Database();
strconn = db.getString();
MySqlConnection conn = new MySqlConnection(strconn);
conn.Open();
try
{
query = "SELECT * FROM surat_masuk WHERE nomor_surat_masuk = @nomor_surat";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@nomor_surat", nomor_surat);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
textBoxNomorSuratMasuk.Text = nomor_surat;
textBoxPerihalSuratMasuk.Text = reader["perihal"].ToString();
if (reader["tanggal_terima"].ToString().Equals("1/1/0001"))
textBoxTanggalTerima.Text = "";
else
textBoxTanggalTerima.Text = reader["tanggal_terima"].ToString();
if (reader["tanggal_surat"].ToString().Equals("1/1/0001"))
textBoxTanggalSurat.Text = "";
else
textBoxTanggalSurat.Text = reader["tanggal_surat"].ToString();
textBoxInstansiPengirimSuratMasuk.Text = reader["pengirim"].ToString();
textBoxAlamatPengirimSuratMasuk.Text = reader["alamat_pengirim"].ToString();
textBoxPenerimaSuratMasuk.Text = reader["penerima"].ToString();
textBoxJabatanTertandaSuratMasuk.Text = reader["jabatan_tertanda"].ToString();
textBoxTertandaPengirimSuratMasuk.Text = reader["tertanda"].ToString();
if (reader["distribusi_tanggal"].ToString().Equals("1/1/0001"))
textBoxTanggalDistribusi.Text = "";
else
textBoxTanggalDistribusi.Text = reader["distribusi_tanggal"].ToString();
textBoxKeteranganSuratMasuk.Text = reader["keterangan"].ToString();
textBoxIsiSuratMasuk.Text = reader["isi_singkat"].ToString();
pictureBoxGambarSuratMasuk.Image = new Bitmap(Application.StartupPath + "\\image_surat_masuk\\" + reader["gambar_surat"].ToString());
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}
conn.Close();
}