public static bool ExportSalesToJsonAndMongoDb(DateTime fromDate, DateTime toDate)
{
var context = new MsSqlEntities();
var productIds = context.Sales
.Where(s => s.Date >= fromDate && s.Date <= toDate)
.Select(s => s.ProductId)
.Distinct();
if (!productIds.Any())
{
return false;
}
List<Report> reports = new List<Report>();
foreach (var productId in productIds)
{
var productName = context.Sales
.Where(s => s.ProductId == productId &&
s.Date >= fromDate &&
s.Date <= toDate)
.Select(s => s.Product.Name)
.FirstOrDefault();
var vendorName = context.Sales
.Where(s => s.ProductId == productId &&
s.Date >= fromDate &&
s.Date <= toDate)
.Select(s => s.Product.Vendor.Name)
.FirstOrDefault();
var quantity = context.Sales
.Where(s => s.ProductId == productId &&
s.Date >= fromDate &&
s.Date <= toDate)
.Sum(s => s.Quantity);
var totalPrice = context.Sales
.Where(s => s.ProductId == productId &&
s.Date >= fromDate &&
s.Date <= toDate)
.Sum(s => s.Quantity * s.UnitPrice);
Report newReport = new Report
{
ProductId = productId,
ProductName = productName,
VendorName = vendorName,
TotalSoldQuantity = quantity,
TotalIncomes = totalPrice
};
reports.Add(newReport);
}
Json.GenerateJsonReports(reports);
MongoDb.InsertReportsInDatabase(reports);
return true;
}
public IList<Sale> GetSales(MsSqlEntities context)
{
var sales = new List<Sale>();
this.ExtractZip(this.ZipPath, this.ZipName, DefaultZipPath);
var reportsDirectories = Directory.GetDirectories(DefaultZipPath);
foreach (var directory in reportsDirectories)
{
var files = Directory.GetFiles(directory);
var salesDate = Path.GetFileName(directory);
var date = DateTime.ParseExact(salesDate, DefaultDateFormat, CultureInfo.InvariantCulture);
foreach (var file in files)
{
var sale = this.GetSaleFromXls(context, file, date);
sales.AddRange(sale);
}
}
if (Directory.Exists(DefaultZipPath))
{
Directory.Delete(DefaultZipPath, true);
}
// context.Sales.AddRange(sales);
// context.SaveChanges();
return sales;
}
public static void DisplayMsSqlMenu()
{
bool sqlExit = false;
while (true)
{
Console.WriteLine("\nMS SQL Options:");
Console.WriteLine("1) Display Aggregated Data:");
Console.WriteLine("2) Load Data from Oracle Database.");
Console.WriteLine("3) Load Excel Reports from ZIP File");
Console.WriteLine("0) Exit.");
Console.Write("\nPlease, select option: ");
string sqlChoice = Console.ReadLine();
switch (sqlChoice)
{
case "0":
sqlExit = true;
break;
case "1":
Console.WriteLine("Selected option: " + sqlChoice);
break;
case "2":
Console.WriteLine("Selected option: " + sqlChoice);
CloneOracleDbToSql.Run();
break;
case "3":
Console.WriteLine("Selected option: " + sqlChoice);
var context = new MsSqlEntities();
var selectedFile = OpenFile();
var fileName = ExtractFileName(selectedFile);
var path = Path.GetDirectoryName(selectedFile);
var data = new ExcelImport(path, fileName).GetSales(context);
context.Sales.AddRange(data);
context.SaveChanges();
break;
default:
Console.WriteLine("Invalid selection!");
break;
}
if (sqlExit)
{
break;
}
}
}
public static bool GenerateXmlReports(DateTime fromDate, DateTime toDate)
{
var context = new MsSqlEntities();
var vendorsName = context.Vendors
.Select(v => v.Name)
.Distinct();
XElement rootElement = new XElement("sales");
foreach (var vendorName in vendorsName)
{
var salesByVendor = context.Sales
.Where(s => s.Date >= fromDate && s.Date <= toDate && s.Vendor.Name == vendorName)
.OrderBy(s => s.Date)
.GroupBy(s => s.Date)
.Select(group => new
{
Date = group.Key,
TotalSum = group.Sum(s => s.Quantity * s.UnitPrice)
});
XElement vendor = new XElement("sale");
vendor.SetAttributeValue("vendor", vendorName);
foreach (var sale in salesByVendor)
{
XElement summary = new XElement("summary");
summary.SetAttributeValue("date", sale.Date.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture));
summary.SetAttributeValue("total-sum", sale.TotalSum);
vendor.Add(summary);
}
rootElement.Add(vendor);
}
rootElement.Save("../../../Sales-by-Vendors-Report.xml");
return true;
}
public static void ImportExpensesInDatabase(string fileName)
{
var context = new MsSqlEntities();
XDocument doc = XDocument.Load(fileName);
var vendorsXml = doc.XPathSelectElements("expenses-by-month/vendor");
foreach (var vendorXml in vendorsXml)
{
string vendorName = vendorXml.Attribute("name").Value;
var expensesXml = vendorXml.XPathSelectElements("expenses");
foreach (var expsenseXml in expensesXml)
{
DateTime month = DateTime.Parse(expsenseXml.Attribute("month").Value);
decimal sum = decimal.Parse(expsenseXml.Value);
Vendor vendor = context.Vendors
.FirstOrDefault(v => v.Name == vendorName);
if (vendor == null)
{
Vendor newVendor = new Vendor();
newVendor.Name = vendorName;
context.Vendors.Add(newVendor);
context.SaveChanges();
vendor = newVendor;
}
int vendorId = vendor.Id;
Expense newExpense = new Expense();
newExpense.Date = month;
newExpense.Sum = sum;
newExpense.VendorId = vendorId;
context.Expenses.Add(newExpense);
}
}
context.SaveChanges();
}
public static bool GeneratePdfReports(DateTime fromDate, DateTime toDate)
{
var context = new MsSqlEntities();
var sales = context.Sales
.Where(s => s.Date >= fromDate && s.Date <= toDate)
.GroupBy(s => s.Date)
.Select(
s => new
{
s.Key,
product = s.Select(p => new
{
p.Product.Name,
p.Quantity,
p.UnitPrice,
location = p.Supermarket.Name,
sum = p.Quantity * p.UnitPrice
})
});
if (sales.Any())
{
using (FileStream fs = new FileStream("../../../sales-reports.pdf", FileMode.Create))
{
Rectangle rec = new Rectangle(PageSize.A4);
Document doc = new Document(rec);
PdfWriter writer = PdfWriter.GetInstance(doc, fs);
doc.Open();
PdfPTable table = new PdfPTable(5);
table.TotalWidth = 510f;
table.LockedWidth = true;
float[] widths = { 2f, 1f, 1f, 3f, 1f };
table.SetWidths(widths);
table.HorizontalAlignment = 1;
table.SpacingBefore = 20f;
table.SpacingAfter = 30f;
PdfPCell cell = new PdfPCell(new Phrase("Aggregated Sales Report"));
cell.Colspan = 5;
cell.HorizontalAlignment = 1;
table.AddCell(cell);
foreach (var product in sales)
{
PdfPCell date =
new PdfPCell(new Phrase("Date: " + product.Key.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture)));
date.Colspan = 5;
date.HorizontalAlignment = 0;
date.BackgroundColor = new BaseColor(235, 235, 235);
table.AddCell(date);
PdfPCell headerProduct = new PdfPCell(new Phrase("Product"));
headerProduct.HorizontalAlignment = 1;
headerProduct.BackgroundColor = new BaseColor(210, 210, 210);
table.AddCell(headerProduct);
PdfPCell headerQuantity = new PdfPCell(new Phrase("Quantity"));
headerQuantity.HorizontalAlignment = 1;
headerQuantity.BackgroundColor = new BaseColor(210, 210, 210);
table.AddCell(headerQuantity);
PdfPCell headerPrice = new PdfPCell(new Phrase("Unit Price"));
headerPrice.HorizontalAlignment = 1;
headerPrice.BackgroundColor = new BaseColor(210, 210, 210);
table.AddCell(headerPrice);
PdfPCell headerLocation = new PdfPCell(new Phrase("Location"));
headerLocation.HorizontalAlignment = 1;
headerLocation.BackgroundColor = new BaseColor(210, 210, 210);
table.AddCell(headerLocation);
PdfPCell headerSum = new PdfPCell(new Phrase("Sum"));
headerSum.HorizontalAlignment = 1;
headerSum.BackgroundColor = new BaseColor(210, 210, 210);
table.AddCell(headerSum);
foreach (var sale in product.product)
{
PdfPCell columnProduct = new PdfPCell(new Phrase(sale.Name));
date.HorizontalAlignment = 1;
table.AddCell(columnProduct);
PdfPCell columnQuantity = new PdfPCell(new Phrase(sale.Quantity.ToString()));
date.HorizontalAlignment = 1;
table.AddCell(columnQuantity);
PdfPCell columnPrice = new PdfPCell(new Phrase(sale.UnitPrice.ToString()));
date.HorizontalAlignment = 1;
table.AddCell(columnPrice);
PdfPCell columnLocation = new PdfPCell(new Phrase(sale.location));
date.HorizontalAlignment = 1;
table.AddCell(columnLocation);
PdfPCell columnSum = new PdfPCell(new Phrase(sale.sum.ToString()));
date.HorizontalAlignment = 1;
table.AddCell(columnSum);
}
PdfPCell footerTitle =
new PdfPCell(
new Phrase("Total sum for " + product.Key.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture) + ": "));
footerTitle.Colspan = 4;
footerTitle.HorizontalAlignment = 2;
table.AddCell(footerTitle);
PdfPCell footerTotal =
new PdfPCell(
new Phrase(product.product.Sum(p => p.Quantity * p.UnitPrice).ToString(CultureInfo.InvariantCulture)));
footerTotal.HorizontalAlignment = 1;
table.AddCell(footerTotal);
}
doc.Add(table);
doc.Close();
}
return true;
}
return false;
}
private Supermarket CheckSupermarketExist(string supermarketName, MsSqlEntities context)
{
var supermarket = context.Supermarkets.FirstOrDefault(s => s.Name == supermarketName);
if (supermarket == null)
{
// TODO: Add new supermarket from report.
supermarket = new Supermarket { Name = supermarketName };
context.Supermarkets.Add(supermarket);
context.SaveChanges();
}
return supermarket;
}
private IList<Sale> GetSaleFromXls(MsSqlEntities context, string file, DateTime date)
{
IList<Sale> sales = new List<Sale>();
var report = new Spreadsheet();
using (report)
{
report.LoadFromFile(file);
Worksheet worksheet = report.Workbook.Worksheets.ByName("Sales");
var supermarketName =
worksheet.Cell(WorksheetSettings.StartRow, WorksheetSettings.StartCell).Value.ToString();
string productName;
Supermarket supermarket;
Product product;
int quantity;
decimal sum;
int currentRow = WorksheetSettings.FirstContentRow;
supermarket = this.CheckSupermarketExist(supermarketName, context);
string checkContent = worksheet.Cell(currentRow, WorksheetSettings.ProductCell).ValueAsString;
while (checkContent != WorksheetSettings.EndRowContent)
{
productName = worksheet.Cell(currentRow, WorksheetSettings.ProductCell).ValueAsString;
quantity = worksheet.Cell(currentRow, WorksheetSettings.QuantityCell).ValueAsInteger;
sum = decimal.Parse(worksheet.Cell(currentRow, WorksheetSettings.ProductSumCell).ValueAsString);
product = this.CheckValidProduct(productName, context);
var sale = new Sale
{
Product = product,
Supermarket = supermarket,
Quantity = quantity,
UnitPrice = product.Price,
Sum = sum,
Date = date,
Vendor = product.Vendor
};
sales.Add(sale);
currentRow++;
checkContent = worksheet.Cell(currentRow, WorksheetSettings.ProductCell).ValueAsString;
}
}
return sales;
}
private Product CheckValidProduct(string productName, MsSqlEntities context)
{
var product = context.Products.FirstOrDefault(p => p.Name == productName);
if (product == null)
{
// TODO: Add new products from report fuctionality.
product = new Product
{
Name = productName,
CategoryId = 2,
MeasureId = 2,
Vendor = context.Vendors.Find(9),
Price = 31.2m
};
context.Products.Add(product);
context.SaveChanges();
}
return product;
}
