// Method to get lama_hari, id_tarif and ongkos
public void getTarif(ref Entity.EntData ead)
{
try
{
string Query1 = "SELECT lama_hari FROM tb_tarif WHERE kota_pengiriman= @kotapengiriman AND kota_tujuan = @kotatujuan AND tipe_barang = @tipebarang;";
SqlCommand cmdDataBase1 = new SqlCommand(Query1, koneksi);
cmdDataBase1.Parameters.AddWithValue("@kotapengiriman", ead.getKotaPengiriman().ToString());
cmdDataBase1.Parameters.AddWithValue("@kotatujuan", ead.getKotaTujuan().ToString());
cmdDataBase1.Parameters.AddWithValue("@tipebarang", ead.getTipeBarang().ToString());
string Query2 = "SELECT id_tarif FROM tb_tarif WHERE kota_pengiriman= @kotapengiriman AND kota_tujuan = @kotatujuan AND tipe_barang = @tipebarang;";
SqlCommand cmdDataBase2 = new SqlCommand(Query2, koneksi);
cmdDataBase2.Parameters.AddWithValue("@kotapengiriman", ead.getKotaPengiriman().ToString());
cmdDataBase2.Parameters.AddWithValue("@kotatujuan", ead.getKotaTujuan().ToString());
cmdDataBase2.Parameters.AddWithValue("@tipebarang", ead.getTipeBarang().ToString());
string Query3 = "SELECT ongkos FROM tb_tarif WHERE kota_pengiriman= @kotapengiriman AND kota_tujuan = @kotatujuan AND tipe_barang = @tipebarang;";
SqlCommand cmdDataBase3 = new SqlCommand(Query3, koneksi);
cmdDataBase3.Parameters.AddWithValue("@kotapengiriman", ead.getKotaPengiriman().ToString());
cmdDataBase3.Parameters.AddWithValue("@kotatujuan", ead.getKotaTujuan().ToString());
cmdDataBase3.Parameters.AddWithValue("@tipebarang", ead.getTipeBarang().ToString());
koneksi.Open();
var value1 = cmdDataBase1.ExecuteScalar();
if (value1 != null)
{
ead.setLamaHari(Int32.Parse(value1.ToString()));
}
var value2 = cmdDataBase2.ExecuteScalar();
if (value2 != null)
{
ead.setIdTarif(Int32.Parse(value2.ToString()));
}
var value3 = cmdDataBase3.ExecuteScalar();
if (value3 != null)
{
ead.setOngkos(Int32.Parse(value3.ToString()));
}
koneksi.Close();
}
catch (SqlException se)
{
Console.WriteLine("ERROR " + se);
}
}
// Method To Update Data
public Boolean updatedata(Entity.EntData ead)
{
status = false;
try
{
// Query Update Table tb_pengirim
string Query1 = "UPDATE tb_pengirim SET nama_pengirim = @namapengirim, jk = @jkpengirim, kode_pos = @kode_pos, no_hub = @no_hub, alamat = @alamat, rt = @rt, rw = @rw, desa = @desa, kec = @kec, id_kabupaten = @kab, id_provinsi = @prov WHERE id_pengirim = @id_pengirim;";
SqlCommand cmdDataBase1 = new SqlCommand(Query1, koneksi);
cmdDataBase1.Parameters.AddWithValue("@namapengirim", ead.getNamaPengirim());
cmdDataBase1.Parameters.AddWithValue("@jkpengirim", ead.getJkPengirim());
cmdDataBase1.Parameters.AddWithValue("@kode_pos", ead.getKodePosPengirim());
cmdDataBase1.Parameters.AddWithValue("@no_hub", ead.getNoHubPengirim());
cmdDataBase1.Parameters.AddWithValue("@alamat", ead.getAlamatPengirim());
cmdDataBase1.Parameters.AddWithValue("@rt", ead.getRtPengirim());
cmdDataBase1.Parameters.AddWithValue("@rw", ead.getRwPengirim());
cmdDataBase1.Parameters.AddWithValue("@desa", ead.getDesaPengirim());
cmdDataBase1.Parameters.AddWithValue("@kec", ead.getKecPengirim());
if (ead.getId_KabPengirim() == 0)
{
cmdDataBase1.Parameters.AddWithValue("@kab", DBNull.Value);
}
else
{
cmdDataBase1.Parameters.AddWithValue("@kab", ead.getId_KabPengirim());
}
if (ead.getId_ProvPengirim() == 0)
{
cmdDataBase1.Parameters.AddWithValue("@prov", DBNull.Value);
}
else
{
cmdDataBase1.Parameters.AddWithValue("@prov", ead.getId_ProvPengirim());
}
cmdDataBase1.Parameters.AddWithValue("@id_pengirim", ead.getIdPengirim());
// Query Update Table tb_penerima
string Query2 = "UPDATE tb_penerima SET nama_penerima = @namapenerima, jk = @jkpenerima, kode_pos = @kode_pos, no_hub = @no_hub, alamat = @alamat, rt = @rt, rw = @rw, desa = @desa, kec = @kec, id_kabupaten = @kab WHERE id_penerima = @id_penerima;";
SqlCommand cmdDataBase2 = new SqlCommand(Query2, koneksi);
cmdDataBase2.Parameters.AddWithValue("@namapenerima", ead.getNamaPenerima());
cmdDataBase2.Parameters.AddWithValue("@jkpenerima", ead.getJkPenerima());
cmdDataBase2.Parameters.AddWithValue("@kode_pos", ead.getKodePosPenerima());
cmdDataBase2.Parameters.AddWithValue("@no_hub", ead.getNoHubPenerima());
cmdDataBase2.Parameters.AddWithValue("@alamat", ead.getAlamatPenerima());
cmdDataBase2.Parameters.AddWithValue("@rt", ead.getRtPenerima());
cmdDataBase2.Parameters.AddWithValue("@rw", ead.getRwPenerima());
cmdDataBase2.Parameters.AddWithValue("@desa", ead.getDesaPenerima());
cmdDataBase2.Parameters.AddWithValue("@kec", ead.getKecPenerima());
cmdDataBase2.Parameters.AddWithValue("@kab", ead.getId_KabPenerima());
cmdDataBase2.Parameters.AddWithValue("@id_penerima", ead.getIdPenerima());
// Query Update Table tb_transaksi
string Query3 = "UPDATE tb_transaksi SET tgl_transaksi = @tgl_transaksi, berat = @berat, nik = @id_pegawai, id_pengirim = @id_pengirim, id_tarif = @id_tarif, id_penerima = @id_penerima WHERE id_transaksi = @id_transaksi";
SqlCommand cmdDataBase3 = new SqlCommand(Query3, koneksi);
cmdDataBase3.Parameters.AddWithValue("@tgl_transaksi", ead.getTglTransaksi());
cmdDataBase3.Parameters.AddWithValue("@berat", ead.getBerat());
cmdDataBase3.Parameters.AddWithValue("@id_pegawai", ead.getIdPegawai());
cmdDataBase3.Parameters.AddWithValue("@id_pengirim", ead.getIdPengirim());
cmdDataBase3.Parameters.AddWithValue("@id_tarif", ead.getIdTarif());
cmdDataBase3.Parameters.AddWithValue("@id_penerima", ead.getIdPenerima());
cmdDataBase3.Parameters.AddWithValue("@id_transaksi", ead.getIdTransaksi());
koneksi.Open();
cmdDataBase1.ExecuteNonQuery();
cmdDataBase2.ExecuteNonQuery();
cmdDataBase3.ExecuteNonQuery();
status = true;
koneksi.Close();
}
catch (SqlException se)
{
Console.WriteLine("ERROR " + se);
}
return(status);
}
// Method To Save Data
public Boolean saveData(Entity.EntData ead)
{
status = false;
SqlCommand cmdDataBase1, cmdDataBase2, cmdDataBase3;
String Query1, Query2, Query3;
try
{
// If There is an id_pengirim duplicate
if (ead.getStatusDuplikatPengirim() == true)
{
// QUERY UPDATE
Query1 = "UPDATE tb_pengirim SET nama_pengirim = @namapengirim, jk = @jkpengirim, kode_pos = @kode_pos, no_hub = @no_hub, alamat = @alamat, rt = @rt, rw = @rw, desa = @desa, kec = @kec, id_kabupaten = @kab, id_provinsi = @prov WHERE id_pengirim = @id_pengirim;";
cmdDataBase1 = new SqlCommand(Query1, koneksi);
cmdDataBase1.Parameters.AddWithValue("@namapengirim", ead.getNamaPengirim());
cmdDataBase1.Parameters.AddWithValue("@jkpengirim", ead.getJkPengirim());
cmdDataBase1.Parameters.AddWithValue("@kode_pos", ead.getKodePosPengirim());
cmdDataBase1.Parameters.AddWithValue("@no_hub", ead.getNoHubPengirim());
cmdDataBase1.Parameters.AddWithValue("@alamat", ead.getAlamatPengirim());
cmdDataBase1.Parameters.AddWithValue("@rt", ead.getRtPengirim());
cmdDataBase1.Parameters.AddWithValue("@rw", ead.getRwPengirim());
cmdDataBase1.Parameters.AddWithValue("@desa", ead.getDesaPengirim());
cmdDataBase1.Parameters.AddWithValue("@kec", ead.getKecPengirim());
if (ead.getId_KabPengirim() == 0)
{
cmdDataBase1.Parameters.AddWithValue("@kab", DBNull.Value);
}
else
{
cmdDataBase1.Parameters.AddWithValue("@kab", ead.getId_KabPengirim());
}
if (ead.getId_ProvPengirim() == 0)
{
cmdDataBase1.Parameters.AddWithValue("@prov", DBNull.Value);
}
else
{
cmdDataBase1.Parameters.AddWithValue("@prov", ead.getId_ProvPengirim());
}
cmdDataBase1.Parameters.AddWithValue("@id_pengirim", ead.getIdPengirim());
}
else
{
// QUERY INSERT
Query1 = "INSERT INTO tb_pengirim VALUES (@id_pengirim, @namapengirim, @jkpengirim, @kode_pos, @no_hub, @alamat, @rt, @rw, @desa, @kec, @kab, @prov);";
cmdDataBase1 = new SqlCommand(Query1, koneksi);
cmdDataBase1.Parameters.AddWithValue("@id_pengirim", ead.getIdPengirim());
cmdDataBase1.Parameters.AddWithValue("@namapengirim", ead.getNamaPengirim());
cmdDataBase1.Parameters.AddWithValue("@jkpengirim", ead.getJkPengirim());
cmdDataBase1.Parameters.AddWithValue("@kode_pos", ead.getKodePosPengirim());
cmdDataBase1.Parameters.AddWithValue("@no_hub", ead.getNoHubPengirim());
cmdDataBase1.Parameters.AddWithValue("@alamat", ead.getAlamatPengirim());
cmdDataBase1.Parameters.AddWithValue("@rt", ead.getRtPengirim());
cmdDataBase1.Parameters.AddWithValue("@rw", ead.getRwPengirim());
cmdDataBase1.Parameters.AddWithValue("@desa", ead.getDesaPengirim());
cmdDataBase1.Parameters.AddWithValue("@kec", ead.getKecPengirim());
if (ead.getId_KabPengirim() == 0)
{
cmdDataBase1.Parameters.AddWithValue("@kab", DBNull.Value);
}
else
{
cmdDataBase1.Parameters.AddWithValue("@kab", ead.getId_KabPengirim());
}
if (ead.getId_ProvPengirim() == 0)
{
cmdDataBase1.Parameters.AddWithValue("@prov", DBNull.Value);
}
else
{
cmdDataBase1.Parameters.AddWithValue("@prov", ead.getId_ProvPengirim());
}
}
// If There is an id_penerima duplicate
if (ead.getStatusDuplikatPenerima() == true)
{
// QUERY UPDATE
Query2 = "UPDATE tb_penerima SET nama_penerima = @namapenerima, jk = @jkpenerima, kode_pos = @kode_pos, no_hub = @no_hub, alamat = @alamat, rt = @rt, rw = @rw, desa = @desa, kec = @kec, id_kabupaten = @kab WHERE id_penerima = @id_penerima;";
cmdDataBase2 = new SqlCommand(Query2, koneksi);
cmdDataBase2.Parameters.AddWithValue("@namapenerima", ead.getNamaPenerima());
cmdDataBase2.Parameters.AddWithValue("@jkpenerima", ead.getJkPenerima());
cmdDataBase2.Parameters.AddWithValue("@kode_pos", ead.getKodePosPenerima());
cmdDataBase2.Parameters.AddWithValue("@no_hub", ead.getNoHubPenerima());
cmdDataBase2.Parameters.AddWithValue("@alamat", ead.getAlamatPenerima());
cmdDataBase2.Parameters.AddWithValue("@rt", ead.getRtPenerima());
cmdDataBase2.Parameters.AddWithValue("@rw", ead.getRwPenerima());
cmdDataBase2.Parameters.AddWithValue("@desa", ead.getDesaPenerima());
cmdDataBase2.Parameters.AddWithValue("@kec", ead.getKecPenerima());
cmdDataBase2.Parameters.AddWithValue("@kab", ead.getId_KabPenerima());
cmdDataBase2.Parameters.AddWithValue("@id_penerima", ead.getIdPenerima());
}
else
{
// QUERY INSERT
Query2 = "INSERT INTO tb_penerima VALUES (@id_penerima, @namapenerima, @jkpenerima, @kode_pos, @no_hub, @alamat, @rt, @rw, @desa, @kec, @kab);";
cmdDataBase2 = new SqlCommand(Query2, koneksi);
cmdDataBase2.Parameters.AddWithValue("@id_penerima", ead.getIdPenerima());
cmdDataBase2.Parameters.AddWithValue("@namapenerima", ead.getNamaPenerima());
cmdDataBase2.Parameters.AddWithValue("@jkpenerima", ead.getJkPenerima());
cmdDataBase2.Parameters.AddWithValue("@kode_pos", ead.getKodePosPenerima());
cmdDataBase2.Parameters.AddWithValue("@no_hub", ead.getNoHubPenerima());
cmdDataBase2.Parameters.AddWithValue("@alamat", ead.getAlamatPenerima());
cmdDataBase2.Parameters.AddWithValue("@rt", ead.getRtPenerima());
cmdDataBase2.Parameters.AddWithValue("@rw", ead.getRwPenerima());
cmdDataBase2.Parameters.AddWithValue("@desa", ead.getDesaPenerima());
cmdDataBase2.Parameters.AddWithValue("@kec", ead.getKecPenerima());
cmdDataBase2.Parameters.AddWithValue("@kab", ead.getId_KabPenerima());
}
// Insert A New Transaction Data
Query3 = "INSERT INTO tb_transaksi (tgl_transaksi, berat, nik, id_pengirim, id_tarif, id_penerima) VALUES (@tgl_transaksi, @berat, @id_pegawai, @id_pengirim, @id_tarif, @id_penerima);";
cmdDataBase3 = new SqlCommand(Query3, koneksi);
cmdDataBase3.Parameters.AddWithValue("@tgl_transaksi", ead.getTglTransaksi());
cmdDataBase3.Parameters.AddWithValue("@berat", ead.getBerat());
cmdDataBase3.Parameters.AddWithValue("@id_pegawai", ead.getIdPegawai());
cmdDataBase3.Parameters.AddWithValue("@id_pengirim", ead.getIdPengirim());
cmdDataBase3.Parameters.AddWithValue("@id_tarif", ead.getIdTarif());
cmdDataBase3.Parameters.AddWithValue("@id_penerima", ead.getIdPenerima());
koneksi.Open();
cmdDataBase1.ExecuteNonQuery();
cmdDataBase2.ExecuteNonQuery();
cmdDataBase3.ExecuteNonQuery();
status = true;
koneksi.Close();
}
catch (SqlException se)
{
Console.WriteLine("ERROR " + se);
}
return(status);
}
