internal static void GenerateSalesReport()
{
using (SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer
= new SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer())
{
var xmlDataQuery =
from v in spEntSQLServer.Vendors
join p in spEntSQLServer.Products
on v.ID equals p.VendorId
join s in spEntSQLServer.Sales
on p.ID equals s.ProductID
join d in spEntSQLServer.Dates
on s.DateID equals d.ID
group new
{
s,
v
}
by new
{
v.Name,
s.Date
} into grouped
select new XmlSalesContainer
{
Date = grouped.Key.Date.DateAndTime,
VendorName = grouped.Key.Name,
Sum = grouped.Sum(x => x.s.Sum)
};
GenerateSalesReport(xmlDataQuery);
}
}
internal static void FillExcelData(
SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer)
{
foreach (var excelRow in ExcelReader.AllSales)
{
int dateID = InsertDate(spEntSQLServer, excelRow);
int supermarketID = InsertSupermarket(spEntSQLServer, excelRow);
InsertSale(spEntSQLServer, excelRow, dateID, supermarketID);
}
}
/// <summary>
/// Copy data from MySQL Database to MSSQL Sever Database
/// </summary>
/// <param name="spEntMySQL">MySQL entity</param>
/// <param name="spEntSQLServer">MSSQL Serve entity</param>
internal static void MigrateData(
MySQL.Models.OpenAccess.SupermarketEntitiesMySQL spEntMySQL,
SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer)
{
if (spEntSQLServer.Database.Exists())
{
return;
}
// Insert Measures
foreach (var measure in spEntMySQL.Measures)
{
SQLServer.Models.EntityFramework.Measure measureToInsert =
new SQLServer.Models.EntityFramework.Measure();
measureToInsert.ID = measure.MeasureID;
measureToInsert.Name = measure.MeasureName;
spEntSQLServer.Measures.Add(measureToInsert);
}
// Inserting Vendors
foreach (var vendor in spEntMySQL.Vendors)
{
SQLServer.Models.EntityFramework.Vendor vendorToInsert =
new SQLServer.Models.EntityFramework.Vendor();
vendorToInsert.ID = vendor.VendorID;
vendorToInsert.Name = vendor.VendorName;
spEntSQLServer.Vendors.Add(vendorToInsert);
}
// Inserting Products
foreach (var product in spEntMySQL.Products)
{
SQLServer.Models.EntityFramework.Product productToInsert =
new SQLServer.Models.EntityFramework.Product();
productToInsert.ID = product.ProductID;
productToInsert.MeasureId = product.MeasureID;
productToInsert.ProductName = product.ProductName;
productToInsert.VendorId = product.VendorID;
productToInsert.BasePrice = product.BasePrice;
spEntSQLServer.Products.Add(productToInsert);
}
spEntSQLServer.SaveChanges();
}
private static int InsertSupermarket(SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer, ExcelData excelRow)
{
var existingSupermarket = spEntSQLServer.Supermarkets.FirstOrDefault(s => s.Name == excelRow.SupermarketName);
if (existingSupermarket == null)
{
SQLServer.Models.EntityFramework.Supermarket supermarketToInsert = new SQLServer.Models.EntityFramework.Supermarket();
supermarketToInsert.Name = excelRow.SupermarketName;
var result = spEntSQLServer.Supermarkets.Add(supermarketToInsert);
spEntSQLServer.SaveChanges();
return(result.SupermarketId);
}
else
{
return(existingSupermarket.SupermarketId);
}
}
private static int InsertSale(
SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer,
ExcelData excelRow,
int dateID,
int supermarketID)
{
SQLServer.Models.EntityFramework.Sale saleToInsert = new SQLServer.Models.EntityFramework.Sale();
saleToInsert.ProductID = excelRow.ProductID;
saleToInsert.SupermarketID = supermarketID;
saleToInsert.DateID = dateID;
saleToInsert.Quantity = excelRow.Quantity;
saleToInsert.Sum = excelRow.Sum;
saleToInsert.UnitPrice = excelRow.UnitPrice;
var a = spEntSQLServer.Sales.Add(saleToInsert);
spEntSQLServer.SaveChanges();
return(a.ID);
}
private static int InsertDate(
SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer,
ExcelData excelRow)
{
var existingDate = spEntSQLServer.Dates.FirstOrDefault(d => d.DateAndTime == excelRow.SaleDate);
if (existingDate == null)
{
SQLServer.Models.EntityFramework.Date dateToInsert = new SQLServer.Models.EntityFramework.Date();
dateToInsert.DateAndTime = excelRow.SaleDate;
var result = spEntSQLServer.Dates.Add(dateToInsert);
spEntSQLServer.SaveChanges();
return(result.ID);
}
else
{
return(existingDate.ID);
}
}
internal static void AggregatePDF(
SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer spEntSQLServer,
DateTime startDate,
DateTime endDate)
{
FileStream file = new FileStream(@"../../Reports.pdf", FileMode.Append);
Document doc = new Document();
PdfWriter writer = PdfWriter.GetInstance(doc, file);
doc.Open();
PdfPTable table = new PdfPTable(5);
table.TotalWidth = 416f;
table.LockedWidth = true;
PdfPCell cell = new PdfPCell(new Phrase("Aggregated Sales Report"));
cell.Colspan = 5;
cell.HorizontalAlignment = 1;
table.AddCell(cell);
decimal grandSum = 0;
using (spEntSQLServer)
{
var dates =
from d in spEntSQLServer.Dates
where d.DateAndTime >= startDate && d.DateAndTime <= endDate
select d;
foreach (var date in dates)
{
PdfPCell cellDate = new PdfPCell(new Phrase("Date : " + date.DateAndTime.ToString("yyyy-MMM-dd", CultureInfo.CreateSpecificCulture("en-US"))));
cellDate.Colspan = 5;
cellDate.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cellDate);
PdfPCell cellProduct = new PdfPCell(new Phrase("Products"));
cellProduct.BackgroundColor = BaseColor.LIGHT_GRAY;
PdfPCell cellQuantity = new PdfPCell(new Phrase("Quantity"));
cellQuantity.BackgroundColor = BaseColor.LIGHT_GRAY;
PdfPCell cellUnitPrice = new PdfPCell(new Phrase("Unit Price"));
cellUnitPrice.BackgroundColor = BaseColor.LIGHT_GRAY;
PdfPCell cellLocation = new PdfPCell(new Phrase("Location"));
cellLocation.BackgroundColor = BaseColor.LIGHT_GRAY;
PdfPCell cellSum = new PdfPCell(new Phrase("Sum"));
cellSum.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cellProduct);
table.AddCell(cellQuantity);
table.AddCell(cellUnitPrice);
table.AddCell(cellLocation);
table.AddCell(cellSum);
decimal totalSum = 0;
SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer sqlServer =
new SQLServer.Data.EntityFramework.SupermarketEntitiesSQLServer();
using (sqlServer)
{
var sales =
from s in sqlServer.Sales
where s.Date.DateAndTime == date.DateAndTime
select s;
foreach (var sale in sales.ToList())
{
if (sale.Product.ProductName != null)
{
table.AddCell(sale.Product.ProductName.ToString());
table.AddCell(sale.Quantity.ToString());
table.AddCell(sale.UnitPrice.ToString());
table.AddCell(sale.Supermarket.Name.ToString());
table.AddCell(sale.Sum.ToString());
totalSum += sale.Sum;
}
}
}
string text = string.Format("Total sum for {0} :", startDate.ToString("yyyy-MMM-dd", CultureInfo.CreateSpecificCulture("en-US")));
PdfPCell cellTotal = new PdfPCell(new Phrase(text));
cellTotal.Colspan = 4;
cellTotal.HorizontalAlignment = 2;
table.AddCell(cellTotal);
table.AddCell(totalSum.ToString());
grandSum += totalSum;
}
}
string grand = string.Format("Grand Total: ");
PdfPCell cellGrand = new PdfPCell(new Phrase(grand));
cellGrand.Colspan = 4;
cellGrand.HorizontalAlignment = 2;
table.AddCell(cellGrand);
table.AddCell(grandSum.ToString());
doc.Add(table);
doc.Close();
}
