public static void SalesToJson()
{
string mongoDbConnectionString = Settings.Default.MongoDbConnectionString;
MongoDatabase mongoDb = Connector.GetDb(mongoDbConnectionString, "Supermarkets");
MongoCollection productVendorSalesMongoDb = mongoDb.GetCollection("ProductVendorSales");
using (SupermarketsEntities1 mssqlContext = new SupermarketsEntities1())
{
var aggregatedSalesReport =
from rd in mssqlContext.ReportDetails
join p in mssqlContext.Products
on rd.ProductId equals p.ProductsId
join v in mssqlContext.Vendors
on p.VendorId equals v.VendorId
select new
{
productId = p.ProductsId,
productName = p.ProductName.TrimEnd(),
vendorName = v.VendorName.TrimEnd(),
quantitySold = rd.Quantity,
incomes = rd.Quantity * rd.UnitPrice
};
var groupedByProduct = aggregatedSalesReport.GroupBy(x => new
{
productId = x.productId,
productName = x.productName,
vendorName = x.vendorName
});
foreach (var productGroup in groupedByProduct)
{
ProductVendorSales newSales = new ProductVendorSales()
{
ProductId = productGroup.Key.productId,
ProductName = productGroup.Key.productName,
VendorName = productGroup.Key.vendorName,
TotalQuantitySold = productGroup.Sum(x => x.quantitySold),
TotalIncomes = productGroup.Sum(x => x.incomes)
};
string newJson = JsonConvert.SerializeObject(newSales);
BsonDocument document = MongoDB.Bson.Serialization.BsonSerializer.Deserialize <BsonDocument>(newJson);
productVendorSalesMongoDb.Insert(document);
//var ProductVendorSalesResults = mongoDb.GetCollection("ProductVendorSales").FindAll();
string jsonExportPath = Settings.Default.JsonExportPath;
using (StreamWriter writer = new StreamWriter(jsonExportPath + "\\" + productGroup.Key.productId + ".json"))
{
writer.Write(newJson);
}
}
}
}
// Task 5
public static void ExportFromXml()
{
string mongoDbConnectionString = Settings.Default.MongoDbConnectionString;
MongoDatabase mongoDb = Connector.GetDb(mongoDbConnectionString, "Supermarkets");
MongoCollection vendorSalesMongoDb = mongoDb.GetCollection("VendorSales");
//TODO : To take it from setting file
string path = @"../../XmlExports/expenses.xml";
XmlDocument doc = new XmlDocument();
doc.Load(path);
XmlNode rootNode = doc.DocumentElement;
using (var context = new SupermarketsEntities1())
{
foreach (XmlNode node in rootNode.ChildNodes)
{
var vendor = node.Attributes["vendor"].Value;
var vendorId = context.Vendors.First(v => v.VendorName == vendor).VendorId;
foreach (XmlNode childNode in node.ChildNodes)
{
// MSSQL Server import
var month = childNode.Attributes["month"].Value;
var expenseSum = decimal.Parse(childNode.InnerText);
DateTime date = DateTime.ParseExact(month, "MMM-yyyy", CultureInfo.InvariantCulture);
VendorSale newVendorSale = new VendorSale
{
Date = date,
Expenses = expenseSum,
VendorId = vendorId
};
context.VendorSales.Add(newVendorSale);
// MongoDb import
var newVendorSaleJson = new
{
date = date,
expenses = expenseSum,
vendorId = vendorId
};
string newJson = JsonConvert.SerializeObject(newVendorSaleJson);
BsonDocument document = MongoDB.Bson.Serialization.BsonSerializer.Deserialize <BsonDocument>(newJson);
vendorSalesMongoDb.Insert(document);
//var VendorSalesResults = mongoDb.GetCollection("VendorSales").FindAll();
}
}
context.SaveChanges();
}
}
public static void ReadReportsToMSSQL()
{
string zipReportsPath = Settings.Default.ZipReportsPath;
string zipReportsExportPath = Settings.Default.ZipReportsExtractPath;
using (ZipFile zip = ZipFile.Read(zipReportsPath))
{
foreach (ZipEntry zipEntry in zip)
{
zipEntry.Extract(zipReportsExportPath, ExtractExistingFileAction.OverwriteSilently);
}
}
using (SupermarketsEntities1 mssqlSupermarketContext = new SupermarketsEntities1())
{
Dictionary <string, Supermarket> supermarketsInTheDB = new Dictionary <string, Supermarket>();
foreach (MSSQLEntityModel.Supermarket supermarket in mssqlSupermarketContext.Supermarkets)
{
supermarketsInTheDB.Add(supermarket.SupermarketName.TrimEnd(), supermarket);
}
string[] dirReportExport = Directory.GetDirectories(zipReportsExportPath);
foreach (string dir in dirReportExport)
{
int indexOfLastSlash = dir.LastIndexOf("\\");
string dirName = dir.Substring(indexOfLastSlash + 1, dir.Length - indexOfLastSlash - 1);
CultureInfo provider = CultureInfo.InvariantCulture;
DateTime currentDate = DateTime.ParseExact(dirName, "dd-MMM-yyyy", provider);
string[] currentDirFiles = Directory.GetFiles(dir, "*.xls");
foreach (string currentFile in currentDirFiles)
{
ExportExcelFileData(mssqlSupermarketContext, currentDate, currentFile, supermarketsInTheDB);
}
}
mssqlSupermarketContext.SaveChanges();
System.IO.DirectoryInfo zipReportsExportDirectoryInfo = new DirectoryInfo(zipReportsExportPath);
foreach (DirectoryInfo innerDir in zipReportsExportDirectoryInfo.GetDirectories())
{
innerDir.Delete(true);
}
}
}
// Task 3
public static void importReportToXML()
{
using (var db = new SupermarketsEntities1())
{
var products = from v in db.Vendors
join p in db.Products
on v.VendorId equals p.VendorId
join rd in db.ReportDetails
on p.ProductsId equals rd.ProductId
join r in db.Reports
on rd.ReportId equals r.ReportId
select new
{
reportDate = r.ReportDate,
productPrice = rd.UnitPrice * rd.Quantity,
vendor = v.VendorName.TrimEnd()
};
var groupedByVendor = products.GroupBy(x => x.vendor);
XElement salesXml = new XElement("sales");
foreach (var vendorGrouped in groupedByVendor)
{
XElement sale = new XElement("sale");
//sale.Attribute("vendor").Value = "dddd";//vendorGrouped.Key.ToString();
sale.Add(new XAttribute("vendor", vendorGrouped.Key));
var groupedByDate = vendorGrouped.GroupBy(x => x.reportDate);
foreach (var dateGroup in groupedByDate)
{
var totalSum = dateGroup.Sum(x => x.productPrice);
XElement summary = new XElement("summary");
summary.Add(new XAttribute("total-sum", totalSum.ToString("F2")));
summary.Add(new XAttribute("date", string.Format("{0:d-MMM-yyyy}", dateGroup.Key)));
sale.Add(summary);
}
salesXml.Add(sale);
}
string xmlReportExtractPath = Settings.Default.XmlReportExtractPath;
salesXml.Save(xmlReportExtractPath);
}
}
public static void MySqlToMSSQLTransfer()
{
//using (SupermarketsEntities1 mssqlSupermarketContext = new SupermarketsEntities1())
//{
// string[] tablesToDelete = new string[] { "VendorSales", "Vendors", "Measures", "Supermarkets", "Reports", "ReportDetails", "Products" };
// string delQuery = "TRUNCATE TABLE Measures";
// var rowsDelete = (mssqlSupermarketContext as IObjectContextAdapter).ObjectContext.ExecuteStoreQuery<int>(delQuery);
// //foreach (var table in tablesToDelete)
// //{
// // string delQuery = "DELETE " + table;
// // var rowsDelete = (mssqlSupermarketContext as IObjectContextAdapter).ObjectContext.ExecuteStoreQuery<int>(delQuery);
// //}
//}
using (EntitiesModel mysqlSupermarketContext = new EntitiesModel())
{
using (SupermarketsEntities1 mssqlSupermarketContext = new SupermarketsEntities1())
{
mssqlSupermarketContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Vendors OFF");
}
using (SupermarketsEntities1 mssqlSupermarketContext = new SupermarketsEntities1())
{
mssqlSupermarketContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Measures OFF");
}
using (SupermarketsEntities1 mssqlSupermarketContext = new SupermarketsEntities1())
{
mssqlSupermarketContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Products OFF");
}
using (SupermarketsEntities1 mssqlSupermarketContext = new SupermarketsEntities1())
{
foreach (var vendor in mysqlSupermarketContext.Vendors)
{
mssqlSupermarketContext.Vendors.Add(ConvertMySqlToMSSQL.ConvertMySqlToMSSQLVendor(vendor));
}
foreach (var measure in mysqlSupermarketContext.Measures)
{
mssqlSupermarketContext.Measures.Add(ConvertMySqlToMSSQL.ConvertMySqlToMSSQLMeasure(measure));
}
foreach (var product in mysqlSupermarketContext.Products)
{
mssqlSupermarketContext.Products.Add(ConvertMySqlToMSSQL.ConvertMySqlToMSSQLProduct(product));
}
mssqlSupermarketContext.SaveChanges();
}
using (SupermarketsEntities1 mssqlSupermarketContext = new SupermarketsEntities1())
{
mssqlSupermarketContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Vendors ON");
}
using (SupermarketsEntities1 mssqlSupermarketContext = new SupermarketsEntities1())
{
mssqlSupermarketContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Measures ON");
}
using (SupermarketsEntities1 mssqlSupermarketContext = new SupermarketsEntities1())
{
mssqlSupermarketContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Products ON");
}
}
}
private static void ExportExcelFileData(SupermarketsEntities1 mssqlSupermarketContext, DateTime currentDate, string currentFile, Dictionary <string, Supermarket> supermarketsInTheDB)
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + currentFile + ";Extended Properties=Excel 12.0;";
OleDbConnection excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
using (excelConnection)
{
OleDbCommand getExcelSheetData = new OleDbCommand(
"SELECT * " +
"FROM [Sales$]", excelConnection);
OleDbDataReader reader = getExcelSheetData.ExecuteReader();
using (reader)
{
bool headerRow = true;
Report newReport = null;
while (reader.Read())
{
var firstColumnValue = reader[0];
if (headerRow)
{
string supermarketNameFull = (string)firstColumnValue;
int indexFirstQuote = supermarketNameFull.IndexOf('“');
int indexSecondQuote = supermarketNameFull.IndexOf('”');
string supermarketName = supermarketNameFull.Substring(indexFirstQuote + 1, indexSecondQuote - indexFirstQuote - 1);
Supermarket currentSupermarket;
if (supermarketsInTheDB.ContainsKey(supermarketName))
{
currentSupermarket = supermarketsInTheDB[supermarketName];
}
else
{
currentSupermarket = new Supermarket();
currentSupermarket.SupermarketName = supermarketName;
mssqlSupermarketContext.Supermarkets.Add(currentSupermarket);
supermarketsInTheDB[supermarketName] = currentSupermarket;
}
newReport = new Report();
newReport.ReportDate = currentDate;
currentSupermarket.Reports.Add(newReport);
headerRow = false;
}
;
int productId;
if (int.TryParse((string)firstColumnValue, out productId))
{
ReportDetail newReportDetail = new ReportDetail();
string secondColumnValue = reader[1].ToString();
newReportDetail.Quantity = int.Parse(secondColumnValue);
string thirdColumnValue = reader[2].ToString();
newReportDetail.UnitPrice = decimal.Parse(thirdColumnValue);
newReport.ReportDetails.Add(newReportDetail);
MSSQLEntityModel.Product currentProduct = mssqlSupermarketContext.Products.Find(productId);
currentProduct.ReportDetails.Add(newReportDetail);
}
}
}
}
}
public static void SalesToPdf()
{
var document = new Document();
string pdfExportPath = Settings.Default.PdfExportPath;
var output = new FileStream(pdfExportPath + "/AggregatedSalesReport.pdf", FileMode.OpenOrCreate);
var writer = PdfWriter.GetInstance(document, output);
document.Open();
PdfPTable mainTable = new PdfPTable(5);
AddNewCell(mainTable, REPORT_TITLE, 5, 1);
using (SupermarketsEntities1 mssqlContext = new SupermarketsEntities1())
{
var aggregatedSalesReport =
from s in mssqlContext.Supermarkets
join r in mssqlContext.Reports
on s.SupermarketId equals r.SupermarketId
join rd in mssqlContext.ReportDetails
on r.ReportId equals rd.ReportId
join p in mssqlContext.Products
on rd.ProductId equals p.ProductsId
join m in mssqlContext.Measures
on p.MeasureId equals m.MeasureId
select new
{
supermarketName = s.SupermarketName.TrimEnd(),
reportDate = r.ReportDate,
quantity = rd.Quantity,
unitPrice = rd.UnitPrice,
unitTotalPrice = rd.Quantity * rd.UnitPrice,
productName = p.ProductName.TrimEnd(),
mesureName = m.MeasureName.TrimEnd()
};
var aggregatedSalesReportGrouped = aggregatedSalesReport.GroupBy(x => x.reportDate);
foreach (var dateGroup in aggregatedSalesReportGrouped)
{
string groupDateAsString = string.Format("{0:d-MMM-yyyy}", dateGroup.Key);
AddNewCell(mainTable, "Date: " + groupDateAsString, 5, 0, "Gray");
AddHeaderRow(mainTable);
foreach (var reportDetail in dateGroup)
{
mainTable.AddCell(reportDetail.productName);
AddNewCell(mainTable, reportDetail.quantity + " " + reportDetail.mesureName, 1, 1);
AddNewCell(mainTable, reportDetail.unitPrice.ToString(), 1, 1);
mainTable.AddCell(reportDetail.supermarketName);
AddNewCell(mainTable, reportDetail.unitTotalPrice.ToString("F2"), 1, 2);
}
AddNewCell(mainTable, "Total sum for " + groupDateAsString + ":", 4, 2);
decimal totalSum = dateGroup.Sum(x => x.unitTotalPrice);
AddNewCell(mainTable, totalSum.ToString("F2"), 1, 2);
}
decimal grandTotal = aggregatedSalesReport.Sum(x => x.unitTotalPrice);
AddNewCell(mainTable, "Grand total:", 4, 2);
AddNewCell(mainTable, grandTotal.ToString("F2"), 1, 2);
}
document.Add(mainTable);
document.Close();
}
public static void VendorsTotalReport(DateTime forDate)
{
//TransferFromMongoDbToSqLite();
using (SupermarketsSqLiteEntities sqlLiteContext = new SupermarketsSqLiteEntities())
{
var vendorProductSalesAndTaxes =
from ps in sqlLiteContext.ProductSalesReports
join pt in sqlLiteContext.ProductTaxes
on ps.ProductName equals pt.ProductName
select new
{
vendorName = ps.VendorName,
productTotalIncomes = ps.TotalIncomes,
productTotalTaxes = ps.TotalIncomes * pt.Tax
};
var vendorSalesAndTaxes =
(from vst in vendorProductSalesAndTaxes
group vst by vst.vendorName into vendorGroup
select new
{
vendorName = vendorGroup.Key,
vendorTotalIncomes = vendorGroup.Sum(x => x.productTotalIncomes),
vendorTotalTaxes = vendorGroup.Sum(x => x.productTotalTaxes)
}).ToArray();
using (SupermarketsEntities1 mssqlContext = new SupermarketsEntities1())
{
var vendorExpences =
(from vs in mssqlContext.VendorSales
join v in mssqlContext.Vendors
on vs.VendorId equals v.VendorId
where ((vs.Date.Year == forDate.Year) && (vs.Date.Month == forDate.Month))
select new
{
vendorExpences = vs.Expenses,
vendorName = v.VendorName.TrimEnd()
}).ToArray();
var vendorFinancialResult =
from ve in vendorExpences
join vst in vendorSalesAndTaxes
on ve.vendorName equals vst.vendorName
select new
{
vendorName = vst.vendorName,
vendorTotalIncomes = vst.vendorTotalIncomes,
vendorTotalTaxes = vst.vendorTotalTaxes,
vendorExpences = ve.vendorExpences,
vendorFinancialResult = vst.vendorTotalIncomes - ve.vendorExpences - vst.vendorTotalTaxes
};
//System.IO.DirectoryInfo zipReportsExportDirectoryInfo = new DirectoryInfo(zipReportsExportPath);
string excelFilePath = Settings.Default.ExcelExportTotalProductReportPath;
System.IO.FileInfo excelFile = new FileInfo(excelFilePath);
excelFile.Delete();
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 12.0 XML;HDR=YES'";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
using (excelConnection)
{
excelConnection.Open();
OleDbCommand createTable = new OleDbCommand(
"CREATE TABLE [Sheet1] ([Vendor] varchar(255), [Incomes] number, [Expenses] number, [Taxes] number, [Financial Result] number)"
, excelConnection);
createTable.ExecuteNonQuery();
OleDbCommand setExcelSheetData = new OleDbCommand(
"INSERT INTO [Sheet1$] ([Vendor], [Incomes], [Expenses], [Taxes], [Financial Result]) " +
"VALUES (@vendorName, @invomes, @expenses, @taxes, @financialResult)", excelConnection);
foreach (var vendorFinancialRecord in vendorFinancialResult)
{
setExcelSheetData.Parameters.AddWithValue("@vendorName", vendorFinancialRecord.vendorName);
setExcelSheetData.Parameters.AddWithValue("@invomes", vendorFinancialRecord.vendorTotalIncomes);
setExcelSheetData.Parameters.AddWithValue("@expenses", vendorFinancialRecord.vendorExpences);
setExcelSheetData.Parameters.AddWithValue("@taxes", vendorFinancialRecord.vendorTotalTaxes);
setExcelSheetData.Parameters.AddWithValue("@financialResult", vendorFinancialRecord.vendorFinancialResult);
setExcelSheetData.ExecuteNonQuery();
}
}
}
}
}
