private void btnSalaryCalculator_Click(object sender, RoutedEventArgs e)
{
using (var context = new CupOfCoffeeContext())
{
try
{
var pdfFile = new PdfFile();
pdfFile.filename = "..\\..\\..\\PDFReport\\employee-sallaries.pdf";
pdfFile.title = "Report: Employees sallaries";
pdfFile.data = SalaryCalculator.Calculate(context);
PdfCreator.Create(pdfFile);
SalaryRecorder.Insert(pdfFile.data, context);
//var pathToAcroRd32 = Environment.GetEnvironmentVariable("ProgramFiles") + @"\Adobe\Reader 11.0\Reader\AcroRd32.exe";
//var adobeInfo = new ProcessStartInfo(pathToAcroRd32, pdfFile.filename);
MessageBox.Show("The report for employees was successfully generated!",
"Generated successfully",
MessageBoxButton.OK,
MessageBoxImage.Information);
Process.Start(pdfFile.filename);
}
catch (Exception)
{
MessageBox.Show("Cannot generate the report for employees!",
"Generation failed",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
}
}
private void btnProductIncomeCalculator_Click(object sender, RoutedEventArgs e)
{
try
{
string path = "..\\..\\..\\Json-Reports\\";
CupOfCoffeeContext context = new CupOfCoffeeContext();
var reports = ProductsReportsLoader.GetProductsSaleInfo(context);
ProductsReportsLoader.GenerateJsonReports(reports, path);
MySqlModel mySqlCOntext = new MySqlModel();
ProductsReportsLoader.AddReports(reports, mySqlCOntext);
MessageBox.Show("The products reports were successfully generated!",
"Generated successfully",
MessageBoxButton.OK,
MessageBoxImage.Information);
Process.Start(path);
}
catch (Exception)
{
MessageBox.Show("Cannot generate the products reports!",
"Generation failed",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
}
public static List <CustomerFeedback> GenerateFeedbacksFromXml(string path)
{
var context = new CupOfCoffeeContext();
var document = new XmlDocument();
document.Load(path);
var root = document.DocumentElement;
var feedbacks = new List <CustomerFeedback>();
foreach (XmlNode order in root.ChildNodes)
{
var orderId = int.Parse(order.Attributes["id"].Value);
var employeeId = context.Orders.Where(o => o.Id == orderId).Select(e => e.EmployeeId).FirstOrDefault();
foreach (XmlNode feedback in order.ChildNodes)
{
var evaluationValue = int.Parse(feedback.Attributes["evaluation"].Value);
var evaluation = (CustomerEvaluation)evaluationValue;
var fb = new CustomerFeedback()
{
CustomerId = int.Parse(feedback.Attributes["customerId"].Value),
EmployeeId = employeeId,
OrderId = orderId,
Content = feedback.InnerText,
Evaluation = evaluation
};
feedbacks.Add(fb);
}
}
return(feedbacks);
}
public static IList <ProductReport> GetProductsSaleInfo(CupOfCoffeeContext context)
{
var results = context.Products.Select(p => new ProductReport()
{
ProductID = p.Id,
ProductName = p.Name,
ProductCategory = p.Category.Name,
TotalIncome = (Nullable <decimal>)p.OrderDetails.Sum(od => od.Quantity * (p.SellPrice - (od.HappyHour ? 0 : p.SellPrice * 0.25m) - (p.SellPrice * (od.Order.Customer.CustomerStatus.Discount / 100)))) ?? 0,
TotalQuantitySold = (Nullable <int>)p.OrderDetails.Sum(g => g.Quantity) ?? 0
}).ToList();
return(results);
}
public static void Insert(IList <EmployeeSalary> salaries, CupOfCoffeeContext context)
{
foreach (var salary in salaries)
{
context.MonthlySalaries.Add(new MonthlySalary
{
EmployeeId = salary.EmployeeID,
Date = DateTime.Now,
Amount = salary.TotalSalary
});
}
context.SaveChanges();
}
public static List <DailyWaitressReport> GetDailyTurnoverByWaitressReports()
{
var context = new CupOfCoffeeContext();
var results = context.Orders
.GroupBy(o => new { Date = DbFunctions.TruncateTime(o.OrderDate), Name = o.Employee.Name })
.Select(gr => new DailyWaitressReport()
{
Date = gr.Key.Date,
Name = gr.Key.Name,
Turnover = gr.Sum(g => g.OrderDetails.Sum(or => or.Quantity * or.Product.SellPrice))
})
.OrderBy(b => b.Date);
return(results.ToList());
}
public static void ExtractDataToSqlServer()
{
var sqlDb = new CupOfCoffeeContext();
using (sqlDb)
{
var productCollection = db.GetCollection <Product>("Products");
var products = productCollection.FindAll();
var categoryCollection = db.GetCollection <Category>("Categories");
var categories = categoryCollection.FindAll();
foreach (var category in categories)
{
category.Products = products.Where(product => product.CategoryId == category.Id).ToList();
sqlDb.Categories.Add(category);
}
sqlDb.SaveChanges();
var employeeCollection = db.GetCollection <Employee>("Employees");
var employees = employeeCollection.FindAll();
var positionCollection = db.GetCollection <Position>("Positions");
var positions = positionCollection.FindAll();
foreach (var position in positions)
{
position.Employees = employees.Where(employee => employee.PositionId == position.Id).ToList();
sqlDb.Positions.Add(position);
}
sqlDb.SaveChanges();
var customerCollection = db.GetCollection <Customer>("Customers");
var customers = customerCollection.FindAll();
var customerStatusCollection = db.GetCollection <CustomerStatus>("CustomerStatuses");
var customerStatuses = customerStatusCollection.FindAll();
foreach (var status in customerStatuses)
{
status.Customers = customers.Where(customer => customer.CustomerStatusId == status.Id).ToList();
sqlDb.CustomerStatuses.Add(status);
}
sqlDb.SaveChanges();
}
}
public static IList <EmployeeSalary> Calculate(CupOfCoffeeContext context)
{
var salaries = new List <EmployeeSalary>();
var currentDate = DateTime.Now;
var lastMonthDate = currentDate.AddMonths(-1);
var results = context.Employees.Select(e => new EmployeeSalary()
{
EmployeeID = e.Id,
Name = e.Name,
BaseSalary = e.Position.BaseSalary,
ExperienceBonus = ((Nullable <decimal>)(DateTime.Now.Year - e.HireDate.Year) ?? 0) * 20,
FeedbackBonus = ((Nullable <decimal>)e.CustomerFeedbacks.Where(cf => cf.Order.OrderDate > lastMonthDate).Average(cf => (decimal)cf.Evaluation) ?? 0) * 20,
TurnoverBonus = ((Nullable <decimal>)e.Orders.Where(
o => o.OrderDate > lastMonthDate
)
.Sum(
o => o.OrderDetails.Sum(
od => od.Quantity * (
od.Product.SellPrice - (
od.HappyHour ? 0 : od.Product.SellPrice * 0.25m
) - (
od.Product.SellPrice * (
od.Order.Customer.CustomerStatus.Discount / 100
)
)
)
)
)
?? 0) * 0.1m
});
salaries = results.ToList();
foreach (var salary in salaries)
{
salary.TotalSalary = salary.BaseSalary + (decimal)salary.ExperienceBonus + (decimal)salary.FeedbackBonus + (decimal)salary.TurnoverBonus;
}
return(salaries);
}
private void btnFeedbackLoader_Click(object sender, RoutedEventArgs e)
{
if (this.filePath != string.Empty)
{
using (var context = new CupOfCoffeeContext())
{
try
{
var feedbacks = XmlParser.GenerateFeedbacksFromXml(this.filePath);
foreach (var feedback in feedbacks)
{
context.CustomerFeedbacks.Add(feedback);
}
context.SaveChanges();
foreach (var feedback in feedbacks)
{
string feedbackAsJson = JsonConvert.SerializeObject(feedback);
}
DatabasePopulator.ImportFeedback(feedbacks);
}
catch (Exception)
{
MessageBox.Show("Cannot load the xml! Please, make sure that the data in it is correct!",
"Error",
MessageBoxButton.OK,
MessageBoxImage.Error);
}
}
}
else
{
MessageBox.Show("There is no xml file chosen!",
"Warning",
MessageBoxButton.OK,
MessageBoxImage.Warning);
}
}
static public bool Parse(string _filePath, string _productsSheet, string _orderSheet)
{
var reportSheet = _productsSheet + "$";
var reportDetail = _orderSheet + "$";
var connectionBuilder = new OleDbConnectionStringBuilder();
connectionBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
connectionBuilder.DataSource = _filePath;
connectionBuilder.Add("Extended Properties", "Excel 8.0;HDR=Yes");
var connectionExcel = new OleDbConnection(connectionBuilder.ConnectionString);
try
{
connectionExcel.Open();
}
catch (InvalidOperationException)
{
Console.WriteLine("Could not open excel file for parsing -> InvalidOperationException");
return(false);
//throw new InvalidOperationException("Could not open excel file for parsing -> InvalidOperationException");
}
catch (OleDbException)
{
Console.WriteLine("Could not open excel file for parsing -> OleDbException");
return(false);
//throw new InvalidOperationException("Could not open excel file for parsing -> OleDbException");
}
using (connectionExcel)
{
var query = "SELECT * FROM [{0}]";
var details = new OleDbCommand(string.Format(query, reportDetail), connectionExcel);
var orderId = 0;
using (var sqlConnection = new CupOfCoffeeContext())
{
var employeeId = 0;
int?custemerId = null;
var date = new DateTime();
using (var data = details.ExecuteReader())
{
data.Read();
employeeId = Convert.ToInt32(data["EmployeeId"]);
if (data["CustomerId"] != null)
{
custemerId = Convert.ToInt32(data["CustomerId"]);
}
date = DateTime.Parse((string)data["Date"]);
}
var order = sqlConnection.Orders.Add(
new Order()
{
EmployeeId = employeeId,
CustomerId = custemerId,
OrderDate = date
}
);
sqlConnection.SaveChanges();
orderId = order.Id;
var products = new OleDbCommand(string.Format(query, reportSheet), connectionExcel);
try
{
using (var data = products.ExecuteReader())
{
while (data.Read())
{
var productId = Convert.ToInt32(data["ProductId"]);
var quantity = Convert.ToInt32(data["Quantity"]);
var happyHour = Convert.ToBoolean(data["HappyHour"]);
sqlConnection.OrderDetails.Add(
new OrderDetail()
{
OrderId = orderId,
ProductId = productId,
Quantity = quantity,
HappyHour = happyHour
}
);
}
}
sqlConnection.SaveChanges();
return(true);
}
catch (Exception)
{
Console.WriteLine("Could not parse excel file for parsing -> InvalidOperationException");
return(false);
}
}
}
}
