/// <summary>
/// Insere histórico
/// </summary>
/// <param name="history"></param>
public void Inserir(History history)
{
using (var bd = new BDEngine())
{
sql.Clear();
sql.Append(@"
INSERT INTO History(card_number, client_id, value, order_id)
VALUES('{0}', '{1}', {2}, '{3}')
");
bd.ExecuteNonQuery(string.Format(sql.ToString(),
history.card_number,
history.client_id,
history.value,
history.order_id));
}
}
/// <summary>
/// Registra o cartão de crédito informado na transação
/// </summary>
/// <param name="creditCard"></param>
public void Inserir(CreditCard creditCard)
{
using (var bd = new BDEngine())
{
sql.Clear();
sql.Append(@"
INSERT INTO CreditCard(card_number, card_holder_name, cvv, exp_date)
VALUES ('{0}', '{1}', {2}, '{3}')
");
bd.ExecuteNonQuery(string.Format(sql.ToString(),
creditCard.card_number,
creditCard.card_holder_name,
creditCard.cvv,
creditCard.exp_date));
}
}
/// <summary>
/// Insere um item ao carrinho
/// </summary>
/// <param name="cart"></param>
public void Inserir(Cart cart)
{
using (var bd = new BDEngine())
{
sql.Clear();
sql.Append(@"
INSERT INTO Cart(cart_id, client_id, product_id, date, time)
VALUES('{0}', '{1}', '{2}', '{3}', '{4}')
");
bd.ExecuteNonQuery(string.Format(sql.ToString(),
cart.cart_id,
cart.client_id,
cart.product_id,
cart.date,
cart.time));
}
}
/// <summary>
/// Obter cartão de crédito por número
/// </summary>
/// <param name="creditCard"></param>
/// <returns></returns>
public Boolean GetCreditCard(String cardNumber)
{
using (var bd = new BDEngine())
{
sql.Append(@"
SELECT card_number
FROM CreditCard c(nolock)
WHERE c.card_number = '{0}'
");
object CreditCard = bd.ExecuteScalar(string.Format(sql.ToString(), cardNumber));
if (CreditCard != null)
{
return(true);
}
}
return(false);
}
/// <summary>
/// Retorna as compras efetuadas por cliente
/// </summary>
/// <returns></returns>
public List <History> ListarPorId(String clientId)
{
List <History> lista = new List <History>();
using (var bd = new BDEngine())
{
sql.Clear();
sql.Append(@"
select
h.client_id,
h.order_id,
o.credit_card,
o.total_to_pay,
o.date
from History h(nolock)
inner join Orders o(nolock) on(o.order_id = h.order_id)
where h.client_id = '{0}'
order by o.date
");
var dr = bd.ObterReader(string.Format(sql.ToString(), clientId));
if (dr.HasRows)
{
while (dr.Read())
{
History history = new History();
history.card_number = dr["credit_card"].ToString();
history.client_id = dr["client_id"].ToString();
history.value = Convert.ToInt32(dr["total_to_pay"]);
history.order_id = dr["order_id"].ToString();
history.date = dr["date"].ToString();
lista.Add(history);
}
}
}
return(lista);
}
/// <summary>
/// Insere uma nova transação de compra
/// </summary>
/// <param name="transaction"></param>
/// <param name="orderId"></param>
public void Inserir(Transaction transaction)
{
using (var bd = new BDEngine())
{
sql.Clear();
sql.Append(@"
INSERT INTO Orders(client_id, cart_id, client_name, total_to_pay, credit_card, order_id, date)
VALUES('{0}', '{1}', '{2}', {3}, '{4}', '{5}', '{6}')
");
bd.ExecuteNonQuery(string.Format(sql.ToString(),
transaction.client_id,
transaction.cart_id,
transaction.client_name,
transaction.total_to_pay,
transaction.credit_card,
transaction.order_id,
transaction.date
));
}
}
/// <summary>
/// Retorna um produto por id
/// </summary>
/// <param name="productId"></param>
/// <returns></returns>
public bool ProdutoPorId(String productId)
{
using (var bd = new BDEngine())
{
sql.Clear();
sql.Append(@"
SELECT product_id
FROM Product
WHERE product_id = '{0}'
");
var obj = bd.ExecuteScalar(string.Format(sql.ToString(), productId));
if (obj != null)
{
return(true);
}
}
return(false);
}
/// <summary>
/// Insere um novo produto
/// </summary>
/// <param name="product"></param>
public void Inserir(Product product)
{
using (var bd = new BDEngine())
{
sql.Clear();
sql.Append(@"
INSERT INTO Product(product_id, artist, year, album, price, store, thumb, date)
VALUES('{0}', '{1}', {2}, '{3}', {4}, '{5}', '{6}', '{7}')
");
bd.ExecuteNonQuery(string.Format(sql.ToString(),
product.product_id,
product.artist,
product.year,
product.album,
product.price,
product.store,
product.thumb,
product.date));
}
}
/// <summary>
/// Lista todos os produtos
/// </summary>
/// <returns></returns>
public List <Product> Listar()
{
List <Product> lista = new List <Product>();
using (var bd = new BDEngine())
{
sql.Clear();
sql.Append(@"
SELECT product_id, artist, year, album, price, store, thumb, date
FROM Product
ORDER BY album DESC
");
var dr = bd.ObterReader(sql.ToString());
if (dr.HasRows)
{
while (dr.Read())
{
Product product = new Product();
product.product_id = dr["product_id"].ToString();
product.artist = dr["artist"].ToString();
product.year = Convert.ToInt32(dr["year"]);
product.album = dr["album"].ToString();
product.price = Convert.ToInt32(dr["price"]);
product.store = dr["store"].ToString();
product.thumb = dr["thumb"].ToString();
product.date = dr["date"].ToString();
lista.Add(product);
}
}
}
return(lista);
}