public static int InsertInSupermarketTable(string supermarketName)
{
SupermarketReportsEntities db = new SupermarketReportsEntities();
using (db)
{
var supermarket = new SupermarketMSSql.Model.Supermarket()
{
Name = supermarketName
};
bool doesExist = db.Supermarkets.Any(s => s.Name == supermarket.Name);
//bool doesExist = db.Supermarkets.Select(s => s.Name == supermarket.Name).ToList().Count > 0;
if (!doesExist)
{
db.Supermarkets.Add(supermarket);
db.SaveChanges();
}
return db.Supermarkets
.Where(s => s.Name == supermarket.Name)
.First()
.SupermarketId;
}
}
public static void PrintToXML()
{
using (SupermarketReportsEntities msSqlDb = new SupermarketReportsEntities())
{
var doc = new XDocument();
var sales = new XElement("sales");
var salesReports = msSqlDb.SalesReports
.Include("Product")
.Include("Supermarket")
.Include("Vendor")
.GroupBy(x => x.Product.Vendor.VendorName)
.ToList();
foreach (var report in salesReports)
{
var sale = new XElement("sale", new XAttribute("vendor", report.First().Product.Vendor.VendorName));
foreach (var item in report)
{
sale.Add(new XElement("summary",
new XAttribute("date", item.ReportDate.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture)),
new XAttribute("total-sum", item.Sum)));
}
sales.Add(sale);
}
doc.Add(sales);
doc.Save("../../SalesReports/Sales-by-Vendors-report.xml");
}
}
public static void CreateSalesReportPdfFile(string location)
{
using (SupermarketReportsEntities msSqlDb = new SupermarketReportsEntities())
{
ICollection<SupermarketMSSql.Model.SalesReport> result = msSqlDb.SalesReports.Include("Product").Include("Supermarket").ToList();
FillPdfReportFile(result, location);
}
}
public static void MigrateDataFromMySqlToMSSql()
{
using (SupermarketMySqlEntities mySqlDb = new SupermarketMySqlEntities())
{
using (SupermarketReportsEntities msSqlDb = new SupermarketReportsEntities())
{
var vendors = mySqlDb.Vendors;
foreach (var vendor in vendors)
{
SupermarketMSSql.Model.Vendor newVendor = new SupermarketMSSql.Model.Vendor
{
VendorName = vendor.VendorName
};
msSqlDb.Vendors.Add(newVendor);
}
msSqlDb.SaveChanges();
var measures = mySqlDb.Measures;
foreach (var measure in measures)
{
SupermarketMSSql.Model.Measure newMeasure = new SupermarketMSSql.Model.Measure
{
MeasureName = measure.MeasureName
};
msSqlDb.Measures.Add(newMeasure);
}
msSqlDb.SaveChanges();
var products = mySqlDb.Products;
foreach (var product in products)
{
var vendor = msSqlDb.Vendors.Where(v => v.VendorName == product.Vendor.VendorName).First();
var measure = msSqlDb.Measures.Where(m => m.MeasureName == product.Measure.MeasureName).First();
SupermarketMSSql.Model.Product newProduct = new SupermarketMSSql.Model.Product
{
ProductName = product.ProductName,
BasePrice = product.BasePrice,
Measures_MeasureId = measure.MeasureId,
Vendors_VendorId = vendor.VendorId
};
msSqlDb.Products.Add(newProduct);
}
msSqlDb.SaveChanges();
}
}
}
public static void InsertSalesReport(int productID, int quantity, decimal unitPrice,
decimal sum, DateTime reportDate, int supermarketID)
{
SupermarketReportsEntities db = new SupermarketReportsEntities();
using (db)
{
SalesReport report = new SalesReport()
{
ProductId = productID,
Quantity = quantity,
ReportDate = reportDate,
SupermarketId = supermarketID,
UnitPrice = unitPrice,
Sum = sum
};
db.SalesReports.Add(report);
db.SaveChanges();
}
}
public static int InsertExpenses(string vendorName, DateTime date, decimal expenses)
{
int vendorID;
SupermarketReportsEntities db = new SupermarketReportsEntities();
using (db)
{
vendorID = db.Vendors
.Where(v => v.VendorName == vendorName)
.First()
.VendorId;
Expens newExpense = new Expens
{
VendorId = vendorID,
Date = date,
Expenses = expenses
};
db.Expenses.Add(newExpense);
db.SaveChanges();
}
return vendorID;
}
public static int GetProductIdByName(string mysqlProductName)
{
SupermarketReportsEntities db = new SupermarketReportsEntities();
using (db)
{
var product = db.Products.Where(p => p.ProductName == mysqlProductName).First();
return product.Productid;
}
}
public static void WriteDataFromSQLite()
{
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
@" Data Source=../../vendors.xls; Persist Security Info=false; Extended Properties=Excel 8.0;";
var conn1 = new OleDbConnection(connectionString);
conn1.Open();
using (conn1)
{
string sql = "CREATE TABLE [Product Reports] (Vendor string, Income decimal, Expenses decimal, Taxes decimal, [Financial Result] decimal)";
var cmd = new OleDbCommand(sql, conn1);
cmd.ExecuteNonQuery();
}
using (ProductReportsEntities sqLiteContext = new ProductReportsEntities())
{
using (SupermarketReportsEntities db = new SupermarketReportsEntities())
{
DateTime currentDate = DateTime.Now;
var reports = db.SalesReports
.Where(d => d.ReportDate.Year == currentDate.Year && d.ReportDate.Month == currentDate.Month)
.GroupBy(v => v.Product.Vendors_VendorId).ToList();
foreach (var item in reports)
{
string vendorName = item.First().Product.Vendor.VendorName;
decimal income = sqLiteContext.ProductReports.Where(v => v.VendorName == vendorName).Sum(s => s.TotalIncomes);
int vendorID = item.First().Product.Vendor.VendorId;
var expencesList = db.Expenses
.Where(v => v.VendorId == vendorID && v.Date.Year == currentDate.Year && v.Date.Month == currentDate.Month)
.GroupBy(vi => vi.VendorId).FirstOrDefault();
if (expencesList == null)
{
continue;
}
decimal expences = expencesList.Sum(e => e.Expenses);
var taxes = sqLiteContext.Taxes;
decimal totalTaxes = 0;
foreach (var tax in taxes)
{
//string taxName = tax.Product_Name.Replace("”", "\"").Replace("“", "\"").Replace("’", "'");
var vId = sqLiteContext.ProductReports.Where(p => p.VendorName.Replace("”", "\"")
.Replace("“", "\"").Replace("’", "'") == vendorName).FirstOrDefault();
if (vId == null)
{
continue;
}
totalTaxes += vId.TotalIncomes;
}
var conn = new OleDbConnection(connectionString);
conn.Open();
using (conn)
{
string sql = "INSERT INTO [Product_Reports$](Vendor, Income, Expenses, Taxes, [Financial Result]) VALUES (@VendorName, @Income, @Expences, @Taxes, @FinancialResult)";
var cmd = new OleDbCommand(sql, conn);
cmd.Parameters.AddWithValue("@VendorName", vendorName);
cmd.Parameters.AddWithValue("@Income", income);
cmd.Parameters.AddWithValue("@Expences", expences);
cmd.Parameters.AddWithValue("@Taxes", totalTaxes);
cmd.Parameters.AddWithValue("@FinancialResult", income - expences - totalTaxes);
cmd.ExecuteNonQuery();
}
//var taxes = sqLiteContext.Taxes;
//foreach (var tax in taxes)
//{
// string taxName = tax.Product_Name;
// var vId = sqLiteContext.ProductReports.Where(p => p.ProductName.Replace("\\", "") == taxName).FirstOrDefault();
// if (vId == null)
// {
// continue;
// }
// Console.WriteLine("{0} {1}", taxName);
//}
}
}
}
}