public static void GetExcelData()
{
using (var db = new CatalogContext())
{
HashSet<string> addedShops = new HashSet<string>();
//this is used for avoiding duplicates and fast search
foreach (var item in db.Shops)
{
addedShops.Add(item.Name);
}
foreach (var path in paths)
{
foreach (var fileName in path.Value)
{
AddShop(fileName, addedShops, db);
}
}
db.SaveChanges();
AddSale(db, paths);
db.SaveChanges();
}
}
public static void AddShop(string fileName, HashSet<string> addedShops, CatalogContext db)
{
var myShop = new Shop();
string shopName = fileName.Split((new string[] { "-Sales" }), StringSplitOptions.None).First();
myShop.Name = shopName;
if (!addedShops.Contains(shopName))
{
db.Shops.Add(myShop);
}
}
public static void CreateXMLFile(string path)
{
XmlWriterSettings settings = new XmlWriterSettings();
XmlWriter document = XmlWriter.Create(path);
document.WriteStartDocument();
document.WriteStartElement("Sales");
using (var db = new CatalogContext())
{
var vendorReport = db.Sales
.Include("Product")
.Include("Product.Vendor")
.GroupBy(x => new { x.Date, x.Product.Vendor.Name })
.OrderBy(x => x.Key.Name);
string vendor = "";
foreach (var item in vendorReport)
{
if (vendor != item.Key.Name)
{
if (vendor != string.Empty)
{
document.WriteEndElement();
}
vendor = item.Key.Name;
document.WriteStartElement("sale");
document.WriteAttributeString("vendor", vendor);
}
document.WriteStartElement("summary");
document.WriteAttributeString("date", item.Key.Date.ToString("dd-MMM-yyyy"));
document.WriteAttributeString("total-sum", item.Sum(x => x.Sum).ToString());
document.WriteEndElement();
}
document.WriteEndDocument();
}
document.Close();
}
private static void CreateJsonObject(CatalogContext db)
{
using (db)
{
var productReport = db.Sales
.Include("Product")
.Include("Product.Vendor")
.Select(x => new
{
x.Sum,
x.Quantity,
x.ProductID,
x.Product.ProductName,
x.Product.Vendor.Name
}
)
.GroupBy(x => x.ProductID);
int count = 0;
foreach (var item in productReport)
{
Console.WriteLine("ID: {0} Name: {1} Vendor: {2} Quantity: {3} Sum: {4}",
item.Key,
item.First().ProductName,
item.First().Name,
item.Sum(x => x.Quantity),
item.Sum(x => x.Sum)
);
ProductReport currentReport = new ProductReport(item.Key, item.First().ProductName, item.First().Name,
Convert.ToDouble(item.Sum(x => x.Quantity)), item.Sum(x => x.Sum));
InsertIntoMongoProductReport(currentReport, count);
count++;
}
}
}
public IndexModel(Catalog.Data.CatalogContext context)
{
_context = context;
}
public EditModel(Catalog.Data.CatalogContext context)
{
_context = context;
}
public static void CreatePDFDocument()
{
var document = new Document();
//string path = Server.MapPath("PDFs");
PdfWriter.GetInstance(document, new FileStream("../../document.pdf", FileMode.Create));
document.Open();
PdfPTable table = new PdfPTable(5);
//leave a gap before and after the table
table.SpacingBefore = 20f;
table.SpacingAfter = 30f;
using (var db = new CatalogContext())
{
//one query to the database
var sales = db.Sales
.Include("Product")
.Include("Shop")
.OrderBy(x => new { x.Date, x.ProductID, x.ShopID })
.ToList();
DateTime reportDate = new DateTime();
decimal totalSum = 0;
foreach (var sale in sales)
{
if (reportDate != sale.Date)
{
if (totalSum != 0)
{
table.AddCell("");
table.AddCell("");
table.AddCell("");
table.AddCell(string.Format("Total sum for: {0}", reportDate.ToString("dd-MMM-yyyy")));
//PdfPCell total = new PdfPCell(new Phrase(string.Format("Total: {0}", totalSum)));
//total.HorizontalAlignment = 1;
//total.Colspan = 4;
//table.AddCell(total);
table.AddCell(string.Format("Total: {0}", totalSum));
totalSum = 0;
}
table.AddCell(string.Format("Date: {0}", sale.Date.ToString("dd-MMM-yyyy")));
table.CompleteRow();
table.AddCell("Product");
table.AddCell("Quantity");
table.AddCell("UnitPrice");
table.AddCell("Location");
table.AddCell("Sum");
reportDate = sale.Date;
}
totalSum += sale.Sum;
table.AddCell(sale.Product.ProductName);
table.AddCell(sale.Quantity.ToString());
table.AddCell(sale.UnitPrice.ToString());
table.AddCell(sale.Shop.Name);
table.AddCell(sale.Sum.ToString());
}
document.Add(table);
document.Close();
}
}
public DeleteModel(Catalog.Data.CatalogContext context)
{
_context = context;
}
static void Main(string[] args)
{
Database.SetInitializer(new Configuration());
using (var db = new CatalogContext())
{
var sale = db.Vendors.First();
//var vendorReport = db.Sales
// .Include("Product")
// .Include("Product.Vendor")
// .GroupBy(x => new { x.Date, x.Product.Vendor.Name })
// .OrderBy(x=> x.Key.Name);
//foreach (var vendor in vendorReport)
//{
// Console.WriteLine("Vendor: {0}", vendor.Key.Name);
// //Console.WriteLine("ProductId: {0}", vendor.Key.ProductID);
// Console.WriteLine("Sum: {0}", vendor.Sum(x=> x.Sum));
//}
//Console.WriteLine("test");
//var productReport = db.Sales
// //.Include("Product")
// //.Include("Product.Vendor")
// .Select(x => new {
// x.Sum,
// x.Quantity,
// x.ProductID,
// x.Product.ProductName,
// x.Product.Vendor.Name
// }
// )
// .GroupBy(x => x.ProductID);
//foreach (var item in productReport)
//{
// Console.WriteLine("ID: {0} Name: {1} Vendor: {2} Quantity: {3} Sum: {4}",
// item.Key,
// item.First().ProductName,
// item.First().Name,
// item.Sum(x => x.Quantity),
// item.Sum(x => x.Sum)
// );
//}
//Console.WriteLine("test");
//Console.WriteLine("{0,15} {1,15} {2,15} {3,15} {4,15}", "Product", "Quantity", "UnitPrice", "Location", "Sum");
//var sales = db.Sales
// .Include("Product")
// .Include("Shop")
// .OrderBy(x => new { x.Date, x.ProductID, x.ShopID})
// .ToList();
//DateTime reportDate = new DateTime();
//foreach (var sale in sales)
//{
// if (reportDate != sale.Date)
// {
// Console.WriteLine("Date: {0}", sale.Date);
// reportDate = sale.Date;
// }
// Console.WriteLine("{0,15} {1,15} {2,15} {3,15} {4,10:F2}",
// sale.Product.ProductName,
// sale.Quantity,
// sale.UnitPrice,
// sale.Shop.Name,
// sale.Sum
// );
//}
}
}
public CreateModel(Catalog.Data.CatalogContext context)
{
_context = context;
}
private static void AddSale(CatalogContext db, Dictionary<string, List<string>> paths)
{
foreach (var path in paths)
{
foreach (var fileName in path.Value)
{
// used for gathering and combing the sells of the same products and same shop
Dictionary<int, Sale> currentSales = new Dictionary<int, Sale>();
OleDbConnection excelConnection =
new OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=..\..\Extracted\" + path.Key + "\\" + fileName + ";" +
@"Extended Properties=""Excel 8.0;HDR=YES""");
string shopName = fileName.Split((new string[] { "-Sales" }), StringSplitOptions.None).First();
excelConnection.Open();
using (excelConnection)
{
using (
OleDbCommand command =
new OleDbCommand(
String.Format("SELECT * FROM [{0}${1}]", "Sales", "B3:E"), excelConnection)
)
{
command.Connection = excelConnection;
OleDbDataReader dataExcel = command.ExecuteReader();
using (dataExcel)
{
while (dataExcel.Read())
{
var productId = dataExcel["ProductID"];
int productIdFull = 0;
if (productId != DBNull.Value)
{
productIdFull = Convert.ToInt32(productId);
}
else
{
continue;
}
//Console.WriteLine("{0}", productIdFull);
DateTime date = DateTime.Parse(path.Key);
//Console.WriteLine("{0}", date);
decimal sum = Convert.ToDecimal(dataExcel["Sum"]);
//Console.WriteLine("{0}", sum);
decimal quantity = Convert.ToDecimal(dataExcel["Quantity"]);
//Console.WriteLine("{0}", quantity);
decimal unitPrice = Convert.ToDecimal(dataExcel["Unit Price"]);
//Console.WriteLine("{0}", unitPrice);
int shopID = db.Shops.Where(x => x.Name.CompareTo(shopName) == 0).First().ShopID;
if (currentSales.ContainsKey(productIdFull))
{
currentSales[productIdFull].Quantity += quantity;
currentSales[productIdFull].Sum += sum;
}
else
{
Sale newSale = new Sale
{
Date = date,
ProductID = productIdFull,
Quantity = quantity,
UnitPrice = unitPrice,
Sum = sum,
ShopID = shopID
};
currentSales[productIdFull] = newSale;
}
}
}
}
}
foreach (var item in currentSales)
{
db.Sales.Add(item.Value);
}
}
}
}
static void Main(string[] args)
{
string path = @"../../file.xml";
Dictionary<string, Dictionary<string, decimal>> data = new Dictionary<string, Dictionary<string, decimal>>();
XmlDocument doc = new XmlDocument();
doc.Load(@"../../file.xml");
XmlNode rootNode = doc.DocumentElement;
//Console.WriteLine("Root node: {0}", rootNode.Name);
decimal expense = 0m;
string txt = "";
int count = 0;
foreach (XmlNode node in rootNode.ChildNodes)
{
string vendor = node.Attributes["vendor"].Value.ToString();
string month = "";
//Console.WriteLine(vendor);
foreach (XmlNode child in node)
{
month = child.Attributes["month"].Value.ToString();
txt = child.InnerText.ToString();
expense = decimal.Parse(txt, CultureInfo.InvariantCulture);
if (!data.ContainsKey(vendor))
{
data[vendor] = new Dictionary<string, decimal>();
}
data[vendor].Add(month, expense);
VendorReport currentReport = new VendorReport(vendor, month, expense);
JsonClient.InsertIntoMongoVendorReport(currentReport, count);
count++;
}
}
using (var db = new CatalogContext())
{
//one query to database
var currentVendors = db.Vendors.ToList();
var currentExpenses = db.VendorExpenses.ToList();
foreach (var vendor in data)
{
int vendorID = currentVendors
.Where(x => x.Name.CompareTo(vendor.Key) == 0)
.Select(x => x.VendorId).First();
foreach (var vendorExpense in vendor.Value)
{
VendorExpense newExpense = new VendorExpense
{
VendorID = vendorID,
Month = DateTime.Parse(vendorExpense.Key),
Expense = vendorExpense.Value
};
var currentExpense = currentExpenses.Where(x => x.VendorID == vendorID && x.Month == DateTime.Parse(vendorExpense.Key)).FirstOrDefault();
if (currentExpense != null)
{
currentExpense.Expense = vendorExpense.Value;
}
else
{
db.VendorExpenses.Add(newExpense);
}
}
}
db.SaveChanges();
}
}
public DetailsModel(Catalog.Data.CatalogContext context)
{
_context = context;
}
static void Main(string[] args)
{
var db = new Catalog.Data.CatalogContext();
CreateJsonObject(db);
}