public static void ImportExpensesFromXml()
{
var expenses = ReadXML.GetObjects("..\\..\\..\\Vendors-Expenses.xml");
using (var context = new SuperMarketContext())
{
foreach (var expense in expenses)
{
context.Expenses.Add(new Expense {
VendorName = expense.Item1, Month = expense.Item2, Value = expense.Item3
});
}
context.SaveChanges();
}
}
public static void GenerateReports()
{
string reportPath = "../../../XMLReports/Sales-by-Vendors-report.xml";
using (var supermarketDb = new SuperMarketContext())
{
XElement root = new XElement("sales");
var vendors = supermarketDb.Vendors.ToList();
foreach (var vendor in vendors)
{
var entries =
from s in supermarketDb.Sales
join p in supermarketDb.Products on s.ProductId equals p.ID
join v in supermarketDb.Vendors on p.VendorID equals v.ID
where v.ID == vendor.ID
group s by s.Date into y
select new
{
Date = y.Key,
Sum = y.Sum(x => x.Sum)
};
// Skip vendors without sales
if (entries.Count() == 0)
{
continue;
}
XElement sale = new XElement("sale");
foreach (var entry in entries)
{
sale.SetAttributeValue("vendor", vendor.VendorName);
sale.Add(new XElement("summary",
new XAttribute("date",
entry.Date.SaleDate),
new XAttribute("total-sum", entry.Sum.ToString("0.00"))));
}
root.Add(sale);
}
root.Save(reportPath);
}
}
private static void InsertToDB(Location location, Date date, Sale sale)
{
SuperMarketContext supermarketDb = new SuperMarketContext();
int dateId = supermarketDb.Dates.Where(x => x.SaleDate == date.SaleDate).Select(x => x.DateId).FirstOrDefault();
int locationId = supermarketDb.Locations.Where(x => x.Name == location.Name).Select(x => x.LocationId).FirstOrDefault();
if ((dateId == 0) && (locationId == 0))
{
supermarketDb.Dates.Add(new Date()
{
SaleDate = date.SaleDate
});
supermarketDb.Locations.Add(new Location()
{
Name = location.Name
});
}
else if (dateId == 0)
{
supermarketDb.Dates.Add(new Date()
{
SaleDate = date.SaleDate
});
sale.LocationId = locationId;
}
else if (locationId == 0)
{
supermarketDb.Locations.Add(new Location()
{
Name = location.Name
});
sale.DateId = dateId;
}
else
{
sale.DateId = dateId;
sale.LocationId = locationId;
}
supermarketDb.Sales.Add(sale);
supermarketDb.SaveChanges();
}
public static void Export()
{
var supermarketDb = new SuperMarketContext();
var strBuilder = new StringBuilder();
var dates = supermarketDb.Dates.Select(x => x.SaleDate).ToArray();
decimal grandTotal = 0m;
strBuilder.Append("<table border='1'>");
strBuilder.Append("<tr>");
strBuilder.Append("<th style=\"font-size:16px; text-align:center;\" colspan='5'>Aggregated Sales Report</th>");
strBuilder.Append("</tr>");
strBuilder.Append("</table>");
for (int i = 0; i < dates.Count(); i++)
{
strBuilder.Append("<table border='1'>");
strBuilder.Append("<tr bgcolor='#BBBBBB'>");
strBuilder.AppendFormat("<th colspan='5'>Date: {0}</th>", dates[i]);
strBuilder.Append("</tr>");
strBuilder.Append("<tr bgcolor='#BBBBBB'>");
strBuilder.Append("<th class=\"th\"><b>Product Name</b></th>");
strBuilder.Append("<th><b>Quantity</b></th>");
strBuilder.Append("<th><b>Unit Price</b></th>");
strBuilder.Append("<th><b>Location</b></th>");
strBuilder.Append("<th><b>Sum</b></th>");
strBuilder.Append("</tr>");
string saleDate = dates[i];
var sales =
from sale in supermarketDb.Sales
join product in supermarketDb.Products
on sale.ProductId equals product.ID
where sale.Date.SaleDate == saleDate
select new
{
Product = product.ProductName,
Quantity = sale.Quantity,
UnitPrice = sale.UnitPrice,
LocationName = sale.Location.Name,
Sum = sale.Sum
};
decimal totalSum = 0;
foreach (var sale in sales)
{
totalSum = totalSum + sale.Sum;
strBuilder.Append("<tr>");
strBuilder.AppendFormat("<td>{0}</td>", sale.Product);
strBuilder.AppendFormat("<td>{0}</td>", sale.Quantity);
strBuilder.AppendFormat("<td>{0}</td>", sale.UnitPrice);
strBuilder.AppendFormat("<td>{0}</td>", sale.LocationName);
strBuilder.AppendFormat("<td>{0}</td>", sale.Sum);
strBuilder.Append("</tr>");
}
grandTotal = grandTotal + totalSum;
strBuilder.Append("<tr>");
strBuilder.AppendFormat("<td colspan='4' align=right>Total Sum for {0}: </td>", dates[i]);
strBuilder.AppendFormat("<td>{0}</td>", totalSum);
strBuilder.Append("</tr>");
strBuilder.Append("</table>");
if (i != 2)
{
strBuilder.Append("<br />");
}
}
strBuilder.Append("<table border='1'>");
strBuilder.Append("<tr>");
strBuilder.Append("<td colspan='4' align=right>Grand total: </td>");
strBuilder.AppendFormat("<td>{0}</td>", grandTotal);
strBuilder.Append("</tr>");
strBuilder.Append("</table>");
PDFBuilder.HtmlToPdfBuilder builder = new PDFBuilder.HtmlToPdfBuilder(PageSize.LETTER);
PDFBuilder.HtmlPdfPage page = builder.AddPage();
page.AppendHtml(strBuilder.ToString());
byte[] file = builder.RenderPdf();
string tempFolder = "../../../PDFReports/";
string tempFileName = DateTime.Now.ToString("yyyy-MM-dd") + "-" + Guid.NewGuid() + ".pdf";
if (Helpers.DirectoryExist(tempFolder))
{
if (!File.Exists(tempFolder + tempFileName))
{
File.WriteAllBytes(tempFolder + tempFileName, file);
}
}
}
public UnitOfWork(SuperMarketContext context)
{
_context = context;
}
public PurchaseRepository(SuperMarketContext context) : base(context)
{
}
public BaseRepository(SuperMarketContext context)
{
_context = context;
}
public ProductRepository(SuperMarketContext context) : base(context)
{
}
public UserRepository(SuperMarketContext context) : base(context)
{
}
public CompanyRepository(SuperMarketContext context) : base(context)
{
}
