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);
}
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 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);
}
public ProductDTO GetProductById(int id)
{
using (var db = new ProductDAL())
{
var product = db.tblProducts.FirstOrDefault(p => p.productId == id);
return(_mapper.Map <ProductDTO>(product));
}
}
public List <ProductDTO> GetProducts()
{
using (var db = new ProductDAL())
{
var prods = db.tblProducts.ToList();
return(_mapper.Map <List <ProductDTO> >(prods));
}
}
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);
}
/// <summary>
/// 拆分产品信息--报单信息
/// 注：把组合产品拆分成单品
/// </summary>
/// <param name="ods">原产品信息</param>
/// <returns>拆分后产品信息</returns>
public static IList <MemberDetailsModel> GetNewOrderDetail(IList <MemberDetailsModel> ods)
{
IList <MemberDetailsModel> orderdetails = new List <MemberDetailsModel>();
foreach (MemberDetailsModel od in ods)
{
if (ProductDAL.GetIsCombine(od.ProductId))
{
IList <ProductCombineDetailModel> comDetails = ProductCombineDetailDAL.GetCombineDetil(od.ProductId);
foreach (ProductCombineDetailModel comDetail in comDetails)
{
int count = 0;
foreach (MemberDetailsModel detail in orderdetails)
{
if (detail.ProductId == comDetail.SubProductID)
{
detail.Quantity = (comDetail.Quantity * od.Quantity) + detail.Quantity;
//detail.NotEnoughProduct = (comDetail.Quantity * od.NotEnoughProduct) + detail.NotEnoughProduct;
count++;
}
}
if (count == 0)
{
MemberDetailsModel orderdetail = new MemberDetailsModel();
orderdetail.Quantity = comDetail.Quantity * od.Quantity;
orderdetail.ProductId = comDetail.SubProductID;
//orderdetail.NotEnoughProduct = comDetail.Quantity * od.NotEnoughProduct;
orderdetails.Add(orderdetail);
}
}
}
else
{
int count = 0;
foreach (MemberDetailsModel detail in orderdetails)
{
if (detail.ProductId == od.ProductId)
{
detail.Quantity = od.Quantity + detail.Quantity;
//detail.NotEnoughProduct = od.NotEnoughProduct + detail.NotEnoughProduct;
count++;
}
}
if (count == 0)
{
MemberDetailsModel orderdetail = new MemberDetailsModel();
orderdetail.Quantity = od.Quantity;
//orderdetail.NotEnoughProduct = od.NotEnoughProduct;
orderdetail.ProductId = od.ProductId;
orderdetails.Add(orderdetail);
}
}
}
return(orderdetails);
}
public void AddProduct(Product product)
{
using var _context = new Project0Context(_options);
ProductDAL newProduct = new ProductDAL
{
Name = product.Name,
Price = Convert.ToDecimal(product.Price)
};
_context.Add(newProduct);
_context.SaveChanges();
}
public void AddToInventory(Product product, Store store, int quantity)
{
using var _context = new Project0Context(_options);
//Check store and product exist
StoreDAL s = _context.Stores.Find(store.ID);
ProductDAL p = _context.Products.Find(product.ID);
if (s == null || p == null)
{
throw new Exception("Product or store does not exist in database");
}
StoreItemDAL newInventoryItem = new StoreItemDAL
{
//Id is auto incrementing, so no need to instantiate one here
StoreId = store.ID,
ProductId = product.ID,
Quantity = quantity
};
_context.Add(newInventoryItem);
_context.SaveChanges();
}