public static int Create(Export ticket)
{
DAL.ConnectDb();
string ticketQuery =
$"INSERT INTO {table} (staffId, date, receiptId) VALUES (@staffId, @date, @receiptId)";
SQLiteCommand ticketCommand = new SQLiteCommand(ticketQuery, DAL.Conn);
ticketCommand.Parameters.AddWithValue("@staffId", ticket.Staff.Id);
ticketCommand.Parameters.AddWithValue("@date", ticket.Date);
ticketCommand.Parameters.AddWithValue("@receiptId", ticket.Receipt.Id);
ticketCommand.ExecuteNonQuery();
ticket.Id = Convert.ToInt32(DAL.Conn.LastInsertRowId);
foreach (TicketDetails detail in ticket.Details)
{
String detailQuery = $"INSERT INTO {subTable} (ticketId, productId, amount) VALUES (@ticketId, @productId, @amount)";
SQLiteCommand detailCommand = new SQLiteCommand(detailQuery, DAL.Conn);
detailCommand.Parameters.AddWithValue("@ticketId", ticket.Id);
detailCommand.Parameters.AddWithValue("@productId", detail.Product.Id);
detailCommand.Parameters.AddWithValue("@amount", detail.Amount);
detailCommand.ExecuteNonQuery();
Product product = ProductDAL.GetById(detail.Product.Id);
product.Amount -= detail.Amount;
ProductDAL.Update(product);
}
return(ticket.Id);
}
private static Export extractData(SQLiteDataReader reader)
{
Export ticket = new Export();
ticket.Id = reader.GetInt32(0);
ticket.Staff = StaffDAL.GetById(reader.GetInt32(1));
ticket.Date = reader.GetString(2);
ticket.Receipt = ReceiptDAL.GetById(reader.GetInt32(3));
string detailsQuery = $"SELECT * FROM {subTable} WHERE ticketId = @ticketId";
SQLiteCommand detailsCommand = new SQLiteCommand(detailsQuery, DAL.Conn);
detailsCommand.Parameters.AddWithValue("@ticketId", ticket.Id);
SQLiteDataReader detailsReader = detailsCommand.ExecuteReader();
while (detailsReader.HasRows)
{
while (detailsReader.Read())
{
TicketDetails details = new TicketDetails();
details.Ticket = ticket;
details.Product = ProductDAL.GetById(detailsReader.GetInt32(1));
details.Amount = detailsReader.GetInt32(2);
ticket.Details.Add(details);
}
detailsReader.NextResult();
}
return(ticket);
}
private static Combo extractData(SQLiteDataReader reader)
{
Combo combo = new Combo();
combo.Id = reader.GetInt32(0);
combo.Name = reader.GetString(1);
combo.Discount = reader.GetInt32(2);
string detailsQuery = $"SELECT * FROM {subTable} WHERE comboId = @comboId";
SQLiteCommand detailsCommand = new SQLiteCommand(detailsQuery, DAL.Conn);
detailsCommand.Parameters.AddWithValue("@comboId", combo.Id);
SQLiteDataReader detailsReader = detailsCommand.ExecuteReader();
while (detailsReader.HasRows)
{
while (detailsReader.Read())
{
ComboDetails details = new ComboDetails();
details.Combo = combo;
details.Product = ProductDAL.GetById(detailsReader.GetInt32(1));
details.Amount = detailsReader.GetInt32(2);
combo.Details.Add(details);
}
detailsReader.NextResult();
}
return(combo);
}
private static bool CheckAmount(Receipt receipt)
{
foreach (ReceiptDetails detail in receipt.Details)
{
Product p = ProductDAL.GetById(detail.Product.Id);
if (p == null)
{
return(false);
}
if (p.Amount < detail.Amount)
{
return(false);
}
}
foreach (ReceiptCombos detail in receipt.Combos)
{
foreach (ComboDetails comboDetails in detail.Combo.Details)
{
if (comboDetails.Product.Amount < comboDetails.Amount * detail.Amount)
{
return(false);
}
}
}
return(true);
}
private static Receipt extractData(SQLiteDataReader reader)
{
Receipt receipt = new Receipt();
receipt.Id = reader.GetInt32(0);
receipt.Recipient = reader.GetString(1);
receipt.Address = reader.GetString(2);
receipt.Phone = reader.GetString(3);
receipt.Status = reader.GetInt32(4);
receipt.Date = reader.GetString(5);
receipt.Customer = CustomerDAL.GetById(reader.GetInt32(6));
string detailsQuery = $"SELECT * FROM {detailsTable} WHERE receiptId = @receiptId";
SQLiteCommand detailsCommand = new SQLiteCommand(detailsQuery, DAL.Conn);
detailsCommand.Parameters.AddWithValue("@receiptId", receipt.Id);
SQLiteDataReader detailsReader = detailsCommand.ExecuteReader();
while (detailsReader.HasRows)
{
while (detailsReader.Read())
{
ReceiptDetails details = new ReceiptDetails();
details.Receipt = receipt;
details.Product = ProductDAL.GetById(detailsReader.GetInt32(1));
details.Amount = detailsReader.GetInt32(2);
receipt.Details.Add(details);
}
detailsReader.NextResult();
}
string combosQuery = $"SELECT * FROM {combosTable} WHERE receiptId = @receiptId";
SQLiteCommand combosCommand = new SQLiteCommand(combosQuery, DAL.Conn);
combosCommand.Parameters.AddWithValue("@receiptId", receipt.Id);
SQLiteDataReader combosReader = combosCommand.ExecuteReader();
while (combosReader.HasRows)
{
while (combosReader.Read())
{
ReceiptCombos combos = new ReceiptCombos();
combos.Receipt = receipt;
combos.Combo = ComboDAL.GetById(combosReader.GetInt32(1));
combos.Amount = combosReader.GetInt32(2);
receipt.Combos.Add(combos);
}
combosReader.NextResult();
}
return(receipt);
}