public bool CreateTablePerangkat()
{
using (SQLiteConnection cc = new SQLiteConnection(cu.getConnection()))
{
try
{
cc.Open();
cmd = new SQLiteCommand();
string query = @"CREATE TABLE [perangkat](
[nama_perangkat] TEXT(50) PRIMARY KEY NOT NULL,
[nip] INTEGER DEFAULT NULL,
[jabatan] TEXT(100) NOT NULL)";
cmd.CommandText = query;
cmd.Connection = cc;
cmd.ExecuteNonQuery();
dBTablePerangkat = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
dBTablePerangkat = false;
}
return(dBTablePerangkat);
}
}
private void loadComboboxItem()
{
ConnectionUtils c = new ConnectionUtils();
using (SQLiteConnection cc = new SQLiteConnection(c.getConnection()))
{
try
{
cc.Open();
cmd = new SQLiteCommand();
string query = @"select nama_perangkat from perangkat";
cmd.CommandText = query;
cmd.Connection = cc;
SQLiteDataReader r = cmd.ExecuteReader();
while (r.Read())
{
cmb_namaPPK.Items.Add(r["nama_perangkat"].ToString());
cmb_namaBend.Items.Add(r["nama_perangkat"].ToString());
}
cc.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
private void cmb_namaBend_DropDownClosed(object sender, EventArgs e)
{
ConnectionUtils c = new ConnectionUtils();
using (SQLiteConnection cc = new SQLiteConnection(c.getConnection()))
{
if (cmb_namaBend.Text.ToString() != null)
{
try
{
cc.Open();
cmd = new SQLiteCommand();
string query = @"select * from perangkat where nama_perangkat = '" + cmb_namaBend.Text.ToString() + "'";
cmd.CommandText = query;
cmd.Connection = cc;
SQLiteDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string nip = r.GetInt32(1).ToString();
string jabatan = r.GetString(2);
txt_nip_bend.Text = nip;
txt_jabatan_bend.Text = jabatan;
}
cc.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
private void cmb_pengikut2_DropDownClosed(object sender, EventArgs e)
{
c = new ConnectionUtils();
using (SQLiteConnection cc = new SQLiteConnection(c.getConnection()))
{
string aval = cmb_namaPerangkat.Text;
string bval = cmb_pengikut1.Text;
if (cmb_namaPerangkat.Text != null)
{
if (aval != string.Empty)
{
if (cmb_pengikut2.Text != aval)
{
if (bval != string.Empty &&
cmb_pengikut2.Text != bval)
{
try
{
cc.Open();
cmd = new SQLiteCommand();
string query = @"select * from perangkat where nama_perangkat = '" + cmb_pengikut2.Text + "'";
cmd.CommandText = query;
cmd.Connection = cc;
SQLiteDataReader r = cmd.ExecuteReader();
while (r.Read())
{
string jabatan = r.GetString(2);
txt_jabatan_pengikut2.Text = jabatan;
}
cc.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
else
{
MessageBox.Show("Maaf Nama Pengikut pertama tidak boleh kosong atau sama dengan Nama Pengikut kedua bosku!", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
cmb_pengikut2.SelectedIndex = -1;
}
}
else
{
MessageBox.Show("Maaf Nama Pengikut tidak boleh sama dengan nama perangkat yang diutus bosku!", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
cmb_pengikut2.SelectedIndex = -1;
}
}
else
{
MessageBox.Show("Harap Masukkan Nama Perangkat yang akan melakukan perjalanan dulu bosku!", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
cmb_pengikut2.SelectedIndex = -1;
}
}
}
}
public bool Login(string nama_pengguna, string kata_sandi)
{
c = new ConnectionUtils();
using (SQLiteConnection cc = new SQLiteConnection(c.getConnection()))
{
try
{
try
{
cc.Open();
}
catch (Exception ex)
{
throw ex;
}
finally
{
SQLiteCommand cmd = new SQLiteCommand();
string query = "SELECT nama_pengguna, kata_sandi FROM pengguna WHERE nama_pengguna = '" + nama_pengguna + "' AND kata_sandi = '" + kata_sandi + "';";
cmd.CommandText = query;
cmd.Connection = cc;
try
{
cmd.ExecuteNonQuery();
SQLiteDataReader r = cmd.ExecuteReader();
if (r.Read())
{
if ((r["nama_pengguna"].ToString().Equals(nama_pengguna.ToString(), StringComparison.InvariantCulture)) && (r["kata_sandi"].ToString().Equals(kata_sandi.ToString(), StringComparison.InvariantCulture)))
{
loginSuccess = true;
}
r.Close();
r.Dispose();
cmd.Dispose();
cc.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
loginSuccess = false;
}
return(loginSuccess);
}
}
public bool createTableLogin(string nama_pengguna, string kata_sandi)
{
c = new ConnectionUtils();
using (SQLiteConnection cc = new SQLiteConnection(c.getConnection()))
{
try
{
try
{
cc.Open();
}
catch (Exception ex)
{
throw ex;
}
finally
{
using (cmd = new SQLiteCommand())
{
string query = @"INSERT INTO pengguna(nama_pengguna, kata_sandi) VALUES(@nama_pengguna, @kata_sandi)";
cmd.Parameters.Add(new SQLiteParameter("@nama_pengguna", nama_pengguna));
cmd.Parameters.Add(new SQLiteParameter("@kata_sandi", kata_sandi));
cmd.CommandText = query;
cmd.Connection = cc;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
tableCreated = true;
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
tableCreated = false;
}
}
return(tableCreated);
}
public bool CreateTableSPPD()
{
using (SQLiteConnection cc = new SQLiteConnection(cu.getConnection()))
{
try
{
cc.Open();
string query = @"CREATE TABLE
[sppd](
[no_agenda] TEXT(50) PRIMARY KEY NOT NULL,
[dasar_surat] TEXT(100) DEFAULT NULL,
[tanggal_surat] TEXT(100) DEFAULT NULL,
[nama_perangkat] TEXT(150) DEFAULT NULL,
[nip] INTEGER DEFAULT NULL,
[jabatan] TEXT(100) DEFAULT NULL,
[maksud_sppd] TEXT(150) DEFAULT NULL,
[alat_transportasi] TEXT(150) DEFAULT NULL,
[tempat_berangkat] TEXT(100) DEFAULT NULL,
[tempat_tujuan] TEXT(150) DEFAULT NULL,
[tanggal_berangkat] TEXT(150) DEFAULT NULL,
[tanggal_kembali] TEXT(150) DEFAULT NULL,
[lama_keberangkatan] TEXT(50) DEFAULT NULL,
[pengikut1] TEXT(100) DEFAULT NULL,
[jabatan_pengikut1] TEXT(150) DEFAULT NULL,
[pengikut2] TEXT(100) DEFAULT NULL,
[jabatan_pengikut2] TEXT(150) DEFAULT NULL,
[sumber_anggaran] TEXT(150) DEFAULT NULL,
[keterangan] TEXT(500) DEFAULT NULL)";
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = query;
cmd.Connection = cc;
cmd.ExecuteNonQuery();
dBTableSPPD = true;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
dBTableSPPD = false;
}
return(dBTableSPPD);
}
}
private void loadTablePerangkat()
{
c = new ConnectionUtils();
using (SQLiteConnection cc = new SQLiteConnection(c.getConnection()))
{
try
{
cc.Open();
cmd = cc.CreateCommand();
cmd.CommandText = @"SELECT nama_perangkat,nip,jabatan FROM perangkat ORDER BY nama_perangkat";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd.CommandText, cc))
{
DataTable ddt = new DataTable();
da.Fill(ddt);
dataGrid.ItemsSource = ddt.AsDataView();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Bosku!", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
private void loadTableSPPD()
{
c = new ConnectionUtils();
using (SQLiteConnection cc = new SQLiteConnection(c.getConnection()))
{
try
{
cc.Open();
cmd = cc.CreateCommand();
cmd.CommandText = @"SELECT no_agenda, dasar_surat, tanggal_surat, nama_perangkat, nip, jabatan, maksud_sppd, alat_transportasi, tempat_berangkat, tempat_tujuan, tanggal_berangkat, tanggal_kembali, lama_keberangkatan, pengikut1, jabatan_pengikut1, pengikut2, jabatan_pengikut2, sumber_anggaran, keterangan FROM sppd ORDER BY no_agenda";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd.CommandText, cc))
{
DataTable ddta = new DataTable();
da.Fill(ddta);
dataGridSppd.ItemsSource = ddta.AsDataView();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Bosku!", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
private void btn_generate_kades_Click(object sender, RoutedEventArgs e)
{
if (!generate)
{
generate = true;
Properties.Settings.Default.ttd_nama_kades = "";
Properties.Settings.Default.ttd_nip_kades = "";
Properties.Settings.Default.ttd_jabatan_kades = "";
Properties.Settings.Default.ttd_customHeader_kades = "";
Properties.Settings.Default.Save();
using (SQLiteConnection cc1 = new SQLiteConnection(cu.getConnection()))
{
try
{
cc1.Open();
cmd = new SQLiteCommand();
string query1 = @"SELECT nama_perangkat FROM perangkat WHERE jabatan = 'Kepala Desa'";
cmd.CommandText = query1;
cmd.Connection = cc1;
SQLiteDataReader r1 = cmd.ExecuteReader();
while (r1.Read())
{
string nk = r1[0].ToString();
txt_kades.Text = nk;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
using (SQLiteConnection cc2 = new SQLiteConnection(cu.getConnection()))
{
try
{
cc2.Open();
cmd = new SQLiteCommand();
string query2 = @"SELECT nip FROM perangkat WHERE jabatan = 'Kepala Desa'";
cmd.CommandText = query2;
cmd.Connection = cc2;
SQLiteDataReader r2 = cmd.ExecuteReader();
while (r2.Read())
{
string nipk = r2[0].ToString();
if (nipk != "0")
{
txt_nip_kades.Text = nipk;
}
else
{
txt_nip_kades.Text = nipk.Replace('0', '-');
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
using (SQLiteConnection cc3 = new SQLiteConnection(cu.getConnection()))
{
try
{
cc3.Open();
cmd = new SQLiteCommand();
string query3 = @"SELECT jabatan FROM perangkat WHERE jabatan = 'Kepala Desa'";
cmd.CommandText = query3;
cmd.Connection = cc3;
SQLiteDataReader r3 = cmd.ExecuteReader();
while (r3.Read())
{
string jk = r3[0].ToString();
txt_jabatan_kades.Text = jk;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
txt_customHeader_kades.IsEnabled = true;
generate_txt1.Content = "Simpan";
iconUbah1.Kind = MaterialDesignThemes.Wpf.PackIconKind.ContentSaveOutline;
}
else
{
generate = false;
txt_customHeader_kades.IsEnabled = false;
generate_txt1.Content = "Generate";
iconUbah1.Kind = MaterialDesignThemes.Wpf.PackIconKind.Buffer;
if (Properties.Settings.Default.ttd_nama_kades == string.Empty)
{
Properties.Settings.Default.ttd_nama_kades = txt_kades.Text.ToString();
Properties.Settings.Default.ttd_nip_kades = txt_nip_kades.Text.ToString();
Properties.Settings.Default.ttd_jabatan_kades = txt_jabatan_kades.Text.ToString();
Properties.Settings.Default.ttd_customHeader_kades = txt_customHeader_kades.Text.ToString();
Properties.Settings.Default.Save();
MessageBox.Show("Data Kepala Desa Sudah Berhasil Disimpan Bosku!", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
}
private void btn_new_Click(object sender, RoutedEventArgs e)
{
edit = false;
c = new ConnectionUtils();
sd = new sppdDAO();
var na = new StringBuilder();
try
{
na.Append(agenda.Text);
na.Append(txt_separator1.Text);
na.Append(txt_autoNumber.Text);
na.Append(txt_separator2.Text);
na.Append(txt_currentMonth.Text);
na.Append(txt_separator3.Text);
na.Append(txt_currentYear.Text);
string no_agenda = na.ToString();
try
{
if (txt_autoNumber.Text != string.Empty &&
txt_ds.Text != string.Empty &&
dp_ts.Text.ToString() != string.Empty &&
cmb_namaPerangkat.Text.ToString() != string.Empty &&
txt_maksud.Text != string.Empty &&
cmb_alat.Text.ToString() != string.Empty &&
txt_tempatBerangkat.Text != string.Empty &&
txt_tempatTujuan.Text != string.Empty &&
dp_tb.Text != string.Empty &&
dp_tk.Text != string.Empty &&
txt_sumberAnggaran.Text != string.Empty
)
{
sppdModel sm = new sppdModel();
try
{
sm.no_agenda = no_agenda.ToString();
sm.dasar_surat = txt_ds.Text.ToString();
sm.tanggal_surat = dp_ts.Text.ToString();
sm.nama_perangkat = cmb_namaPerangkat.Text.ToString();
sm.nip = IntegerExtensions.ParseInt(txt_nip.Text);
sm.jabatan = txt_jabatan.Text.ToString();
sm.maksud_sppd = txt_maksud.Text.ToString();
sm.alat_transportasi = cmb_alat.Text.ToString();
sm.tempat_berangkat = txt_tempatBerangkat.Text.ToString();
sm.tempat_tujuan = txt_tempatTujuan.Text.ToString();
sm.tanggal_berangkat = dp_tb.Text.ToString();
sm.tanggal_kembali = dp_tk.Text.ToString();
sm.lama_keberangkatan = txt_lk.Text.ToString();
sm.pengikut1 = cmb_pengikut1.Text.ToString();
sm.jabatan_pengikut1 = txt_jabatan_pengikut1.Text.ToString();
sm.pengikut2 = cmb_pengikut2.Text.ToString();
sm.jabatan_pengikut2 = txt_jabatan_pengikut2.Text.ToString();
sm.sumber_anggaran = txt_sumberAnggaran.Text.ToString();
sm.keterangan = txt_ket.Text.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
try
{
using (SQLiteConnection con = new SQLiteConnection(c.getConnection()))
{
try
{
con.Open();
}
catch (Exception ex)
{
throw ex;
}
using (SQLiteTransaction trn = con.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
sd.insertDataSPPD(sm, trn);
}
catch (Exception ex)
{
trn.Rollback();
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
emptyFields();
loadTableSPPD();
}
}
}
else
{
MessageBox.Show("Semua yang terdapat tanda (*) wajib diisi Bosku!", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}