public static List<ProductReport> CreateReportForEveryProductFromSQLServer()
{
SQLServerContext db = new SQLServerContextFactory().Create();
var reports = new List<ProductReport>();
using (db)
{
var productsCount = GetProductsCount(db);
var productSalesData = GetProductsDataFromDb(db);
reports = new List<ProductReport>(productsCount);
for (int i = 1; i <= productsCount; i++)
{
var currentProduct = new ProductReport(i);
foreach (var product in productSalesData)
{
if (product.Id == currentProduct.Id)
{
currentProduct.ProductCode = product.ProductCode;
currentProduct.Name = product.Name;
currentProduct.ShopNames.Add(product.ShopName);
currentProduct.TotalQuantitySold += product.TotalQuantitySold;
currentProduct.TotalIncomes += product.TotalIncomes;
}
}
reports.Add(currentProduct);
}
}
return reports;
}
/// <summary>
/// Exports to XML all remaining products and the shops they are located in
/// </summary>
/// <param name="db"></param>
public static void RemainingQuantities()
{
//create root element and database
var db = new SQLServerContextFactory().Create();
XElement root = new XElement("products");
//make a collection with all the data you want to export to XML. Use as many joins as needed
var products = db.Products.OrderBy(p => p.QuantityInStock);
//go through all items in the collection
foreach (var product in products)
{
//for every nested element you must create new instance of XElement
XElement currentProduct = new XElement("product"); //create tag
currentProduct.SetAttributeValue("name", product.Name); //set attribute
currentProduct.SetAttributeValue("description", product.Description); //set another attribute
XElement productInfo = new XElement("info"); //nest element after "Product"
productInfo.Add(new XElement("price", product.Price)); //add element inside "Info"
//you can create those as new XElement like the creation of "Info"
productInfo.Add(new XElement("quantity", product.QuantityInStock));
//add info to product
currentProduct.Add(productInfo);
//add current set of tags to root
root.Add(currentProduct);
}
string methodName = MethodBase.GetCurrentMethod().Name;
root.Save(Helpers.NamingFactory.BuildName(methodName, fileType));
}
/// <summary>
/// TODO - Export to XML all sales that happened in the last month
/// </summary>
public static void AllSales()
{
//create root element and database
var db = new SQLServerContextFactory().Create();
XElement root = new XElement("products");
//make a collection with all the data you want to export to XML. Use as many joins as needed
var sales = db.Sales.OrderBy(s => s.Shop.Name);
//go through all items in the collection
foreach (var sale in sales)
{
//for every nested element you must create new instance of XElement
XElement currentProduct = new XElement("sale"); //create tag
currentProduct.SetAttributeValue("vendor", sale.Shop.Name); //set attribute
XElement productInfo = new XElement("summary"); //nest element after "Product"
productInfo.Add(new XElement("date", sale.SaleDate)); //add element inside "Info"
//you can create those as new XElement like the creation of "Info"
productInfo.Add(new XElement("total-sum", (sale.Product.Price * sale.Quantity).ToString("#.##")));
//add info to product
currentProduct.Add(productInfo);
//add current set of tags to root
root.Add(currentProduct);
}
string methodName = MethodBase.GetCurrentMethod().Name;
root.Save(Helpers.NamingFactory.BuildName(methodName, fileType));
}
private void InsertToDatabase(int shopID, int productID, int quantity, DateTime date)
{
var dbConnection = new SQLServerContextFactory().Create();
using (dbConnection)
{
var sale = new Sale();
sale.Shop = dbConnection.Shops.Find(shopID);
sale.Product = dbConnection.Products.Find(productID);
sale.Quantity = quantity;
sale.SaleDate = date;
dbConnection.Sales.Add(sale);
dbConnection.SaveChanges();
}
}
public static void AllSales()
{
var db = new SQLServerContextFactory().Create();
var strBuilder = new StringBuilder();
var sales = db.Sales.OrderBy(s => s.Shop.Name);
strBuilder.Append("<table border='1'>");
strBuilder.Append("<tr>");
strBuilder.Append("<th style=\"font-size:16px; text-align:center;\" colspan='5'>Aggregated Sales</th>");
strBuilder.Append("</tr>");
strBuilder.Append("<tr>");
strBuilder.Append("<td>Location</td>");
strBuilder.Append("<td>Product</td>");
strBuilder.Append("<td>Quantity</td>");
strBuilder.Append("<td>Price</td>");
strBuilder.Append("<td>Sum</td>");
strBuilder.Append("</tr>");
foreach (var sale in sales)
{
strBuilder.Append("<tr>");
strBuilder.AppendFormat("<td>{0}</td>", sale.Shop.Name);
strBuilder.AppendFormat("<td>{0}</td>", sale.Product.Name);
strBuilder.AppendFormat("<td>{0}</td>", sale.Quantity);
strBuilder.AppendFormat("<td>{0}</td>", sale.Product.Price);
strBuilder.AppendFormat("<td>{0}</td>", sale.Quantity * sale.Product.Price);
strBuilder.Append("</tr>");
}
strBuilder.Append("</table>");
var resultingTable = strBuilder.ToString();
PDFBuilder.HtmlToPdfBuilder builder = new PDFBuilder.HtmlToPdfBuilder(PageSize.LETTER);
PDFBuilder.HtmlPdfPage page = builder.AddPage();
page.AppendHtml(resultingTable);
byte[] file = builder.RenderPdf();
string methodName = MethodBase.GetCurrentMethod().Name;
File.WriteAllBytes(Helpers.NamingFactory.BuildName(methodName, fileType), file);
}
public static void RemainingQuantities()
{
var db = new SQLServerContextFactory().Create();
var strBuilder = new StringBuilder();
var products = db.Products.OrderBy(p => p.QuantityInStock);
strBuilder.Append("<table border='1'>");
strBuilder.Append("<tr>");
strBuilder.Append("<th style=\"font-size:16px; text-align:center;\" colspan='4'>Available products</th>");
strBuilder.Append("</tr>");
strBuilder.Append("<tr>");
strBuilder.Append("<td>Product Name</td>");
strBuilder.Append("<td>Description</td>");
strBuilder.Append("<td>Quantity in stock</td>");
strBuilder.Append("<td>Price</td>");
strBuilder.Append("</tr>");
foreach (var product in products)
{
strBuilder.Append("<tr>");
strBuilder.AppendFormat("<td>{0}</td>", product.Name);
strBuilder.AppendFormat("<td>{0}</td>", product.Description);
strBuilder.AppendFormat("<td>{0}</td>", product.QuantityInStock);
strBuilder.AppendFormat("<td>{0}</td>", product.Price);
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 methodName = MethodBase.GetCurrentMethod().Name;
File.WriteAllBytes(Helpers.NamingFactory.BuildName(methodName, fileType), file);
}
private static void XMLtoSQL(string filePath)
{
try
{
XDocument xmlDoc = XDocument.Load(filePath);
var sales =
from sale in xmlDoc.Descendants("sale")
select new
{
pid = int.Parse(sale.Element("productId").Value),
sid = int.Parse(sale.Element("shopId").Value),
qua = int.Parse(sale.Element("quantity").Value),
date = DateTime.Parse(sale.Element("saleDate").Value),
};
var sqlServerConnection = new SQLServerContextFactory().Create();
foreach (var sale in sales)
{
var newSale = new Sale();
newSale.Product = sqlServerConnection.Products.Find(sale.pid);
newSale.Shop = sqlServerConnection.Shops.Find(sale.sid);
newSale.Quantity = sale.qua;
newSale.SaleDate = sale.date;
sqlServerConnection.Sales.Add(newSale);
}
sqlServerConnection.SaveChanges();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
return;
}
}
private static void ShowAllProductsInSQLServer()
{
var sqlServerConnection = new SQLServerContextFactory().Create();
using (sqlServerConnection)
{
var products = sqlServerConnection.Products;
Console.WriteLine("##################################################");
foreach (var product in products)
{
Console.Write("#");
Console.WriteLine("{1} - {0} - {3} - {2}", product.Name, product.ProductCode, product.Price, product.Description);
}
Console.WriteLine("##################################################");
}
}
