private static void ReadExcelsFromDirectory(string filePath)
{
DataTable dt = new DataTable("newtable");
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\""))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sales$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.FillSchema(dt, SchemaType.Source);
adapter.Fill(dt);
}
connection.Close();
}
string location = dt.Rows[1][0].ToString();
SuperMarket newSupermarket = new SuperMarket()
{
Name = location,
};
for (int i = 3; i < dt.Rows.Count - 1; i++)
{
int prodId = 0;
string productId = dt.Rows[i][0].ToString();
int.TryParse(productId, out prodId);
if (prodId > 0)
{
using (var ctx = new SupermarketEntities())
{
if (ctx.Products.Find(prodId) != null)
{
var supermarket = ctx.SuperMarkets.Where(s => s.Name == newSupermarket.Name).ToList();
if (supermarket.Count == 0)
{
ctx.SuperMarkets.Add(newSupermarket);
supermarket.Add(newSupermarket);
}
Sale newSale = new Sale()
{
ProductId = prodId,
SuperMarketId = supermarket[0].Id,
Date = DateTime.Now,
Quantity = int.Parse(dt.Rows[i][1].ToString()),
Price = decimal.Parse(dt.Rows[i][2].ToString()),
Sum = decimal.Parse(dt.Rows[i][3].ToString())
};
ctx.Sales.Add(newSale);
ctx.SaveChanges();
}
}
}
}
}
public static void CreateReports()
{
SupermarketEntities sqlDb = new SupermarketEntities();
using (sqlDb)
{
var queryProducts = from vendors in sqlDb.Vendors
join products in sqlDb.Products
on vendors.Id equals products.VendorId
join sales in sqlDb.Sales
on products.Id equals sales.ProductId
select new
{
ProductId = products.Id,
PriductName = products.Name,
VendorName = vendors.Name,
Quantity = sales.Quantity,
Income = sales.Sum
};
var goupedProducts = from products in queryProducts
group products by products.ProductId into p
select new
{
ProductId = p.Select(a => a.ProductId).FirstOrDefault(),
ProductName = p.Select(a => a.PriductName).FirstOrDefault(),
VendorName = p.Select(a => a.VendorName).FirstOrDefault(),
TotalQuantitySold = p.Sum(a => a.Quantity),
TotalIncomes = p.Sum(a => a.Income)
};
foreach (var grouped in goupedProducts)
{
string json = JsonConvert.SerializeObject(grouped);
string path = "..\\Product-Reports\\" + grouped.ProductId + ".json";
using (StreamWriter writer = new StreamWriter(path, false))
{
writer.WriteLine(json);
}
var mongoClient = new MongoClient("mongodb://localhost/");
var mongoServer = mongoClient.GetServer();
var supermarketDb = mongoServer.GetDatabase("supermarket");
var productsReport = supermarketDb.GetCollection("productsReport");
ProductReportMongo prodReport = new ProductReportMongo
{
ProductId = grouped.ProductId,
ProductName = grouped.ProductName,
VendorName = grouped.VendorName,
TotalQuantitySold = grouped.TotalQuantitySold,
TotalIncomes = grouped.TotalIncomes
};
productsReport.Insert(prodReport);
}
}
}
private static void GenerateExcelVendorReport()
{
SupermarketEntities sqlDb = new SupermarketEntities();
using (sqlDb)
{
SupermarketSqliteEntities sqliteDb = new SupermarketSqliteEntities();
using (sqliteDb)
{
var vendorsInfo = from sales in sqlDb.Sales
join products in sqlDb.Products
on sales.ProductId equals products.Id
join vendors in sqlDb.Vendors
on products.VendorId equals vendors.Id
join expenses in sqlDb.Expenses
on vendors.Id equals expenses.VendorId
group new
{
ProductName = products.Name,
VendorName = vendors.Name,
Sum = sales.Sum,
Expenses = expenses.Value,
}
by vendors.Name;
var slqTaxes = sqliteDb.Taxes;
foreach (var vendor in vendorsInfo)
{
Console.WriteLine(vendor.Key);
foreach (var inner in vendor)
{
foreach (var tax in inner.ProductName)
{
}
}
}
}
}
}
public static void TakeDataFromMySql()
{
SupermarketEntities sqlDb = new SupermarketEntities();
using (sqlDb)
{
SupermarketData mySql = new SupermarketData();
using (mySql)
{
List<string> measureNames = mySql.Measures.Select(x => x.Name).ToList();
foreach (string measureName in measureNames)
{
int measurementCount = sqlDb.Measures.Select(x => x.Name).Where(x => x == measureName).Count();
if (measurementCount == 0)
{
sqlDb.Measures.Add(new Measure { Name = measureName });
}
}
sqlDb.SaveChanges();
List<string> vendorNames = mySql.Vendors.Select(x => x.Name).ToList();
foreach (string vendorName in vendorNames)
{
int vendorNamesCount = sqlDb.Vendors.Select(x => x.Name).Where(x => x == vendorName).Count();
if (vendorNamesCount == 0)
{
sqlDb.Vendors.Add(new Vendor { Name = vendorName });
}
}
sqlDb.SaveChanges();
var products = mySql.Products.ToList();
foreach (Product mySqlProduct in products)
{
string productName = mySqlProduct.Name;
string productVendorName = mySqlProduct.Vendor.Name;
string productMeasureName = mySqlProduct.Measure.Name;
decimal productBasePrice = mySqlProduct.BasePrice;
var sqlProduct = sqlDb.Products.Where(x => x.Name == productName).ToList();
if (sqlProduct.Count==0)
{
Product newProduct = new Product
{
Name=productName
};
sqlDb.Products.Add(newProduct);
sqlProduct.Add(newProduct);
}
sqlProduct[0].BasePrice = productBasePrice;
Vendor sqlVendor = sqlDb.Vendors.Where(
x => x.Name == productVendorName).FirstOrDefault();
sqlProduct[0].Vendor= sqlVendor;
Measure sqlMeasure = sqlDb.Measures.Where(
x => x.Name == productMeasureName).FirstOrDefault();
sqlProduct[0].Measure = sqlMeasure;
sqlDb.SaveChanges();
}
}
}
}
public static void GeneratePdfDocument()
{
Document pdfDoc = new Document(PageSize.A4, 5, 5, 15, 15);
PdfWriter.GetInstance(pdfDoc, new FileStream("report.pdf", FileMode.Create));
pdfDoc.Open();
PdfPTable table = new PdfPTable(5);
PdfPCell cell = new PdfPCell();
cell.Colspan = 5;
cell.HorizontalAlignment = Element.ALIGN_MIDDLE;
cell.Phrase = new Phrase("Aggregated Sales Report");
table.AddCell(cell);
SupermarketEntities sqlDb = new SupermarketEntities();
using (sqlDb)
{
var salesProducts = from sales in sqlDb.Sales
join products in sqlDb.Products
on sales.ProductId equals products.Id
join measures in sqlDb.Measures
on products.MeasureId equals measures.Id
select new
{
Date=sales.Date,
Product=products.Name,
Quantity=sales.Quantity,
UnitPrice=sales.Price,
Sum=sales.Sum,
Measures=measures.Name,
SupermarketId=sales.SuperMarketId
};
var salesQueryProducts = from sales in salesProducts
join supermarkets in sqlDb.SuperMarkets
on sales.SupermarketId equals supermarkets.Id
select new
{
Date = sales.Date,
Product = sales.Product,
Quantity = sales.Quantity,
UnitPrice = sales.UnitPrice,
Sum = sales.Sum,
Measures=sales.Measures,
Location = supermarkets.Name
};
var groupedProducts = from groupedSales in salesQueryProducts
group groupedSales by groupedSales.Date;
foreach (var groupItem in groupedProducts)
{
cell.Phrase = new Phrase("Date: " + groupItem.First().Date.Date);
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(cell);
PdfPCell secondCell = new PdfPCell();
secondCell.Phrase = new Phrase("Product");
secondCell.BackgroundColor = BaseColor.LIGHT_GRAY;
secondCell.Phrase.Font.SetStyle(Font.BOLD);
table.AddCell(secondCell);
secondCell.Phrase = new Phrase("Quantity");
secondCell.BackgroundColor = BaseColor.LIGHT_GRAY;
secondCell.Phrase.Font.SetStyle(Font.BOLD);
table.AddCell(secondCell);
secondCell.Phrase = new Phrase("UnitPrice");
secondCell.BackgroundColor = BaseColor.LIGHT_GRAY;
secondCell.Phrase.Font.SetStyle(Font.BOLD);
table.AddCell(secondCell);
secondCell.Phrase = new Phrase("Location");
secondCell.BackgroundColor = BaseColor.LIGHT_GRAY;
secondCell.Phrase.Font.SetStyle(Font.BOLD);
table.AddCell(secondCell);
secondCell.Phrase = new Phrase("Sum");
secondCell.BackgroundColor = BaseColor.LIGHT_GRAY;
secondCell.Phrase.Font.SetStyle(Font.BOLD);
table.AddCell(secondCell);
secondCell.BackgroundColor = BaseColor.WHITE;
decimal sum = 0;
foreach (var item in groupItem)
{
secondCell.Phrase = new Phrase(item.Product);
table.AddCell(secondCell);
secondCell.Phrase = new Phrase(item.Quantity + " " + item.Measures);
table.AddCell(secondCell);
secondCell.Phrase = new Phrase(item.UnitPrice.ToString());
table.AddCell(secondCell);
secondCell.Phrase = new Phrase(item.Location);
table.AddCell(secondCell);
secondCell.Phrase = new Phrase(item.Sum.ToString());
table.AddCell(secondCell);
sum += item.Sum;
}
cell.Colspan = 4;
cell.BackgroundColor = BaseColor.WHITE;
cell.HorizontalAlignment = PdfPCell.ALIGN_RIGHT;
cell.Phrase = new Phrase("Total sum for " + groupItem.First().Date.Date);
cell.HorizontalAlignment = PdfPCell.ALIGN_MIDDLE;
table.AddCell(cell);
secondCell.Phrase = new Phrase(sum.ToString());
table.AddCell(secondCell);
cell.Colspan = 5;
cell.BackgroundColor = BaseColor.LIGHT_GRAY;
}
}
pdfDoc.Add(table);
pdfDoc.Close();
}
