public static void CreateXml(string filePath)
{
using (SupermarketDBContext sqldb = new SupermarketDBContext())
{
string format = "dd-MMM-yyyy";
XElement sales = new XElement("sales");
foreach (var vendor in sqldb.Vendors.ToList().Where(
x=> x.Products.Any(y => y.SalesReports.Count > 0)))
{
XElement elSale = new XElement("sale");
elSale.SetAttributeValue("vendor", vendor.VendorName.ToString());
foreach (var product in vendor.Products.ToList())
{
foreach (var report in product.SalesReports.ToList())
{
XElement summary = new XElement("summary");
summary.SetAttributeValue("date", report.ReportDate.ToString(format));
summary.SetAttributeValue("total-sum", report.Sum.ToString());
elSale.Add(summary);
}
}
sales.Add(elSale);
}
sales.Save(filePath);
}
}
public static void SeedDatabase()
{
using (SupermarketDbModel mysql = new SupermarketDbModel())
using (SupermarketDBContext sql = new SupermarketDBContext())
{
//sql.Database.Delete();
foreach (var measure in mysql.Measures)
{
if (!sql.Measures.Any(x=> x.MeasureId == measure.MeasureId))
{
SupermarketEF.Model.Measure measureToAdd = new SupermarketEF.Model.Measure
{
MeasureId = measure.MeasureId,
MeasureName = measure.MeasureName
};
sql.Measures.Add(measureToAdd);
}
}
sql.SaveChanges();
foreach (var vendor in mysql.Vendors)
{
if (!sql.Vendors.Any(x => x.VendorId == vendor.VendorId))
{
SupermarketEF.Model.Vendor vendorToAdd = new SupermarketEF.Model.Vendor
{
//Products = new HashSet<SupermarketEF.Model.Product>(),
VendorId = vendor.VendorId,
VendorName = vendor.VendorName
};
sql.Vendors.Add(vendorToAdd);
}
sql.SaveChanges();
}
foreach (var product in mysql.Products)
{
if (!sql.Products.Any(x => x.ProductId == product.ProductId))
{
SupermarketEF.Model.Product productToAdd = new SupermarketEF.Model.Product
{
VendorId = product.VendorId,
ProductName = product.ProductName,
MeasureId = product.MeasureId,
BasePrice = product.BasePrice
};
sql.Products.Add(productToAdd);
}
sql.SaveChanges();
}
}
}
public static void ParseExcelFile(SupermarketDBContext sqlDb, string folderDate, string file)
{
string connectStr = @"Provider=Microsoft.ACE.OLEDB.12.0; " +
@"Data Source=" + file + "; " +
@"Extended Properties='Excel 12.0 Xml; HDR=YES'";
OleDbConnection dbCon = new OleDbConnection(connectStr);
string sqlQuery = "select * from [Sales$]";
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(sqlQuery, dbCon);
DataSet currentData = new DataSet();
myDataAdapter.Fill(currentData, "Sales");
DataRowCollection excelRows = currentData.Tables["Sales"].Rows;
string location = string.Empty;
DateTime saleDate = DateTime.Parse(folderDate);
location = excelRows[0][0].ToString();
for (int row = 2; row < excelRows.Count - 1; row++)
{
int productId = 0;
int quantity = 0;
decimal unitPrice = 0;
decimal sum = 0;
int.TryParse(excelRows[row][0].ToString(), out productId);
int.TryParse(excelRows[row][1].ToString(), out quantity);
decimal.TryParse(excelRows[row][2].ToString(), out unitPrice);
decimal.TryParse(excelRows[row][3].ToString(), out sum);
if (productId != 0)
{
SalesReport currentReport = new SalesReport
{
Location = location,
ProductId = productId,
Quantity = quantity,
Sum = sum,
UnitPrice = unitPrice,
ReportDate = saleDate
};
sqlDb.SalesReports.Add(currentReport);
sqlDb.SaveChanges();
}
}
}
public static void ParseExcelDirectory(SupermarketDBContext sqldb, string directoryPath)
{
DirectoryInfo dir = new DirectoryInfo(directoryPath);
foreach (var subDirectory in dir.GetDirectories())
{
FileInfo[] files = subDirectory.GetFiles();
//DateTime folderDate = DateTime.Parse(subDirectory.Name);
foreach (var item in files)
{
Console.WriteLine(item.FullName);
ParseExcelFile(sqldb, subDirectory.Name, item.FullName);
sqldb.SaveChanges();
Console.WriteLine();
}
}
}
public static void TestReportReader()
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
SupermarketDBContext sqldb = new SupermarketDBContext();
ExtractZipFIle(@"../Sample-Sales-Reports.zip", @"../Reports");
ParseExcelDirectory(sqldb, @"../Reports/");
}
public static void CreatePdf(string path)
{
Document myDoc = new Document(PageSize.A4.Rotate());
iTextSharp.text.pdf.PdfWriter.GetInstance(myDoc, new FileStream(path, FileMode.Create));
myDoc.Open();
PdfPTable table = new PdfPTable(5);
table.TotalWidth = 800f;
table.LockedWidth = true;
Font font = new Font(Font.FontFamily.COURIER, 20f, 1);
font.SetStyle("bold");
PdfPCell cell = new PdfPCell(new Phrase("Aggregated Sales Report", font));
cell.Colspan = 5;
cell.Indent = 50;
cell.HorizontalAlignment = Element.ALIGN_CENTER;
cell.Padding = 5;
cell.BackgroundColor = new BaseColor(229, 228, 226);
table.AddCell(cell);
using (SupermarketDBContext sqlContext = new SupermarketDBContext())
{
//MAGIC QUERRY
//var salesRep = ( from sr in sqlContext.SalesReports
// join p in sqlContext.Products
// on sr.ProductId equals p.ProductId
// select new { sr, p.ProductName }).Distinct().GroupBy(x => x.sr.ReportDate);
var salesReports = sqlContext.SalesReports.GroupBy(x => x.ReportDate).ToList();
decimal grandTotal = 0m;
foreach (var reports in salesReports)
{
string format = "dd-MMM-yyyy";
var date = reports.First().ReportDate.ToString(format);
PdfPCell dateCell = new PdfPCell(
new Phrase(date, font));
dateCell.Colspan = 5;
dateCell.Indent = 50;
dateCell.HorizontalAlignment = Element.ALIGN_CENTER;
dateCell.Padding = 5;
dateCell.BackgroundColor = new BaseColor(229, 228, 226);
table.AddCell(dateCell);
foreach (var report in reports)
{
string productId = report.Product.ProductName.ToString();
string quantity = report.Quantity.ToString();
string unitPrice = report.UnitPrice.ToString();
string location = report.Location.ToString();
string sum = report.Sum.ToString();
table.AddCell(productId);
table.AddCell(quantity);
table.AddCell(unitPrice);
table.AddCell(location);
table.AddCell(sum);
}
PdfPCell footrCell = new PdfPCell(new Phrase("Total sum for " + date));
footrCell.Colspan = 4;
footrCell.Indent = 50;
footrCell.HorizontalAlignment = Element.ALIGN_RIGHT;
footrCell.Padding = 5;
table.AddCell(footrCell);
//PdfPCell totalCell = new PdfPCell();
decimal reportSum = reports.Sum(x => x.Sum);
grandTotal += reportSum;
table.AddCell(reportSum.ToString());
}
PdfPCell grandFootrCell = new PdfPCell(new Phrase("Grand total:"));
grandFootrCell.Colspan = 4;
grandFootrCell.Indent = 50;
grandFootrCell.HorizontalAlignment = Element.ALIGN_RIGHT;
grandFootrCell.Padding = 5;
table.AddCell(grandFootrCell);
table.AddCell(new PdfPCell(new Phrase(grandTotal.ToString(),font)));
myDoc.Add(table);
}
myDoc.Close();
}
