public static void LoadVendorExpenses()
{
XmlDocument doc = new XmlDocument();
doc.Load("../../../Vendors-Expenses.xml");
XmlNode rootNode = doc.DocumentElement;
List<Expens> expenses = new List<Expens>();
using (var db = new SupermarketEntities())
{
string vendorName;
var mongoClient = new MongoClient("mongodb://localhost/");
var mongoServer = mongoClient.GetServer();
var productReports = mongoServer.GetDatabase("Product-Reports");
var reports = productReports.GetCollection("expenses");
foreach (XmlNode item in rootNode.ChildNodes)
{
XmlAttribute atr = item.Attributes[0];
vendorName = atr.Value;
int id = db.Vendors.Where(x => x.VendorName == vendorName).Select(x => x.ID).FirstOrDefault();
foreach (XmlNode node in item.ChildNodes)
{
Expens expense = new Expens();
string atrDate = node.Attributes[0].Value.ToString();
string dateFormat = "MMM-yyyy";
DateTime date = DateTime.ParseExact(atrDate, dateFormat, CultureInfo.InvariantCulture);
decimal expenseValue = decimal.Parse(node.InnerText);
expense.Date = date;
expense.Expenses = expenseValue;
expense.VendorId = id;
expenses.Add(expense);
ExpenseByName expenseByName = new ExpenseByName();
expenseByName.VendorName = vendorName;
expenseByName.Date = expense.Date;
expenseByName.Expenses = expense.Expenses;
reports.Insert(expenseByName);
}
}
foreach (Expens item in expenses)
{
db.Expenses.Add(item);
}
db.SaveChanges();
WriteToSqlLite(reports);
}
}
static void Main()
{
using (var db = new EntitiesModel())
{
var vendors = db.Vendors;
var products = db.Products;
var measures = db.Measures;
using (var dbSql = new SupermarketEntities())
{
//foreach (var vendor in vendors)
//{
// Supermarket_EF.Data.Vendor vendorObj = new Supermarket_EF.Data.Vendor();
// vendorObj.ID = vendor.IdvendorsID;
// vendorObj.VendorName = vendor.VendorName;
// dbSql.Vendors.Add(vendorObj);
//}
//foreach (var measure in measures)
//{
// Supermarket_EF.Data.Measure measureObj = new Supermarket_EF.Data.Measure()
// {
// ID = measure.ID,
// MeasureName = measure.MeasureName
// };
// dbSql.Measures.Add(measureObj);
//}
//foreach (var product in products)
//{
// Supermarket_EF.Data.Product productObj = new Supermarket_EF.Data.Product()
// {
// ID = product.ID,
// VendorID = product.VendorID,
// ProductName = product.ProductName,
// MeasureID = product.MeasureID,
// BasePrice = (decimal)product.BasePrice
// };
// dbSql.Products.Add(productObj);
//}
//dbSql.SaveChanges();
//MyExtract();
//string dirPath = "../../../Extracted Files";
//var dir = Directory.GetDirectories(dirPath);
//Traverse(dir);
//PdfReportCreator.CreatePDFs();
//XmlReportCreator.CreateReport();
//ExportReportInMongoDB.CreateReport();
//VendorExpenses.LoadVendorExpenses();
VendorsReports.CreateExcel();
}
}
}
public static void CreateReport()
{
using (var dbSql = new SupermarketEntities())
{
var reportsQuery = from sale in dbSql.Sales.Include("Products").Include("Vendors")
orderby sale.ProductID
group sale by new { sale.ProductID, sale.Product.ProductName, sale.Product.Vendor.VendorName }
into g
let totalSold = g.Sum(x => x.Quanity)
let totalIncome = g.Sum(y => y.Sum)
select new TotalReport
{
product_id = g.Key.ProductID,
product_name = g.Key.ProductName,
vendor_name = g.Key.VendorName,
total_quantity_sold = totalSold,
total_incomes = totalIncome
};
string pathString = "../../../Product-Reports";
System.IO.Directory.CreateDirectory(pathString);
var mongoClient = new MongoClient("mongodb://localhost/");
var mongoServer = mongoClient.GetServer();
var productReports = mongoServer.GetDatabase("Product-Reports");
var reports = productReports.GetCollection("reports");
foreach (var report in reportsQuery)
{
reports.Insert<TotalReport>(report);
string jsonReports = report.ToJson();
jsonReports = jsonReports.Replace('_', '-');
jsonReports = jsonReports.Replace(",", ",\n");
File.WriteAllText(pathString + "/" + report.product_id + ".json",
jsonReports);
}
WriteToSqlLite(reports);
}
}
private static void ReadWriteExcell(string file)
{
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file +
@";Extended Properties=""Excel 12.0 Xml;HDR=Yes;""";
OleDbConnection dbCon = new OleDbConnection(connectionString);
dbCon.Open();
using (dbCon)
{
OleDbCommand readTable = new OleDbCommand("SELECT * FROM [Sales$]", dbCon);
OleDbDataReader reader = readTable.ExecuteReader();
using (var db = new SupermarketEntities())
{
using (reader)
{
Sale saleObj = new Sale();
reader.Read();
string locationName = reader[0].ToString();
string dateFormat = "dd-MMM-yyyy";
string currDate = file.Substring(file.Length - 15, 11);
DateTime date = DateTime.ParseExact(currDate, dateFormat, CultureInfo.InvariantCulture);
reader.Read();
while (reader.Read())
{
if (reader[0].ToString().CompareTo("…") != 0)
{
int? locId = db.Locations
.Where(x => x.LocationName == locationName)
.Select(x => x.ID)
.FirstOrDefault();
Location location = new Location();
if (locId == 0)
{
location.LocationName = locationName;
saleObj.Location = location;
}
else
{
saleObj.LocationID = (int)locId;
}
saleObj.ProductID = int.Parse(reader[0].ToString());
saleObj.Quanity = int.Parse(reader[1].ToString());
saleObj.UnitPrice = decimal.Parse(reader[2].ToString());
saleObj.Sum = decimal.Parse(reader[3].ToString());
saleObj.Date = date;
db.Sales.Add(saleObj);
db.SaveChanges();
}
else
{
reader.Read();
reader.Read();
}
}
}
}
}
}
public static void CreatePDFs()
{
Console.WriteLine("PDF Report creation started...");
StringBuilder sb = new StringBuilder();
using (var dbEF = new SupermarketEntities())
{
sb.Append("<table cellpadding='5' border='1'>");
sb.Append("<tr><th align='center'><b>Aggregated Sales Report</b></th></tr>");
sb.Append("</table>");
sb.Append("<table cellpadding='5' border='1'>");
decimal grandTotal = 0M;
var db = dbEF.Sales.Select(x => x.Date).OrderBy(d => d.Value).Distinct();
foreach (var dateTime in db)
{
var date = DateTime.Parse(dateTime.ToString()).ToShortDateString();
//Console.WriteLine(dateTime);
sb.AppendFormat("<tr bgcolor='silver'><td colspan='5'>Date: {0}</td></tr>", date);
sb.Append("<tr bgcolor='silver'>");
sb.Append("<td><b>Product</b></td>");
sb.Append("<td><b>Quantity</b></td>");
sb.Append("<td><b>Unit Price</b></td>");
sb.Append("<td><b>Location</b></td>");
sb.Append("<td><b>Sum</b></td>");
sb.Append("</tr>");
var d = dbEF.Sales.Where(x => x.Date == dateTime);
decimal sum = 0M;
foreach (var item in d)
{
sb.Append("<tr>");
sb.AppendFormat("<td>{0}</td>", item.Product.ProductName);
sb.AppendFormat("<td>{0}</td>", item.Quanity);
sb.AppendFormat("<td>{0:F2}</td>", item.UnitPrice);
sb.AppendFormat("<td>{0}</td>", item.Location.LocationName);
sb.AppendFormat("<td>{0:F2}</td>", item.Sum);
sb.Append("</tr>");
sum += item.Sum;
}
grandTotal += sum;
sb.Append("<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>");
sb.AppendFormat("<tr><td colspan='4' align='right'>Total sum for {0}:</td><td><b>{1:F2}</b></td></tr>", date, sum);
}
sb.AppendFormat("<tr><td colspan='4' align='right'>Grand Total:</td><td><b>{0:F2}</b></td></tr>", grandTotal);
sb.Append("</table>");
Console.WriteLine("PDF Report generated.");
}
PDFBuilder.HtmlToPdfBuilder builder = new PDFBuilder.HtmlToPdfBuilder(PageSize.LETTER);
PDFBuilder.HtmlPdfPage page = builder.AddPage();
page.AppendHtml(sb.ToString());
byte[] file = builder.RenderPdf();
string tempFolder = "../../../PdfResult\\";
string tempFileName = DateTime.Now.ToString("yyyy-MM-dd") + "-" + Guid.NewGuid() + ".pdf";
if (Helpers.Helper.DirectoryExist(tempFolder))
{
if (!File.Exists(tempFolder + tempFileName))
File.WriteAllBytes(tempFolder + tempFileName, file);
}
}