public static void SetDataFromXls(List<List<SaleInfo>> reports)
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<SqlServerEntities, Configuration>());
using (var db = new SqlServerEntities())
{
foreach (var file in reports)
{
foreach (var rep in file)
{
var supermarket = db.Supermarkets.FirstOrDefault(x => x.SupermarketName == rep.Location);
if (supermarket == null)
{
supermarket = new Supermarket { SupermarketName = rep.Location };
db.Supermarkets.Add(supermarket);
}
var sale = new Sale
{
ProductId = rep.ProductId,
Supermarket = supermarket,
Date = rep.SaleDate,
Quantity = rep.Quantity,
UnitPrice = rep.UnitPrice,
Sum = rep.Sum
};
db.Sales.Add(sale);
db.SaveChanges();
}
}
}
}
public static void SaveData()
{
var mongoClient = new MongoClient(@"mongodb://localhost/");
var mongoServer = mongoClient.GetServer();
var productsDb = mongoServer.GetDatabase("ProductsDb");
var products = productsDb.GetCollection<FullIProductInformation>("products");
SqlServerEntities db = new SqlServerEntities();
using (db)
{
var salesReports = db.Sales
.Include("Product")
.Include("Supermarket")
.Include("Product.Vendor")
.ToList()
.GroupBy(x => x.Product.ProductName);
foreach (var item in salesReports)
{
FullIProductInformation info = new FullIProductInformation();
info.TotalIncomes = item.Sum(x => x.Sum);
info.ProductName = item.Key;
info.TotalQuantitySold = item.Sum(x => x.Quantity);
info.VendorName = item.First().Product.Vendor.VendorName;
info.ProductId = item.First().ProductId;
SaveToFile(info);
products.Insert(info);
}
}
}
public static void SetData(
List<VendorInformation> vendors,
List<MeasureInformation> measures,
List<ProductInformation> products)
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<SqlServerEntities, Configuration>());
using (var db = new SqlServerEntities())
{
foreach (var vend in vendors)
{
db.Vendors.Add(new Vendor
{
VendorId = vend.VendorId,
VendorName = vend.VendorName
});
}
foreach (var meas in measures)
{
db.Measures.Add(new Measure
{
MeasureId = meas.MeasureId,
MeasureName = meas.MeasureName
});
}
foreach (var prod in products)
{
db.Products.Add(new Product
{
ProductName = prod.ProductName,
BasePrice = (decimal)prod.BasePrice,
MeasureId = prod.MeasureId,
VendorId = prod.VendorId
});
}
db.SaveChanges();
}
}
public static List<XMLReportInfo> XMLPersiter()
{
List<XMLReportInfo> info = new List<XMLReportInfo>();
Database.SetInitializer(new MigrateDatabaseToLatestVersion<SqlServerEntities, Configuration>());
using (var db = new SqlServerEntities())
{
var salesReports = (from s in db.Sales
group s by s.Product.Vendor.VendorName).ToList();
foreach (var report in salesReports)
{
XMLReportInfo curReport = new XMLReportInfo();
curReport.Key = report.Key;
List<decimal> sums = new List<decimal>();
List<DateTime> dates = new List<DateTime>();
foreach (var item in report)
{
dates.Add(item.Date);
sums.Add(item.Sum);
}
curReport.Sums = sums;
curReport.Dates = dates;
info.Add(curReport);
}
}
return info;
}
public static void GenerateTaxes()
{
using (SqlServerEntities db = new SqlServerEntities())
{
var names = from p in db.Products
select new
{
Product = p.ProductName,
Id = p.ProductId
};
using (VendorsDB dbLite = new VendorsDB())
{
Random rnd = new Random();
foreach (var item in names)
{
dbLite.Taxes.Add(new Tax() { Id = item.Id, ProductName = item.Product, Tax1 = rnd.Next(1, 40) });
}
dbLite.SaveChanges();
}
}
}
public static void CreatePDF()
{
Document myDocument = new Document(PageSize.A4.Rotate());
try
{
PdfWriter.GetInstance(myDocument, new FileStream("..\\..\\salesReport.pdf", FileMode.Create));
PdfPTable table = new PdfPTable(5);
float[] widths = new float[] { 25f, 10f, 10f, 45f, 10f };
table.SetWidths(widths);
table.DefaultCell.FixedHeight = 27f;
table.DefaultCell.VerticalAlignment = Element.ALIGN_MIDDLE;
myDocument.Open();
SqlServerEntities context = new SqlServerEntities();
var sales = (context.Sales.Include("Product").Include("Supermarket").Include("Product.Measure")).ToList().GroupBy(sale => sale.Date);
decimal totalSum = 0;
myDocument.Open();
var headerFont = FontFactory.GetFont("Arial", 14, Font.BOLD);
PdfPCell header = new PdfPCell(new Phrase("Aggregate Sales Report", headerFont));
header.Colspan = 5;
header.FixedHeight = 27f;
header.HorizontalAlignment = Element.ALIGN_CENTER;
header.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(header);
foreach (var sale in sales)
{
decimal currentSum = 0;
var font = FontFactory.GetFont("Arial", 14, Font.BOLD);
PdfPCell date = new PdfPCell(new Phrase("Date: " + (sale.Key.Date).ToString("dd-MM-yyyy"), font));
date.Colspan = 5;
date.FixedHeight = 27f;
date.BackgroundColor = new BaseColor(210, 210, 210);
date.HorizontalAlignment = Element.ALIGN_LEFT;
date.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(date);
PdfPCell product = new PdfPCell(new Phrase("Product", font));
product.FixedHeight = 27f;
product.BackgroundColor = new BaseColor(210, 210, 210);
product.HorizontalAlignment = Element.ALIGN_LEFT;
product.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(product);
PdfPCell quantity = new PdfPCell(new Phrase("Quantity", font));
quantity.FixedHeight = 27f;
quantity.BackgroundColor = new BaseColor(210, 210, 210);
quantity.HorizontalAlignment = Element.ALIGN_LEFT;
quantity.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(quantity);
PdfPCell price = new PdfPCell(new Phrase("Unit Price", font));
price.FixedHeight = 27f;
price.BackgroundColor = new BaseColor(210, 210, 210);
price.HorizontalAlignment = Element.ALIGN_LEFT;
price.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(price);
PdfPCell location = new PdfPCell(new Phrase("Location", font));
location.FixedHeight = 27f;
location.BackgroundColor = new BaseColor(210, 210, 210);
location.HorizontalAlignment = Element.ALIGN_LEFT;
location.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(location);
PdfPCell sumCol = new PdfPCell(new Phrase("Sum", font));
sumCol.FixedHeight = 27f;
sumCol.BackgroundColor = new BaseColor(210, 210, 210);
sumCol.HorizontalAlignment = Element.ALIGN_LEFT;
sumCol.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(sumCol);
foreach (var item in sale)
{
table.AddCell((item.Product.ProductName).ToString());
table.AddCell((item.Quantity).ToString() + " " + item.Product.Measure.MeasureName);
table.AddCell((item.UnitPrice).ToString());
table.AddCell(String.Format("Supermarket \"{0}\"", item.Supermarket.SupermarketName));
PdfPCell sum = new PdfPCell(new Phrase((item.Sum).ToString()));
sum.HorizontalAlignment = Element.ALIGN_RIGHT;
table.AddCell(sum);
currentSum += item.Sum;
totalSum += item.Sum;
}
PdfPCell footerDate = new PdfPCell(new Phrase("Total sum for " + (sale.Key.Date).ToString("dd-MM-yyyy") + ": "));
footerDate.Colspan = 4;
footerDate.FixedHeight = 27f;
footerDate.HorizontalAlignment = Element.ALIGN_RIGHT;
footerDate.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(footerDate);
PdfPCell current = new PdfPCell(new Phrase(currentSum.ToString()));
current.FixedHeight = 27f;
current.HorizontalAlignment = Element.ALIGN_RIGHT;
current.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(current);
}
PdfPCell grandTotal = new PdfPCell(new Phrase("Grand total: "));
grandTotal.Colspan = 4;
grandTotal.FixedHeight = 27f;
grandTotal.BackgroundColor = new BaseColor(210, 210, 210);
grandTotal.HorizontalAlignment = Element.ALIGN_RIGHT;
grandTotal.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(grandTotal);
PdfPCell total = new PdfPCell(new Phrase(totalSum.ToString()));
total.BackgroundColor = new BaseColor(210, 210, 210);
total.HorizontalAlignment = Element.ALIGN_RIGHT;
total.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(total);
myDocument.Add(table);
}
catch (DocumentException de)
{
Console.WriteLine(de.Message);
}
catch (IOException ioe)
{
Console.WriteLine(ioe.Message);
}
myDocument.Close();
}
