public static void LoadDataFromXml(string xmlFilePath, NewEntitiesModel context)
{
XDocument document = XDocument.Load(xmlFilePath);
var vendorSales = from sales in document.Descendants("sale")
select sales;
foreach (var sale in vendorSales)
{
string vendorName = sale.Attribute("vendor").Value;
var expenses = from expense in sale.Descendants("expenses")
select expense;
foreach (var expense in expenses)
{
VendorExpensesEntity entity = new VendorExpensesEntity();
entity.Expenses = decimal.Parse(expense.Value, CultureInfo.InvariantCulture);
entity.VendorName = vendorName;
string date = expense.Attribute("month").Value;
entity.Date = DateTime.Parse(date);
ParseIntoSqlServer(entity, context);
}
}
Console.WriteLine("XML data sent to DBs");
}
static void Main()
{
using (NewEntitiesModel context = new NewEntitiesModel())
{
DatabaseMover.MoveDatabase();
ExcelReader.ParseZip(@"../../../ZIPs/Sample-Sales-Reports.zip", context);
Console.WriteLine("Parsed excels into db");
}
PdfGenerator.GenerateReport();
using (NewEntitiesModel context = new NewEntitiesModel())
{
XmlWriter.CreateXmlReport(context);
}
using (NewEntitiesModel context = new NewEntitiesModel())
{
XmlLoader.LoadDataFromXml(@"../../../ZIPs/Vendor-Expenses.xml", context);
}
using (NewEntitiesModel context = new NewEntitiesModel())
{
JSONWriter.WriteJSON(context);
}
SQLiteConnector.CreateSQLiteDB();
}
static void Main()
{
//DatabaseMover.MoveDatabase();
//using (NewEntitiesModel context = new NewEntitiesModel())
//{
// ExcelReader.ParseZip(@"../../../ZIPs/Sample-Sales-Reports.zip", context);
//}
//PdfGenerator.GenerateReport();
//using (NewEntitiesModel context = new NewEntitiesModel())
//{
// XmlLoader.LoadDataFromXml(@"../../../ZIPs/Vendor-Expenses.xml", context);
//}
using (NewEntitiesModel context = new NewEntitiesModel())
{
XmlWriter.CreateXmlReport(context);
}
///*DateTime currentDate = DateTime.Now;
//Console.WriteLine(currentDate.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture)); currentDate.ToString("dd-mm-yyyy", CultureInfo.InvariantCulture);
//decimal number = 1455.8m;
//Console.WriteLine(string.Format("{0:0.00}", number));*/
//using (NewEntitiesModel context = new NewEntitiesModel())
//{
// JSONWriter.WriteJSON(context);
//}
}
public static void MoveDatabase()
{
using (OldEntitiesModel context = new OldEntitiesModel())
{
using (NewEntitiesModel newContext = new NewEntitiesModel())
{
foreach (var product in context.Products)
{
SqlServer.Product newProduct = new SqlServer.Product();
newProduct.Id = product.Id;
newProduct.BasePrice = product.BasePrice;
newProduct.ProductName = product.ProductName;
newContext.Products.Add(newProduct);
newContext.SaveChanges();
}
foreach (var vendor in context.Vendors)
{
SqlServer.Vendor newVendor = new SqlServer.Vendor();
newVendor.Id = vendor.Id;
newVendor.VendorName = vendor.VendorName;
foreach (var product in vendor.Products)
{
var productToAdd = newContext.Products.Where(
p => p.Id.Equals(product.Id))
.FirstOrDefault();
if (productToAdd != null)
{
newVendor.Products.Add(productToAdd);
}
}
newContext.Vendors.Add(newVendor);
newContext.SaveChanges();
}
foreach (var measure in context.Measures)
{
SqlServer.Measure newMeasure = new SqlServer.Measure();
newMeasure.Id = measure.Id;
newMeasure.MeasureName = measure.MeasureName;
foreach (var product in measure.Products)
{
var productToAdd = newContext.Products.Where(
p => p.Id.Equals(product.Id))
.FirstOrDefault();
if (productToAdd != null)
{
newMeasure.Products.Add(productToAdd);
}
}
newContext.Measures.Add(newMeasure);
newContext.SaveChanges();
}
}
}
}
public static void ParseExcelFiles(string directoryPath, NewEntitiesModel context)
{
var directories = Directory.GetDirectories(directoryPath);
foreach (var directory in directories)
{
var files = Directory.GetFiles(directory);
DateTime date = DateTime.Parse(Path.GetFileName(directory), CultureInfo.InvariantCulture);
foreach (var file in files)
{
GenerateReportsFromExcel(file, context, date);
}
}
}
public static void ParseIntoSqlServer(VendorExpensesEntity entity, NewEntitiesModel context)
{
MonthlyExpenseReport report = new MonthlyExpenseReport();
report.Date = entity.Date;
report.Expenses = entity.Expenses;
int reportId = -1;
if (context.MonthlyExpenseReports.Count() == 0)
{
reportId = 0;
}
else
{
reportId = context.MonthlyExpenseReports.Max(r => r.Id);
}
reportId++;
report.Id = reportId;
var vendor = context.Vendors.Where(
s => s.VendorName.Equals(entity.VendorName))
.FirstOrDefault();
if (vendor == null)
{
vendor = new Vendor();
vendor.VendorName = entity.VendorName;
int biggestId = -1;
if (context.Vendors.Count() == 0)
{
biggestId = 0;
}
else
{
biggestId = context.Vendors.Max(i => i.Id);
}
biggestId++;
vendor.Id = biggestId;
context.Vendors.Add(vendor);
context.SaveChanges();
}
report.VendorId = vendor.Id;
context.MonthlyExpenseReports.Add(report);
context.SaveChanges();
}
public static void WriteJSON(NewEntitiesModel context)
{
/*var productVendors = from product in context.Products.Include("Vendor")
join vendor in context.Vendors
on product.VendorId equals vendor.Id
select new
{
ProductId = product.Id,
ProductName = product.ProductName,
VendorName = vendor.VendorName
};
var productReports = from product in productVendors
join report in context.Reports
on product.ProductId equals report.ProductId
select new
{
ProductId = product.ProductId,
ProductName = product.ProductName,
VendorName = product.VendorName,
Quantity = report.Quantity,
Sum = report.Sum
} into anon
orderby anon.ProductId
select anon;
foreach (var product in productReports)
{
Console.WriteLine("{0}.{1} by {2} sold {3} for {4}", product.ProductId, product.ProductName, product.VendorName,
product.Quantity, product.Sum);
}*/
/*foreach (var product in context.Products.Include("Vendor").Include("Reports"))
{
int productId = product.Id;
string vendorName = product.Vendor.VendorName;
string productName = product.ProductName;
decimal quantity = 0;
decimal sum = 0;
foreach (var report in product.Reports)
{
quantity += report.Quantity.Value;
sum += report.Sum.Value;
}
Console.WriteLine(productId + " : " + vendorName + " : " + productName + " : " + quantity + " : " + sum);
}*/
}
public static void WriteJSON(NewEntitiesModel context)
{
var productItemsCollection = new MongoHelper<ProductItem>("Products");
productItemsCollection.MongoCollection.Drop();
foreach (var product in context.Products.Include("Vendor").Include("Reports"))
{
int productId = product.Id;
string vendorName = product.Vendor.VendorName;
string productName = product.ProductName;
decimal quantity = 0;
decimal sum = 0;
foreach (var report in product.Reports)
{
quantity += report.Quantity.Value;
sum += report.Sum.Value;
}
// Console.WriteLine(productId + " : " + vendorName + " : " + productName + " : " + quantity + " : " + sum);
var tempObject = new ProductItem()
{
ProductId = productId,
ProductName = productName,
VendorName = vendorName,
Quantity = quantity,
Sum = sum
};
productItemsCollection.InsertData(tempObject);
}
var productList = productItemsCollection.LoadData<ProductItem>().ToJson();
var removeId = Regex.Replace(productList, "\"_id\".*?\\,", "", RegexOptions.IgnoreCase);
var formatedList = Regex.Replace(removeId, ",", ",\n", RegexOptions.IgnoreCase);
var removedBracesRight = Regex.Replace(formatedList, "},", "\n},", RegexOptions.IgnoreCase);
var removedBracesLeft = Regex.Replace(removedBracesRight, "{", "{\n", RegexOptions.IgnoreCase);
File.Delete(@"../../../GeneratedReports/report.json");
File.WriteAllText(@"../../../GeneratedReports/report.json", removedBracesLeft);
Console.WriteLine("JSON report created.");
}
public static void GenerateExcelReport(int month)
{
var dbMSSQL = new NewEntitiesModel();
using (dbMSSQL)
{
var dbSQLite = new SQLiteConnection("Data Source=SQLiteSupermarketDB.sqlite;Version=3;");
dbSQLite.Open();
Dictionary<string, decimal> productTaxes = new Dictionary<string, decimal>();
SQLiteCommand select = new SQLiteCommand("select * from ProductTaxes", dbSQLite);
var reader = select.ExecuteReader();
while (reader.Read())
{
string name = (string)reader["Name"];
decimal tax = (decimal)reader["Tax"];
productTaxes[name] = tax/100;
}
dbSQLite.Close();
var salesByVendors =
from re in dbMSSQL.Reports
join p in dbMSSQL.Products on re.ProductId equals p.Id
join v in dbMSSQL.Vendors on p.VendorId equals v.Id
where re.Date.Value.Month == month
group new { v, re } by new { v.VendorName }
into grp
select new { vendor = grp.Key.VendorName, income = grp.Sum(re => re.re.Sum) };
//foreach (var item in salesByVendors)
//{
// Console.WriteLine(item.vendor + "-->" + item.income);
//}
var expensesByVendors =
from me in dbMSSQL.MonthlyExpenseReports
join ve in dbMSSQL.Vendors on me.VendorId equals ve.Id
where me.Date.Value.Month == month
group new { ve, me } by new { ve.VendorName }
into grp
select new { vendor = grp.Key.VendorName, expenses = grp.Sum(me => me.me.Expenses) };
//foreach (var item in expensesByVendors)
//{
// Console.WriteLine(item.vendor + "-->" + item.expenses);
//}
//var taxesByVendors =
// from re in dbMSSQL.Reports
// join taxes in productTaxes on re.Product.ProductName equals taxes.Key
// join ve in dbMSSQL.Vendors on re.ProductId equals ve.Id
// where re.Date.Value.Month == month
// group new {ve, re, taxes} by new {ve.VendorName}
// into grp
// select new {vendor = grp.Key.VendorName, totalTaxes = grp.Sum((tax,sales) => tax.taxes.Value * sales.re.Sum))};
}
}
public static void CreateSQLiteDB()
{
//SQLiteConnection.CreateFile("SQLiteSupermarketDB.sqlite");
var dbConnection = new SQLiteConnection("Data Source=SQLiteSupermarketDB.sqlite;Version=3;");
dbConnection.Open();
using (dbConnection)
{
//Creating main table
string sqlStatement = "create table VendorIncomes(" +
"Id integer not null," +
"Vendors nvarchar(50)," +
"Incomes numeric(10,5)," +
"Expenses numeric(10,5)," +
"Taxes numeric(10,5)," +
"primary key (Id)" +
");";
//string sqlStatement = "drop table VendorIncomes";
SQLiteCommand createIncomes = new SQLiteCommand(sqlStatement, dbConnection);
createIncomes.ExecuteNonQuery();
//SQLiteCommand insert = new SQLiteCommand("insert into VendorIncomes(Vendors) values('Kamenitza'),('Zagorka');", dbConnection);
//insert.ExecuteNonQuery();
//SQLiteCommand select = new SQLiteCommand("select * from VendorIncomes", dbConnection);
//var reader = select.ExecuteReader();
//while (reader.Read())
//{
// string fileNames = (string)reader["Vendors"];
// Console.WriteLine(fileNames);
//}
//Creating taxes table
string sqlStatementTaxes = "create table ProductTaxes(" +
"Id integer not null," +
"Name nvarchar(50)," +
"Tax numeric(10,5)," +
"primary key (Id)" +
");";
SQLiteCommand cmd = new SQLiteCommand(sqlStatementTaxes, dbConnection);
cmd.ExecuteNonQuery();
List<string> products;
using (var entityDb = new NewEntitiesModel())
{
products =
(from p in entityDb.Products
select p.ProductName).ToList();
}
StringBuilder sb = new StringBuilder();
sb.Append("insert into ProductTaxes(Name, Tax) values");
Random randGenerator = new Random();
foreach (var prod in products)
{
double tax = randGenerator.Next(10, 22);
sb.AppendFormat("('{0}','{1}'),", prod, tax);
}
sb.Length--;
sb.Append(";");
//Console.WriteLine(sb.ToString());
var insertIntoTaxSql = sb.ToString();
SQLiteCommand insertCmd = new SQLiteCommand(insertIntoTaxSql, dbConnection);
insertCmd.ExecuteNonQuery();
}
Console.WriteLine("SQLite db created and populated");
}
private static void AddTableFooter(PdfPTable table, DateTime date, Font fontStyle, NewEntitiesModel db)
{
BaseFont bfTimes = BaseFont.CreateFont(BaseFont.TIMES_ROMAN, BaseFont.CP1252, false);
Font boldFontStyle = new Font(bfTimes, 10, Font.BOLD, BaseColor.BLACK);
var footer = new PdfPCell(new Phrase("Total sum for " +
date.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture) +
": ", fontStyle));
footer.Colspan = 4;
footer.HorizontalAlignment = Element.ALIGN_RIGHT;
footer.VerticalAlignment = Element.ALIGN_MIDDLE;
table.AddCell(footer);
var dailySum = (from reports in db.Reports
where reports.Date == date
select reports.Sum).Sum();
var totalSum = new PdfPCell(
new Phrase((string.Format("{0:f2}", double.Parse(dailySum.ToString()))),
boldFontStyle));
totalSum.HorizontalAlignment = Element.ALIGN_RIGHT;
table.AddCell(totalSum);
}
public static void GenerateReport()
{
Document pdfDocument = new Document(PageSize.A4, 10, 10, 10, 10);
PdfWriter writer = PdfWriter.GetInstance(pdfDocument, new FileStream("../../../GeneratedReports/TestPdf.pdf", FileMode.Create));
pdfDocument.Open();
BaseFont bfTimes = BaseFont.CreateFont(BaseFont.TIMES_ROMAN, BaseFont.CP1252, false);
Font fontStyle = new Font(bfTimes, 10, Font.NORMAL, BaseColor.BLACK);
PdfPTable table = new PdfPTable(5);
table.SetWidths(new int[] { 50, 30, 30, 80, 20 });
var header = new PdfPCell(new Phrase("Aggregated Sales Report", fontStyle));
header.Colspan = 5;
header.HorizontalAlignment = Element.ALIGN_CENTER;
header.VerticalAlignment = Element.ALIGN_MIDDLE;
header.FixedHeight = 30f;
table.AddCell(header);
using (var db = new NewEntitiesModel())
{
var uniqueDates = (from dateReports in db.Reports
select dateReports.Date).Distinct().ToList();
foreach (var date in uniqueDates)
{
var dailyReport = (from report in db.Reports
join product in db.Products
on report.ProductId equals product.Id
join measures in db.Measures
on product.MeasureId equals measures.Id
join supermarket in db.Supermarkets
on report.SupermarketId equals supermarket.Id
where report.Date == date.Value
select new
{
ProductName = product.ProductName,
Quantity = report.Quantity,
MeasureName = measures.MeasureName,
UnitPrice = report.UnitPrice,
Location = supermarket.SupermarketName,
Sum = report.Sum
}).ToList();
AddTableHeader(table, DateTime.Parse(date.ToString()), fontStyle);
foreach (var reportRow in dailyReport)
{
table.AddCell(new Phrase(reportRow.ProductName, fontStyle));
table.AddCell(
new Phrase(
(string.Format("{0:f0}", double.Parse(reportRow.Quantity.ToString()) + " " + reportRow.MeasureName)),
fontStyle));
table.AddCell(
new Phrase((string.Format("{0:f2}", double.Parse(reportRow.UnitPrice.ToString()))),
fontStyle));
table.AddCell(new Phrase(reportRow.Location, fontStyle));
var c5 = new PdfPCell(new Phrase((string.Format("{0:f2}", double.Parse(reportRow.Sum.ToString()))), fontStyle));
c5.HorizontalAlignment = Element.ALIGN_RIGHT;
table.AddCell(c5);
}
AddTableFooter(table, DateTime.Parse(date.ToString()), fontStyle, db);
}
pdfDocument.Add(table);
pdfDocument.Close();
writer.Close();
}
}
public static void CreateXmlReport(NewEntitiesModel context)
{
var vendorProducts = from vendor in context.Vendors
join product in context.Products
on vendor.Id equals product.VendorId
select new
{
ProductId = product.Id,
VendorName = vendor.VendorName
};
var vendorReports = from vendor in vendorProducts
join report in context.Reports
on vendor.ProductId equals report.ProductId
select new
{
VendorName = vendor.VendorName,
date = report.Date,
sum = report.Sum
} into anon
orderby anon.VendorName, anon.date
select anon;
/*foreach (var report in vendorReports)
{
Console.WriteLine("{0} : {1} : {2}", report.VendorName, report.date, report.sum);
}*/
using (XmlTextWriter writer = new XmlTextWriter(xmlFilePath, encoding))
{
writer.Formatting = Formatting.Indented;
writer.IndentChar = '\t';
writer.Indentation = 1;
writer.WriteStartDocument();
writer.WriteStartElement("Sales");
string currentVendor = string.Empty;
DateTime currentDate = new DateTime(1950, 11, 11);
bool isStartedDocumentGlobal = false;
decimal currentSum = 0;
foreach (var report in vendorReports)
{
if (report.VendorName != currentVendor)
{
if (isStartedDocumentGlobal)
{
writer.WriteStartElement("summary");
string dateString = currentDate.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture);
writer.WriteAttributeString("date", dateString);
writer.WriteAttributeString("total-sum", string.Format("{0:0.00}", currentSum));
writer.WriteEndElement();
writer.WriteEndElement();
}
else
{
isStartedDocumentGlobal = true;
}
writer.WriteStartElement("sale");
writer.WriteAttributeString("vendor", report.VendorName);
currentVendor = report.VendorName;
currentSum = 0;
currentDate = report.date.Value;
}
if ((currentDate.Year != report.date.Value.Year) || (currentDate.Month != report.date.Value.Month)
|| (currentDate.Day != report.date.Value.Day))
{
writer.WriteStartElement("summary");
string dateString = currentDate.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture);
writer.WriteAttributeString("date", dateString);
writer.WriteAttributeString("total-sum", string.Format("{0:0.00}", currentSum));
writer.WriteEndElement();
currentSum = report.sum.Value;
currentDate = report.date.Value;
}
else
{
currentSum += report.sum.Value;
}
}
writer.WriteStartElement("summary");
string dataString = currentDate.ToString("dd-MMM-yyyy", CultureInfo.InvariantCulture);
writer.WriteAttributeString("date", dataString);
writer.WriteAttributeString("total-sum", string.Format("{0:0.00}", currentSum));
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
}
Console.WriteLine("XML report generated.");
}
public static void GenerateReportsFromExcel(string excelFilePath, NewEntitiesModel context, DateTime date)
{
string excelConnectionString = @"Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + excelFilePath + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""";
using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
{
excelConnection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM [Sales$]", excelConnection);
OleDbDataReader reader = command.ExecuteReader();
string[] serviceInfo = new string[5];
using (reader)
{
int counter = 0;
while (reader.Read())
{
if (counter == 5)
{
break;
}
for (int i = 0; i < reader.FieldCount; i++)
{
if (!(reader[i] is DBNull))
{
serviceInfo[counter] = reader[i].ToString();
counter++;
}
}
}
int nestedCounter = 0;
string[] currentArguments = new string[4];
do
{
nestedCounter = 0;
bool isEnd = false;
bool isChecked = false;
for (int i = 0; i < reader.FieldCount; i++)
{
if (!(reader[i] is DBNull))
{
if (!isChecked)
{
isChecked = true;
if (reader[i].ToString().StartsWith("Total sum"))
{
isEnd = true;
break;
}
}
currentArguments[nestedCounter] = reader[i].ToString();
nestedCounter++;
if (nestedCounter == 4)
{
WriteToDatabase(serviceInfo[0], currentArguments, context, date);
}
}
}
if (isEnd)
{
break;
}
}
while (reader.Read());
reader.Close();
}
}
}
public static void ParseZip(string zipPath, NewEntitiesModel context)
{
ZipHelper.ExtractEntriesFromZip(zipPath, zipDirectory);
ParseExcelFiles(zipDirectory, context);
}
private static void WriteToDatabase(string supermarketName, string[] currentArguments, NewEntitiesModel context, DateTime date)
{
Report generatedReport = new Report();
generatedReport.ProductId = int.Parse(currentArguments[0]);
generatedReport.Quantity = decimal.Parse(currentArguments[1]);
generatedReport.UnitPrice = decimal.Parse(currentArguments[2]);
generatedReport.Sum = decimal.Parse(currentArguments[3]);
generatedReport.Date = date;
int reportId = -1;
if (context.Reports.Count() == 0)
{
reportId = 0;
}
else
{
reportId = context.Reports.Max(r => r.Id);
}
reportId++;
generatedReport.Id = reportId;
var supermarket = context.Supermarkets.Where(
s => s.SupermarketName.Equals(supermarketName))
.FirstOrDefault();
if (supermarket == null)
{
supermarket = new Supermarket();
supermarket.SupermarketName = supermarketName;
int biggestId = -1;
if (context.Supermarkets.Count() == 0)
{
biggestId = 0;
}
else
{
biggestId = context.Supermarkets.Max(i => i.Id);
}
biggestId++;
supermarket.Id = biggestId;
context.Supermarkets.Add(supermarket);
context.SaveChanges();
}
generatedReport.SupermarketId = supermarket.Id;
context.Reports.Add(generatedReport);
context.SaveChanges();
}
