public static void TransferFromMssqlToMysql()
{
var mssqlData = new SupermarketChainMssqlData();
var mySqlData = new SupermarketChainMySqlData();
var mySqlVendors = mssqlData.Vendors.All().ToList();
var mySqlMeasure = mssqlData.Measures.All().ToList();
var mySqlProducts = mssqlData.Products.All().ToList();
var mySqlIncomes = mssqlData.Sales
.All()
.Select(s => new { Income = (s.Quantity * s.Product.Price), ProductId = s.ProductId })
.ToList();
foreach (var vendor in mySqlVendors)
{
var vendors = new Vendor()
{
Name = vendor.Name
};
mySqlData.Vendors.Add(vendors);
mySqlData.SaveChanges();
}
foreach (var measure in mySqlMeasure)
{
var measures = new Measure()
{
Name = measure.Name
};
mySqlData.Measures.Add(measures);
mySqlData.SaveChanges();
}
foreach (var product in mySqlProducts)
{
var products = new Product()
{
Name = product.Name,
Price = product.Price,
VendorId = mySqlData.Vendors.All().FirstOrDefault(v => v.Name == product.Vendor.Name).Id,
MeasureId = mySqlData.Measures.All().FirstOrDefault(m => m.Name == product.Measure.Name).Id
};
mySqlData.Products.Add(products);
mySqlData.SaveChanges();
}
foreach (var income in mySqlIncomes)
{
var incomes = new Income()
{
IncomeValue = income.Income,
ProductId = income.ProductId
};
mySqlData.Incomes.Add(incomes);
mySqlData.SaveChanges();
}
}
public static void GenerateJsonReports()
{
Console.WriteLine("Enter startDate and endDate separated by space in format(yyyy-mm-dd):");
string input = Console.ReadLine();
try
{
string[] dates = input.Split(' ');
DateTime startDate = DateTime.Parse(dates[0]);
DateTime endDate = DateTime.Parse(dates[1]);
if (startDate > endDate)
{
throw new ArgumentException("StartDate must be before endDate");
}
var myssqlData = new SupermarketChainMssqlData();
var jsSerializer = new JavaScriptSerializer();
var sales = myssqlData.Sales
.All()
.Where(s => s.SoldDate >= startDate && s.SoldDate <= endDate)
.Select(
s => new
{
productId = s.ProductId,
productName = s.Product.Name,
vendorName = s.Product.Vendor.Name,
totalQuantitySold = s.Quantity,
totalIncomes = s.Quantity * s.Product.Price
}).ToList();
foreach (var sale in sales)
{
var productJson = jsSerializer.Serialize(sale);
SaveDataInMongoDb(productJson);
if (!File.Exists(Constants.ReportPath + sale.productId + ".json"))
{
File.WriteAllText(Constants.ReportPath + sale.productId + ".json", productJson);
}
}
}
catch (Exception)
{
Console.WriteLine("Invalid input.");
}
}
public static void TransferDataFromOracleToMssql()
{
var oracleData = new SupermarketChainOracleData();
var mssqlData = new SupermarketChainMssqlData();
int productsAdded = 0;
int measuresAdded = 0;
int vendorsAdded = 0;
Console.WriteLine("Data transfer...");
var oracleVendors = oracleData.Vendors.All().ToList();
var oracleMeasures = oracleData.Measures.All().ToList();
var oracleProducts = oracleData.Products.All().ToList();
foreach (var oracleVendor in oracleVendors)
{
var checkIfVendorExists = mssqlData.Vendors.SearchFor(p => p.Name == oracleVendor.Name).FirstOrDefault();
if (checkIfVendorExists == null)
{
mssqlData.Vendors.Add(
new Vendor
{
Name = oracleVendor.Name
});
mssqlData.SaveChanges();
vendorsAdded++;
}
}
Console.WriteLine(vendorsAdded + " from " + oracleVendors.Count + " products added.");
Console.WriteLine(oracleVendors.Count - vendorsAdded + " vendors already exist in the database.");
foreach (var oracleMeasure in oracleMeasures)
{
var checkIfMeasureExists = mssqlData.Measures.SearchFor(p => p.Name == oracleMeasure.Name).FirstOrDefault();
if (checkIfMeasureExists == null)
{
mssqlData.Measures.Add(
new Measure
{
Name = oracleMeasure.Name
});
mssqlData.SaveChanges();
measuresAdded++;
}
}
Console.WriteLine(measuresAdded + " from " + oracleMeasures.Count + " products added.");
Console.WriteLine(oracleMeasures.Count - measuresAdded + " measures already exist in the database.");
foreach (var oracleProduct in oracleProducts)
{
var checkIfProductExists = mssqlData.Products.SearchFor(p => p.Name == oracleProduct.Name).FirstOrDefault();
if (checkIfProductExists == null)
{
mssqlData.Products.Add(
new Product
{
Name = oracleProduct.Name,
MeasureId = mssqlData.Measures.All().FirstOrDefault(m => m.Name == oracleProduct.Measure.Name).Id,
Price = oracleProduct.Price,
VendorId = mssqlData.Vendors.All().FirstOrDefault(m => m.Name == oracleProduct.Vendor.Name).Id
});
mssqlData.SaveChanges();
productsAdded++;
}
}
Console.WriteLine(productsAdded + " from " + oracleProducts.Count + " products added.");
Console.WriteLine(oracleProducts.Count - productsAdded + " products already exist in the database.");
}
public static void TransferFromMssqlToMysql()
{
var mssqlData = new SupermarketChainMssqlData();
var mySqlData = new SupermarketChainMySqlData();
var mySqlVendors = mssqlData.Vendors.All().ToList();
var mySqlMeasure = mssqlData.Measures.All().ToList();
var mySqlProducts = mssqlData.Products.All().ToList();
var mySqlIncomes = mssqlData.Sales
.All()
.Select(s => new { Income = (s.Quantity * s.Product.Price), ProductId = s.ProductId })
.ToList();
foreach (var vendor in mySqlVendors)
{
var vendors = new Vendor()
{
Name = vendor.Name
};
mySqlData.Vendors.Add(vendors);
mySqlData.SaveChanges();
}
foreach (var measure in mySqlMeasure)
{
var measures = new Measure()
{
Name = measure.Name
};
mySqlData.Measures.Add(measures);
mySqlData.SaveChanges();
}
foreach (var product in mySqlProducts)
{
var products = new Product()
{
Name = product.Name,
Price = product.Price,
VendorId = mySqlData.Vendors.All().FirstOrDefault(v => v.Name == product.Vendor.Name).Id,
MeasureId = mySqlData.Measures.All().FirstOrDefault(m => m.Name == product.Measure.Name).Id
};
mySqlData.Products.Add(products);
mySqlData.SaveChanges();
}
foreach (var income in mySqlIncomes)
{
var incomes = new Income()
{
IncomeValue = income.Income,
ProductId = income.ProductId
};
mySqlData.Incomes.Add(incomes);
mySqlData.SaveChanges();
}
}
public static void ExportDataToPdf(DateTime startDate, DateTime endDate)
{
var myssqlData = new SupermarketChainMssqlData();
var document = new Document(PageSize.A4, 50, 50, 10, 10);
// Create a new PdfWriter object, specifying the output stream
var output = File.Create(Constants.ReportPath + "PdfReport.pdf");
var writer = PdfWriter.GetInstance(document, output);
// Open the Document for writing
document.Open();
var salesInfoTable = new PdfPTable(5);
salesInfoTable.TotalWidth = 100f;
salesInfoTable.HorizontalAlignment = 0;
salesInfoTable.SpacingBefore = 5;
salesInfoTable.SpacingAfter = 5;
salesInfoTable.DefaultCell.Border = 0;
salesInfoTable.SetWidths(new float[] { 3f, 1.5f, 2f, 3f, 1f });
//set fonts
BaseFont bfTimes = BaseFont.CreateFont(BaseFont.TIMES_ROMAN, BaseFont.CP1252, false);
Font normal = new Font(bfTimes, 10);
Font bold = new Font(bfTimes, 11, Font.BOLD);
PdfPCell cellHeader = new PdfPCell(new Phrase("Aggregated Sales Report"));
cellHeader.Colspan = 5;
cellHeader.HorizontalAlignment = 1; //0=Left, 1=Centre, 2=Right
cellHeader.BackgroundColor = new BaseColor(135, 196, 28);
cellHeader.PaddingTop = 10f;
cellHeader.PaddingBottom = 10f;
salesInfoTable.AddCell(cellHeader);
salesInfoTable.AddCell(new Phrase("Product", bold));
salesInfoTable.AddCell(new Phrase("Quantity", bold));
salesInfoTable.AddCell(new Phrase("Unit Price", bold));
salesInfoTable.AddCell(new Phrase("Location", bold));
salesInfoTable.AddCell(new Phrase("Sum", bold));
var salesQuery = myssqlData.Sales
.All()
.Where(s => s.SoldDate >= startDate && s.SoldDate <= endDate)
.Select(
s => new
{
productName = s.Product.Name,
totalQuantitySold = s.Quantity,
unitPrice = s.Product.Price,
location = s.Supermarket.Name,
totalIncomes = s.Quantity * s.Product.Price
}).ToList();
foreach (var sale in salesQuery)
{
salesInfoTable.AddCell(sale.productName);
salesInfoTable.AddCell(Convert.ToDecimal(sale.totalQuantitySold).ToString(CultureInfo.InvariantCulture));
salesInfoTable.AddCell(Convert.ToDecimal(sale.unitPrice).ToString(CultureInfo.InvariantCulture));
salesInfoTable.AddCell(sale.location);
salesInfoTable.AddCell(Convert.ToDecimal(sale.totalIncomes).ToString(CultureInfo.InvariantCulture));
}
document.Add(salesInfoTable);
document.Close();
}
public static void TransferDataFromOracleToMssql()
{
var oracleData = new SupermarketChainOracleData();
var mssqlData = new SupermarketChainMssqlData();
int productsAdded = 0;
int measuresAdded = 0;
int vendorsAdded = 0;
Console.WriteLine("Data transfer...");
var oracleVendors = oracleData.Vendors.All().ToList();
var oracleMeasures = oracleData.Measures.All().ToList();
var oracleProducts = oracleData.Products.All().ToList();
foreach (var oracleVendor in oracleVendors)
{
var checkIfVendorExists = mssqlData.Vendors.SearchFor(p => p.Name == oracleVendor.Name).FirstOrDefault();
if (checkIfVendorExists == null)
{
mssqlData.Vendors.Add(
new Vendor
{
Name = oracleVendor.Name
});
mssqlData.SaveChanges();
vendorsAdded++;
}
}
Console.WriteLine(vendorsAdded + " from " + oracleVendors.Count + " products added.");
Console.WriteLine(oracleVendors.Count - vendorsAdded + " vendors already exist in the database.");
foreach (var oracleMeasure in oracleMeasures)
{
var checkIfMeasureExists = mssqlData.Measures.SearchFor(p => p.Name == oracleMeasure.Name).FirstOrDefault();
if (checkIfMeasureExists == null)
{
mssqlData.Measures.Add(
new Measure
{
Name = oracleMeasure.Name
});
mssqlData.SaveChanges();
measuresAdded++;
}
}
Console.WriteLine(measuresAdded + " from " + oracleMeasures.Count + " products added.");
Console.WriteLine(oracleMeasures.Count - measuresAdded + " measures already exist in the database.");
foreach (var oracleProduct in oracleProducts)
{
var checkIfProductExists = mssqlData.Products.SearchFor(p => p.Name == oracleProduct.Name).FirstOrDefault();
if (checkIfProductExists == null)
{
mssqlData.Products.Add(
new Product
{
Name = oracleProduct.Name,
MeasureId = mssqlData.Measures.All().FirstOrDefault(m => m.Name == oracleProduct.Measure.Name).Id,
Price = oracleProduct.Price,
VendorId = mssqlData.Vendors.All().FirstOrDefault(m => m.Name == oracleProduct.Vendor.Name).Id
});
mssqlData.SaveChanges();
productsAdded++;
}
}
Console.WriteLine(productsAdded + " from " + oracleProducts.Count + " products added.");
Console.WriteLine(oracleProducts.Count - productsAdded + " products already exist in the database.");
}
public static void Generate()
{
string input = Console.ReadLine();
try
{
string[] dates = input.Split(' ');
DateTime startDate = DateTime.Parse(dates[0]);
DateTime endDate = DateTime.Parse(dates[1]);
if (startDate > endDate)
{
throw new ArgumentException("StartDate must be before endDate");
}
var msSqlData = new SupermarketChainMssqlData();
XmlDocument xml = new XmlDocument();
XmlElement salesElement = xml.CreateElement("sales");
xml.AppendChild(salesElement);
var vendors = msSqlData.Sales
.SearchFor(s => s.SoldDate >= startDate && s.SoldDate <= endDate)
.GroupBy(v => v.Product.Vendor.Name,
(key, val) => new { Name = key, Sales = val.Select(d => new { d.SoldDate, Sum = d.Quantity * d.Product.Price }) });
foreach (var vendor in vendors)
{
XmlElement saleElement = xml.CreateElement("sale");
saleElement.SetAttribute("vendor", vendor.Name);
Console.WriteLine(vendor.Name);
var sales = vendor.Sales.GroupBy(
s => s.SoldDate,
(key, val) => new { Date = key, Sum = val.Sum(t => t.Sum) });
foreach (var sale in sales)
{
XmlElement summaryElement = xml.CreateElement("summary");
summaryElement.SetAttribute("date", sale.Date.ToString("dd-MMM-yyyy"));
summaryElement.SetAttribute("total-sum", sale.Sum.ToString(CultureInfo.InvariantCulture));
saleElement.AppendChild(summaryElement);
Console.WriteLine(sale.Date.ToString("dd-MMM-yyyy"));
Console.WriteLine(sale.Sum);
}
salesElement.AppendChild(saleElement);
}
string output = xml.OuterXml;
StreamWriter file = new StreamWriter(Constants.XmlReportsPath);
file.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
XDocument doc = XDocument.Parse(output);
file.WriteLine(doc);
file.Close();
}
catch (Exception)
{
Console.WriteLine("Invalid input.");
}
}
public static void Generate()
{
string input = Console.ReadLine();
try
{
string[] dates = input.Split(' ');
DateTime startDate = DateTime.Parse(dates[0]);
DateTime endDate = DateTime.Parse(dates[1]);
if (startDate > endDate)
{
throw new ArgumentException("StartDate must be before endDate");
}
var msSqlData = new SupermarketChainMssqlData();
XmlDocument xml = new XmlDocument();
XmlElement salesElement = xml.CreateElement("sales");
xml.AppendChild(salesElement);
var vendors = msSqlData.Sales
.SearchFor(s => s.SoldDate >= startDate && s.SoldDate <= endDate)
.GroupBy(v => v.Product.Vendor.Name,
(key, val) => new { Name = key, Sales = val.Select(d => new { d.SoldDate, Sum = d.Quantity * d.Product.Price }) });
foreach (var vendor in vendors)
{
XmlElement saleElement = xml.CreateElement("sale");
saleElement.SetAttribute("vendor", vendor.Name);
Console.WriteLine(vendor.Name);
var sales = vendor.Sales.GroupBy(
s => s.SoldDate,
(key, val) => new { Date = key, Sum = val.Sum(t => t.Sum) });
foreach (var sale in sales)
{
XmlElement summaryElement = xml.CreateElement("summary");
summaryElement.SetAttribute("date", sale.Date.ToString("dd-MMM-yyyy"));
summaryElement.SetAttribute("total-sum", sale.Sum.ToString(CultureInfo.InvariantCulture));
saleElement.AppendChild(summaryElement);
Console.WriteLine(sale.Date.ToString("dd-MMM-yyyy"));
Console.WriteLine(sale.Sum);
}
salesElement.AppendChild(saleElement);
}
string output = xml.OuterXml;
StreamWriter file = new StreamWriter(Constants.XmlReportsPath);
file.WriteLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
XDocument doc = XDocument.Parse(output);
file.WriteLine(doc);
file.Close();
}
catch (Exception)
{
Console.WriteLine("Invalid input.");
}
}
public static void ExportDataToPdf(DateTime startDate, DateTime endDate)
{
var myssqlData = new SupermarketChainMssqlData();
var document = new Document(PageSize.A4, 50, 50, 10, 10);
// Create a new PdfWriter object, specifying the output stream
var output = File.Create(Constants.ReportPath + "PdfReport.pdf");
var writer = PdfWriter.GetInstance(document, output);
// Open the Document for writing
document.Open();
var salesInfoTable = new PdfPTable(5);
salesInfoTable.TotalWidth = 100f;
salesInfoTable.HorizontalAlignment = 0;
salesInfoTable.SpacingBefore = 5;
salesInfoTable.SpacingAfter = 5;
salesInfoTable.DefaultCell.Border = 0;
salesInfoTable.SetWidths(new float[] { 3f, 1.5f, 2f, 3f, 1f });
//set fonts
BaseFont bfTimes = BaseFont.CreateFont(BaseFont.TIMES_ROMAN, BaseFont.CP1252, false);
Font normal = new Font(bfTimes, 10);
Font bold = new Font(bfTimes, 11, Font.BOLD);
PdfPCell cellHeader = new PdfPCell(new Phrase("Aggregated Sales Report"));
cellHeader.Colspan = 5;
cellHeader.HorizontalAlignment = 1; //0=Left, 1=Centre, 2=Right
cellHeader.BackgroundColor = new BaseColor(135, 196, 28);
cellHeader.PaddingTop = 10f;
cellHeader.PaddingBottom = 10f;
salesInfoTable.AddCell(cellHeader);
salesInfoTable.AddCell(new Phrase("Product", bold));
salesInfoTable.AddCell(new Phrase("Quantity", bold));
salesInfoTable.AddCell(new Phrase("Unit Price", bold));
salesInfoTable.AddCell(new Phrase("Location", bold));
salesInfoTable.AddCell(new Phrase("Sum", bold));
var salesQuery = myssqlData.Sales
.All()
.Where(s => s.SoldDate >= startDate && s.SoldDate <= endDate)
.Select(
s => new
{
productName = s.Product.Name,
totalQuantitySold = s.Quantity,
unitPrice = s.Product.Price,
location = s.Supermarket.Name,
totalIncomes = s.Quantity * s.Product.Price
}).ToList();
foreach (var sale in salesQuery)
{
salesInfoTable.AddCell(sale.productName);
salesInfoTable.AddCell(Convert.ToDecimal(sale.totalQuantitySold).ToString(CultureInfo.InvariantCulture));
salesInfoTable.AddCell(Convert.ToDecimal(sale.unitPrice).ToString(CultureInfo.InvariantCulture));
salesInfoTable.AddCell(sale.location);
salesInfoTable.AddCell(Convert.ToDecimal(sale.totalIncomes).ToString(CultureInfo.InvariantCulture));
}
document.Add(salesInfoTable);
document.Close();
}