public static void ReadMySQL(SalesContext SqlDatabase, SupermarketModel mySqlTest)
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<SalesContext, Configuration>());
foreach (var vendor in mySqlTest.Vendors)
{
if (!SqlDatabase.Vendors.Any(x => x.Name == vendor.VendorName))
{
SqlDatabase.Vendors.Add(new Models.MSSQL.Vendor
{
Name = vendor.VendorName,
});
}
}
SqlDatabase.SaveChanges();
foreach (var product in mySqlTest.Products.Include("Vendors").Include("Measures"))
{
SqlDatabase.Products.Add(new Sales.Models.MSSQL.Product
{
Name = product.ProductName,
Vendor = SqlDatabase.Vendors.Where(x => x.Name == product.Vendor.VendorName).First(),
BasePrice = (decimal)product.BasePrice,
Measure = (Models.MSSQL.Measure)Enum.Parse(typeof(Models.MSSQL.Measure), product.Measure.MeasureName)
});
}
SqlDatabase.SaveChanges();
}
public static void ReadExcel(SalesContext db, SupermarketModel sql)
{
var results =
new ReadExcelReports("../../../Data/Sample-Sales-Reports.zip", "../../../Temp/ExcelReports/").Extract().Read();
foreach (var result in results)
{
foreach (var row in result.Items)
{
var productName = sql.Products.Where(x => x.ID == row.Id).First().ProductName;
var product = db.Products.Where(x => x.Name == productName).First();
var supermarket = db.Supermarkets.Where(x => x.Name == result.Name).FirstOrDefault();
if (supermarket == null)
{
supermarket = new Supermarket();
supermarket.Name = result.Name;
}
db.Records.Add(new Models.MSSQL.Record
{
Quantity = row.Quantity,
UnitPrice = row.UnitPrice,
Product = product,
Date = result.Date,
Supermarket = supermarket
});
db.SaveChanges();
}
}
db.SaveChanges();
Directory.Delete("../../../Temp/", true);
}
public static void Generate()
{
using (var db = new SalesContext())
{
var sales = new XElement("sales");
foreach (var supermarket in db.Supermarkets)
{
var sale = new XElement("sale");
sale.SetAttributeValue("vendor", supermarket.Name);
var records = db.Records
.Where(x => x.Supermarket.Id == supermarket.Id)
.GroupBy(x => x.Date)
.OrderBy(x => x.Key)
.Select(x => new { Date = x.Key, Sum = x.Sum(y => y.Quantity * y.UnitPrice) });
foreach (var record in records)
{
var summary = new XElement("summary");
summary.SetAttributeValue("date", record.Date.ToShortDateString());
summary.SetAttributeValue("total-sum", record.Sum.ToString("00"));
sale.Add(summary);
}
sales.Add(sale);
}
sales.Save(FilePath);
//Console.WriteLine(File.ReadAllText(FilePath));
}
}
public static void GetPdfReport(SalesContext SqlDatabase)
{
var creator = new PDFCreator(@"../../../Data/Reports/");
creator.CreatePDF("report");
var data = (from table in SqlDatabase.Records
join supermarkets in SqlDatabase.Supermarkets
on table.Supermarket.Id equals supermarkets.Id
select table).GroupBy(x => x.Date).OrderBy(x => x.Key);
foreach (var date in data)
{
creator.CreateTable(5);
creator.AddTableHeader(date.Key.ToShortDateString(), 5);
creator.AddColumnNames(new string[] { "Product", "Unit Price", "Quantity", "Location", "Sum" });
foreach (var item in date)
{
creator.AddContent(new string[] { item.Product.Name.ToString(), item.UnitPrice.ToString(), (item.Quantity.ToString() + " " + item.Product.Measure.ToString()), item.Supermarket.Name, (item.UnitPrice * item.Quantity).ToString() });
}
creator.AddCurrentSum(date.Key.ToShortDateString());
creator.AddTable();
}
creator.CloseFile();
}
static void Main()
{
Debug.Listeners.Add(new ConsoleTraceListener());
Database.SetInitializer(new MigrateDatabaseToLatestVersion<SalesContext, Configuration>());
var sqlContext = new SalesContext();
var mySqlContext = new SupermarketModel();
using (sqlContext)
{
//Problem #1 – Load Excel Reports from ZIP File
MySqlToSql.ReadMySQL(sqlContext, mySqlContext);
ParseExcelReport.ReadExcel(sqlContext, mySqlContext);
//Problem #2 – Generate PDF Aggregated Sales Reports
Generator.GetPdfReport(new SalesContext());
//Problem #3 – Generate XML Sales Report by Vendors
SalesReportsByVendors.Generate();
//Problem #4 – Product Reports
ProductReports.Generate();
//Problem #5 – Load Vendor Expenses from XML
SaveExpenses.Save();
//Problem #6 – Vendors Total Report
VendorsTotalReport.Generate();
}
}
public static void PassToSql(
IEnumerable<Sales.Models.Excel.SupermarketData> results,
SalesContext db,
SupermarketModel sql)
{
using (db)
{
foreach (var result in results)
{
foreach (var row in result.Items)
{
var productName = sql.Products.Where(x => x.ID == row.Id).First().ProductName;
var product = db.Products.Where(x => x.Name == productName).First();
var supermarket = db.Supermarkets.Where(x => x.Name == result.Name).FirstOrDefault();
if (supermarket == null)
{
supermarket = new Supermarket();
supermarket.Name = result.Name;
}
db.Records.Add(new Models.MSSQL.Record
{
Quantity = row.Quantity,
UnitPrice = row.UnitPrice,
Product = product,
Date = result.Date,
Supermarket = supermarket
});
db.SaveChanges();
}
}
db.SaveChanges();
}
}
public static void Generate()
{
using (var db = new SalesContext())
{
foreach (var product in db.Products)
{
var path = DirectoryPath + product.Id + ".json";
Debug.WriteLine("Processing: " + path);
using (var output = new StreamWriter(path))
{
var records = db.Records.Where(x => x.Product.Id == product.Id);
if (!records.Any())
{
Debug.WriteLine(" Skipping...");
continue;
}
var quantity = records.Sum(x => x.Quantity);
var serializedProduct = new Product
{
ProductId = product.Id,
ProductName = product.Name,
VendorName = product.Vendor.Name,
TotalQuantitySold = quantity,
TotalIncomes = quantity * product.BasePrice
};
var result = JsonConvert.SerializeObject(serializedProduct, Formatting.Indented);
output.WriteLine(result);
Debug.WriteLine(result);
// TODO: Decouple
Save(serializedProduct);
}
}
}
}
public static void Save()
{
var expenses = ParseExpenses.Read("../../../Data/expenses.xml");
{
var server = new MongoClient("mongodb://localhost").GetServer();
var db = server.GetDatabase("salesDB");
var sales = db.GetCollection<Expenses>("vendorExpenses");
sales.InsertBatch(expenses);
}
using (var db = new SalesContext())
{
foreach (var expense in expenses)
{
var vendor = db.Vendors.FirstOrDefault(x => x.Name == expense.Vendor);
if (vendor == null)
{
continue;
}
foreach (var item in expense.List)
{
vendor.Expenses.Add(new Models.MSSQL.Expenses
{
DateTime = item.Key,
Money = item.Value
});
}
}
db.SaveChanges();
}
}