public static void Load()
{
var context = new SupermarketChainContext();
XDocument doc = XDocument.Load("../../../Expences.xml");
var vendorExpenses = from vendor in doc.Descendants("vendor")
select new
{
vendorName = vendor.Attribute("name").Value,
expences = vendor.Elements("expenses")
};
foreach (var v in vendorExpenses)
{
var vendor = context.Vendors.Where(ven => ven.VendorName == v.vendorName).First();
foreach (var e in v.expences)
{
DateTime dt = DateTime.Parse(e.Attribute("month").Value);
decimal amount = Decimal.Parse(e.Value);
context.Expences.Add(new Expence
{
Vendor = vendor,
Amount = amount,
Date = dt
});
}
}
context.SaveChanges();
Console.WriteLine("Expences from Expences.xml have been loaded!");
}
public static void DisplayProductsCountInMssqlAndMysql(SupermarketChainContext context)
{
using (SupermarketChainMySQL contextMySQL = new SupermarketChainMySQL(MySQL.connectionString))
{
var prodsInMSSQL = context.Products.Count();
var prodsInMySQL = contextMySQL.Products.Count();
Console.WriteLine("Products in MSSQL: {0} items;\nProducts in MySQL: {1} items;", prodsInMSSQL, prodsInMySQL);
}
}
public static void ImportExprencesFromMSSqlToMySQL(SupermarketChainContext context)
{
using (SupermarketChainMySQL contextMySQL = new SupermarketChainMySQL(MySQL.connectionString))
{
using (var mySqlDBTransaction = contextMySQL.Database.BeginTransaction())
{
try
{
var expencesInMSSQL = context.Expences.ToList();
var counter = 0;
foreach (var expence in expencesInMSSQL)
{
if (expenceExist(expence, contextMySQL))
{
Console.WriteLine("ID:{0}, Time:{1}, is present at the MySQL table and will not be added.", expence.Id, expence.Date);
}
else
{
contextMySQL.Expences.Add(new Model.Expence
{
Amount = expence.Amount,
Date = expence.Date,
VendorId = expence.VendorId
});
Console.WriteLine("ID:{0}, Time:{1} is added to MySQL table.", expence.Id, expence.Date);
counter++;
}
}
contextMySQL.SaveChanges();
mySqlDBTransaction.Commit();
Console.WriteLine("{0} expences added to MySQL database", counter);
}
catch (Exception)
{
mySqlDBTransaction.Rollback();
}
}
}
}
public static void ImportMeasuresFromMSSqlToMySQL(SupermarketChainContext context)
{
using (SupermarketChainMySQL contextMySQL = new SupermarketChainMySQL(MySQL.connectionString))
{
using (var mySqlDBTransaction = contextMySQL.Database.BeginTransaction())
{
try
{
var measuresInMSSQL = context.Measures.ToList();
var counter = 0;
foreach (var measure in measuresInMSSQL)
{
if (contextMySQL.Measures.Where(m => m.MeasureName.Equals(measure.MeasureName)).Any())
{
Console.WriteLine("{0} is present at the MySQL table and will not be added.", measure.MeasureName);
}
else
{
contextMySQL.Measures.Add(new Model.Measure
{
MeasureName = measure.MeasureName
});
Console.WriteLine("{0} is added to MySQL table.", measure.MeasureName);
counter++;
}
}
contextMySQL.SaveChanges();
mySqlDBTransaction.Commit();
Console.WriteLine("{0} measures added to MySQL database", counter);
}
catch (Exception)
{
mySqlDBTransaction.Rollback();
}
}
}
}
public static void Generate(SupermarketChainContext context, DateTime startDate, DateTime endDate)
{
var saleReports = context.SaleReports
.Where(sl => sl.SaleTime >= startDate && sl.SaleTime <= endDate)
.Select(sl => new
{
Productname = sl.Product.ProductName,
Quantity = sl.Quantity,
UnitPrice = sl.Product.Price,
Location = sl.Product.Vendor.VendorName,
SaleDate = sl.SaleTime
});
var groupedByDate = new Dictionary<DateTime, HashSet<SaleReportInfo>>();
foreach (var i in saleReports)
{
if (groupedByDate.ContainsKey(i.SaleDate))
{
groupedByDate[i.SaleDate].Add(new SaleReportInfo(i.Productname, i.Quantity, i.UnitPrice, i.Location));
}
else
{
var saleReportsHashSet = new HashSet<SaleReportInfo>()
{
new SaleReportInfo(i.Productname, i.Quantity, i.UnitPrice, i.Location)
};
groupedByDate.Add(i.SaleDate, saleReportsHashSet);
}
}
Document doc = new Document(iTextSharp.text.PageSize.LETTER, 35, 35, 70, 60);
PdfWriter writer = PdfWriter.GetInstance(doc, new FileStream("../../../SaleReports.pdf", FileMode.Create));
doc.Open();
PdfPTable table = new PdfPTable(5);
float[] widths = new float[] { 100f, 100f, 100f, 100f, 100f };
table.WidthPercentage = 100;
PdfPCell header = new PdfPCell(new Phrase("Aggregated Sales Report"));
header.Colspan = 5;
header.HorizontalAlignment = 1;
table.AddCell(header);
foreach (var sl in groupedByDate)
{
var date = new PdfPCell(new Phrase(String.Format("{0:dd-MMM-yyyy}", sl.Key)));
date.Colspan = 5;
date.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(date);
PdfPCell h1 = new PdfPCell(new Phrase("Product"));
h1.BackgroundColor = BaseColor.GRAY;
table.AddCell(h1);
h1 = new PdfPCell(new Phrase("Quantity"));
h1.BackgroundColor = BaseColor.GRAY;
table.AddCell(h1);
h1 = new PdfPCell(new Phrase("Unit Price"));
h1.BackgroundColor = BaseColor.GRAY;
table.AddCell(h1);
h1 = new PdfPCell(new Phrase("Location"));
h1.BackgroundColor = BaseColor.GRAY;
table.AddCell(h1);
h1 = new PdfPCell(new Phrase("Sum"));
h1.BackgroundColor = BaseColor.GRAY;
table.AddCell(h1);
foreach (var s in sl.Value)
{
table.AddCell(s.ProductName);
table.AddCell(s.Quantity.ToString());
table.AddCell(s.UnitPrice.ToString());
table.AddCell(s.Location);
table.AddCell(s.Sum.ToString());
}
var msg = new PdfPCell(new Phrase(string.Format("Total sum for {0}: ", String.Format("{0:dd-MMM-yyyy}", sl.Key))));
msg.HorizontalAlignment = 2;
msg.Colspan = 4;
table.AddCell(msg);
var totalSum = sl.Value.Sum(slr => slr.Sum);
var totalSumCell = new PdfPCell(new Phrase(totalSum.ToString()));
table.AddCell(totalSumCell);
}
doc.Add(table);
doc.Close();
}
public static void ImportSalesReportsFromMSSqlToMySQL(SupermarketChainContext context)
{
using (SupermarketChainMySQL contextMySQL = new SupermarketChainMySQL(MySQL.connectionString))
{
using (var mySqlDBTransaction = contextMySQL.Database.BeginTransaction())
{
try
{
var salesReportsInMSSQL = context.SaleReports.ToList();
var counter = 0;
foreach (var saleReport in salesReportsInMSSQL)
{
if(saleExist(saleReport, contextMySQL))
{
Console.WriteLine("ID:{0}, Time:{1}, is present at the MySQL table and will not be added.", saleReport.Id, saleReport.SaleTime);
}
else
{
contextMySQL.SaleReports.Add(new Model.SaleReport
{
ProductId = saleReport.ProductId,
Quantity = saleReport.Quantity,
SaleTime = saleReport.SaleTime,
VendorId = saleReport.VendorId
});
Console.WriteLine("ID:{0}, Time:{1} is added to MySQL table.", saleReport.Id, saleReport.SaleTime);
counter++;
}
}
contextMySQL.SaveChanges();
mySqlDBTransaction.Commit();
Console.WriteLine("{0} salereports added to MySQL database", counter);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
mySqlDBTransaction.Rollback();
}
}
}
}
public static void ImportProductsFromMSSqlToMySQL(SupermarketChainContext context)
{
using (SupermarketChainMySQL contextMySQL = new SupermarketChainMySQL(MySQL.connectionString))
{
using (var mySqlDBTransaction = contextMySQL.Database.BeginTransaction())
{
try
{
var productsInMSSQL = context.Products.ToList();
var counter = 0;
foreach (var product in productsInMSSQL)
{
if (contextMySQL.Products.Where(p => p.ProductName.Equals(product.ProductName)).Any())
{
Console.WriteLine("{0} is present at the MySQL table and will not be added.", product.ProductName);
}
else
{
contextMySQL.Products.Add(new Model.Product
{
ProductName = product.ProductName,
MeasureID = product.MeasureID,
VendorId = product.VendorId,
Price = product.Price
});
Console.WriteLine("{0} is added to MySQL table.", product.ProductName);
counter++;
}
}
contextMySQL.SaveChanges();
mySqlDBTransaction.Commit();
Console.WriteLine("{0} products added to MySQL database", counter);
}
catch (Exception)
{
mySqlDBTransaction.Rollback();
}
}
}
}
