public static List <Spd> getSpd()
{
List <Spd> spd = new List <Spd> ();
try {
using (SQLiteConnection conn = new SQLiteConnection(connectionString)) {
conn.Open();
string query = "SELECT * FROM v_data";
using (SQLiteCommand cmd = new SQLiteCommand(query, conn)) {
using (SQLiteDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
Spd p = new Spd();
p.id = Int32.Parse(reader["id"].ToString());
p.kode = reader["kode"].ToString();
p.p_nama = reader["p_nama"].ToString();
p.nip = reader["nip"].ToString();
p.t_tujuan = reader["t_tujuan"].ToString();
p.penjabat = reader["penjabat"].ToString();
p.t_jabatan = reader["t_jabatan"].ToString();
p.tgl_berangkat = reader["tgl_berangkat"].ToString();
spd.Add(p);
}
}
}
conn.Close();
}
} catch (SQLiteException e) {
throw;
}
return(spd);
}
public static List <Spd> getSpdById(int id)
{
List <Spd> spd = new List <Spd> ();
try {
using (SQLiteConnection conn = new SQLiteConnection(connectionString)) {
conn.Open();
string query = "SELECT * FROM v_data WHERE id=@id";
using (SQLiteCommand cmd = new SQLiteCommand(query, conn)) {
cmd.Prepare();
cmd.Parameters.AddWithValue("@id", id);
using (SQLiteDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
Spd p = new Spd();
p.id = Int32.Parse(reader["id"].ToString());
p.kode = reader["kode"].ToString();
p.pegawai_id = Int32.Parse(reader["pegawai_id"].ToString());
p.p_nama = reader["p_nama"].ToString();
p.nip = reader["nip"].ToString();
p.tb = reader["tb"].ToString();
p.maksud = reader["maksud"].ToString();
p.transport = reader["transport"].ToString();
p.trasport_id = Int32.Parse(reader["transport_id"].ToString());
p.t_berangkat = reader["t_berangkat"].ToString();
p.t_tujuan = reader["t_tujuan"].ToString();
p.t_tujuan_id = Int32.Parse(reader["t_tujuan_id"].ToString());
p.maksud = reader["maksud"].ToString();
p.penjabat = reader["penjabat"].ToString();
p.t_jabatan = reader["t_jabatan"].ToString();
p.lama = Int32.Parse(reader["lama"].ToString());
p.tgl_berangkat = reader["tgl_berangkat"].ToString();
p.tgl_kembali = reader["tgl_kembali"].ToString();
p.no_surat = reader["no_surat"].ToString();
p.tgl_tugas = reader["tgl_tugas"].ToString();
p.akun = reader["akun"].ToString();
spd.Add(p);
}
}
}
conn.Close();
}
} catch (SQLiteException e) {
throw;
}
return(spd);
}
public static List <Spd> getSpd(string cari)
{
List <Spd> spd = new List <Spd> ();
try {
using (SQLiteConnection conn = new SQLiteConnection(connectionString)) {
conn.Open();
string query = "SELECT * FROM v_data WHERE p_nama LIKE @nama OR nip LIKE @nip OR t_tujuan LIKE @tujuan";
using (SQLiteCommand cmd = new SQLiteCommand(query, conn)) {
cmd.Prepare();
cmd.Parameters.AddWithValue("@nip", "%" + cari + "%");
cmd.Parameters.AddWithValue("@nama", "%" + cari + "%");
cmd.Parameters.AddWithValue("@tujuan", "%" + cari + "%");
using (SQLiteDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
Spd p = new Spd();
p.id = Int32.Parse(reader["id"].ToString());
p.kode = reader["kode"].ToString();
p.p_nama = reader["p_nama"].ToString();
p.nip = reader["nip"].ToString();
p.t_tujuan = reader["t_tujuan"].ToString();
p.penjabat = reader["penjabat"].ToString();
p.t_jabatan = reader["t_jabatan"].ToString();
p.tgl_berangkat = reader["tgl_berangkat"].ToString();
spd.Add(p);
}
}
}
conn.Close();
}
} catch (SQLiteException e) {
throw;
}
return(spd);
}
public static int UpdateSPD(Spd s, List <Pengikut> pengikut, Biaya b)
{
int r = -1;
try {
using (SQLiteConnection conn = new SQLiteConnection(connectionString)) {
conn.Open();
using (SQLiteTransaction tr = conn.BeginTransaction()) {
using (SQLiteCommand cmd = new SQLiteCommand(conn)) {
cmd.Transaction = tr;
cmd.CommandText = "UPDATE data SET kode=@kode, pegawai=@pegawai, tb=@tb, maksud=@maksud, transport=@transport, t_berangkat=@t_berangkat, t_tujuan=@t_tujuan, penjabat=@penjabat, jabatan=@jabatan, lama=@lama, tgl_berangkat=@tgl_berangkat, tgl_kembali=@tgl_kembali, no_surat=@no_surat, tgl_tugas=@tgl_tugas, akun=@akun WHERE id=@id";
cmd.Prepare();
cmd.Parameters.AddWithValue("@id", s.id);
cmd.Parameters.AddWithValue("@kode", s.kode);
cmd.Parameters.AddWithValue("@pegawai", s.pegawai_id);
cmd.Parameters.AddWithValue("@tb", s.tb);
cmd.Parameters.AddWithValue("@maksud", s.maksud);
cmd.Parameters.AddWithValue("@transport", s.trasport_id);
cmd.Parameters.AddWithValue("@t_berangkat", s.t_berangkat_id);
cmd.Parameters.AddWithValue("@t_tujuan", s.t_tujuan_id);
cmd.Parameters.AddWithValue("@penjabat", s.penjabat);
cmd.Parameters.AddWithValue("@jabatan", s.t_jabatan);
cmd.Parameters.AddWithValue("@lama", s.lama);
cmd.Parameters.AddWithValue("@tgl_berangkat", s.tgl_berangkat);
cmd.Parameters.AddWithValue("@tgl_kembali", s.tgl_kembali);
cmd.Parameters.AddWithValue("@no_surat", s.no_surat);
cmd.Parameters.AddWithValue("@tgl_tugas", s.tgl_tugas);
cmd.Parameters.AddWithValue("@akun", s.akun);
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE biaya SET harian=@harian, h_lama=@h_lama, h_total=@h_total, penginapan=@penginapan, p_lama=@p_lama, p_total=@p_total, transport_pp=@transport_pp, transport_loak=@transport_loak, damri=@damri, lain=@lain WHERE id=@id";
cmd.Prepare();
cmd.Parameters.AddWithValue("@id", b.id);
cmd.Parameters.AddWithValue("@harian", b.harian);
cmd.Parameters.AddWithValue("@h_lama", b.h_lama);
cmd.Parameters.AddWithValue("@h_total", b.h_total);
cmd.Parameters.AddWithValue("@penginapan", b.penginapan);
cmd.Parameters.AddWithValue("@p_lama", b.p_lama);
cmd.Parameters.AddWithValue("@p_total", b.p_total);
cmd.Parameters.AddWithValue("@transport_pp", b.transport_pp);
cmd.Parameters.AddWithValue("@transport_loak", b.transport_loak);
cmd.Parameters.AddWithValue("@damri", b.damri);
cmd.Parameters.AddWithValue("@lain", b.lain_lain);
cmd.ExecuteNonQuery();
foreach (var p in pengikut)
{
cmd.CommandText = "INSERT INTO pengikut (pegawai, ket, data, tgl_lahir) VALUES (@pegawai, @ket, @data, @tgl_lahir)";
if (p.id != -1)
{
cmd.CommandText = "UPDATE pengikut SET pegawai=@pegawai, ket=@ket, data=@data, t_lahir=@t_lahir WHERE id=@id";
cmd.Parameters.AddWithValue("@id", p.id);
}
cmd.Parameters.AddWithValue("@pegawai", p.pegawai);
cmd.Parameters.AddWithValue("@ket", p.ket);
cmd.Parameters.AddWithValue("@data", s.id);
cmd.Parameters.AddWithValue("@t_lahir", p.t_lahir);
cmd.ExecuteNonQuery();
}
}
tr.Commit();
r = 1;
}
conn.Close();
}
} catch (SQLiteException e) {
throw;
}
return(r);
}
public static int InsertSPD(Spd s, List <Pengikut> pengikut, Biaya b)
{
int r = -1;
try {
using (SQLiteConnection conn = new SQLiteConnection(connectionString)) {
conn.Open();
using (SQLiteTransaction tr = conn.BeginTransaction()) {
using (SQLiteCommand cmd = new SQLiteCommand(conn)) {
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO data (kode, pegawai, tb, maksud, transport, t_berangkat, t_tujuan, penjabat, jabatan, lama, tgl_berangkat, tgl_kembali, no_surat, tgl_tugas, akun) VALUES (@kode,@pegawai,@tb,@maksud,@transport,@t_berangkat,@t_tujuan,@penjabat,@jabatan,@lama,@tgl_berangkat,@tgl_kembali,@no_surat,@tgl_tugas,@akun); SELECT last_insert_rowid()";
cmd.Prepare();
cmd.Parameters.AddWithValue("@kode", s.kode);
cmd.Parameters.AddWithValue("@pegawai", s.pegawai_id);
cmd.Parameters.AddWithValue("@tb", s.tb);
cmd.Parameters.AddWithValue("@maksud", s.maksud);
cmd.Parameters.AddWithValue("@transport", s.trasport_id);
cmd.Parameters.AddWithValue("@t_berangkat", s.t_berangkat_id);
cmd.Parameters.AddWithValue("@t_tujuan", s.t_tujuan_id);
cmd.Parameters.AddWithValue("@penjabat", s.penjabat);
cmd.Parameters.AddWithValue("@jabatan", s.t_jabatan);
cmd.Parameters.AddWithValue("@lama", s.lama);
cmd.Parameters.AddWithValue("@tgl_berangkat", s.tgl_berangkat);
cmd.Parameters.AddWithValue("@tgl_kembali", s.tgl_kembali);
cmd.Parameters.AddWithValue("@no_surat", s.no_surat);
cmd.Parameters.AddWithValue("@tgl_tugas", s.tgl_tugas);
cmd.Parameters.AddWithValue("@akun", s.akun);
int id = Int32.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "INSERT INTO biaya(harian, h_lama, h_total, penginapan, p_lama, p_total, transport_pp, transport_loak, damri, lain, data) VALUES (@harian,@h_lama,@h_total,@penginapan,@p_lama,@p_total,@transport_pp,@transport_loak,@damri,@lain,@data)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@harian", b.harian);
cmd.Parameters.AddWithValue("@h_lama", b.h_lama);
cmd.Parameters.AddWithValue("@h_total", b.h_total);
cmd.Parameters.AddWithValue("@penginapan", b.penginapan);
cmd.Parameters.AddWithValue("@p_lama", b.p_lama);
cmd.Parameters.AddWithValue("@p_total", b.p_total);
cmd.Parameters.AddWithValue("@transport_pp", b.transport_pp);
cmd.Parameters.AddWithValue("@transport_loak", b.transport_loak);
cmd.Parameters.AddWithValue("@damri", b.damri);
cmd.Parameters.AddWithValue("@lain", b.lain_lain);
cmd.Parameters.AddWithValue("@data", id);
cmd.ExecuteNonQuery();
foreach (var p in pengikut)
{
cmd.CommandText = "INSERT INTO pengikut (pegawai, ket, data, t_lahir) VALUES (@pegawai, @ket, @data, @t_lahir)";
cmd.Parameters.AddWithValue("@pegawai", p.pegawai);
cmd.Parameters.AddWithValue("@ket", p.ket);
cmd.Parameters.AddWithValue("@data", id);
cmd.Parameters.AddWithValue("@t_lahir", p.t_lahir);
cmd.ExecuteNonQuery();
}
}
tr.Commit();
r = 1;
}
conn.Close();
}
} catch (SQLiteException e) {
throw;
}
return(r);
}
private void btnSimpan_Click(object sender, EventArgs e)
{
Spd s = new Spd();
List <Pengikut> p = new List <Pengikut>();
Biaya b = new Biaya();
s.kode = txtKode.Text;
s.pegawai_id = ((KeyValuePair <int, string>)cmbPegawai.SelectedItem).Key;
s.tb = cmbTb.SelectedItem.ToString();
s.maksud = txtMaksud.Text;
s.trasport_id = ((KeyValuePair <int, string>)cmbTrasportasi.SelectedItem).Key;
s.t_berangkat_id = ((KeyValuePair <int, string>)cmbBerangkat.SelectedItem).Key;
s.t_tujuan_id = ((KeyValuePair <int, string>)cmbTujuan.SelectedItem).Key;
s.penjabat = txtPenjabat.Text;
s.t_jabatan = txtTJabatan.Text;
s.lama = Int32.Parse(txtLama.Text);
s.tgl_berangkat = dtTglBerangkat.Value.ToString("yyyy-MM-dd");
s.tgl_kembali = dtTglKembali.Value.ToString("yyyy-MM-dd");
s.no_surat = txtNoSuratTuga.Text;
s.tgl_tugas = dtTglSurat.Value.ToString("yyyy-MM-dd");
s.akun = txtAkun.Text;
if (cbPengikut1.Checked)
{
Pengikut p1 = new Pengikut();
if (aksi == "update")
{
p1.id = pengikut.ElementAtOrDefault(0) == null ? -1 : pengikut[0].id;
}
p1.pegawai = ((KeyValuePair <int, string>)cmbPengikut1.SelectedItem).Key;
p1.ket = txtKetPengikut1.Text;
p1.t_lahir = tgl_lahir.Text;
p.Add(p1);
}
if (cbPengikut2.Checked)
{
Pengikut p2 = new Pengikut();
if (aksi == "update")
{
p2.id = pengikut.ElementAtOrDefault(1) == null ? -1 : pengikut[1].id;
}
p2.pegawai = ((KeyValuePair <int, string>)cmbPengikut2.SelectedItem).Key;
p2.ket = txtKetPengikut2.Text;
p2.t_lahir = tgl_lahir1.Text;
p.Add(p2);
}
if (cbPengikut3.Checked)
{
Pengikut p3 = new Pengikut();
if (aksi == "update")
{
p3.id = pengikut.ElementAtOrDefault(2) == null ? -1 : pengikut[2].id;
}
p3.pegawai = ((KeyValuePair <int, string>)cmbPengikut3.SelectedItem).Key;
p3.ket = txtKetPengikut3.Text;
p3.t_lahir = tgl_lahir2.Text;
p.Add(p3);
}
b.harian = string.IsNullOrEmpty(txtHarian.Text) ? 0 : int.Parse(txtHarian.Text);
b.h_lama = string.IsNullOrEmpty(txtHLama.Text) ? 0 : int.Parse(txtHLama.Text);
b.h_total = Int32.Parse(txtHLama.Text) * Int32.Parse(txtHarian.Text);
b.penginapan = string.IsNullOrEmpty(txtPenginapan.Text) ? 0 : int.Parse(txtPenginapan.Text);
b.p_lama = string.IsNullOrEmpty(txtP_lama.Text) ? 0 : int.Parse(txtP_lama.Text);
b.p_total = Int32.Parse(txtP_lama.Text) * Int32.Parse(txtPenginapan.Text);
b.transport_pp = string.IsNullOrEmpty(txtTransport.Text) ? 0 : int.Parse(txtTransport.Text);
b.transport_loak = string.IsNullOrEmpty(txtLTransport.Text) ? 0 : int.Parse(txtLTransport.Text);
b.damri = string.IsNullOrEmpty(txtLTransport.Text) ? 0 : int.Parse(txtDamri.Text);
b.lain_lain = string.IsNullOrEmpty(txtLain_lain.Text) ? 0 : int.Parse(txtLain_lain.Text);
int result = -1;
if (aksi == "insert")
{
result = DbQuery.InsertSPD(s, p, b);
}
else if (aksi == "update")
{
foreach (var ss in spd)
{
s.id = ss.id;
}
foreach (var bi in biaya)
{
b.id = bi.id;
}
result = DbQuery.UpdateSPD(s, p, b);
}
string msg = "Terjadi Kesalahan Pada Saat Menyimpan Data";
if (result > 0)
{
msg = "Berhasil! Data Telah Tersimpan";
}
MessageBox.Show(msg);
if (Application.OpenForms["Form1"] != null)
{
(Application.OpenForms["Form1"] as Form1).RefSpd();
}
this.Close();
}