public static void Export()
{
string MyConString = "SERVER=localhost;DATABASE=supermarketchain;UID=root;";
MySqlConnection connection = new MySqlConnection(MyConString);
var context= new SupermarketChainDbContext();
var vendors = context.Vendors.Select(v => v.Name);
AddVendorsToMySQL(vendors, connection);
AddProductsToMySQL(context, connection);
AddExpensesToMySQL(context, connection);
var incomes = context.Sales.GroupBy(s => s.Product).Select(p => new
{
productName=p.Select(s=>s.Product.Name),
sum=p.Sum(s=>s.Sum)
});
foreach (var income in incomes)
{
try
{
string cmdPull = @"SELECT id FROM supermarketchain.products where name='" + income.productName.FirstOrDefault() + "'";
MySqlCommand pull = new MySqlCommand(cmdPull, connection);
connection.Open();
MySqlDataReader reader = pull.ExecuteReader();
int? productId = null;
while (reader.Read())
{
productId = int.Parse(reader.GetString(0));
}
connection.Close();
string cmdPush = "INSERT INTO incomes(sum,product_id)VALUES ('" + income.sum + "','" + productId + "')";
MySqlCommand cmd = new MySqlCommand(cmdPush, connection);
connection.Open();
int result = cmd.ExecuteNonQuery();
connection.Close();
//lblError.Text = "Data Saved";
}
catch (Exception ex)
{
//Console.Write("not entered");
Console.WriteLine(ex.Message);
//lblError.Text = ex.Message;
}
}
}
public static void Export()
{
bool validDate;
var startDate = EnterFirstDate();
var endDate = EnterEndDate(startDate);
var context = new SupermarketChainDbContext();
var client = new MongoClient();
var db = client.GetDatabase("SuperMarketChainReports");
var collection = db.GetCollection<BsonDocument>("SalesByProductReports");
var products = context.Sales.Where(s => s.Date <= endDate && s.Date >= startDate)
.GroupBy(s => s.ProductId)
.Select(
p => new
{
productName = p.Select(s => s.Product.Name),
productId = p.Key,
vendorName = p.Select(s => s.Product.Vendor.Name),
totalQuantity = p.Sum(s => s.Quantity),
totalIncome = p.Sum(s => s.Sum)
});
if (!products.Any())
{
Console.WriteLine("There are no sales during that period {0} - {1}", startDate, endDate);
}
else
{
foreach (var product in products)
{
var document = new BsonDocument
{
{ "product-id", product.productId },
{ "product-name", product.productName.First() },
{ "vendor-name", product.vendorName.First() },
{ "total-quantity-sold", product.totalQuantity },
{ "total-incomes", product.totalIncome.ToString() }
};
// Upload to the DB
collection.InsertOneAsync(document).Wait();
// Export Data
SaveToJson(document, product.productId);
}
}
}
private static void AddExpensesToMySQL(SupermarketChainDbContext context, MySqlConnection connection)
{
var expenses = context.Expenses.Select(e => new
{
sum = e.Sum,
date = e.Date,
vendorName = e.Vendor.Name
});
foreach (var expense in expenses)
{
try
{
string cmdPull = @"SELECT id FROM supermarketchain.vendors where name='" + expense.vendorName + "'";
MySqlCommand pull = new MySqlCommand(cmdPull, connection);
connection.Open();
MySqlDataReader reader = pull.ExecuteReader();
int? vendorId = null;
while (reader.Read())
{
vendorId = int.Parse(reader.GetString(0));
}
connection.Close();
string cmdPush = "INSERT INTO expenses(sum,period,vendor_id)VALUES ('" + expense.sum + "','" +
expense.date.ToString("yyyy-MM-dd HH:mm:ss") + "','" + vendorId + "')";
MySqlCommand cmd = new MySqlCommand(cmdPush, connection);
connection.Open();
int result = cmd.ExecuteNonQuery();
connection.Close();
//lblError.Text = "Data Saved";
}
catch (Exception ex)
{
//Console.Write("not entered");
Console.WriteLine(ex.Message);
//lblError.Text = ex.Message;
}
}
}
public static void Main(string[] args)
{
//// Testing SupermarketChainDbContext
var dbContext = new SupermarketChainDbContext();
Console.WriteLine(dbContext.Vendors.First(v => v.Name == "Kamenitza").Name);
//Console.WriteLine(dbContext.Vendors.Count());
//// Testing repository
//var dbVendors = new Repository<Vendor>();
//dbVendors.Add(new Vendor { Name = "Zagorka" });
//dbVendors.SaveChanges();
//// Testing unit of work
var data = new SupermarketChainData();
//Console.WriteLine(data.Vendors.All().FirstOrDefault(v => v.Name == "Zagorka").Name);
//Console.WriteLine(data.Supermarkets.All().FirstOrDefault(v => v.Name == "Supermarket “Bourgas – Plaza”").Name);
//PDFReportGenerator.GeneratePdfReport(DateTime.ParseExact("20-Jul-2014", "dd-MMM-yyyy", CultureInfo.InvariantCulture), DateTime.ParseExact("23-Jul-2014", "dd-MMM-yyyy", CultureInfo.InvariantCulture), data);
//JsonReportsToMongoDB.Export();
//XMLReportSalesByVendor.GenerateReport(DateTime.ParseExact("20-Jul-2014", "dd-MMM-yyyy", CultureInfo.InvariantCulture), DateTime.ParseExact("23-Jul-2014", "dd-MMM-yyyy", CultureInfo.InvariantCulture), dbContext);
//ImportExpensesFromXML.Import();
ExportDataToMySQL.Export();
}
public static void GenerateReport(DateTime startDate, DateTime endDate, SupermarketChainDbContext context)
{
var salesByVendors =
context.Sales.Where(s => s.Date >= startDate && s.Date < endDate)
.GroupBy(s => new { Vendor = s.Product.Vendor.Name, Date = s.Date})
.Select(s => new
{
Vendor = s.Key.Vendor,
Date = s.Key.Date,
TotalSum = s.Sum(a => a.Sum)
});
var groupedByDaysAndVendorsSales =
salesByVendors.GroupBy(s => s.Vendor).ToList();
var report = new XDocument();
var salesReportXml = new XElement("sales");
foreach (var sale in groupedByDaysAndVendorsSales)
{
var vendorXML = new XElement("sale", new XAttribute("vendor", sale.Key));
foreach (var row in sale)
{
var newRowXML = new XElement("summary", new XAttribute("date", row.Date.ToString("dd-MMM-yyyy")), new XAttribute("total-sum", row.TotalSum));
vendorXML.Add(newRowXML);
}
salesReportXml.Add(vendorXML);
}
report.Add(salesReportXml);
Console.WriteLine(report);
report.Save("../../salesReportByVendors.xml");
}
private static void AddProductsToMySQL(SupermarketChainDbContext context, MySqlConnection connection)
{
var products = context.Products.Select(p => new
{
name = p.Name,
vendorName = p.Vendor.Name
});
foreach (var product in products)
{
try
{
string cmdPull = @"SELECT id FROM supermarketchain.vendors where name='" + product.vendorName + "'";
MySqlCommand pull = new MySqlCommand(cmdPull, connection);
connection.Open();
MySqlDataReader reader = pull.ExecuteReader();
int? vendorId = null;
while (reader.Read())
{
vendorId = int.Parse(reader.GetString(0));
}
connection.Close();
string cmdPush = "INSERT INTO products(name,vendor_id)VALUES ('" + product.name + "','" + vendorId + "')";
MySqlCommand cmd = new MySqlCommand(cmdPush, connection);
connection.Open();
int result = cmd.ExecuteNonQuery();
connection.Close();
//lblError.Text = "Data Saved";
}
catch (Exception ex)
{
//Console.Write("not entered");
Console.WriteLine(ex.Message);
//lblError.Text = ex.Message;
}
}
}
