public static void JsonProductSalses(DateTime startDate, DateTime endDate, string path)
{
var context = new SqlMarketContext();
var productSalesInfo = context.Sales
.Where(s => s.Date >= startDate && s.Date <= endDate)
.Select(s => new
{
s.ProductId,
Product = s.Product.Name,
Vendor = s.Product.Vendor.Name,
TotalQuantitySold = context.Sales
.Where(prId => prId.ProductId == s.ProductId)
.Sum(prSum => prSum.Quantity),
TotalIncomes = context.Sales
.Where(prId => prId.ProductId == s.ProductId)
.Sum(prSum => prSum.TotalSum)
});
foreach (var salesReport in productSalesInfo)
{
var serializer = new JavaScriptSerializer();
var json = serializer.Serialize(salesReport);
File.WriteAllText(path + salesReport.ProductId + ".json", json);
}
}
public int? GetSupermarketIdByName(string supermarketName)
{
var context = new SqlMarketContext();
var supermarketId = context.Supermarkets.FirstOrDefault(s => s.Name == supermarketName).Id;
return supermarketId;
}
public int? GetProductIdByName(string productName)
{
var context = new SqlMarketContext();
var productId = context.Products.FirstOrDefault(s => s.Name == productName).Id;
return productId;
}
public MarketData GetData()
{
using (var context = new SqlMarketContext())
{
this.GetTowns(context.Towns);
this.GetSupermarkets(context.Supermarkets);
this.GetMeasures(context.Measures);
this.GetProductsTypes(context.ProductsTypes);
this.GetVendors(context.Vendors);
this.GetProducts(context.Products);
this.GetSales(context.Sales);
this.GetVendorExpenses(context.VendorExpenses);
return this;
}
}
public static void TransferData(MarketData marketData)
{
using (var context = new SqlMarketContext())
{
TransferTowns(marketData.Towns, context);
TransferSupermarkets(marketData.Supermarkets, context);
TransferProductsTypes(marketData.ProductsTypes, context);
TransferVendors(marketData.Vendors, context);
TransferMeasures(marketData.Measures, context);
TransferProducts(marketData.Products, context);
TransferSalesReports(marketData.SalesReports, context);
TransferVendorExpenses(marketData.VendorExpenses, context);
context.SaveChanges();
}
}
public static IQueryable<IGrouping<DateTime, SaleReport>> FindSalesByDateRange(DateTime startDate, DateTime endDate)
{
var context = new SqlMarketContext();
var sales = context.Sales
.Where(s => s.Date >= startDate && s.Date <= endDate)
.Select(s => new SaleReport
{
Product = s.Product.Name,
Quantity = s.Quantity,
ProductType = s.Product.ProductType.Name,
UnitPrice = s.UnitPrice,
Location = s.Supermarket.Name,
Sum = s.TotalSum,
Date = s.Date
})
.GroupBy(s => s.Date);
return sales;
}
private static void TransferProductsTypes(IEnumerable<ProductType> productsTypes, SqlMarketContext context)
{
productsTypes.ToList()
.ForEach(pt => context.ProductsTypes.AddOrUpdate(n => n.Id, pt));
}
private static void TransferMeasures(IEnumerable<Measure> measures, SqlMarketContext context)
{
measures.ToList()
.ForEach(m => context.Measures.AddOrUpdate(n => n.Id, m));
}
private static void TransferVendors(IEnumerable<Vendor> vendors, SqlMarketContext context)
{
vendors.ToList()
.ForEach(v => context.Vendors.AddOrUpdate(n => n.Id, v));
}
private static void TransferVendorExpenses(IEnumerable<VendorExpense> vendorExpenses, SqlMarketContext context)
{
vendorExpenses.ToList()
.ForEach(vs => context.VendorExpenses.AddOrUpdate(n => new { n.VendorId, n.Month }, vs));
}
private static void TransferTowns(IEnumerable<Town> towns, SqlMarketContext context)
{
towns.ToList()
.ForEach(t => context.Towns.AddOrUpdate(n => n.Id, t));
}
private static void TransferSupermarkets(IEnumerable<Supermarket> supermarkets, SqlMarketContext context)
{
supermarkets.ToList()
.ForEach(s => context.Supermarkets.AddOrUpdate(n => n.Id, s));
}
private static void TransferSalesReports(IEnumerable<Sale> salesReports, SqlMarketContext context)
{
salesReports.ToList()
.ForEach(sr => context.Sales.AddOrUpdate(n => new { n.SupermarketId, n.ProductId, n.Date }, sr));
}
public static void ZipExcelReportsToMsSql()
{
var sqlContext = new SqlMarketContext();
Console.WriteLine(SeparatorLiner);
Console.WriteLine("Extracting data from reports...\n");
var zipExtractor = new ExcelSalesExtractor(SalesImportPath, sqlContext);
var data = zipExtractor.ExtractData();
Console.WriteLine("\nSending data to SQL Server...");
MsSqlManager.TransferData(data);
Console.WriteLine("Sales reports imported.");
Console.WriteLine(SeparatorLiner);
}
public static void XmlExpensesReportToMsSql()
{
var sqlContext = new SqlMarketContext();
Console.WriteLine(SeparatorLiner);
Console.WriteLine("Extracting data from report... ");
var xmlExpensesReportToMsSql = new XmlVendorExprensesImport(ExpensesImportPath, sqlContext);
var data = xmlExpensesReportToMsSql.GetReportData();
Console.WriteLine("Sending data to SQL Server...");
MsSqlManager.TransferData(data);
Console.WriteLine("Vendor expense report imported.");
Console.WriteLine(SeparatorLiner);
}
