public List <VBill> QueryServing(int tableID)
{
using (var db = new FrontDeskEntities())
{
var servingTable = db.ServingTable.FirstOrDefault(t => t.ID == tableID);
if (servingTable == null)
{
return(null);
}
return(db.VBill.Where(b => b.BillID == servingTable.BillID).ToList());
}
}
public DBResult Pay(BillViewResult billViewResult)
{
using (var db = new FrontDeskEntities())
{
var bill = db.Bill.FirstOrDefault(b => b.ID == billViewResult.ID);
if (bill == null)
{
return(DBResult.NotFound);
}
EntityHelper.CopyEntity(billViewResult, bill);
var servingTable = db.ServingTable.FirstOrDefault(t => t.ID == billViewResult.TableID);
if (servingTable == null)
{
return(DBResult.NotFound);
}
db.ServingTable.Remove(servingTable);
db.SaveChanges();
return(DBResult.Succeed);
}
}
public List <double>[] GetMoneyData(DateTime dateMin, DateTime dateMax)
{
dateMax = dateMax.AddDays(1);
var results = new List <double>[3] {
new List <double>(), new List <double>(), new List <double>()
};
using (var frontDeskEntities = new FrontDeskEntities())
{
using (var warehouseEntities = new WarehouseEntities())
{
var bills = frontDeskEntities.Bill.Where(b => b.Date >= dateMin && b.Date < dateMax).ToList();
for (DateTime date = dateMin; date < dateMax;)
{
double moneyResult = 0;
double discountResult = 0;
double costResult = 0;
DateTime dateNext = date.AddDays(1);
var billsByDay = bills.Where(b => b.Date >= date && b.Date < dateNext);
foreach (var bill in billsByDay)
{
double discount = 100;
if (bill.Discount != null)
{
discount = bill.Discount.Value;
}
double payPercent = discount / 100;
double discountPercent = (100 - discount) / 100;
var details = frontDeskEntities.BillDetails.Where(d => d.POID != null && d.WarehouseID != null);
if (details.Count() == 0)
{
continue;
}
foreach (var detail in details)
{
var dish = frontDeskEntities.Dish.FirstOrDefault(d => d.Name == detail.DishName);
if (dish == null)
{
continue;
}
double totalPay = (double)dish.Price * detail.Amount;
moneyResult += totalPay * payPercent;
discountResult += totalPay * discountPercent;
if (!dish.InventoryControl || dish.UnitConversion == null)
{
continue;
}
var purchaseOrder = warehouseEntities.PODetails.FirstOrDefault(d => d.ID == detail.POID);
if (purchaseOrder == null)
{
continue;
}
costResult += (double)purchaseOrder.Price * detail.Amount * dish.UnitConversion.Value;
}
}
results[0].Add(Math.Round(moneyResult, 2));
results[1].Add(Math.Round(discountResult, 2));
results[2].Add(Math.Round(costResult, 2));
date = dateNext;
}
}
}
return(results);
}
public List <double>[] GetSingleData(string dishName, DateTime dateMin, DateTime dateMax)
{
dateMax = dateMax.AddDays(1);
var results = new List <double>[3] {
new List <double>(), new List <double>(), new List <double>()
};
using (var frontDeskEntities = new FrontDeskEntities())
{
using (var warehouseEntities = new WarehouseEntities())
{
var dish = frontDeskEntities.Dish.FirstOrDefault(d => d.Name == dishName);
if (dish == null)
{
return(results);
}
var bills = frontDeskEntities.Bill.Where(b => b.Date >= dateMin && b.Date < dateMax).ToList();
for (DateTime date = dateMin; date < dateMax;)
{
double amountResult = 0;
double moneyResult = 0;
double costResult = 0;
DateTime dateNext = date.AddDays(1);
var billsByDay = bills.Where(b => b.Date >= date && b.Date < dateNext);
foreach (var bill in billsByDay)
{
var details = frontDeskEntities.BillDetails.Where(d => d.OrderID == bill.ID && d.DishName == dishName);
if (details.Count() == 0)
{
continue;
}
amountResult += details.Sum(d => d.Amount);
double discount = 100;
if (bill.Discount != null)
{
discount = bill.Discount.Value;
}
moneyResult += details.Sum(d => (double)dish.Price * d.Amount * (discount / 100));
if (!dish.InventoryControl || dish.UnitConversion == null)
{
costResult = -1;
continue;
}
foreach (var detail in details)
{
var purchaseOrder = warehouseEntities.PODetails.FirstOrDefault(d => d.ID == detail.POID);
if (purchaseOrder == null)
{
continue;
}
costResult += (double)purchaseOrder.Price * detail.Amount * dish.UnitConversion.Value;
}
}
results[0].Add(amountResult);
results[1].Add(moneyResult);
results[2].Add(costResult);
date = dateNext;
}
}
}
return(results);
}
public DBResult Discount(BillViewResult billViewResult, out BillViewResult newBillViewResult)
{
newBillViewResult = new BillViewResult();
using (var frontDeskEntities = new FrontDeskEntities())
{
using (var warehouseEntities = new WarehouseEntities())
{
if (string.IsNullOrEmpty(billViewResult.DiscountType))
{
billViewResult.ExactCost = billViewResult.TotalCost;
}
else
{
var discount = frontDeskEntities.Discount.FirstOrDefault(d => d.Description == billViewResult.DiscountType);
if (discount == null)
{
return(DBResult.WrongParameter);
}
billViewResult.Discount = discount.DiscountPercent;
if (!string.IsNullOrEmpty(discount.IgnoredCategories))
{
billViewResult.ExactCost = 0;
var ignoredCategories = discount.IgnoredCategories.Split('，');
var detailsViewResults = frontDeskEntities.BillDetails.Where(d => d.OrderID == billViewResult.ID);
foreach (var detailsViewResult in detailsViewResults)
{
var dish = frontDeskEntities.Dish.FirstOrDefault(d => d.Name == detailsViewResult.DishName);
if (dish == null)
{
return(DBResult.NotFound);
}
var category = warehouseEntities.Category.FirstOrDefault(c => c.ID == dish.CategoryID);
if (category == null)
{
return(DBResult.WrongParameter);
}
var amount = decimal.Parse(detailsViewResult.Amount.ToString());
if (ignoredCategories.Contains(category.Name))
{
billViewResult.ExactCost += dish.Price * amount;
}
else
{
billViewResult.ExactCost += (dish.Price * amount) * discount.DiscountPercent / 100;
}
}
}
else
{
billViewResult.ExactCost = billViewResult.TotalCost * discount.DiscountPercent / 100;
}
}
var bill = frontDeskEntities.Bill.FirstOrDefault(b => b.ID == billViewResult.ID);
if (bill == null)
{
return(DBResult.NotFound);
}
EntityHelper.CopyEntity(billViewResult, bill);
frontDeskEntities.SaveChanges();
}
}
newBillViewResult = billViewResult;
return(DBResult.Succeed);
}
public DBResult Add(BillViewResult billViewResult)
{
int detailsCount = 1;
var detailsForeID = BuilderDictionary.ForeIDDictionary.FirstOrDefault(i => i.Key == typeof(BillDetailsViewResult)).Value;
var nowDate = DateTime.Now;
using (var frontDeskEntities = new FrontDeskEntities())
{
using (var warehouseEntities = new WarehouseEntities())
{
foreach (var detailsViewResult in billViewResult.Details)
{
detailsViewResult.ID = detailsForeID + nowDate.ToString("yyyyMMddHHmmss") + detailsCount;
detailsViewResult.OrderID = billViewResult.ID;
detailsCount++;
var details = EntityHelper.CopyEntity(detailsViewResult, new BillDetails());
if (EntityHelper.HasNullProperty(details, new string[] { "WarehouseID", "POID" }))
{
return(DBResult.WrongParameter);
}
var detailsExistResult = CheckExists(frontDeskEntities.BillDetails, new string[] { "ID" }, details);
if (detailsExistResult != DBResult.Succeed)
{
return(detailsExistResult);
}
var dish = frontDeskEntities.Dish.FirstOrDefault(d => d.Name == details.DishName);
if (dish == null)
{
return(DBResult.NotFound);
}
var amount = decimal.Parse(detailsViewResult.Amount.ToString());
billViewResult.TotalCost += dish.Price * amount;
if (dish.InventoryControl)
{
var product = warehouseEntities.Product.FirstOrDefault(p => p.Name == dish.Name);
if (product == null)
{
return(DBResult.NotFound);
}
var inventoryList = warehouseEntities.InventoryList
.Where(l => l.ProductID == product.ID).OrderBy(l => l.POID).OrderBy(l => l.WarehouseID).FirstOrDefault(l => l.Amount >= 0);
if (inventoryList == null)
{
return(DBResult.InventoryEmpty);
}
var unitConversion = dish.UnitConversion;
if (unitConversion == null)
{
return(DBResult.NotFound);
}
var consumedAmount = details.Amount * unitConversion.Value;
if (inventoryList.Amount - consumedAmount < 0)
{
return(DBResult.InventoryEmpty);
}
inventoryList.Amount -= consumedAmount;
details.WarehouseID = inventoryList.WarehouseID;
details.POID = inventoryList.POID;
}
frontDeskEntities.BillDetails.Add(details);
}
billViewResult.ExactCost = billViewResult.TotalCost;
var bill = EntityHelper.CopyEntity(billViewResult, new Bill());
if (EntityHelper.HasNullProperty(bill, new string[] { "TotalCost", "Discount", "DiscountType", "ExactCost", "Remark", "MemberID", "ReceivedMoney", "Change" }))
{
return(DBResult.WrongParameter);
}
var billExistResult = CheckExists(frontDeskEntities.Bill, new string[] { "ID" }, bill);
if (billExistResult != DBResult.Succeed)
{
return(billExistResult);
}
frontDeskEntities.Bill.Add(bill);
if (frontDeskEntities.ServingTable.Any(t => t.ID == bill.TableID))
{
return(DBResult.TableExisted);
}
frontDeskEntities.ServingTable.Add(new ServingTable {
ID = bill.TableID, BillID = bill.ID
});
frontDeskEntities.SaveChanges();
warehouseEntities.SaveChanges();
return(DBResult.Succeed);
}
}
}
private List <MonthlyReportViewResult> CalculateMonthlyReport(DateTime dateStart, DateTime?dateEnd = null)
{
int reportCount = 1;
int productCount = 1;
if (dateEnd == null)
{
dateEnd = dateStart.Month == 12 ? new DateTime(dateStart.Year + 1, 1, 1) : new DateTime(dateStart.Year, dateStart.Month + 1, 1);
}
string reportForeID = BuilderDictionary.ForeIDDictionary.FirstOrDefault(p => p.Key == typeof(MonthlyReportViewResult)).Value;
string detailsForeID = BuilderDictionary.ForeIDDictionary.FirstOrDefault(p => p.Key == typeof(MReportDetailsViewResult)).Value;
var reports = new List <MonthlyReportViewResult>();
using (var reportEntities = new ReportEntities())
{
using (var warehouseEntities = new WarehouseEntities())
{
using (var frontDeskEntities = new FrontDeskEntities())
{
var startOfTerms = reportEntities.StartOfTerm.Where(s => s.Date == dateStart);
var vPurchaseOrders = reportEntities.VPurchaseOrder.Where(o => o.Date >= dateStart && o.Date < dateEnd);
var vCreditOrders = reportEntities.VCreditOrder.Where(o => o.Date >= dateStart && o.Date < dateEnd);
var vMaterialsRequisitions = reportEntities.VMaterialsRequisition.Where(o => o.Date >= dateStart && o.Date < dateEnd);
var vMaterialsReturnOrders = reportEntities.VMaterialsReturnOrder.Where(o => o.Date >= dateStart && o.Date < dateEnd);
var vStockingLists = reportEntities.VStockingList.Where(o => o.Date >= dateStart && o.Date < dateEnd);
var vBills = frontDeskEntities.VBill.Where(o => o.Date >= dateStart && o.Date < dateEnd);
var warehouses = warehouseEntities.Warehouse.ToList();
var products = warehouseEntities.Product.ToList();
foreach (var warehouse in warehouses)
{
var viewResult = new MonthlyReportViewResult
{
ID = reportForeID + dateStart.ToString("yyyyMMddHHmmss") + reportCount,
WarehouseName = warehouse.Name,
Date = dateStart,
Details = new List <MReportDetailsViewResult>()
};
reportCount++;
foreach (var product in products)
{
var productStart = startOfTerms.FirstOrDefault(s => s.ProductName == product.Name && s.WarehouseName == warehouse.Name);
var productPurchaseOrders = vPurchaseOrders.Where(o => o.ProductName == product.Name && o.WarehouseName == warehouse.Name);
if (productStart == null && productPurchaseOrders.Count() == 0)
{
continue;
}
var productEnd = warehouseEntities.VInventoryList.Where(l => l.ProductName == product.Name && l.WarehouseName == warehouse.Name);
double productEndAmount = productEnd.Count() == 0 ? 0 : productEnd.Sum(l => l.Amount);
var detailsViewResult = new MReportDetailsViewResult
{
ID = detailsForeID + dateStart.ToString("yyyyMMddHHmmss") + productCount,
OrderID = viewResult.ID,
ProductName = product.Name,
PurchasePrice = 0,
PurchaseAmount = 0,
PurchaseTotalPrice = 0,
CreditPrice = 0,
CreditAmount = 0,
CreditTotalPrice = 0,
MRequisitionPrice = 0,
MRequisitionAmount = 0,
MRequisitionTotalPrice = 0,
MReturnPrice = 0,
MReturnAmount = 0,
MReturnTotalPrice = 0,
SListPrice = 0,
SListAmount = 0,
SListTotalPrice = 0,
StartOfTerm = EntityHelper.CopyEntity(productStart, new StartOfTermViewResult())
};
productCount++;
var productCreditOrders = vCreditOrders
.Where(o => o.ProductName == product.Name && o.WarehouseName == warehouse.Name).ToList();
var productMaterialsRequisitions = vMaterialsRequisitions
.Where(o => o.ProductName == product.Name && o.WarehouseName == warehouse.Name).ToList();
var productMaterialsReturnOrders = vMaterialsReturnOrders
.Where(o => o.ProductName == product.Name && o.WarehouseName == warehouse.Name).ToList();
var productStockingLists = vStockingLists
.Where(o => o.ProductName == product.Name && o.WarehouseName == warehouse.Name).ToList();
if (productPurchaseOrders != null)
{
double?totalPrice = productPurchaseOrders.Sum(o => o.TotalPrice);
double amount = productPurchaseOrders.Sum(o => o.Amount);
if (totalPrice != null && amount != 0)
{
detailsViewResult.PurchaseTotalPrice = (decimal)totalPrice.Value;
detailsViewResult.PurchaseAmount = amount;
detailsViewResult.PurchasePrice = (decimal)(totalPrice.Value / amount);
}
}
if (productMaterialsRequisitions != null)
{
double?totalPrice = productMaterialsRequisitions.Sum(o => o.TotalPrice);
double amount = productMaterialsRequisitions.Sum(o => o.Amount);
if (totalPrice != null && amount != 0)
{
detailsViewResult.MRequisitionTotalPrice = (decimal)totalPrice.Value;
detailsViewResult.MRequisitionAmount = amount;
detailsViewResult.MRequisitionPrice = (decimal)(totalPrice.Value / amount);
}
}
if (productMaterialsReturnOrders != null)
{
double?totalPrice = productMaterialsReturnOrders.Sum(o => o.TotalPrice);
double amount = productMaterialsReturnOrders.Sum(o => o.Amount);
if (totalPrice != null && amount != 0)
{
detailsViewResult.MReturnTotalPrice = (decimal)totalPrice.Value;
detailsViewResult.MReturnAmount = amount;
detailsViewResult.MReturnPrice = (decimal)(totalPrice.Value / amount);
}
}
if (productCreditOrders != null)
{
double?totalPrice = productCreditOrders.Sum(o => o.TotalPrice);
double amount = productCreditOrders.Sum(o => o.Amount);
if (totalPrice != null && amount != 0)
{
detailsViewResult.CreditTotalPrice = (decimal)totalPrice.Value;
detailsViewResult.CreditAmount = amount;
detailsViewResult.CreditPrice = (decimal)(totalPrice.Value / amount);
}
}
if (productCreditOrders != null)
{
double?totalPrice = productCreditOrders.Sum(o => o.TotalPrice);
double amount = productCreditOrders.Sum(o => o.Amount);
if (totalPrice != null && amount != 0)
{
detailsViewResult.CreditTotalPrice = (decimal)totalPrice.Value;
detailsViewResult.CreditAmount = amount;
detailsViewResult.CreditPrice = (decimal)(totalPrice.Value / amount);
}
}
if (productStockingLists != null)
{
double?totalPrice = productStockingLists.Sum(o => o.TotalPrice);
double amount = productStockingLists.Sum(o => o.Amount);
if (totalPrice != null && amount != 0)
{
detailsViewResult.SListTotalPrice = (decimal)totalPrice.Value;
detailsViewResult.SListAmount = amount;
detailsViewResult.SListPrice = (decimal)(totalPrice.Value / amount);
}
}
var dish = frontDeskEntities.Dish.FirstOrDefault(d => d.Name == product.Name && d.InventoryControl == true);
if (dish != null && dish.UnitConversion != null)
{
double unitConversion = dish.UnitConversion.Value;
var dishBills = vBills.Where(b => b.DishName == dish.Name);
foreach (var dishBill in dishBills)
{
var dishDetails = frontDeskEntities.BillDetails.FirstOrDefault(d => d.ID == dishBill.DetailsID);
if (dishDetails == null)
{
continue;
}
decimal productPrice = warehouseEntities.PODetails.FirstOrDefault(d => d.ID == dishDetails.POID).Price;
double productAmount = dishBill.Amount * unitConversion;
detailsViewResult.BillTotalPrice += productPrice * (decimal)productAmount;
detailsViewResult.BillAmount += productAmount;
}
if (detailsViewResult.BillAmount != 0)
{
detailsViewResult.BillPrice = detailsViewResult.BillTotalPrice / (decimal)detailsViewResult.BillAmount;
}
}
viewResult.Details.Add(detailsViewResult);
}
reports.Add(viewResult);
}
}
}
}
return(reports);
}
