public string UbahData(Pegawai pg)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "UPDATE pegawai SET Nama='" + pg.NamaPegawai + "',TglLahir='" + pg.TglLahir.ToString("yyyy-MM-dd") + "', Alamat='" +
pg.Alamat + "', Gaji='" + pg.Gaji + "',Username='******', Password='******', IdJabatan='" +
pg.KategoriJabatan.IdJabatan + "' WHERE KodePegawai = '" + pg.KodePegawai + "'";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan ExecuteNonQuery untuk menjalankan perintah INSERT/UPDATE/DELETE
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string GenerateKode()
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "SELECT KodePelanggan FROM Pelanggan ORDER BY KodePelanggan DESC LIMIT 1";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan MySqlDataReader dan ExecuteReader untuk menjalankan perintah SELECT
MySqlDataReader data = c.ExecuteReader();
//jika data reader bisa terbaca (selama masih ada data)
if (data.Read() == true)
{
//mendapatkan kode Pelanggan dari hasil data reader
int kdTerbaru = int.Parse(data.GetValue(0).ToString()) + 1;
kodeTerbaru = kdTerbaru.ToString();
}
//hapus MySqlCommand setelah selesai
c.Dispose();
//hapus data reader setelah selesai
data.Dispose();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string TambahData(NotaBeli nota)
{
Koneksi k1 = new Koneksi();
k1.Connect();
string sql1 = "INSERT INTO NotaBeli(NoNota,Tanggal,KodeSupplier,KodePegawai) VALUES('" + nota.NoNota + "','" +
nota.Tanggal.ToString("yyyy-MM-dd hh:mm;ss") + "','" + nota.Supplier.KodeSupplier + "','" +
nota.Pegawai.KodePegawai + "')";
MySqlCommand c1 = new MySqlCommand(sql1, k1.KoneksiDB);
try
{
c1.ExecuteNonQuery();
for (int i = 0; i < nota.JumlahBarangNota; i++)
{
Koneksi k2 = new Koneksi();
k2.Connect();
string sql2 = "INSERT INTO NotaBeliDetil(NoNota,KodeBarang,Harga,Jumlah) VALUES ('" + nota.NoNota + "','" +
nota.ListNotaDetil[i].BarangNota.KodeBarang + "','" + nota.ListNotaDetil[i].HargaBeli + "','" +
nota.ListNotaDetil[i].JumlahBeli + "')";
MySqlCommand c2 = new MySqlCommand(sql2, k2.KoneksiDB);
c2.ExecuteNonQuery();
}
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string GenerateNoNota()
{
Koneksi k = new Koneksi();
k.Connect();
string sql = "SELECT SUBSTRING(NoNota,9,3) AS noUrutTransaksi " +
"FROM NotaBeli WHERE Date(Tanggal)=Date(CURRENT_DATE) " +
"ORDER BY NoNota DESC LIMIT 1";
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
MySqlDataReader data = c.ExecuteReader();
string noUrutTransTerbaru = "";
if (data.Read() == true)
{
int noUrutTrans = int.Parse(data.GetValue(0).ToString()) + 1;
noUrutTransTerbaru = noUrutTrans.ToString();
if (noUrutTransTerbaru.Length == 1)
{
noUrutTransTerbaru = "00" + noUrutTransTerbaru;
}
else if (noUrutTransTerbaru.Length == 2)
{
noUrutTransTerbaru = "0" + noUrutTransTerbaru;
}
}
else
{
noUrutTransTerbaru = "001";
}
string tahun = DateTime.Now.Year.ToString();
string bulan = DateTime.Now.Month.ToString();
if (bulan.Length == 1)
{
bulan = "0" + bulan;
}
string tanggal = DateTime.Now.Day.ToString();
if (tanggal.Length == 1)
{
tanggal = "0" + tanggal;
}
noNotaTerbaru = tahun + bulan + tanggal + noUrutTransTerbaru.ToString();
c.Dispose();
data.Dispose();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string GenerateKode()
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "SELECT SUBSTRING(KodeBarang,2,4) FROM Barang ORDER BY KodeBarang DESC LIMIT 1";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan MySqlDataReader dan ExecuteReader untuk menjalankan perintah SELECT
MySqlDataReader data = c.ExecuteReader();
//jika data reader bisa terbaca (selama masih ada data)
if (data.Read() == true)
{
//mendapatkan kode kategori dari hasil data reader
int kdTerbaru = int.Parse(data.GetValue(0).ToString()) + 1;
kodeterbaru = kdTerbaru.ToString();
//Format kode kategori
if (kodeterbaru.Length == 1)
{
kodeterbaru = "B000" + kodeterbaru;
}
else if (kodeterbaru.Length == 2)
{
kodeterbaru = "B00" + kodeterbaru;
}
else if (kodeterbaru.Length == 3)
{
kodeterbaru = "B0" + kodeterbaru;
}
else
{
kodeterbaru = "B" + kodeterbaru;
}
}
//hapus MySqlCommand setelah selesai
c.Dispose();
//hapus data reader setelah selesai
data.Dispose();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
private static MySqlDataReader JalankanPerintahQuery(string pSql)
{
Koneksi k = new Koneksi();
k.Connect();
MySqlCommand c = new MySqlCommand(pSql, k.KoneksiDB);
MySqlDataReader hasil = c.ExecuteReader();
return(hasil);
}
private static void JalankanPerintahDML(string pSql)
{
Koneksi k = new Koneksi();
k.Connect();
//buat mysqlcommand
MySqlCommand c = new MySqlCommand(pSql, k.KoneksiDB);
//gunakan executenonquery untuk menjalankan perintah insert/update/delete
c.ExecuteNonQuery();
}
public string CariData(string kriteria, string nilaiKriteria)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "SELECT B.KodePegawai, B.Nama,B.TglLahir, B.Alamat,B.Gaji,B.Username,B.Password ,K.IdJabatan, K.Nama AS kategori" +
" FROM Pegawai B INNER JOIN Jabatan K ON B.IdJabatan=K.IdJabatan" +
" WHERE B." + kriteria + " LIKE '%" + nilaiKriteria + "%'";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan MySqlDataReader dan ExecuteReader untuk menjalankan perintah SELECT
MySqlDataReader data = c.ExecuteReader();
//selama data reader masih bisa terbaca (selama masih ada data)
while (data.Read() == true)
{
string kode = data.GetValue(0).ToString();
string nama = data.GetValue(1).ToString();
DateTime tgl = DateTime.Parse(data.GetValue(2).ToString());
string alamat = data.GetValue(3).ToString();
int gaji = int.Parse(data.GetValue(4).ToString());
string user = data.GetValue(5).ToString();
string pass = data.GetValue(6).ToString();
string idJ = data.GetValue(7).ToString();
string nmJ = data.GetValue(8).ToString();
//create objek bertipe Kategori
Jabatan jb = new Jabatan(idJ, nmJ);
//simpan ke list
Pegawai pg = new Pegawai(kode, nama, tgl, alamat, gaji, user, pass, jb);
listPegawai.Add(pg);
}
//hapus MySqlCommand setelah selesai
c.Dispose();
//hapus data reader setelah selesai
data.Dispose();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string CariData(string kriteria, string nilaiKriteria)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "SELECT B.KodeBarang, B.Nama, B.HargaJual, B.Stok, K.KodeKategori, K.Nama AS kategori FROM Barang B " +
"INNER JOIN Kategori K ON B.kodeKategori=K.KodeKategori " +
"WHERE " + kriteria + " LIKE '%" + nilaiKriteria + "%'";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan MySqlDataReader dan ExecuteReader untuk menjalankan perintah SELECT
MySqlDataReader data = c.ExecuteReader();
//selama data reader masih bisa terbaca (selama masih ada data)
while (data.Read() == true)
{
//mendapatkan kode kategori dari hasil data reader
string kode = data.GetValue(0).ToString();
//mendapatkan nama kategori dari hasil data reader
string nama = data.GetValue(1).ToString();
int hrgJual = int.Parse(data.GetValue(2).ToString());
int stok = int.Parse(data.GetValue(3).ToString());
string kdKategori = data.GetValue(4).ToString();
string nmKategori = data.GetValue(5).ToString();
//create objek bertipe Kategori
Kategori kat = new Kategori(kdKategori, nmKategori);
//simpan ke list
Barang brg = new Barang(kode, nama, hrgJual, stok, kat);
listBarang.Add(brg);
}
//hapus MySqlCommand setelah selesai
c.Dispose();
//hapus data reader setelah selesai
data.Dispose();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string BacaSemuaData()
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "SELECT * FROM Supplier";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan MySqlDataReader dan ExecuteReader untuk menjalankan perintah SELECT
MySqlDataReader data = c.ExecuteReader();
//selama data reader masih bisa terbaca (selama masih ada data)
while (data.Read() == true)
{
//mendapatkan kode Pelanggan dari hasil data reader
string kode = data.GetValue(0).ToString();
//mendapatkan nama Pelanggan dari hasil data reader
string nama = data.GetValue(1).ToString();
//mendapatkan alamat Pelanggan dari hasil data reader
string alamat = data.GetValue(2).ToString();
//mendapatkan telp Pelanggan dari hasil data reader
//create objek bertipe Pelanggan
Supplier s = new Supplier(kode, nama, alamat);
//simpan ke list
listSupplier.Add(s);
}
//hapus MySqlCommand setelah selesai
c.Dispose();
//hapus data reader setelah selesai
data.Dispose();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string CariData(string kriteria, string nilaiKriteria)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "SELECT * FROM Pelanggan WHERE " + kriteria + " LIKE '%" + nilaiKriteria + "%'";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan MySqlDataReader dan ExecuteReader untuk menjalankan perintah SELECT
MySqlDataReader data = c.ExecuteReader();
//selama data reader masih bisa terbaca (selama masih ada data)
while (data.Read() == true)
{
//mendapatkan kode Pelanggan dari hasil data reader
string kode = data.GetValue(0).ToString();
//mendapatkan nama Pelanggan dari hasil data reader
string nama = data.GetValue(1).ToString();
//mendapatkan alamat Pelanggan dari hasil data reader
string alamat = data.GetValue(2).ToString();
//mendapatkan telp Pelanggan dari hasil data reader
string telp = data.GetValue(3).ToString();
//create objek bertipe Pelanggan
Pelanggan p = new Pelanggan(kode, nama, alamat, telp);
//simpan ke list
listPelanggan.Add(p);
}
//hapus MySqlCommand setelah selesai
c.Dispose();
//hapus data reader setelah selesai
data.Dispose();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string TambahData(Pegawai pg)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "INSERT INTO Pegawai(KodePegawai, Nama,TglLahir,Alamat,Gaji,Username,Password,IdJabatan) VALUES ('" + pg.KodePegawai + "','" + pg.NamaPegawai + "','" + pg.TglLahir.ToString("yyyy-MM-dd") + "','" + pg.Alamat + "','" + pg.Gaji + "','" +
pg.Username + "','" + pg.Password + "','" + pg.KategoriJabatan.IdJabatan + "')";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan ExecuteNonQuery untuk menjalankan perintah INSERT/UPDATE/DELETE
c.ExecuteNonQuery();
string hasilBuatUser = BuatUserBaru(pg, "localhost");
if (hasilBuatUser == "sukses")
{
string hasilberiHak = BeriHakAkses(pg, "localhost");
if (hasilberiHak == "sukses")
{
return("sukses");
}
else
{
return("gagal memberikan hak akses. pesan kesalanhan = " + hasilberiHak);
}
}
else
{
return("Gagal membuat user. pesan Kesalahan = " + hasilBuatUser);
}
}
catch (Exception e)
{
return("pesan gagal " + e.Message);
}
}
public string BuatUserBaru(Pegawai pg, string namaServer)
{
Koneksi k = new Koneksi();
k.Connect();
string sql = "CREATE USER '" + pg.Username + "'@'" + namaServer + "' IDENTIFIED BY '" + pg.Password + "'";
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string ubahPasswordUser(string user, string pass)
{
Koneksi k = new Koneksi();
k.Connect();
string sql = "SET PASSWORD FOR '" + user + "'@'localhost=PASSWORD('" + pass + "')";
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string HapusUser(string user, string pass)
{
Koneksi k = new Koneksi();
k.Connect();
string sql = "DROP USER '" + user + "'@'localhost'";
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string BeriHakAkses(Pegawai pg, string namaServer)
{
Koneksi k = new Koneksi();
k.Connect();
string sql = "GRANT ALL PRIVILEGES ON si_jual_beli.* TO '" + pg.Username + "'@'" + namaServer + "'";
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string TambahData(Barang brg)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "INSERT INTO Barang(KodeBarang, Nama,HargaJual,Stok,KodeKategori) VALUES ('" + brg.KodeBarang + "','" + brg.NamaBarang + "','" + brg.HargaJual + "','" + brg.Stok + "','" + brg.KategoriBarang.KodeKategori + "')";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan ExecuteNonQuery untuk menjalankan perintah INSERT/UPDATE/DELETE
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string UbahData(Kategori kat)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "UPDATE kategori SET nama = '" + kat.NamaKategori + "' WHERE KodeKategori = '" + kat.KodeKategori + "'";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan ExecuteNonQuery untuk menjalankan perintah INSERT/UPDATE/DELETE
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string TambahData(Jabatan jb)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "INSERT INTO Jabtan(IdJabatan, Nama) VALUES ('" + jb.IdJabatan + "','" + jb.NamaJabatan + "')";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan ExecuteNonQuery untuk menjalankan perintah INSERT/UPDATE/DELETE
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string UbahData(Pelanggan p)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "UPDATE Pelanggan SET Nama = '" + p.NamaPelanggan + "', Alamat = '" + p.AlamatPelanggan + "', Telepon = '" + p.Telepon + "' WHERE KodePelanggan = '" + p.KodePelanggan + "'";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan ExecuteNonQuery untuk menjalankan perintah INSERT/UPDATE/DELETE
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string TambahData(Supplier s)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "INSERT INTO Supplier(KodePelanggan, Nama, Alamat) VALUES ('" + s.KodeSupplier + "','" + s.NamaSupplier + "','" + s.AlamatSupplier + "')";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan ExecuteNonQuery untuk menjalankan perintah INSERT/UPDATE/DELETE
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string HapusData(Pegawai pg)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "DELETE FROM Pegawai WHERE KodeBarang = '" + pg.KodePegawai + "'";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan ExecuteNonQuery untuk menjalankan perintah INSERT/UPDATE/DELETE
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string UpdateStokBarang(DetilNotaJual detilNota)
{
Koneksi k = new Koneksi();
k.Connect();
string sql = "";
sql = " UPDATE barang SET Stok = Stok - " + detilNota.JumlahJual +
" WHERE KodeBarang = '" + detilNota.BarangNota.KodeBarang + "'";
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string UbahData(Barang brg)
{
Koneksi k = new Koneksi();
k.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "UPDATE barang SET Nama = '" + brg.NamaBarang + "', HargaJual = '" + brg.HargaJual + "', Stok = '" + brg.Stok + "', KodeKategori = '" + brg.KategoriBarang.KodeKategori
+ "' WHERE KodeBarang = '" + brg.KodeBarang + "'";
//Buat MySqlCommand
MySqlCommand c = new MySqlCommand(sql, k.KoneksiDB);
try
{
//Gunakan ExecuteNonQuery untuk menjalankan perintah INSERT/UPDATE/DELETE
c.ExecuteNonQuery();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string BacaSemuaData()
{
Koneksi k1 = new Koneksi();
k1.Connect();
string sql1 = "SELECT N.NoNota,N.Tanggal, N.KodePelanggan, Plg.Nama AS NamaPelanggan, Plg.Alamat AS AlamatPelanggan, N.KodePegawai, Peg.Nama AS NamaPegawai " +
" FROM NotaJual N INNER JOIN Pelanggan Plg ON N.KodePelanggan=Plg.KodePelanggan " +
" INNER JOIN Pegawai Peg ON N.KodePegawai=Peg.KodePegawai " +
" ORDER BY N.NoNota DESC ";
MySqlCommand c1 = new MySqlCommand(sql1, k1.KoneksiDB);
try
{
MySqlDataReader data1 = c1.ExecuteReader();
while (data1.Read() == true)
{
string nomorNota = data1.GetValue(0).ToString();
DateTime tglNota = DateTime.Parse(data1.GetValue(1).ToString());
string kdPelanggan = data1.GetValue(2).ToString();
string nmPelanggan = data1.GetValue(3).ToString();
string alamatPelanggan = data1.GetValue(4).ToString();
Pelanggan Plg = new Pelanggan();
Plg.KodePelanggan = kdPelanggan;
Plg.NamaPelanggan = nmPelanggan;
Plg.AlamatPelanggan = alamatPelanggan;
string kdPegawai = data1.GetValue(5).ToString();
string nmPegawai = data1.GetValue(6).ToString();
Pegawai Peg = new Pegawai();
Peg.KodePegawai = kdPegawai;
Peg.NamaPegawai = nmPegawai;
List <DetilNotaJual> listDetilNota = new List <DetilNotaJual>();
Koneksi k2 = new Koneksi();
k2.Connect();
string sql2 = "SELECT NJD.KodeBarang, B.Nama, NJD.Harga, NJD.Jumlah " +
" FROM notajual N INNER JOIN notajualdetil NJD ON N.NoNota=NJD.NoNota " +
" INNER JOIN Barang B ON NJD.KodeBarang = B.KodeBarang " +
" WHERE N.NoNota = '" + nomorNota + "'";
MySqlCommand c2 = new MySqlCommand(sql2, k2.KoneksiDB);
MySqlDataReader data2 = c2.ExecuteReader();
while (data2.Read() == true)
{
string kdBarang = data2.GetValue(0).ToString();
string nmBarang = data2.GetValue(1).ToString();
Barang Brg = new Barang();
Brg.KodeBarang = kdBarang;
Brg.NamaBarang = nmBarang;
int hrgJual = int.Parse(data2.GetValue(2).ToString());
int jmlJual = int.Parse(data2.GetValue(3).ToString());
DetilNotaJual detilNota = new DetilNotaJual(Brg, hrgJual, jmlJual);
listDetilNota.Add(detilNota);
}
c2.Dispose();
data2.Dispose();
NotaJual nota = new NotaJual(nomorNota, tglNota, Plg, Peg, listDetilNota);
listNotaJual.Add(nota);
}
c1.Dispose();
data1.Dispose();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
public string CariData(string kriteria, string nilaiKriteria)
{
Koneksi k1 = new Koneksi();
k1.Connect();
//tuliskan perintah SQL yang akan dijalankan
string sql = "SELECT N.NoNota,N.Tanggal, N.KodePelanggan, Plg.Nama AS NamaPelanggan, Plg.Alamat AS AlamatPelanggan, N.KodePegawai, Peg.Nama AS NamaPegawai " +
" FROM NotaJual N INNER JOIN Pelanggan Plg ON N.KodePelanggan=Plg.KodePelanggan " +
" INNER JOIN Pegawai Peg ON N.KodePegawai=Peg.KodePegawai " +
" WHERE " + kriteria + " LIKE '%" + nilaiKriteria + "%'";
//Buat MySqlCommand
MySqlCommand c1 = new MySqlCommand(sql, k1.KoneksiDB);
try
{
//Gunakan MySqlDataReader dan ExecuteReader untuk menjalankan perintah SELECT
MySqlDataReader data1 = c1.ExecuteReader();
//selama data reader masih bisa terbaca (selama masih ada data)
while (data1.Read() == true)
{
//mendapatkan kode kategori dari hasil data reader
string nomorNota = data1.GetValue(0).ToString();
//mendapatkan nama kategori dari hasil data reader
DateTime tglNota = DateTime.Parse(data1.GetValue(1).ToString());
string kdPlg = data1.GetValue(2).ToString();
string nmPlg = data1.GetValue(3).ToString();
string almatPlg = data1.GetValue(4).ToString();
Pelanggan Plg = new Pelanggan();
Plg.KodePelanggan = kdPlg;
Plg.NamaPelanggan = nmPlg;
Plg.AlamatPelanggan = almatPlg;
string kodePeg = data1.GetValue(5).ToString();
string nmPeg = data1.GetValue(6).ToString();
Pegawai Peg = new Pegawai();
Peg.KodePegawai = kodePeg;
Peg.NamaPegawai = nmPeg;
List <DetilNotaJual> listDetilNota = new List <DetilNotaJual>();
Koneksi k2 = new Koneksi();
k2.Connect();
string sql2 = "SELECT NJD.KodeBarang, B.Nama, NJD.Harga, NJD.Jumlah " +
" FROM notajual N INNER JOIN notajualdetil NJD ON N.NoNota=NJD.NoNota " +
" INNER JOIN Barang B ON NJD.KodeBarang = B.KodeBarang " +
" WHERE N.NoNota = '" + nomorNota + "'";
MySqlCommand c2 = new MySqlCommand(sql2, k2.KoneksiDB);
MySqlDataReader data2 = c2.ExecuteReader();
while (data2.Read() == true)
{
string kdBarang = data2.GetValue(0).ToString();
string nmBarang = data2.GetValue(1).ToString();
Barang Brg = new Barang();
Brg.KodeBarang = kdBarang;
Brg.NamaBarang = nmBarang;
int hrgJual = int.Parse(data2.GetValue(2).ToString());
int jmlJual = int.Parse(data2.GetValue(3).ToString());
DetilNotaJual detilNota = new DetilNotaJual(Brg, hrgJual, jmlJual);
listDetilNota.Add(detilNota);
}
c2.Dispose();
data2.Dispose();
NotaJual nota = new NotaJual(nomorNota, tglNota, Plg, Peg, listDetilNota);
listNotaJual.Add(nota);
}
c1.Dispose();
data1.Dispose();
return("sukses");
}
catch (Exception e)
{
return(e.Message);
}
}
//masih belum
public void printLaporanPenjualan(String filename, String periode)
{
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "Report";
worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 6]].Merge();
worKsheeT.Cells[1, 1] = "Laporan Penjualan";
worKsheeT.Cells.Font.Size = 15;
worKsheeT.Cells[2, 1] = "Tanggal";
worKsheeT.Cells[2, 2] = "NoNota";
worKsheeT.Cells[2, 3] = "Nama Barang";
worKsheeT.Cells[2, 4] = "Jumlah";
worKsheeT.Cells[2, 5] = "Harga";
worKsheeT.Cells[2, 6] = "Total";
//worKsheeT.Cells[2, 5] = "Kategori";
Microsoft.Office.Interop.Excel.Style style = excel.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Bold = true;
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Koneksi k = new Koneksi();
k.Connect();
int baris = 3;
String q = "SELECT DATE_FORMAT(Tanggal,'%d-%M-%Y') as Tanggal,b.Nama,nd.Jumlah,nd.Harga,(nd.Harga*nd.Jumlah) as Subtotal,nb.NoNota " +
"FROM notajual nb " +
"INNER JOIN notajualdetil nd ON (nb.NoNota=nd.NoNota) " +
"INNER JOIN barang b ON (b.KodeBarang=nd.KodeBarang) " +
"WHERE DATE_FORMAT(Tanggal,'%Y-%M')=?periode";
MySqlCommand c = new MySqlCommand(q, k.KoneksiDB);
c.Parameters.Add(new MySqlParameter("periode", periode));
MySqlDataReader rd = c.ExecuteReader();
int total = 0;
while (rd.Read())
{
worKsheeT.Cells[baris, 1] = rd["Tanggal"];
worKsheeT.Cells[baris, 2] = rd["NoNota"];
worKsheeT.Cells[baris, 3] = rd["Nama"];
worKsheeT.Cells[baris, 4] = rd["Jumlah"];
worKsheeT.Cells[baris, 5] = rd["Harga"];
worKsheeT.Cells[baris, 6] = rd["Subtotal"];
total = total + Int32.Parse(rd["Subtotal"] + "");
baris = baris + 1;
}
worKsheeT.Cells[baris, 6] = total;
Microsoft.Office.Interop.Excel.Range angkaStyles = excel.get_Range("E3:F" + baris);
angkaStyles.NumberFormat = "##,#";
Microsoft.Office.Interop.Excel.Range rangeStyles = excel.get_Range("A1:F20");
Microsoft.Office.Interop.Excel.Range rangeStyles2 = excel.get_Range("A1:F2");
Microsoft.Office.Interop.Excel.Range rangeStyles3 = excel.get_Range("A1:F" + baris);
rangeStyles2.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//rangeStyles.Value2 = "'Style Test";
//rangeStyles.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//rangeStyles3.BorderAround2();
rangeStyles3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
rangeStyles3.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
k.KoneksiDB.Close();
worKbooK.SaveAs(filename);
worKbooK.Close();
excel.Quit();
ProcessStartInfo info = new ProcessStartInfo();
info.Verb = "print";
info.FileName = @filename;
info.CreateNoWindow = true;
info.WindowStyle = ProcessWindowStyle.Hidden;
Process p = new Process();
p.StartInfo = info;
p.Start();
p.WaitForInputIdle();
System.Threading.Thread.Sleep(3000);
//if (false == p.CloseMainWindow())
//p.Kill();
}
catch (Exception ex)
{
String msg = ex.Message;
String abc = "aa";
}
finally
{
worKsheeT = null;
celLrangE = null;
worKbooK = null;
}
}
public void printPegawai(String filename)
{
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "Report Pegawai";
worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 4]].Merge();
worKsheeT.Cells[1, 1] = "Laporan Pegawai";
worKsheeT.Cells.Font.Size = 15;
worKsheeT.Cells[2, 1] = "Kode Pegawai";
worKsheeT.Cells[2, 2] = "Nama";
worKsheeT.Cells[2, 3] = "Tanggal Lahir";
worKsheeT.Cells[2, 4] = "Alamat";
worKsheeT.Cells[2, 5] = "Gaji";
worKsheeT.Cells[2, 6] = "Username";
worKsheeT.Cells[2, 7] = "Password";
worKsheeT.Cells[2, 8] = "IdJabatan";
//worKsheeT.Cells[2, 5] = "Kategori";
Microsoft.Office.Interop.Excel.Style style = excel.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Bold = true;
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Koneksi k = new Koneksi();
k.Connect();
int baris = 3;
String q = "SELECT * FROM Pegawai";
MySqlCommand c = new MySqlCommand(q, k.KoneksiDB);
MySqlDataReader rd = c.ExecuteReader();
while (rd.Read())
{
worKsheeT.Cells[baris, 1] = rd["KodePegawai"];
worKsheeT.Cells[baris, 2] = rd["Nama"];
worKsheeT.Cells[baris, 3] = rd["TglLahir"];
worKsheeT.Cells[baris, 4] = rd["Alamat"];
worKsheeT.Cells[baris, 5] = rd["Gaji"];
worKsheeT.Cells[baris, 6] = rd["Username"];
worKsheeT.Cells[baris, 7] = rd["Password"];
worKsheeT.Cells[baris, 8] = rd["IdJabatan"];
/*if (worKsheeT.Cells[baris, 8] = "J1")
* {
* worKsheeT.Cells[baris, 8] = "Pegawai Pembelian";
* }
* else if (worKsheeT.Cells[baris, 8] = "J2")
* {
* worKsheeT.Cells[baris, 8] = "Kasir";
* }
* else if (worKsheeT.Cells[baris, 8] = "J3")
* {
* worKsheeT.Cells[baris, 8] = "Manager";
* }*/
baris = baris + 1;
}
Microsoft.Office.Interop.Excel.Range angkaStyles = excel.get_Range("A3:H" + baris);
angkaStyles.NumberFormat = "##,#";
Microsoft.Office.Interop.Excel.Range rangeStyles = excel.get_Range("A1:H20");
Microsoft.Office.Interop.Excel.Range rangeStyles2 = excel.get_Range("A1:H2");
Microsoft.Office.Interop.Excel.Range rangeStyles3 = excel.get_Range("A1:H" + baris);
rangeStyles2.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//rangeStyles.Value2 = "'Style Test";
//rangeStyles.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//rangeStyles3.BorderAround2();
rangeStyles3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
rangeStyles3.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
k.KoneksiDB.Close();
/*
* MySqlConnection mc = new MySqlConnection(Koneksi.strCon);
* MySqlCommand cmd = new MySqlCommand(q, mc);
* MySqlDataReader rd = cmd.ExecuteReader();
*
*
* int barisS= 2;
* while (rd.Read())
* {
* worKsheeT.Cells[barisS, 1] = rd["KodeBarang"];
* worKsheeT.Cells[barisS, 2] = rd["NamaBarang"];
* worKsheeT.Cells[barisS, 3] = rd["HargaJual"];
* worKsheeT.Cells[barisS, 4] = rd["Stok"];
* barisS++;
* //worKsheeT.Cells[2, 5] = "Kategori";
* }*/
//DateTime dt = DateTime.Now;
worKbooK.SaveAs(filename);
worKbooK.Close();
excel.Quit();
/*ProcessStartInfo info = new ProcessStartInfo();
* info.Verb = "print";
* info.FileName = @filename;
* info.CreateNoWindow = true;
* info.WindowStyle = ProcessWindowStyle.Hidden;
*
* Process p = new Process();
* p.StartInfo = info;
* p.Start();
*
* p.WaitForInputIdle();
* System.Threading.Thread.Sleep(3000);*/
//if (false == p.CloseMainWindow())
//p.Kill();
}
catch (Exception ex)
{
}
finally
{
worKsheeT = null;
celLrangE = null;
worKbooK = null;
}
}