public static string TotalPenjualanBulanan(int pBulan, List <OrderPenjualan> listHasilData) { // Mencari total penjualan bulan lalu dan bulan ini string sql = "SELECT MONTH(a.tanggal) AS bulan, SUM(a.jumlah * a.harga_satuan) AS total_penjualan " + "FROM(SELECT OP.id, OP.tanggal, OP.unit, B.Kode, B.nama, B.jumlah, B.satuan, B.harga_satuan, B.keterangan " + "FROM order_penjualan OP " + "INNER JOIN Barang B ON OP.id = B.id_order_penjualan) a " + "WHERE MONTH(a.tanggal) = '" + pBulan + "' OR MONTH(a.tanggal) = '" + (pBulan - 1) + "' " + "GROUP BY bulan ORDER BY bulan"; try { MySqlDataReader hasilData = Koneksi.JalankanPerintahQuery(sql); listHasilData.Clear(); while (hasilData.Read() == true) { OrderPenjualan op = new OrderPenjualan(); op.NoOrder = hasilData.GetValue(0).ToString(); // bulan Barang b = new Barang(); b.HargaSatuan = int.Parse(hasilData.GetValue(1).ToString()); // total penjualan op.TambahBarang(b); listHasilData.Add(op); } return("1"); } catch (MySqlException exc) { return(exc.Message + ". Perintah sql : " + sql); } }
public static string BacaData(string pKriteria, string pNilaiKriteria, List <OrderPenjualan> listHasilData) { string sql = ""; if (pKriteria == "") { sql = "SELECT OP.Id, OP.Tanggal, C.Id, C.Nama, OP.Unit, B.Kode, B.Nama, B.Jumlah, B.Satuan, B.harga_satuan, B.keterangan" + " FROM order_penjualan OP INNER JOIN Customer C ON OP.id_customer = C.id" + " INNER JOIN Barang B ON OP.id = B.id_order_penjualan"; } else { sql = "SELECT OP.Id, OP.Tanggal, C.Id, C.Nama, OP.Unit, B.Kode, B.Nama, B.Jumlah, B.Satuan, B.harga_satuan, B.keterangan" + " FROM order_penjualan OP INNER JOIN Customer C ON OP.id_customer = C.id" + " INNER JOIN Barang B ON OP.id = B.id_order_penjualan" + " WHERE " + pKriteria + " LIKE '%" + pNilaiKriteria + "%'"; } try { MySqlDataReader hasilData = Koneksi.JalankanPerintahQuery(sql); listHasilData.Clear(); while (hasilData.Read() == true) { OrderPenjualan op = new OrderPenjualan(); op.NoOrder = hasilData.GetValue(0).ToString(); op.Tanggal = DateTime.Parse(hasilData.GetValue(1).ToString()); op.Unit = hasilData.GetValue(4).ToString(); Customer c = new Customer(); c.IdCustomer = int.Parse(hasilData.GetValue(2).ToString()); c.Nama = hasilData.GetValue(3).ToString(); op.Customer = c; Barang b = new Barang(); b.Kode = hasilData.GetValue(5).ToString(); b.Nama = hasilData.GetValue(6).ToString(); b.Jumlah = int.Parse(hasilData.GetValue(7).ToString()); b.Satuan = hasilData.GetValue(8).ToString(); b.HargaSatuan = int.Parse(hasilData.GetValue(9).ToString()); b.Keterangan = hasilData.GetValue(10).ToString(); op.TambahBarang(b); listHasilData.Add(op); } return("1"); } catch (MySqlException exc) { return(exc.Message + ". Perintah sql : " + sql); } }