public Akun GetDataCustomerByUsername(string username)
{
Akun result = null;
try
{
using (SqlCommand cmd = new SqlCommand(@"select * from akun Where username = @username", _conn))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@username", username);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
if (reader.Read())
{
result = new Akun
{
Username = reader["username"].ToString(),
Nama = reader["Nama"].ToString(),
Password = reader["Password"].ToString(),
Total = Convert.ToDecimal(reader["Total"]),
Pict = reader["Pict"] as byte[]
};
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public List <Penjualan> SejarahPenjualan(Akun akun = null, string connString = null, string noOrder = null)
{
List <Penjualan> listSejarahPenjualan = null;
string sqlString = @"select p.NoOrder, p.Tanggal, p.Username, p.Kode, p.Quantity, p.Total from penjualan p inner join akun a on a.username = p.Username inner join barang b on b.Kode = p.Kode";
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = _conn;
if (akun != null)
{
sqlString += " where p.username = @username";
}
if (noOrder != null)
{
sqlString += " where p.noOrder like @noOrder";
}
cmd.CommandText = sqlString;
cmd.Parameters.Clear();
if (akun != null)
{
cmd.Parameters.AddWithValue("@username", akun.Username);
}
if (noOrder != null)
{
cmd.Parameters.AddWithValue("@noOrder", $"%{noOrder}%");
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
listSejarahPenjualan = new List <Penjualan>();
if (reader.HasRows)
{
while (reader.Read())
{
listSejarahPenjualan.Add(new Penjualan
{
DataAkun = new AkunDAO(connString).GetDataCustomerByUsername(reader["username"].ToString()),
DataBarang = new BarangDAO(connString).GetDataBarangByKode(reader["kode"].ToString()),
NoOrder = reader["noOrder"].ToString(),
Quantity = int.Parse(reader["quantity"].ToString()),
Tanggal = Convert.ToDateTime(reader["Tanggal"].ToString()),
Total = Convert.ToDecimal(reader["Total"])
});
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listSejarahPenjualan);
}
public List <Akun> GetAllDataAccount(Akun akn = null)
{
List <Akun> listData = null;
string sqlString = @"select * from akun";
try
{
using (SqlCommand cmd = new SqlCommand())
{
if (akn != null)
{
sqlString += " where nama like @nama and username like @username";
}
sqlString += " order by username";
cmd.CommandText = sqlString;
cmd.Connection = _conn;
if (akn != null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@nama", $"%{akn.Nama}%");
cmd.Parameters.AddWithValue("@username", $"%{akn.Username}%");
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
listData = new List <Akun>();
while (reader.Read())
{
listData.Add(new Akun
{
Username = reader["username"].ToString(),
Nama = reader["Nama"].ToString(),
Password = reader["Password"].ToString(),
Total = Convert.ToDecimal(reader["Total"]),
Pict = reader["Pict"] as byte []
});
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(listData);
}
public void UpdateTotal(Akun temp, decimal total)
{
try
{
using (SqlCommand cmd = new SqlCommand(@"update akun set total = @total where username = @username", _conn))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@total", temp.Total += total);
cmd.Parameters.AddWithValue("@username", temp.Username);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
}
public int AddAkun(Akun akun)
{
int result = 0;
SqlTransaction trans = null;
try
{
trans = _conn.BeginTransaction();
using (SqlCommand cmd = new SqlCommand(@"insert into akun values (@username, @nama, @password, @total, @pict)", _conn, trans))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@username", akun.Username);
cmd.Parameters.AddWithValue("@nama", akun.Nama);
cmd.Parameters.AddWithValue("@password", akun.Password);
cmd.Parameters.AddWithValue("@total", akun.Total);
cmd.Parameters.AddWithValue("@pict", akun.Pict);
result = cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
if (trans != null)
{
trans.Rollback();
}
throw ex;
}
finally
{
if (trans != null)
{
trans.Dispose();
}
}
return(result);
}
