private static List<ProductReport> getAllReports(SupermarketContext db)
{
var reports = new List<ProductReport>();
foreach (var product in db.Products)
{
int totalQuantitySold = 0;
var foundProduct = db.SalesReports.FirstOrDefault(x => x.ProductID == product.ProductID);
if(foundProduct!=null)
{
totalQuantitySold = db.SalesReports.Where(x => x.ProductID == product.ProductID)
.Sum(x => x.Quantity);
}
ProductReport report = new ProductReport()
{
productId = product.ProductID,
productName = product.ProductName,
vendorName = product.Vendor.VendorName,
totalQuantitySold = totalQuantitySold,
totalIncomes = product.BasePrice * totalQuantitySold
};
reports.Add(report);
}
return reports;
}
static void Main()
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<SupermarketContext, Configuration>());
var db = new SupermarketContext();
string conStr = "Server=localhost; Port=3306; Database=supermarketdb; Uid=root; Pwd=6okolad; pooling=true";
SupermarketModel mySqlDB = new SupermarketModel(conStr);
db.Database.Delete();
TransferData(mySqlDB, db);
TransferDataConnTable(mySqlDB, db);
db.Database.Connection.Close();
//Unpack data from zip file
ZipDataReader.ExtractData(@"../../../Sample-Sales-Reports.zip", @"../../../Extracted Files");
ExcelDataReader.TransferDataFromExcelToDB(db, "../../../Extracted Files");
Directory.Delete(@"../../../Extracted Files", true);
//Create Pdf File
CreatePdf.AddDataToPdf(db);
//Create XML File
CreateXMLDocument.AddDataToXML(db);
//Save Json files in the file system
CreateJsonDocument.saveJsonFiles(@"../../../Product-Reports", db);
CreateJsonDocument.saveReportsToMongoDB(db);
}
// Copy data from auto-generates MySql classes to code first made classes for MS SQL Database
public static void TransferData(SupermarketModel mySQL, SupermarketContext msSQL)
{
foreach (var measure in mySQL.Measures)
{
var newMeasure = new SupermarketDB.Models.Measure
{
MeasureID = measure.MeasureID,
MeasureName = measure.MeasureName
};
msSQL.Measures.Add(newMeasure);
msSQL.SaveChanges();
}
foreach (var vendor in mySQL.Vendors)
{
var newVendor = new SupermarketDB.Models.Vendor
{
VendorID = vendor.VendorID,
VendorName = vendor.VendorName
};
msSQL.Vendors.Add(newVendor);
msSQL.SaveChanges();
}
}
public static void saveReportsToMongoDB(SupermarketContext db)
{
var mongoClient = new MongoClient("mongodb://*****:*****@dharma.mongohq.com:10068/Supermarket");
var mongoServer = mongoClient.GetServer();
var supermarketDB = mongoServer.GetDatabase("Supermarket");
var reportsCollection = supermarketDB.GetCollection("Reports");
var reports = getAllReports(db);
foreach (ProductReport report in reports)
{
reportsCollection.Insert(report);
}
}
public static void saveJsonFiles(string path, SupermarketContext db)
{
var directory = Directory.CreateDirectory(path);
var reports = getAllReports(db);
foreach (ProductReport report in reports)
{
string currentPath = path + "/" + report.productId.ToString() + ".json";
using (StreamWriter writer = new StreamWriter(currentPath))
{
writer.Write(JsonConvert.SerializeObject(report));
}
}
}
public static void TransferDataConnTable(SupermarketModel mySQL, SupermarketContext msSQL)
{
foreach (var product in mySQL.Products)
{
var newProduct = new SupermarketDB.Models.Product
{
ProductID = product.ProductID,
VendorID = product.VendorID,
MeasureID = product.MeasureID,
ProductName = product.ProductName,
BasePrice = product.BasePrice
};
msSQL.Products.Add(newProduct);
msSQL.SaveChanges();
}
}
public static void AddDataToXML(SupermarketContext db)
{
XElement saleXML = new XElement("sales");
foreach (var vendor in db.Vendors)
{
XElement sale = new XElement("sale");
sale.SetAttributeValue("vendor", vendor.VendorName);
saleXML.Add(sale);
foreach (var product in vendor.Products)
{
foreach (var report in product.Reports)
{
XElement summary = new XElement("summary");
summary.Add(new XAttribute("date", report.ReportDate));
summary.Add(new XAttribute("total-sum", report.Sum));
sale.Add(summary);
}
}
}
saleXML.Save("../../../Sales-by-Vendors-report.xml");
}
public static void AddDataToPdf(SupermarketContext db)
{
Document doc = new Document();
PdfAWriter.GetInstance(doc, new FileStream(@"../../.." + "/Aggregated-Sales-Report.pdf", FileMode.Create));
doc.Open();
PdfPTable table = new PdfPTable(5);
Font fontBold = new Font(Font.FontFamily.HELVETICA, 14f);
fontBold.SetStyle("bold");
Font fontNormal = new Font(Font.FontFamily.HELVETICA, 12f);
PdfPCell header = new PdfPCell(new Phrase("Aggregated Sales Report", fontBold));
header.Colspan = 5;
header.HorizontalAlignment = 1;
table.AddCell(header);
using (db)
{
var dates =
(from d in db.SalesReports
select d.ReportDate).Distinct();
decimal grandTotal = 0;
foreach (var date in dates)
{
PdfPCell cellDate = new PdfPCell(new Phrase("Date : " + date.ToShortDateString()));
cellDate.Colspan = 5;
table.AddCell(cellDate);
PdfPCell cellProduct = new PdfPCell(new Phrase("Products", fontBold));
table.AddCell(cellProduct);
PdfPCell cellQuantity = new PdfPCell(new Phrase("Quantity", fontBold));
table.AddCell(cellQuantity);
PdfPCell cellUnitPrice = new PdfPCell(new Phrase("Unit Price", fontBold));
table.AddCell(cellUnitPrice);
PdfPCell cellLocation = new PdfPCell(new Phrase("Location", fontBold));
table.AddCell(cellLocation);
PdfPCell cellSum = new PdfPCell(new Phrase("Sum", fontBold));
table.AddCell(cellSum);
var sales =
(from s in db.SalesReports
where s.ReportDate == date
select s);
decimal totalSum = 0;
foreach (var sale in sales)
{
if (sale.Product.ProductName != null)
{
table.AddCell(sale.Product.ProductName);
table.AddCell(sale.Quantity.ToString() + " " + db.Measures
.Where(x => x.MeasureID == sale.Product.MeasureID)
.Select(x => x.MeasureName).First().ToString());
table.AddCell(sale.UnitPrice.ToString());
table.AddCell(sale.SupermarketName);
table.AddCell(sale.Sum.ToString());
totalSum += sale.Sum;
}
}
string text = string.Format("Total sum for {0} :", date.ToShortDateString());
PdfPCell cellTotal = new PdfPCell(new Phrase(text, fontNormal));
cellTotal.Colspan = 4;
cellTotal.HorizontalAlignment = 2;
table.AddCell(cellTotal);
table.AddCell(totalSum.ToString());
grandTotal += 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(grandTotal.ToString());
doc.Add(table);
}
doc.Close();
}
public static void TransferDataFromExcelToDB(SupermarketContext DBconn, string directory)
{
DirectoryInfo dir = new DirectoryInfo(directory);
var directories = dir.GetDirectories();
foreach (var d in directories)
{
var date = DateTime.Parse(d.Name);
var files = d.GetFiles();
foreach (var f in files)
{
string excelFile = f.FullName;
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=" + excelFile + ";" +
@"Extended Properties='Excel 12.0 Xml;HDR=YES'";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
DataSet newDataSet = new DataSet();
using (conn)
{
OleDbCommand cmd = new OleDbCommand("SELECT * from [Sales$]", conn);
OleDbDataAdapter newDataAdapter = new OleDbDataAdapter(cmd);
newDataAdapter.Fill(newDataSet, "Sales");
}
DataTable table = newDataSet.Tables[0];
// Get supermarket name
DataRow row = table.Rows[0];
DataColumn col = table.Columns[0];
string line = row[col].ToString();
int spaceIndex = line.IndexOf(' ');
string supermarketName = line.Substring(spaceIndex + 1);
decimal sum = decimal.Parse(table.Rows[table.Rows.Count - 1][table.Columns.Count - 1].ToString());
List<decimal[]> allData = new List<decimal[]>();
for (int r = 2; r < table.Rows.Count - 1; r++)
{
DataRow dataRow = table.Rows[r];
var productData = new decimal[4];
decimal result = 0;
for (int c = 0; c < 4; c++)
{
DataColumn dataCol = table.Columns[c];
decimal.TryParse(dataRow[dataCol].ToString(), out result);
productData[c] = result;
}
if (result != 0)
{
allData.Add(productData);
}
}
foreach (var report in allData)
{
var productId = (int)report[0];
var quantity = (int)report[1];
var unitPrice = report[2];
var reportSum = report[3];
var saleReport = new SaleReport
{
ProductID = productId,
Quantity = quantity,
UnitPrice = unitPrice,
Sum = reportSum,
SupermarketName = supermarketName,
ReportDate = date
};
DBconn.SalesReports.Add(saleReport);
DBconn.SaveChanges();
}
}
}
}