private static void WriteSalesToWriter(XmlWriter writer, DateTime startDate, DateTime endDate)
{
using (var db = new SupermarketsChainEntities())
{
foreach (var vendor in db.Vendors.ToList())
{
var totalSalesSumByDate = db.Sales
.Where(sale =>
sale.Product.VendorId == vendor.Id &&
sale.DateOfSale >= startDate &&
sale.DateOfSale <= endDate)
.OrderBy(sale => sale.DateOfSale)
.GroupBy(sale => sale.DateOfSale)
.Select(group => new
{
Date = group.Key,
TotalSum = group.Sum(g => g.PricePerUnit * g.Quantity)
})
.ToList();
if (totalSalesSumByDate.Any())
{
writer.WriteStartElement("sale");
writer.WriteAttributeString("vendor", vendor.Name);
foreach (var sale in totalSalesSumByDate)
{
AddSaleToVendor(writer, sale.Date, sale.TotalSum);
}
writer.WriteEndElement();
}
}
}
}
private static void ExportSales(OracleConnection connection, SupermarketsChainEntities db)
{
const string query = "SELECT PRODUCT_NAME, LOCATION_NAME, QUANTITY, DATE_SALE, PRICE_PER_UNIT FROM SALES S " +
"JOIN PRODUCTS P ON P.PRODUCT_ID = S.PRODUCT_ID JOIN LOCATIONS L ON L.LOCATION_ID = S.LOCATION_ID";
using (var command = new OracleCommand(query, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var productName = (string)reader["PRODUCT_NAME"];
var locationName = (string)reader["LOCATION_NAME"];
db.Sales.Add(new Sale
{
Product = db.Products.FirstOrDefault(p => p.Name == productName),
Location = db.Locations.FirstOrDefault(l => l.Name == locationName),
Quantity = (decimal)reader["QUANTITY"],
DateOfSale = (DateTime)reader["DATE_SALE"],
PricePerUnit = (decimal)(double)reader["PRICE_PER_UNIT"]
});
}
}
}
}
private static void ImportProducts(MySqlConnection mySqlConnection, SupermarketsChainEntities db)
{
var products = db.Products.Select(p => new
{
p.Name,
VendorName = p.Vendor.Name,
TotalIncome = db.Sales
.Where(s => s.ProductId == p.Id)
.Select(s => s.Quantity * s.PricePerUnit)
.DefaultIfEmpty(0)
.Sum()
});
foreach (var product in products)
{
var query = string.Format(
"USE supermarketschain; INSERT INTO products (name,vendor_id,total_income) VALUES('{0}',{1},{2});",
product.Name,
GetVendorId(product.VendorName, mySqlConnection),
product.TotalIncome);
using (var command = new MySqlCommand(query, mySqlConnection))
{
command.ExecuteNonQuery();
}
}
}
private static void ImportVendors(MySqlConnection mySqlConnection, SupermarketsChainEntities db)
{
var vendorNames = db.Vendors.Select(v => "('" + v.Name + "')");
var query = string.Format(
"USE supermarketschain; INSERT IGNORE INTO vendors (name) VALUES {0};",
string.Join(",", vendorNames));
using (var command = new MySqlCommand(query, mySqlConnection))
{
command.ExecuteNonQuery();
}
}
public static void ImportDataFromSqlServer()
{
var mySqlConnection = new MySqlConnection(Settings.Default.MySqlConnectionString);
mySqlConnection.Open();
using (mySqlConnection)
{
using (var sqlServerDb = new SupermarketsChainEntities())
{
ImportVendors(mySqlConnection, sqlServerDb);
ImportExpenses(mySqlConnection, sqlServerDb);
ImportProducts(mySqlConnection, sqlServerDb);
}
}
}
public static void Main()
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
const string XmlResultFileName = @"..\..\..\result.xml";
var context = new SupermarketsChainEntities();
var startDate = new DateTime(2015, 1, 1);
var endDate = new DateTime(2015, 3, 30);
Console.WriteLine("Generating report from sales to xml...");
var sales = SalesByVendors(context, startDate, endDate);
GenerateXmlFromSales(sales, XmlResultFileName);
Console.WriteLine("The report is done!");
}
private static Location GetOrCreateLocation(string locationName, SupermarketsChainEntities db)
{
var location = db.Locations.FirstOrDefault(l => l.Name == locationName);
if (location == null)
{
location = new Location {
Name = locationName
};
db.Locations.Add(location);
db.SaveChanges();
}
return(location);
}
private static void ImportExpenses(MySqlConnection mySqlConnection, SupermarketsChainEntities db)
{
foreach (var expense in db.Expenses.Select(e => new { VendorName = e.Vendor.Name, e.Value }))
{
var query = string.Format(
"USE supermarketschain; INSERT INTO expenses (vendor_id,value) VALUES({0},{1});",
GetVendorId(expense.VendorName, mySqlConnection),
expense.Value);
using (var command = new MySqlCommand(query, mySqlConnection))
{
command.ExecuteNonQuery();
}
}
}
private static void ExportLocations(OracleConnection connection, SupermarketsChainEntities db)
{
using (var command = new OracleCommand("SELECT LOCATION_NAME FROM LOCATIONS", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
db.Locations.Add(new Location
{
Name = (string)reader["LOCATION_NAME"]
});
}
}
}
}
private static void ExportMeasures(OracleConnection connection, SupermarketsChainEntities db)
{
using (var command = new OracleCommand("SELECT MEASURE_NAME FROM MEASURES", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
db.Measures.Add(new Measure
{
Name = (string)reader["MEASURE_NAME"]
});
}
}
}
}
private static void ExportVendors(OracleConnection connection, SupermarketsChainEntities db)
{
using (var command = new OracleCommand("SELECT VENDOR_NAME FROM VENDORS", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
db.Vendors.Add(new Vendor
{
Name = (string)reader["VENDOR_NAME"]
});
}
}
}
}
private static Product GetOrCreateProduct(string productName, SupermarketsChainEntities db)
{
var product = db.Products.FirstOrDefault(p => p.Name == productName);
if (product == null)
{
product = new Product
{
Name = productName,
Vendor = new Vendor {
Name = productName.Split(' ').Last() + " Corp."
}
};
db.Products.Add(product);
db.SaveChanges();
}
return(product);
}
public static void ExportDbToSqlServer()
{
var connection = new OracleConnection(Settings.Default.OracleConnectionString);
connection.Open();
using (connection)
{
using (var sqlServerDb = new SupermarketsChainEntities())
{
ExportMeasures(connection, sqlServerDb);
ExportVendors(connection, sqlServerDb);
ExportLocations(connection, sqlServerDb);
sqlServerDb.SaveChanges();
ExportProducts(connection, sqlServerDb);
sqlServerDb.SaveChanges();
ExportSales(connection, sqlServerDb);
sqlServerDb.SaveChanges();
}
}
}
private static Dictionary <string, SortedSet <Summary> > SalesByVendors(SupermarketsChainEntities context, DateTime startDate, DateTime endDate)
{
using (context)
{
var salesResult = new Dictionary <string, SortedSet <Summary> >();
var sales = context.Sales.Select(s => new
{
s.Product,
s.Product.Vendor,
s.SoldOn
});
foreach (var sale in sales)
{
if (startDate <= sale.SoldOn && sale.SoldOn <= endDate)
{
var vendorName = sale.Product.Vendor.Name;
if (!salesResult.ContainsKey(vendorName))
{
salesResult[vendorName] = new SortedSet <Summary>();
}
var summary = new Summary(sale.SoldOn, sale.Product.Price);
var summariesWithEqualDates = salesResult[vendorName].Where(x => x.Date == summary.Date);
if (summariesWithEqualDates.Count() == 0)
{
salesResult[vendorName].Add(summary);
}
else
{
summariesWithEqualDates.FirstOrDefault().TotalSum += summary.TotalSum;
}
}
}
return(salesResult);
}
}
private static void ExportProducts(OracleConnection connection, SupermarketsChainEntities db)
{
const string query = "SELECT PRODUCT_NAME, VENDOR_NAME, MEASURE_NAME FROM PRODUCTS P " +
"JOIN VENDORS V ON V.VENDOR_ID = P.VENDOR_ID JOIN MEASURES M ON M.MEASURE_ID = P.MEASURE_ID";
using (var command = new OracleCommand(query, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var vendorName = (string)reader["VENDOR_NAME"];
var measureName = (string)reader["MEASURE_NAME"];
db.Products.Add(new Product
{
Name = (string)reader["PRODUCT_NAME"],
Vendor = db.Vendors.FirstOrDefault(v => v.Name == vendorName),
Measure = db.Measures.FirstOrDefault(m => m.Name == measureName)
});
}
}
}
}
private static IEnumerable <ProductTotalSale> GetSalesByProduct(DateTime startDate, DateTime endDate)
{
using (var sqlServerDb = new SupermarketsChainEntities())
{
return(sqlServerDb.Sales
.Where(sale => sale.DateOfSale >= startDate && sale.DateOfSale <= endDate)
.GroupBy(sale => new
{
sale.ProductId,
ProductName = sale.Product.Name,
VendorName = sale.Product.Vendor.Name
})
.Select(group => new ProductTotalSale
{
ProductId = group.Key.ProductId,
ProductName = group.Key.ProductName,
VendorName = group.Key.VendorName,
QuantitySold = (double)group.Sum(g => g.Quantity),
TotalIncomes = (double)group.Sum(g => g.Quantity * g.PricePerUnit)
})
.ToList());
}
}
public static void ImportExpenses()
{
var expenses = ReadExpensesFromXml();
using (var db = new SupermarketsChainEntities())
{
foreach (var expense in expenses)
{
var existingVendor = db.Vendors.FirstOrDefault(v => v.Name == expense.Vendor.Name);
if (existingVendor != null)
{
expense.Vendor = existingVendor;
}
else
{
db.Vendors.Add(expense.Vendor);
db.SaveChanges();
}
}
db.Expenses.AddRange(expenses);
db.SaveChanges();
}
}
private static void AddSalesToDb(IExcelDataReader excelReader, DateTime currentDate)
{
using (var db = new SupermarketsChainEntities())
{
var salesTable = excelReader.AsDataSet().Tables["Sales"];
var locationName = (string)salesTable.Rows[1].ItemArray[1];
var currentLocation = GetOrCreateLocation(locationName, db);
for (var i = 3; i < salesTable.Rows.Count; i++)
{
if (((string)salesTable.Rows[i].ItemArray[1]).Contains("Total sum"))
{
break;
}
var productName = (string)salesTable.Rows[i].ItemArray[1];
productName = Regex.Replace(productName, @"[^\w'\. ]", string.Empty);
var currentProduct = GetOrCreateProduct(productName, db);
var quantity = (double)salesTable.Rows[i].ItemArray[2];
var pricePerUnit = (double)salesTable.Rows[i].ItemArray[3];
db.Sales.Add(new Sale
{
Location = currentLocation,
DateOfSale = currentDate,
Product = currentProduct,
Quantity = (decimal)quantity,
PricePerUnit = (decimal)pricePerUnit
});
}
db.SaveChanges();
}
}
public static void ExportSales(DateTime startDate, DateTime endDate)
{
using (var pdfDocument = new Document())
{
var file = File.Create(Settings.Default.PdfSalesReportLocation);
PdfWriter.GetInstance(pdfDocument, file);
pdfDocument.Open();
var table = new PdfPTable(5)
{
TotalWidth = 550f,
LockedWidth = true
};
table.SetWidths(new[] { 150f, 70f, 70f, 230f, 70f });
table.AddCell(new PdfPCell(
new Phrase("Aggregated Sales Report", new Font(BaseFont, 14, Font.BOLD)))
{
Colspan = 5,
HorizontalAlignment = 1,
BackgroundColor = new BaseColor(255, 255, 255),
PaddingTop = 10f,
PaddingBottom = 10f
});
var grandTotal = 0m;
using (var db = new SupermarketsChainEntities())
{
var dates = db.Sales
.Select(sale => sale.DateOfSale)
.Where(date => date >= startDate && date <= endDate)
.Distinct()
.ToList();
foreach (var date in dates)
{
AddHeaderToTable(table, date);
var sales = db.Sales
.Where(sale => sale.DateOfSale == date)
.Select(sale => new
{
sale.Product.Name,
sale.Quantity,
sale.PricePerUnit,
sale.DateOfSale,
Location = sale.Location.Name,
TotalValue = sale.Quantity * sale.PricePerUnit
})
.ToList();
foreach (var sale in sales)
{
AddNormalCellToTable(table, sale.Name);
AddNormalCellToTable(table, sale.Quantity.ToString("F2"));
AddNormalCellToTable(table, sale.PricePerUnit.ToString("F2"));
AddNormalCellToTable(table, sale.Location);
AddNormalCellToTable(table, sale.TotalValue.ToString("F2"));
}
var totalSum = sales.Sum(sale => sale.TotalValue);
AddSaleFooterToTable(table, date, totalSum);
grandTotal += totalSum;
}
}
AddGrandTotalToTable(table, grandTotal);
pdfDocument.Add(table);
pdfDocument.Close();
}
}
