private static void AddSalesToDb(IExcelDataReader excelReader, DateTime currentDate)
{
using (var db = new ChainOfSupermarketsContext())
{
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 Main()
{
var sqlServerContext = new ChainOfSupermarketsContext();
var mysqlDatabase = new MySQLDatabase(sqlServerContext);
mysqlDatabase.Migrate();
}
/*
public static IMarketData LoadData()
{
var data = new MarketData();
using (var MsDb = new ChainOfSupermarketsContext())
{
MsDb.Measures.ForEachAsync(m => data.Measures.Add(m)).Wait();
MsDb.Products.ToList().ForEach(p => data.Products.Add(p));
MsDb.Sales.ForEachAsync(s => data.Sales.Add(s)).Wait();
MsDb.Supermarkets.ForEachAsync(sup => data.Supermarkets.Add(sup)).Wait();
MsDb.Vendors.ForEachAsync(v => data.Vendors.Add(v)).Wait();
MsDb.VendorExpenses.ForEachAsync(ve => data.VendorExpenses.Add(ve)).Wait();
}
return data;
}
*/
public static IList<ReportContainer> GetSalesForPeriod(DateTime startDate, DateTime endDate)
{
var db = new ChainOfSupermarketsContext();
var sales = db.Sales
.Where(s => s.DateOfSale >= startDate && s.DateOfSale <= endDate)
.GroupBy(s => s.DateOfSale)
.Select(g => new ReportContainer
{
SaleReport = g.Select(s => new ReportData
{
ProductName = s.Product.ProductName,
Quantity = s.Quantity,
Measure = s.Product.Measure.MeasureName,
Price = s.Product.Price,
VendorName = s.Vendor.VendorName,
TotalSum = s.Product.Price * s.Quantity,
Date = s.DateOfSale,
Id = s.ProductId,
})
.ToList()
})
.ToList();
return sales;
}
public static void MigrateToMySql(ChainOfSupermarketsContext sqlServerContext)
{
mysqlConnection = new MySqlConnection(CONNECTION_STRING);
SQLServerContext = sqlServerContext;
CreateDbSchema();
ImportDataFromSqlServer();
}
public static void Main()
{
var marketsContext = new ChainOfSupermarketsContext();
JsonReporter jsonReport = new JsonReporter(marketsContext, "..\\..\\Json-Reports");
//JsonReporter jsonReport = new JsonReporter("..\\..\\..\\Helper.Files\\Json-Reports");
DateTime startDate = DateTime.Now.AddDays(-5);
DateTime endDate = DateTime.Now.AddDays(-4);
jsonReport.GenerateReport(startDate, endDate);
}
private static Location GetOrCreateLocation(string locationName, ChainOfSupermarketsContext 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;
}
public static void Main()
{
var db = new ChainOfSupermarketsContext();
// JSON Reports
string reportsDirectoryPath = @"..\..\Json-Reports";
var json = new JsonReport(db, reportsDirectoryPath);
DateTime start = new DateTime(27/7/2015);
DateTime end = new DateTime(28/7/2015);
json.ProductSalesForPeriod(start, end);
Console.WriteLine("JSON reports generate.");
Console.ReadLine();
}
private static void ExportVendors(OracleConnection connection, ChainOfSupermarketsContext 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()
{
VendorName = (string)reader["VENDOR_NAME"]
});
}
}
}
}
private static void ExportMeasures(OracleConnection connection, ChainOfSupermarketsContext 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
{
MeasureName = (string)reader["MEASURE_NAME"]
});
}
}
}
}
private static void ExportProducts(OracleConnection connection, ChainOfSupermarketsContext db)
{
using (var command = new OracleCommand("SELECT PRODUCT_NAME, PRICE, VENDOR_ID, MEASURE_ID FROM PRODUCTS", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
db.Products.Add(new Product()
{
ProductName = (string)reader["PRODUCT_NAME"],
Price = (decimal)reader["PRICE"],
MeasureId = Convert.ToInt32(reader["MEASURE_ID"]),
VendorId = Convert.ToInt32(reader["VENDOR_ID"]),
});
}
}
}
}
public static void ExportDbToSqlServer()
{
var connection = new OracleConnection(Settings.Default.OracleConnectionString);
connection.Open();
using (connection)
{
using (var sqlServerDb = new ChainOfSupermarketsContext())
{
ExportMeasures(connection, sqlServerDb);
sqlServerDb.SaveChanges();
ExportVendors(connection, sqlServerDb);
sqlServerDb.SaveChanges();
ExportProducts(connection, sqlServerDb);
sqlServerDb.SaveChanges();
}
}
}
public IEnumerable<ProductTotalSale> GetDataFromSqlServer(DateTime startDate, DateTime endDate)
{
var result = new List<ProductTotalSale>();
var context = new ChainOfSupermarketsContext();
var allSales = context.Sales
.Where(s => s.DateOfSale >= startDate && s.DateOfSale <= endDate)
.ToList();
foreach (var pr in context.Products)
{
var currentProducts = allSales.Where(x => x.Product.Id == pr.Id);
double totalQuantityForProduct = 0.0;
double totalMoneyForProduct = 0.0;
int prodId = 0;
foreach (var currentProduct in currentProducts)
{
totalQuantityForProduct += (double)currentProduct.Quantity;
totalMoneyForProduct +=
(double)currentProduct.Quantity * (double)currentProduct.PricePerUnit;
prodId = currentProduct.ProductId;
}
var reportForCurrentProduct = new ProductTotalSale()
{
ProductId = prodId,
ProductName = pr.ProductName,
VendorName = pr.Vendors.VendorName,
QuantitySold = totalQuantityForProduct,
TotalIncomes = totalMoneyForProduct
};
result.Add(reportForCurrentProduct);
}
return result;
}
public static IList<ReportContainer> GetSalesOfEachProductForPeriod(DateTime startDate, DateTime endDate)
{
var db = new ChainOfSupermarketsContext();
var sales = db.Sales
//.Where(s => s.Date >= startDate && s.Date <= endDate)
.GroupBy(s => s.Product.ProductName)
.Select(g => new ReportContainer
{
PrductName = g.Key,
SaleReport = g.Select(s => new ReportData
{
Id = s.ProductId,
Price = s.Product.Price,
Quantity = s.Quantity,
VendorName = s.Vendor.VendorName
})
.ToList()
})
.ToList();
return sales;
}
public XlsImporter(ChainOfSupermarketsContext context)
{
this.db = context;
}
/*
public static void SaveData(IMarketData marketData)
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<ChainOfSupermarketsContext, Configuration>());
try
{
var db = new ChainOfSupermarketsContext();
/*
var newVendorIds = marketData.Vendors.Select(v => v.Id).ToList()
.Except(db.Vendors.Select(ve => ve.Id).ToList()).ToList();
var newVendorEntityes = marketData.Vendors.Where(x => newVendorIds.Contains(x.Id)).ToList();
db.Vendors.AddRange(newVendorEntityes);
var newSupermarketsId = marketData.Supermarkets.Select(v => v.Id).ToList()
.Except(db.Supermarkets.Select(ve => ve.Id).ToList()).ToList();
var newSupermarketsEntityes = marketData.Supermarkets.Where(x => newSupermarketsId.Contains(x.Id)).ToList();
db.Supermarkets.AddRange(newSupermarketsEntityes);
var newMeasuresId = marketData.Measures.Select(v => v.Id).ToList()
.Except(db.Measures.Select(ve => ve.Id).ToList()).ToList();
var newMeasuresEntityes = marketData.Measures.Where(x => newMeasuresId.Contains(x.Id)).ToList();
db.Measures.AddRange(newMeasuresEntityes);
var newProductsId = marketData.Products.Select(v => v.Id).ToList()
.Except(db.Products.Select(ve => ve.Id).ToList()).ToList();
var newProductsEntityes = marketData.Products.Where(x => newProductsId.Contains(x.Id)).ToList();
db.Products.AddRange(newProductsEntityes);
var newSales = SaleDuplicateChecker(marketData.Sales);
db.Sales.AddRange(newSales);
var newExpenses = ExpenseDuplicateChecker(marketData.VendorExpenses);
db.VendorExpenses.AddRange(newExpenses);
db.SaveChanges();
}
catch (DbUpdateException ex)
{
var a = ex;
}
catch (Exception exe)
{
var a = exe;
}
}
public static ICollection<IEntity> FilterById(ICollection<IEntity> newEntity, ICollection<IEntity> oldEntity)
{
var newId = newEntity.Select(v => v.Id).ToList()
.Except(oldEntity.Select(ve => ve.Id).ToList()).ToList();
var filteredEntityes = newEntity.Where(x => newId.Contains(x.Id)).ToList();
return filteredEntityes;
}
private static ICollection<Sale> SaleDuplicateChecker(ICollection<Sale> newSales)
{
var db = new ChainOfSupermarketsContext();
var salesToAdd = new List<Sale>() { };
foreach (var newSale in newSales)
{
var existInDatabase = db.Sales.Any(s => (s.Date == newSale.Date) &&
(s.Supermarket.Name == newSale.Supermarket.Name) &&
(s.Product.Name == newSale.Product.Name)).ToString();
if (existInDatabase == "False")
{
Sale sale = new Sale()
{
ProductId = newSale.Product.Id,
SupermarketId = newSale.Supermarket.Id,
Quantity = newSale.Quantity,
Date = newSale.Date
};
salesToAdd.Add(sale);
}
}
return salesToAdd;
}
*/
/*
private static ICollection<VendorExpenses> ExpenseDuplicateChecker(ICollection<VendorExpenses> newVendorExpenses)
{
var db = new ChainOfSupermarketsContext();
var result = new List<VendorExpenses>() { };
foreach (var newExpense in newVendorExpenses)
{
var existInDatabase = db.VendorExpenses.Any(s => (s.Date == newExpense.Date) &&
(s.VendorId == newExpense.VendorId)).ToString();
if (existInDatabase == "False")
{
result.Add(newExpense);
}
}
return result;
}
*/
public static IList<ReportContainer> GetSalesGroupByVendorAndDate(DateTime startDate, DateTime endDate)
{
var db = new ChainOfSupermarketsContext();
var sales = db.Sales
.Where(s => s.DateOfSale >= startDate && s.DateOfSale <= endDate)
.Select(s => new { Suppermarket = s.Vendor.VendorName, s.DateOfSale, TotalPrice = (s.Product.Price * s.Quantity) })
.GroupBy(s => s.Suppermarket)
.Select(g => new ReportContainer
{
SupermarkeName = g.Key,
SaleReport = g.GroupBy(s => s.DateOfSale)
.Select(gd => new ReportData { Date = gd.Key, TotalSum = gd.Sum(s => s.TotalPrice) })
.ToList()
})
.ToList();
return sales;
}
static void Main()
{
var db = new ChainOfSupermarketsContext();
var MeasureCount = db.Measures.Count();
ExportDbToSqlServer();
}
public static void Main()
{
var sqlServerContext = new ChainOfSupermarketsContext();
MySQLDatabase.MigrateToMySql(sqlServerContext);
}
private static Product GetOrCreateProduct(string productName, ChainOfSupermarketsContext db)
{
var product = db.Products.FirstOrDefault(p => p.ProductName == productName);
if (product == null)
{
product = new Product()
{
ProductName = productName,
Vendors = new Vendor { VendorName = productName.Split(' ').Last() + " Corp." }
};
db.Products.Add(product);
db.SaveChanges();
}
return product;
}
public MySQLDatabase(ChainOfSupermarketsContext context)
{
this.mysqlConnection = new MySqlConnection(CONNECTION_STRING);
this.sqlServerContext = context;
}
public JsonReport(ChainOfSupermarketsContext context, string path)
{
this.dbContext = context;
this.folderPath = path;
}
public JsonReporter(ChainOfSupermarketsContext context, string outputPath)
{
this.marketContext = context;
}
public void ExportSales(DateTime startDate, DateTime endDate)
{
using (var pdfDocument = new Document())
{
var file = File.Create(DataManagement.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 ChainOfSupermarketsContext())
{
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.ProductName,
sale.Quantity,
sale.PricePerUnit,
sale.DateOfSale,
Location = sale.Location.Name,
TotalValue = sale.Quantity * sale.PricePerUnit
})
.ToList();
foreach (var sale in sales)
{
AddNormalCellToTable(table, sale.ProductName);
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();
}
}
static void Main()
{
var db = new ChainOfSupermarketsContext();
//var MeasureCount = db.Measures.Count();
//ExportDbToSqlServer();
//var import = new XlsImporter(db);
//import.ImportSales();
//var export = new PdfExporter();
//export.ExportSales("01-Jan-2015", "25-Feb-2015");
//var importXML = new LoadXML();
//importXML.ImportXML();
var exportXML = new ExportReportAsXML();
exportXML.ExportSales("20-Jul-2014","22-Jul-2014");
}
