public static void Transfer(SupermarketsEntities sqlserver)
{
using (var mysql = new MySqlSupermarket())
{
// SET IDENTITY_INSERT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188059.aspx
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Vendors ON");
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Measures ON");
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Products ON");
var mysqlTables = new IEnumerable[] { mysql.Vendors, mysql.Measures, mysql.Products };
var sqlserverEntityFactories = new Func<object>[] { () => new Vendor(), () => new Measure(), () => new Product() };
var sqlserverTables = new DbSet[] { sqlserver.Vendors, sqlserver.Measures, sqlserver.Products };
for (int ii = 0; ii < mysqlTables.Length; ii++)
{
foreach (var mysqlObject in mysqlTables[ii])
{
var sqlserverObject = sqlserverEntityFactories[ii]();
sqlserverObject.LoadPropertiesFrom(mysqlObject);
sqlserverTables[ii].Add(sqlserverObject);
}
}
sqlserver.SaveChanges();
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Vendors OFF");
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Measures OFF");
sqlserver.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Products OFF");
}
}
static void Main()
{
using (var context = new SupermarketsEntities())
{
GenerateAggregateReport(context, "../../aggregated-sales-report.xml");
}
}
public static void Transfer(SupermarketsEntities sqlserver)
{
var data = ExcelReader.GetReportsData("files\\reports.zip", "files\\temp");
foreach (var superMarketSales in data.GroupBy(o => o[0]))
{
var supermarket = sqlserver.Supermarkets.Where(v => v.Name == superMarketSales.Key).FirstOrDefault();
if (supermarket == null)
{
supermarket = new Supermarket { Name = superMarketSales.Key };
sqlserver.Supermarkets.Add(supermarket);
}
foreach (var saleData in superMarketSales)
{
var sale = new ProductSupermarketSale
{
Supermarket = supermarket,
DateSold = DateTime.Parse(saleData[1]),
ProductId = int.Parse(saleData[2]),
UnitPrice = decimal.Parse(saleData[3]),
Quantity = decimal.Parse(saleData[4]),
};
sqlserver.Sales.Add(sale);
}
}
sqlserver.SaveChanges();
}
static void Main()
{
using (var context = new SupermarketsEntities())
{
WriteVendorExpensesReport(context, @"..\..\VendorExpenses.xml");
}
}
public static void GenerateAggregateReport(SupermarketsEntities sqlserver, string fileName)
{
Encoding encoding = Encoding.GetEncoding("utf-8");
using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding))
{
writer.Formatting = Formatting.Indented;
writer.IndentChar = '\t';
writer.Indentation = 1;
WriteVendorSales(writer, sqlserver);
}
}
public static void WriteVendorExpensesReport(SupermarketsEntities sqlserver, string filename)
{
using (XmlReader reader = XmlReader.Create(filename))
{
string currentVendor = string.Empty;
while (reader.Read())
{
if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "sale"))
{
currentVendor = reader.GetAttribute("vendor");
if (sqlserver.Vendors.Any(v => v.Name == currentVendor))
{
Vendor vendor = new Vendor();
vendor.Name = currentVendor;
sqlserver.Vendors.Add(vendor);
}
}
if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "expenses"))
{
Vendor vendor = sqlserver.Vendors.Where(v => v.Name == currentVendor).FirstOrDefault();
if (vendor != null)
{
DateTime monthDate = DateTime.Parse(string.Format("01-{0}", reader.GetAttribute("month")));
VendorExpenses expense = new VendorExpenses();
expense.VendorId = vendor.Id;
expense.Month = monthDate.Month;
expense.Year = monthDate.Year;
expense.Expenses = reader.ReadElementContentAsDecimal();
sqlserver.VendorExpenses.Add(expense);
AddToMongoDB(expense);
}
}
}
sqlserver.SaveChanges();
}
}
public static void GeneratePdfReport(SupermarketsEntities sqlserver, string outputFile)
{
//context.Database.CreateIfNotExists();
PdfPTable table = new PdfPTable(5);
table.WidthPercentage = 100;
table.SetWidths(new int[] { 37, 16, 11, 25, 11 });
AddTableHeader(table);
var aggregatedSales = from sale in sqlserver.Sales.Include("Product").Include("Product.Measure").Include("Supermarket")
group sale by sale.DateSold
into g select new { Date = g.Key, Sum = g.Sum(y => y.Quantity * y.UnitPrice), Sales = g };
foreach (var saleDate in aggregatedSales)
{
AddDayHeader(table, saleDate.Date);
var salesByDay = from sale in saleDate.Sales.ToList()
select new
{
Product = sale.Product.Name,
Quantity = sale.Quantity,
Mea = sale.Product.Measure.Name,
UnitPrice = sale.UnitPrice,
Location = sale.Supermarket.Name,
Sum = sale.Quantity * sale.UnitPrice
};
foreach (var sale in salesByDay)
{
table.AddCell(sale.Product);
table.AddCell(string.Format("{0} {1}", sale.Quantity.ToString(), sale.Mea));
table.AddCell(sale.UnitPrice.ToString("F2"));
table.AddCell(sale.Location);
table.AddCell(sale.Sum.ToString("F2"));
}
AddDayTotal(table, saleDate.Date, saleDate.Sum);
}
SaveToFile(outputFile, table);
}
private static void WriteVendorSales(XmlTextWriter writer, SupermarketsEntities sqlserver)
{
writer.WriteStartDocument();
writer.WriteStartElement("sales");
var query = from sale in sqlserver.Sales
group sale by new { VendorName = sale.Product.Vendor.Name, DateSold = sale.DateSold }
into g select new
{
VendorName = g.Key.VendorName,
DateSold = g.Key.DateSold,
TotalSum = g.Sum(y => y.Quantity * y.UnitPrice)
};
string currVendor = string.Empty;
foreach (var item in query)
{
if (item.VendorName != currVendor)
{
if (currVendor != String.Empty)
{
writer.WriteEndElement();
}
writer.WriteStartElement("sale");
writer.WriteAttributeString("vendor", item.VendorName);
currVendor = item.VendorName;
}
writer.WriteStartElement("summary");
writer.WriteAttributeString("date", item.DateSold.ToString());
writer.WriteAttributeString("total-sum", item.TotalSum.ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
}
static void Main(string[] args)
{
// Console.SetIn(new StringReader("N\nN\nN\nN\nN\nY\nY\n"));
if (Ask("Run Task 1?"))
{
using (var sqlserver = new SupermarketsEntities(true))
{
// Database.SetInitializer<SupermarketsEntities>(new DropCreateDatabaseAlways<SupermarketsEntities>());
// sqlserver.Database.Initialize(true);
if (Ask("Transfer from MySQL?"))
{
MySqlTransfer.Transfer(sqlserver);
Console.WriteLine("Transfer complete");
}
if (Ask("Transfer from Excel?"))
{
ExcelTransfer.Transfer(sqlserver);
Console.WriteLine("Transfer complete");
}
}
}
using (var sqlserver = new SupermarketsEntities())
{
if (Ask("Run Task 2?"))
{
var file = @"output\report-aggregate-sales.pdf";
Directory.CreateDirectory("output");
Supermarkets.Task2.PDF.PdfSalesReport.GeneratePdfReport(sqlserver, file);
Console.WriteLine(string.Format("PDF aggregate sales report in {0}", file));
}
if (Ask("Run Task 3?"))
{
var file = @"output\report-vendor-sales.xml";
Directory.CreateDirectory("output");
Supermarkets.Task3.XML.GenerateXMLFile.GenerateAggregateReport(sqlserver, file);
Console.WriteLine(string.Format("XML vendor sales report in {0}", file));
}
if (Ask("Run task 4?"))
{
Supermarkets.Task4.MongoDB.InsertIntoMongoDB.GenerateMongoDBProductReport();
Console.WriteLine("Data uploaded to MongoDb");
}
if (Ask("Run task 5?"))
{
var file = @"files\VendorExpenses.xml";
Supermarkets.Task5.VendorExpencesXML.GenerateVendorExpenses.WriteVendorExpensesReport(sqlserver, file);
Console.WriteLine("Task 5: expenses added to MongoDb and SQLServer");
}
if (Ask("Run Task 6?"))
{
var db = @"output\taxes.sqlite";
File.Delete(db);
File.Copy(@"files\\taxes_and_vendor_financials.sqlite", db);
var file = @"output\final-report.xlsx";
using (var sqlite = new Supermarkets.SQLite.EntityFramework.SQLiteTaxesEntities())
{
Supermarkets.Task6.MongoToSQLite.LoadMongoIntoSQLite.Load(sqlite);
Supermarkets.Task6.TotalReport.ExcelWriter.GenerateExcel(sqlite.VendorFinancialResults, file);
}
Console.WriteLine(string.Format("Loaded SQLite in {0}", db));
Console.WriteLine(string.Format("Final report in {0}", file));
}
}
}
static void Main(string[] args)
{
using (var entities = new SupermarketsEntities())
{
}
}
static void Main()
{
var filename = Path.Combine(@"..\..\", string.Format("SalesReport{0}.pdf", DateTime.Today.ToString("yyyyMMdd")));
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.InvariantCulture;
using (SupermarketsEntities context = new SupermarketsEntities())
{
GeneratePdfReport(context, filename);
}
System.Diagnostics.Process.Start(filename);
}
