static void Main(string[] args)
{
using (SupermarketsContext db = new SupermarketsContext())
{
TransferDB(db);
ReadExcelReports.ReadReports();
Console.WriteLine("Crated MSSQL Databas");
Console.WriteLine("Generated Pdf");
GenerateXMLSalesByVendors.Generate();
Console.WriteLine("Generated xml sales report");
GenerateJSONReports.StoreJsonReports();
Console.WriteLine("Generated json reports");
LoadVendorExpences.ReadXml();
Console.WriteLine("Read xml vendor expences");
MongoDBContext.GenerateMongoDB();
Console.WriteLine("Generated mongodg database");
}
}
public static void ReadXml()
{
using (SupermarketsContext db = new SupermarketsContext())
{
using (XmlReader reader = XmlReader.Create("../../Vendors-Expenses.xml"))
{
while (reader.Read())
{
if ((reader.NodeType == XmlNodeType.Element) &&
(reader.Name == "sale"))
{
string vendorName = reader.GetAttribute("vendor");
var vendorID =
from vendor in db.Vendors
where vendor.Name == vendorName
select vendor;
while (!((reader.NodeType == XmlNodeType.EndElement) && (reader.Name == "sale")))
{
reader.Read();
if ((reader.NodeType == XmlNodeType.Element) &&
(reader.Name == "expenses"))
{
DateTime date = DateTime.Parse(reader.GetAttribute("month"));
decimal expences = decimal.Parse(reader.ReadInnerXml());
VendorExpense newVendorExpense = new VendorExpense
{
Date = date,
Expense = expences,
Vendor = vendorID.First(),
};
db.VendorExpenses.Add(newVendorExpense);
db.SaveChanges();
}
}
}
}
}
}
}
private static void TransferDB(SupermarketsContext db)
{
using (db)
{
using (var mySqlContext = new ProductsModel())
{
var products = mySqlContext.Products.ToList();
var measures = mySqlContext.Measures.ToList();
var vendors = mySqlContext.Vendors.ToList();
db.SaveChanges();
foreach (var vendor in vendors)
{
var newVendor = new Supermarkets.Model.Vendor { Name = vendor.VendorName };
newVendor.Id = vendor.VendorID;
db.Vendors.Add(newVendor);
}
foreach (var measure in measures)
{
var newMeasure = new Supermarkets.Model.Measure { Name = measure.MeasureName };
newMeasure.Id = measure.MeasureID;
db.Measures.Add(newMeasure);
}
foreach (var product in products)
{
var newProduct = new Supermarkets.Model.Product { Id = product.ProductsID, Name = product.ProductName };
newProduct.Vendor = db.Vendors.Find(product.VendorID);
newProduct.Measure = db.Measures.Find(product.MeasureID);
db.Products.Add(newProduct);
db.SaveChanges();
}
db.SaveChanges();
}
}
}
public static List<string> ExatractJsonData(out List<int> ids)
{
ids = new List<int>();
List<string> jsons = new List<string>();
using (SupermarketsContext dbContexet = new SupermarketsContext())
{
Dictionary<string, object> jsonDict = new Dictionary<string, object>();
foreach (var product in dbContexet.Products.Include("Vendor").Include("SaleDetails"))
{
jsonDict.Clear();
decimal quantity = 0;
decimal totalSum = 0;
foreach (var productSaleDetail in product.SaleDetails)
{
quantity += productSaleDetail.Quantity;
totalSum += productSaleDetail.Sum;
}
ids.Add(product.Id);
jsonDict["product-id"] = product.Id;
jsonDict["product-name"] = product.Name;
jsonDict["vendor-name"] = product.Vendor.Name;
jsonDict["total-quantity-sold"] = quantity;
jsonDict["total-icomes"] = totalSum;
string json = JsonConvert.SerializeObject(jsonDict, Formatting.Indented);
jsons.Add(json);
}
/*
var result =
from product in dbContexet.Products
join vendor in dbContexet.Vendors
on product.VendorId equals vendor.Id
join sd in dbContexet.SaleDetails
on product.Id equals sd.ProductId
group sd by new
{
product.Id,
product.Name,
vendorName = vendor.Name
} into g
select new
{
ProductId = g.Key.Id,
ProductName = g.Key.Name,
VendorName = g.Key.vendorName,
TotalQnt = g.Sum(x => x.Quantity),
TotalIncome = g.Sum(x => x.Quantity * x.UnitPrice)
};
Directory.CreateDirectory(rptDir);
foreach (var item in result)
{
string json = JsonConvert.SerializeObject(item, Formatting.Indented);
Console.WriteLine(json);
File.WriteAllText(rptDir + "\\" + item.ProductId.ToString("00") + ".json", json);
}
*/
}
return jsons;
}
private static void CreateSaleDetails(int saleId, string productId, string quantity, string unitPrice)
{
using (SupermarketsContext dbContext = new SupermarketsContext())
{
SaleDetail newSaleDetail = new SaleDetail
{
ProductId = int.Parse(productId),
Quantity = int.Parse(quantity),
UnitPrice = decimal.Parse(unitPrice),
SaleId = saleId
};
dbContext.SaleDetails.Add(newSaleDetail);
dbContext.SaveChanges();
}
}
private static int InsertSales(int idMarket, DateTime salesDate)
{
using (SupermarketsContext dbContext = new SupermarketsContext())
{
var allSales = dbContext.Sales.Where(sale => (sale.Date == salesDate && sale.MarketId == idMarket)).FirstOrDefault();
if (allSales == null)
{
Sale newSale = new Sale
{
Date = salesDate,
MarketId = idMarket
};
dbContext.Sales.Add(newSale);
dbContext.SaveChanges();
return newSale.Id;
}
else
{
return allSales.Id;
}
}
}
private static int InsertMarket(string marketLocation, int idCity)
{
using (SupermarketsContext dbContext = new SupermarketsContext())
{
var allMarkets = dbContext.Markets.Where(market => market.Address == marketLocation).FirstOrDefault();
if (allMarkets == null)
{
Market newMarket = new Market
{
Address = marketLocation,
CityId = idCity
};
dbContext.Markets.Add(newMarket);
dbContext.SaveChanges();
return newMarket.Id;
}
else
{
return allMarkets.Id;
}
}
}
private static int GetCityId(string filePath)
{
string[] filesNameData = filePath.Split(new char[] { '-', '\\' });
string sityName = filesNameData[4].ToString();
using (SupermarketsContext dbContext = new SupermarketsContext())
{
var allCities = dbContext.Cities.Where(city => city.Name == sityName).FirstOrDefault();
if (allCities == null)
{
City newCity = new City
{
Name = sityName
};
dbContext.Cities.Add(newCity);
dbContext.SaveChanges();
return newCity.Id;
}
else
{
return allCities.Id;
}
}
}
