public static void Execute()
{
string topPath = @"../../../../WritingFiles/";
string jsonPath = @"../../../../WritingFiles/JSON/";
Directory.Delete(topPath, true);
Directory.CreateDirectory(topPath);
Directory.CreateDirectory(jsonPath);
SuperMarketEntities dbContext = new SuperMarketEntities();
using (dbContext)
{
dbContext.Database.ExecuteSqlCommand("DELETE FROM SalesReports");
dbContext.Database.ExecuteSqlCommand("DELETE FROM Locations");
dbContext.Database.ExecuteSqlCommand("DELETE FROM Products");
dbContext.Database.ExecuteSqlCommand("DELETE FROM VendorExpenses");
dbContext.Database.ExecuteSqlCommand("DELETE FROM Vendors");
dbContext.Database.ExecuteSqlCommand("DELETE FROM Measures");
dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (SalesReports, reseed, 0)");
dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Locations, reseed, 0)");
dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Products, reseed, 0)");
dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (VendorExpenses, reseed, 0)");
dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Vendors, reseed, 0)");
dbContext.Database.ExecuteSqlCommand("DBCC CHECKIDENT (Measures, reseed, 0)");
dbContext.SaveChanges();
}
}
public static void PopulateVendorExpensesInSQL()
{
SuperMarketEntities dbContext = new SuperMarketEntities();
List<VendorExpense> vendorExpenses = XMLDataTransefer.GetVendorExpenses();
using(dbContext)
{
VendorExpens currReport = null;
foreach (var vendorExpnese in vendorExpenses)
{
for (int itemIdex = 0; itemIdex < vendorExpnese.MonthDates.Count; itemIdex++)
{
currReport = new VendorExpens();
currReport.VendorID = vendorExpnese.VendorID;
currReport.MonthDate = vendorExpnese.MonthDates[itemIdex];
currReport.Expnenses = vendorExpnese.Expenses[itemIdex];
dbContext.VendorExpenses.Add(currReport);
}
}
dbContext.SaveChanges();
}
}
static DataSender()
{
dbContext = new SuperMarketEntities();
products = new List<ProductEntity>();
vendors = new List<VendorEntity>();
measures = new List<MeasureEntity>();
}
public static void Generate()
{
SuperMarketEntities dbContext = new SuperMarketEntities();
XElement xml = new XElement("sales");
using (dbContext)
{
foreach (var vendor in dbContext.Vendors)
{
XElement sales = new XElement("sale");
XAttribute currentVendor = new XAttribute("vendor", vendor.VendorName);
sales.Add(currentVendor);
var query = from s in dbContext.SalesReports
join p in dbContext.Products on s.ProductID equals p.ProductID
join v in dbContext.Vendors on p.VendorID equals v.VendorID
where v.VendorName == vendor.VendorName
group s by s.Date into d
select new
{
date = d.Key,
totalSum = d.Sum(x => x.Sum)
};
foreach (var summary in query)
{
XElement currentSummary = new XElement("summary");
string formatDate = summary.date.ToString("dd-MMM-yyyy", new CultureInfo("en-US"));
XAttribute currentDate = new XAttribute("date", formatDate);
currentSummary.Add(currentDate);
string formatTotalSum = summary.totalSum.ToString("F2", new CultureInfo("en-US"));
XAttribute currentSum = new XAttribute("total-sum", formatTotalSum);
currentSummary.Add(currentSum);
sales.Add(currentSummary);
}
xml.Add(sales);
}
xml.Save("../../../../WritingFiles/Sales-by-Vendors-report.xml");
}
}
public static void PopulateMeasures()
{
Measure currMeasure = null;
using (DataSender.dbContext = new SuperMarketEntities())
{
foreach (var measure in DataSender.measures)
{
currMeasure = new Measure();
currMeasure.MeasureID = measure.MeasureID;
currMeasure.Name = measure.Name;
dbContext.Measures.Add(currMeasure);
dbContext.SaveChanges();
}
}
}
public static List<ProductReport> GetProductReports()
{
using (context = new SuperMarketEntities())
{
var result = (from product in context.Products
join vendor in context.Vendors
on product.VendorID equals vendor.VendorID
join saleReport in context.SalesReports
on product.ProductID equals saleReport.ProductID
select new
{
ProductId = product.ProductID,
ProductName = product.ProductName,
VendorName = vendor.VendorName,
Income = saleReport.Sum,
Quantity = saleReport.Quantity
}).GroupBy(x => x.ProductId);
foreach (var item in result)
{
ProductReport currReport = new ProductReport();
currReport.ProductId = item.First().ProductId;
currReport.ProductName = item.First().ProductName;
currReport.VendorName = item.First().VendorName;
decimal totalIncome = 0;
int totalQuantities = 0;
foreach (var sp in item)
{
totalIncome = totalIncome + sp.Income;
totalQuantities = totalQuantities + sp.Quantity;
}
currReport.TotalIncomes = totalIncome;
currReport.TotalQuantitySold = totalQuantities;
SQLReader.productReports.Add(currReport);
}
}
return SQLReader.productReports;
}
public static void PopulateProducts()
{
Product currProduct = null;
using (DataSender.dbContext = new SuperMarketEntities())
{
foreach (var product in DataSender.products)
{
currProduct = new Product();
currProduct.ProductID = product.ProductID;
currProduct.ProductName = product.Name;
currProduct.MeasureID = product.MeasureID;
currProduct.BasePrice = product.BasePrice;
currProduct.VendorID = product.VendorID;
dbContext.Products.Add(currProduct);
dbContext.SaveChanges();
}
}
}
public static void Generate()
{
StringBuilder html = new StringBuilder();
html.Append("<!DOCTYPE html><html><body><h1>Aggregated Sales Report</h1>");
SuperMarketEntities dbContext = new SuperMarketEntities();
using (dbContext)
{
var dateList = dbContext.SalesReports.Select(x => x.Date).Distinct();
foreach (var date in dateList)
{
html.Append(string.Format("<h4 style=\"color:#555\">Date: {0}</h4>",
date.ToString("dd-MMM-yyyy", new CultureInfo("en-US"))));
var products = dbContext.SalesReports.
Join(dbContext.Products,
(s => s.ProductID), (p => p.ProductID), (s, p) =>
new
{
Date = s.Date,
Product = p.ProductName,
Quantity = s.Quantity,
MeasureID = p.MeasureID,
Price = s.UnitPrice,
LocationID = s.LocationID,
Sum = s.Sum
});
var measures = products.Join(dbContext.Measures,
(sp => sp.MeasureID), (m => m.MeasureID), (sp, m) =>
new
{
Date = sp.Date,
Product = sp.Product,
Quantity = sp.Quantity,
Measure = m.Name,
Price = sp.Price,
LocationID = sp.LocationID,
Sum = sp.Sum
});
var locations = measures.Join(dbContext.Locations,
(spm => spm.LocationID), (l => l.LocationID), (spm, l) =>
new
{
Date = spm.Date,
Product = spm.Product,
Quantity = spm.Quantity,
Measure = spm.Measure,
Price = spm.Price,
Location = l.Supermarket,
Sum = spm.Sum
}).Select(spml => spml).
Where(spml => spml.Date == date);
html.Append("<div><table border=\"1\"><tr bgcolor=\"#aaa\">" +
"<th>Product</th><th>Quantity</th><th>Unit Price</th><th>Location</th><th>Sum</th></tr><tr>");
decimal totalSum = 0m;
foreach (var item in locations)
{
html.Append(string.Format("<td>{0}</td><td>{1}</td><td>{2:F2}</td><td>{3}</td><td>{4:F2}</td></tr><tr>",
item.Product, item.Quantity + " " + item.Measure, item.Price,
item.Location.Replace('“', '"').Replace('–', '-').Replace('”', '"'), item.Sum));
totalSum += item.Sum;
}
html.Append(string.Format("<td colspan=\"4\">Total sum for {0}:</td><td>{1:F2}</td></tr><tr></table></div>",
date.ToString("dd-MMM-yyyy", new CultureInfo("en-US")), totalSum));
}
}
html.Append("</body></html>");
Docs.Pdf.HtmlToPdf convertion = new Docs.Pdf.HtmlToPdf();
convertion.OpenHTML(html.ToString());
convertion.SavePDF(PDFGenerator.PDF_PATH);
}
public static void PopulateVendors()
{
Vendor currVendor = null;
using (DataSender.dbContext = new SuperMarketEntities())
{
foreach (var vendor in DataSender.vendors)
{
currVendor = new Vendor();
currVendor.VendorID = vendor.VendorID;
currVendor.VendorName = vendor.Name;
dbContext.Vendors.Add(currVendor);
dbContext.SaveChanges();
}
}
}
public static Dictionary<string, List<Tuple<string, decimal>>> GetVendorsAndProducts(DateTime checkDate)
{
Dictionary<string, List<Tuple<string, decimal>>> vendorAndProducts = new Dictionary<string, List<Tuple<string, decimal>>>();
using (var context = new SuperMarketEntities())
{
var vendors =
(from vendor in context.Vendors
join product in context.Products
on vendor.VendorID equals product.VendorID
select new
{
VendorID = vendor.VendorID,
ProductID = product.ProductID,
VendorName = vendor.VendorName,
ProductName = product.ProductName,
}).GroupBy(x => x.VendorName);
foreach (var vendor in vendors)
{
List<Tuple<string, decimal>> currVendorProducts = new List<Tuple<string, decimal>>();
foreach (var product in vendor)
{
var query = from s in context.SalesReports
where s.ProductID == product.ProductID && s.Date.Month == checkDate.Month && s.Date.Year == checkDate.Year
group s by new { s.Date.Month, s.Date.Year } into d
select new
{
date = d.Key,
totalSum = d.Sum(x => x.Sum)
};
currVendorProducts.Add(new Tuple<string, decimal>(product.ProductName, query.First().totalSum));
}
vendorAndProducts.Add(vendor.First().VendorName, currVendorProducts);
}
}
return vendorAndProducts;
}
private static Dictionary<string, int> GetVendorsNameIdPairs()
{
Dictionary<string, int> vendors = new Dictionary<string, int>();
SuperMarketEntities dbcontext = new SuperMarketEntities();
using (dbcontext)
{
foreach (var vendor in dbcontext.Vendors)
{
vendors.Add(vendor.VendorName, vendor.VendorID);
}
}
return vendors;
}
private static Dictionary<int, string> GetVendorsIdNamePairs()
{
Dictionary<int, string> vendors = new Dictionary<int, string>();
SuperMarketEntities dbcontext = new SuperMarketEntities();
using (dbcontext)
{
foreach (var vendor in dbcontext.Vendors)
{
vendors.Add(vendor.VendorID, vendor.VendorName);
}
}
return vendors;
}