public static void LoadExcelReportsFromZipFile(string zipFile)
{
string executionFolder = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
string salesReportsFolder = Path.Combine(executionFolder, "SalesReports");
ZipFileManager.Extract(zipFile, salesReportsFolder);
DirectoryInfo salesReportsDirectory = new DirectoryInfo(salesReportsFolder);
DirectoryInfo[] reportDirectories = salesReportsDirectory.GetDirectories();
using (var msSQLServerContext = new SupermarketEntities())
{
foreach (DirectoryInfo reportDirectory in reportDirectories)
{
DateTime reportDate = DateTime.ParseExact(
reportDirectory.Name,
"dd-MMM-yyyy",
CultureInfo.InvariantCulture);
FileInfo[] reportFiles = reportDirectory.GetFiles("*.xls");
foreach (FileInfo reportFile in reportFiles)
{
System.Data.DataTable contentsTable = ExcelManager.GetContents(reportFile.FullName);
InsertExcelFileDataInMSSQLServerDB(reportDate, contentsTable, msSQLServerContext);
}
}
}
}
public static void CreateSalesReport()
{
SupermarketEntities supermarkets = new SupermarketEntities();
var queryResult =
from vendor in supermarkets.Vendors
select new
{
Vendor = vendor.VendorName,
dayToDayReprots =
from sale in supermarkets.Sales
join product in supermarkets.Products
on sale.ProductId equals product.ProductId
join report in supermarkets.Reports
on sale.ReportId equals report.ReportId
where product.VendorId == vendor.VendorId
group sale by report.ReportDate
into reportForDate
select new
{
Date = reportForDate.Key,
TotalSum = reportForDate.Sum(p => p.ProductTotalSum)
}
};
string fileName = "../../report.xml";
Encoding encoding = Encoding.GetEncoding("windows-1251");
using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding))
{
writer.Formatting = Formatting.Indented;
writer.IndentChar = '\t';
writer.Indentation = 1;
writer.WriteStartDocument();
writer.WriteStartElement("sales");
foreach (var vendor in queryResult)
{
Console.WriteLine("Writing report for " + vendor.Vendor);
writer.WriteStartElement("sale");
writer.WriteAttributeString("vendor", vendor.Vendor);
foreach (var dayReport in vendor.dayToDayReprots)
{
writer.WriteStartElement("summary");
string dateString = string.Format("{0:dd-MMM-yyyy}", dayReport.Date);
writer.WriteAttributeString("date", dateString);
writer.WriteAttributeString("total-sum", dayReport.TotalSum.ToString("N2"));
writer.WriteEndElement();
}
writer.WriteEndElement();
}
writer.WriteEndDocument();
}
}
private static void GeneratePDFAggregatedSalesReport()
{
using (var msSQLServerContext = new SupermarketEntities())
{
var sales =
from product in msSQLServerContext.Products
join unit in msSQLServerContext.Units
on product.UnitId equals unit.UnitId
join sale in msSQLServerContext.Sales
on product.ProductId equals sale.ProductId
join report in msSQLServerContext.Reports
on sale.ReportId equals report.ReportId
join localShop in msSQLServerContext.LocalShops
on report.LocalShopId equals localShop.LocalShopId
select new
{
ProductName = product.ProductName,
ProductQuantity = sale.ProductQuantity,
Units = unit.UnitName,
ProductUnitPrice = sale.ProductUnitPrice,
LocalShopName = localShop.LocalShopName,
SaleDate = report.ReportDate,
TotalSum = sale.ProductTotalSum
};
var reports =
from sale in sales
group sale by sale.SaleDate into salesByDate
select new
{
SalesTotalSum = salesByDate.Sum(s => s.TotalSum),
Sales = salesByDate
};
foreach (var report in reports)
{
foreach (var sale in report.Sales)
{
Console.WriteLine(
"{0} {1:N0} {2} {3} {4} {5} {6}",
sale.ProductName,
sale.ProductQuantity,
sale.Units,
sale.ProductUnitPrice,
sale.LocalShopName,
sale.SaleDate,
sale.TotalSum);
}
Console.WriteLine("--------------------------------" + report.SalesTotalSum);
}
}
}
/// <summary>
/// Should be executed only once - when we need to populate the
/// MS SQL Server database.
/// </summary>
private static void CopyMySQLDataToMSSQLServer()
{
using (SupermarketModel mySQLContext = new SupermarketModel())
{
using (SupermarketEntities msSQLServerContext = new SupermarketEntities())
{
foreach (var mySQLVendor in mySQLContext.Vendors)
{
var msSQLServerVendor = new EntityFrameworkModels.Vendor
{
VendorName = mySQLVendor.VendorName
};
msSQLServerContext.Vendors.Add(msSQLServerVendor);
msSQLServerContext.SaveChanges();
}
foreach (var mySQLUnit in mySQLContext.Units)
{
var msSQLServerUnit = new EntityFrameworkModels.Unit
{
UnitName = mySQLUnit.UnitName
};
msSQLServerContext.Units.Add(msSQLServerUnit);
msSQLServerContext.SaveChanges();
}
foreach (var mySQLProduct in mySQLContext.Products)
{
var msSQLServerProduct = new EntityFrameworkModels.Product
{
VendorId = mySQLProduct.VendorID,
UnitId = mySQLProduct.UnitID,
ProductName = mySQLProduct.ProductName,
BasePrice = mySQLProduct.BasePrice
};
msSQLServerContext.Products.Add(msSQLServerProduct);
msSQLServerContext.SaveChanges();
}
}
}
}
public static void GeneratePDFAggregatedSalesReport()
{
string executionFolder = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
string salesReportFile = Path.Combine(executionFolder, "Sales-Report.pdf");
var fs = new FileStream(salesReportFile, FileMode.Create);
var document = new Document(PageSize.A4, 25, 25, 30, 30);
PdfWriter writer = PdfWriter.GetInstance(document, fs);
document.Open();
var table = new PdfPTable(5);
table.TotalWidth = 545f;
table.LockedWidth = true;
float[] widths = new float[] { 0.75f, 0.5f, 0.25f, 1.25f, 0.25f };
table.SetWidths(widths);
table.SpacingBefore = 20f;
table.SpacingAfter = 30f;
var cellTitle = new PdfPCell(new Phrase("Aggregated Sales Report", new Font(Font.HELVETICA, FontSize, Font.BOLD)))
{
Colspan = 5,
HorizontalAlignment = 1
};
cellTitle.PaddingBottom = 10f;
cellTitle.PaddingLeft = 10f;
cellTitle.PaddingTop = 4f;
table.AddCell(cellTitle);
using (var msSQLServerContext = new SupermarketEntities())
{
var sales =
from product in msSQLServerContext.Products
join unit in msSQLServerContext.Units
on product.UnitId equals unit.UnitId
join sale in msSQLServerContext.Sales
on product.ProductId equals sale.ProductId
join report in msSQLServerContext.Reports
on sale.ReportId equals report.ReportId
join localShop in msSQLServerContext.LocalShops
on report.LocalShopId equals localShop.LocalShopId
select new
{
ProductName = product.ProductName,
ProductQuantity = sale.ProductQuantity,
Units = unit.UnitName,
ProductUnitPrice = sale.ProductUnitPrice,
LocalShopName = localShop.LocalShopName,
SaleDate = report.ReportDate,
TotalSum = sale.ProductTotalSum
};
var reports =
from sale in sales
group sale by sale.SaleDate into salesByDate
select new
{
SalesTotalSum = salesByDate.Sum(s => s.TotalSum),
Sales = salesByDate
};
foreach (var report in reports)
{
DateTime reportDate = DateTime.Now;
bool first = true;
foreach (var sale in report.Sales)
{
if (first)
{
reportDate = sale.SaleDate;
WriteTableHeader(table, reportDate);
first = false;
}
var cellProduct = new PdfPCell(new Phrase(sale.ProductName, new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
cellProduct.PaddingBottom = 10f;
cellProduct.PaddingLeft = 10f;
cellProduct.PaddingTop = 4f;
table.AddCell(cellProduct);
var cellQuantity = new PdfPCell(new Phrase(sale.ProductQuantity.ToString() + " " + sale.Units, new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
cellQuantity.PaddingBottom = 10f;
cellQuantity.PaddingLeft = 10f;
cellQuantity.PaddingTop = 4f;
table.AddCell(cellQuantity);
var cellUnitPrice = new PdfPCell(new Phrase(sale.ProductUnitPrice.ToString("N2"), new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
cellUnitPrice.PaddingBottom = 10f;
cellUnitPrice.PaddingLeft = 10f;
cellUnitPrice.PaddingTop = 4f;
table.AddCell(cellUnitPrice);
var cellLocation = new PdfPCell(new Phrase(sale.LocalShopName, new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
cellLocation.PaddingBottom = 10f;
cellLocation.PaddingLeft = 10f;
cellLocation.PaddingTop = 4f;
table.AddCell(cellLocation);
var cellSum = new PdfPCell(new Phrase(sale.TotalSum.ToString("N2"), new Font(Font.HELVETICA, FontSize, Font.NORMAL)));
cellSum.PaddingBottom = 10f;
cellSum.PaddingLeft = 10f;
cellSum.PaddingTop = 4f;
table.AddCell(cellSum);
}
var cellReportTotalSum = new PdfPCell(new Phrase("Total sum for " + reportDate.ToString("dd-MMM-yyyy") + ":", new Font(Font.HELVETICA, FontSize, Font.NORMAL)))
{
Colspan = 4,
HorizontalAlignment = 2
};
cellReportTotalSum.PaddingBottom = 10f;
cellReportTotalSum.PaddingLeft = 10f;
cellReportTotalSum.PaddingTop = 4f;
table.AddCell(cellReportTotalSum);
var cellTotalSum = new PdfPCell(new Phrase(report.SalesTotalSum.ToString("N2"), new Font(Font.HELVETICA, FontSize, Font.BOLD)));
cellTotalSum.PaddingBottom = 10f;
cellTotalSum.PaddingLeft = 10f;
cellTotalSum.PaddingTop = 4f;
table.AddCell(cellTotalSum);
}
document.Add(table);
// Close the document
document.Close();
// Close the writer instance
writer.Close();
// Always close open filehandles explicity
fs.Close();
}
}
public static void GenerateProductReports(string folderName)
{
DirectoryInfo directory = Directory.CreateDirectory(folderName);
List<MongoProductReport> mongoProductsReports = new List<MongoProductReport>();
MongoDBManager<MongoProductReport> mongo = new MongoDBManager<MongoProductReport>();
using (var msSQLServerContext = new SupermarketEntities())
{
var sales =
from product in msSQLServerContext.Products
join vendor in msSQLServerContext.Vendors
on product.VendorId equals vendor.VendorId
join sale in msSQLServerContext.Sales
on product.ProductId equals sale.ProductId
group sale by sale.ProductId into productsById
select new
{
ProductId = productsById.Key,
SalesTotalQuantity = productsById.Sum(s => s.ProductQuantity),
SalesTotalSum = productsById.Sum(s => s.ProductTotalSum)
};
var productReports =
from sale in sales
join product in msSQLServerContext.Products
on sale.ProductId equals product.ProductId
join vendor in msSQLServerContext.Vendors
on product.VendorId equals vendor.VendorId
select new
{
ProductId = sale.ProductId,
ProductName = product.ProductName,
VendorName = vendor.VendorName,
SalesTotalQuantity = sale.SalesTotalQuantity,
SalesTotalSum = sale.SalesTotalSum
};
foreach (var productReport in productReports)
{
JObject jsonObject = new JObject(
new JProperty("product-id", productReport.ProductId),
new JProperty("product-name", productReport.ProductName),
new JProperty("vendor-name", productReport.VendorName),
new JProperty("total-quantity-sold", productReport.SalesTotalQuantity),
new JProperty("total-incomes", productReport.SalesTotalSum));
string filePath = Path.Combine(folderName, string.Format("{0}.json", productReport.ProductId));
using (FileStream file = File.Create(filePath))
{
using (StreamWriter writer = new StreamWriter(file))
{
writer.Write(jsonObject.ToString());
}
}
MongoProductReport mongoProductReport = new MongoProductReport
{
ProductId = productReport.ProductId,
ProductName = productReport.ProductName,
VendorName = productReport.VendorName,
TotalQuantitySold = productReport.SalesTotalQuantity,
TotalIncomes = productReport.SalesTotalSum
};
mongoProductsReports.Add(mongoProductReport);
}
}
mongo.InsertInMongoDB(mongoProductsReports, "ProductsReports");
}
/// <summary>
/// Method for extracting expenses reports in XML format
/// </summary>
/// <param name="filePath">Filepath for the .xml file</param>
public static void ReadVendorMonthlyExpenses(string filePath)
{
XmlReader reader = XmlReader.Create(filePath);
List<MongoVendorExpense> mongoVendorExpensesList = new List<MongoVendorExpense>();
using (var msSQLServerContext = new SupermarketEntities())
{
using (reader)
{
int vendorId = 0;
while (reader.Read())
{
//Insert a new vendor
if (reader.NodeType == XmlNodeType.Element && reader.Name == "sale")
{
string vendorName = reader.GetAttribute("vendor");
var vendor = msSQLServerContext.Vendors.FirstOrDefault(v => v.VendorName == vendorName);
if (vendor == null)
{
// the vendor doesn't exist
vendor = new Vendor
{
VendorName = vendorName
};
msSQLServerContext.Vendors.Add(vendor);
msSQLServerContext.SaveChanges();
}
vendorId = vendor.VendorId;
}
//Insert Expenses
if (reader.NodeType == XmlNodeType.Element && reader.Name == "expenses")
{
string month = reader.GetAttribute("month");
DateTime monthAsDate = DateTime.ParseExact(month, "MMM-yyyy", CultureInfo.InvariantCulture);
decimal amount = reader.ReadElementContentAsDecimal();
var vendorMonthlyExpense = new VendorExpense
{
VendorId = vendorId,
Month = monthAsDate.Month,
Year = monthAsDate.Year,
Amount = amount
};
var mongoVendorExpense = new MongoVendorExpense
{
VendorId = vendorId,
Month = monthAsDate.Month,
Year = monthAsDate.Year,
Amount = amount
};
mongoVendorExpensesList.Add(mongoVendorExpense);
msSQLServerContext.VendorExpenses.Add(vendorMonthlyExpense);
}
}
}
MongoDBManager<MongoVendorExpense> mongoDBInserter = new MongoDBManager<MongoVendorExpense>();
mongoDBInserter.InsertInMongoDB(mongoVendorExpensesList, "VendorExpenses");
msSQLServerContext.SaveChanges();
}
}
private static void InsertExcelFileDataInMSSQLServerDB(
DateTime reportDate,
System.Data.DataTable contentsTable,
SupermarketEntities msSQLServerContext)
{
using (TransactionScope scope = new TransactionScope())
{
string localShopName = contentsTable.Rows[0][0].ToString().Trim();
var localShop = msSQLServerContext.LocalShops.FirstOrDefault(ls => ls.LocalShopName == localShopName);
if (localShop == null)
{
localShop = new LocalShop
{
LocalShopName = localShopName
};
msSQLServerContext.LocalShops.Add(localShop);
msSQLServerContext.SaveChanges();
}
int rowsCount = contentsTable.Rows.Count;
int colsCount = contentsTable.Columns.Count;
var report = new Report
{
LocalShopId = localShop.LocalShopId,
ReportDate = reportDate,
ReportTotalSum = decimal.Parse(contentsTable.Rows[rowsCount - 1][3].ToString())
};
msSQLServerContext.Reports.Add(report);
msSQLServerContext.SaveChanges();
for (int row = 2; row < rowsCount - 1; row++)
{
int productId = int.Parse(contentsTable.Rows[row][0].ToString());
double productQuantity = double.Parse(contentsTable.Rows[row][1].ToString());
decimal productUnitPrice = decimal.Parse(contentsTable.Rows[row][2].ToString());
decimal productTotalSum = decimal.Parse(contentsTable.Rows[row][3].ToString());
var sale = new Sale
{
ReportId = report.ReportId,
ProductId = productId,
ProductQuantity = productQuantity,
ProductUnitPrice = productUnitPrice,
ProductTotalSum = productTotalSum
};
msSQLServerContext.Sales.Add(sale);
}
msSQLServerContext.SaveChanges();
scope.Complete();
}
}
private static void FillVendorsTotalReportsExcel(string excelFilePath, string sheetName)
{
OleDbConnection connection = new OleDbConnection(
string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'",
excelFilePath));
connection.Open();
using (connection)
{
using (var msSQLServerContext = new SupermarketEntities())
{
var vendorFinalReports = VendorFinalReport.FinalReportUtilities.QueryIt();
//var vendors =
// from vendor in msSQLServerContext.Vendors
// select vendor;
foreach (var vendor in vendorFinalReports)
{
OleDbCommand insertCommand = new OleDbCommand(
string.Format(
"INSERT INTO [NewSheet] ([Vendor], [Incomes], [Expenses], [Taxes], [Financial Result]) " +
"VALUES (@vendor, @incomes, @expenses, @taxes, @financialResult)"),
connection);
insertCommand.Parameters.AddWithValue("@vendor", vendor.VendorName);
insertCommand.Parameters.AddWithValue("@incomes", vendor.TotalIncome);
insertCommand.Parameters.AddWithValue("@expenses", vendor.Expenses);
insertCommand.Parameters.AddWithValue("@taxes", vendor.TotanTaxes);
insertCommand.Parameters.AddWithValue("@financialResult", vendor.Result);
insertCommand.ExecuteNonQuery();
}
}
}
}