private static void CreateSQLite()
{
SQLiteConnection.CreateFile("SQLiteDatabase.sqlite");
SQLiteConnection m_dbConnection =
new SQLiteConnection("Data Source=SQLiteDatabase.sqlite;Version=3;");
m_dbConnection.Open();
string sql = "CREATE TABLE taxes (productname NVARCHAR(100), tax INT)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
var context = new SupermarketChainContext();
var products = context.Products.Select(p => p.ProductName).Distinct();
var r = new Random();
foreach (var item in products)
{
string prodName = item;
string insert = "insert into taxes (productname, tax) values ('" + prodName + "', '" + r.Next(10, 30) + "')";
SQLiteCommand cmd = new SQLiteCommand(insert, m_dbConnection);
cmd.ExecuteNonQuery();
}
string taxReader = "select * from taxes order by tax desc";
SQLiteCommand readCommand = new SQLiteCommand(taxReader, m_dbConnection);
SQLiteDataReader reader = readCommand.ExecuteReader();
while (reader.Read())
Console.WriteLine("Name: " + reader["productName"] + "\tScore: " + reader["tax"] + "%");
m_dbConnection.Close();
}
static void Main()
{
DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now;
var context = new SupermarketChainContext();
var productData = context.SaleReports
.Where(s => DateTime.Compare(s.SaleTime, startDate) > 0 && DateTime.Compare(s.SaleTime, endDate) < 0)
.GroupBy(sl => sl.ProductId).Select(g => new
{
sum = g.Sum(p => p.Quantity),
productID = g.Select(p => p.ProductId),
productName = g.Select(p => p.Product.ProductName),
price = g.Sum(p => p.Product.Price),
vendor = g.Select(p => p.Vendor.VendorName)
});
foreach (var item in productData)
{
decimal decNumber;
double sum = Double.Parse(item.price.ToString());
double mult = sum * item.sum;
JSONObject JO = new JSONObject();
JO.productID = item.productID.ElementAt(0);
JO.productName = item.productName.ElementAt(0);
JO.quantitySold = item.sum;
JO.income = mult;
JO.vendorName = item.vendor.ElementAt(0);
var serializer = new JavaScriptSerializer();
var json = serializer.Serialize(JO);
File.WriteAllText(@"..\..\..\Json-Reports\" + item.productID.ElementAt(0) + ".json", json);
}
}
static void Main()
{
MongoClient client = new MongoClient("mongodb://localhost");
MongoServer server = client.GetServer();
MongoDatabase db = server.GetDatabase("Reports");
var saleReports = db.GetCollection<MongoDBObject>("SalesByProductReports");
var context = new SupermarketChainContext();
var serializer = new JavaScriptSerializer();
string[] filePaths = Directory.GetFiles(@"..\..\..\Json-Reports");
foreach (var item in filePaths)
{
var des = serializer.Deserialize<SuperMarketChain.JSON.JSONObject>(System.IO.File.ReadAllText(item));
saleReports.Insert(new MongoDBObject()
{
Id = ObjectId.GenerateNewId().ToString(),
ProductID = des.productID,
ProductName = des.productName,
income = des.income,
QuantitySold = des.quantitySold,
Vendor = des.vendorName
});
}
}
public static void GetVendorReport(DateTime firstDate, DateTime secondDate)
{
var context = new SupermarketChainContext();
var reportData = new Dictionary<string, Dictionary<DateTime, decimal>>();
var sales = context.SaleReports
.Where(s => s.SaleTime >= firstDate && s.SaleTime <= secondDate)
.Select(s => new
{
Vendor = s.Vendor,
Date = s.SaleTime,
Quantity = s.Quantity,
Product = s.Product
});
foreach (var s in sales)
{
string vendorName = s.Vendor.VendorName;
if (reportData.ContainsKey(vendorName))
{
reportData[vendorName].Add(s.Date, (decimal)s.Quantity * s.Product.Price);
}
else
{
var dic = new Dictionary<DateTime, Decimal>();
dic.Add(s.Date, (decimal)s.Quantity * s.Product.Price);
reportData.Add(vendorName, dic);
}
}
var xmlReport = new XElement("sales");
foreach (var r in reportData)
{
XElement sale = new XElement("sale",
new XAttribute("vendor", r.Key));
for (int i = 0; i < r.Value.Keys.Count; i++)
{
sale.Add(new XElement("summary",
new XAttribute("date", r.Value.Keys.ElementAt(i).ToShortDateString()),
new XAttribute("sum", r.Value[r.Value.Keys.ElementAt(i)])));
}
xmlReport.Add(sale);
}
xmlReport.Save("../../../SalesReport.xml");
Console.WriteLine("XML Sales Report Created Check The Root Directory");
}
public static void UploadVendorsToSQL(ICollection<Vendor> vendors, SupermarketChainContext context)
{
foreach (var vendor in vendors)
{
var vendorSQL = new SuperMarketChain.Model.Vendor()
{
VendorName = vendor.VendorName
};
if (context.Vendors.Where(v => v.VendorName.Equals(vendorSQL.VendorName)).Count() == 0)
{
context.Vendors.Add(vendorSQL);
Console.WriteLine("Vendor " + vendorSQL.VendorName + " will be uploaded to SQL db." + vendorSQL.VendorName.Count());
}
else
{
Console.WriteLine("Vendor " + vendorSQL.VendorName + " is already in SQL db and will not be uploaded.");
}
}
if (context.ChangeTracker.HasChanges())
{
context.SaveChanges();
}
}
public static void UploadProductsToSQL(ICollection<Product> products, SupermarketChainContext context)
{
foreach (var product in products)
{
var vendorName = GetVendorName("EVGENI-PC", "admin", "1111", product.VendorId);
var vendorId = context.Vendors.Where(v => v.VendorName.Equals(vendorName)).Select(v => v.ID).FirstOrDefault();
var productSQL = new SuperMarketChain.Model.Product()
{
VendorId = vendorId,
ProductName = product.ProductName,
MeasureID = product.MeasureId,
Price = product.Price
};
if (context.Products.Where(p => p.ProductName.Equals(productSQL.ProductName)).Count() == 0)
{
context.Products.Add(productSQL);
Console.WriteLine("Product " + productSQL.ProductName + " will be uploaded to SQL db");
}
else
{
Console.WriteLine("Product " + productSQL.ProductName + " is already in SQL db and will not be uploaded.");
}
}
if (context.ChangeTracker.HasChanges())
{
context.SaveChanges();
}
}
public static void UploadMeasuresToSQL(ICollection<Measure> measures, SupermarketChainContext context)
{
foreach (var measure in measures)
{
var measureSQL = new SuperMarketChain.Model.Measure()
{
ID = measure.Id,
MeasureName = measure.MeasureName
};
if (context.Measures.Where(m => m.MeasureName.Equals(measureSQL.MeasureName)).Count() == 0)
{
context.Measures.Add(measureSQL);
Console.WriteLine("Measure " + measureSQL.MeasureName + " will be uploaded to SQL db.");
}
else
{
Console.WriteLine("Measure " + measureSQL.MeasureName + " is already in SQL db and will not be uploaded.");
}
}
if (context.ChangeTracker.HasChanges())
{
context.SaveChanges();
}
}
private int productId(string title, SupermarketChainContext cont)
{
var names = cont.Products.Where(v => v.ProductName == title).Select(v => v.Id);
foreach (var item in names)
{
return item;
}
return 0;
}
private bool productExist(string name, decimal price, int vendId)
{
var cont = new SupermarketChainContext();
return cont.Products.Any(p => p.ProductName == name && Decimal.Equals(p.Price, price) && p.VendorId == vendId);
}
private int vendorId(string title, SupermarketChainContext cont)
{
var names = cont.Vendors.Where(v => v.VendorName == title).Select(v => v.ID);
foreach (var item in names)
{
return item;
}
return 0;
}
private bool vendorExist(string title, SupermarketChainContext cont)
{
return cont.Vendors.Any(v => v.VendorName == title);
}
private bool saleExist(int productId, double quantity, DateTime time, int vendId)
{
var cont = new SupermarketChainContext();
return cont.SaleReports.Any(s => s.ProductId == productId && s.Quantity == quantity && s.VendorId == vendId && DateTime.Compare(s.SaleTime, time) == 0);
}
private void readExcell(string path)
{
decimal decNumber;
double doubNumber;
var context = new SupermarketChainContext();
string title = "";
bool isTitle = false;
bool isProd = false;
string conString =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";" +
@"Extended Properties='Excel 8.0;HDR=Yes;'";
using (OleDbConnection connection = new OleDbConnection(conString))
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from [Sales$]", connection);
using (var dbContextTransaction = context.Database.BeginTransaction())
{
try
{
using (OleDbDataReader dr = command.ExecuteReader())
{
isTitle = false;
isProd = false;
int counter = 0;
while (dr.Read())
{
var row1Col0 = dr[counter];
var row1Col1 = dr[counter + 1];
var row1Col2 = dr[counter + 2];
var row1Col3 = dr[counter + 3];
if (isTitle == false)
{
title = dr[counter].ToString();
isTitle = true;
if (vendorExist(dr[counter].ToString(), context) == false)
{
context.Vendors.Add(new Vendor
{
VendorName = title
});
context.SaveChanges();
}
continue;
}
else if (dr[counter].ToString() == "Total sum:")
{
continue;
}
if (isProd == false)
{
isProd = true;
continue;
}
Decimal.TryParse(dr[counter + 2].ToString(), out decNumber);
int id = vendorId(title, context);
if (productExist(dr[counter].ToString(), decNumber, id) == false)
{
context.Products.Add(new Product
{
ProductName = dr[counter].ToString(),
MeasureID = 1,
Price = decNumber,
VendorId = id
});
}
Double.TryParse(dr[counter + 1].ToString(), out doubNumber);
if (saleExist(productId(dr[counter].ToString(), context), doubNumber, date, id) == false)
{
context.SaleReports.Add(new SaleReport
{
ProductId = productId(dr[counter].ToString(), context),
Quantity = doubNumber,
SaleTime = date,
VendorId = id
});
}
context.SaveChanges();
}
}
dbContextTransaction.Commit();
}
catch (Exception ex)
{
dbContextTransaction.Rollback();
}
}
}
}
