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