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; } }