static int GetSupermarketId(string name)
{
using (var db = new SupermarketDB())
{
return(db.Supermarkets.First(x => x.StoreName == name).Id);
}
}
private static void UploadNewProducts(
SupermarketDB sqlDbContext, List <int> uploadedVendorsIds,
List <int> uploadedMeasuresIds, List <Product> products)
{
foreach (var product in products)
{
if (uploadedVendorsIds.Contains(product.Vendor.ID))
{
sqlDbContext.Vendors.Attach(product.Vendor);
}
else
{
var newVendor = new Vendor {
VendorName = product.Vendor.VendorName, ID = product.Vendor.ID
};
sqlDbContext.Vendors.Add(newVendor);
product.Vendor = newVendor;
}
if (uploadedMeasuresIds.Contains(product.Measure.ID))
{
sqlDbContext.Measures.Attach(product.Measure);
}
else
{
var newMeasure = new Measure {
MeasureName = product.Measure.MeasureName, ID = product.Measure.ID
};
sqlDbContext.Measures.Add(newMeasure);
product.Measure = newMeasure;
}
sqlDbContext.Products.Add(product);
}
}
private static void TestUploaded()
{
using (var dbContextSuper = new SupermarketDB())
{
var products = dbContextSuper.Products.Include("Measure").Include("Vendor");
foreach (var product in products)
{
Console.WriteLine(product.Vendor.VendorName + " " + product.Vendors_ID);
}
}
}
static void Main(string[] args)
{
PDFGenerator pdf = new PDFGenerator();
XMLGenerator xml = new XMLGenerator();
SupermarketDB dbContext = new SupermarketDB();
using (dbContext)
{
pdf.GeneratePDF(dbContext);
xml.GenerateXML(dbContext);
}
}
private static List <TotalProductSales> GetTotalProductSales(SupermarketDB supermarketDB)
{
var productReports = supermarketDB.Database.SqlQuery <TotalProductSales>(@"
SELECT SUM(s.UnitPrice * s.Quantity) as TotalIncomes,
SUM(s.Quantity) as [QuantitySold],
s.ProductId ,
MAX(p.ProductName) as ProductName,
MAX(v.VendorName) as VendorName
FROM [Supermarket].[dbo].[SaleByDates] s
JOIN Products p ON s.ProductId = p.ID
JOIN Vendors v ON p.Vendor_ID = v.ID
GROUP BY s.ProductId
ORDER BY s.ProductId").ToList();
return(productReports);
}
private static void TransferRemainingVendors(SupermarketDbMySQL mySqlDbContext)
{
using (var sqlDbContext = new SupermarketDB())
{
var uploadedVendorsIds = sqlDbContext.Vendors.Select(v => v.ID).ToList();
var vendors = mySqlDbContext.Vendors.Where(v => !uploadedVendorsIds.Contains(v.ID)).ToList();
foreach (var vendor in vendors)
{
sqlDbContext.Vendors.Add(vendor);
}
sqlDbContext.SaveChanges();
}
}
private static void TransferRemainingMeasures(SupermarketDbMySQL mySqlDbContext)
{
using (var sqlDbContext = new SupermarketDB())
{
var uploadedMeasuresIds = sqlDbContext.Measures.Select(m => m.ID).ToList();
var measures = mySqlDbContext.Measures.Where(m => !uploadedMeasuresIds.Contains(m.ID)).ToList();
foreach (var measure in measures)
{
sqlDbContext.Measures.Add(measure);
}
sqlDbContext.SaveChanges();
}
}
static void AddSupermarket(string name)
{
using (var db = new SupermarketDB())
{
var count = db.Supermarkets.Where(x => x.StoreName == name).Count();
if (count == 0)
{
var supermarket = new Supermarket.Models.Supermarket {
StoreName = name
};
db.Supermarkets.Add(supermarket);
db.SaveChanges();
}
}
}
static void Main(string[] args)
{
using (var supermarketDB = new SupermarketDB())
{
Console.WriteLine("Getting data from SQL server....");
var productReports = GetTotalProductSales(supermarketDB);
var client = new MongoClient(Local);
Console.WriteLine("Saving product reports to MongoDb (Local)....");
AllSalesReportsSqlToMongo(supermarketDB, productReports, client);
//TestSaved(client);
Console.WriteLine("Generating jsonFiles - output to {0}", FilesPath);
GenerateJsonReportFilesFiles(client);
}
}
private static void AllSalesReportsSqlToMongo(
SupermarketDB supermarketDB, List <TotalProductSales> productReports, MongoClient client)
{
var db = client.GetServer().GetDatabase(Database);
if (!db.CollectionExists(CollectionReportsTest))
{
db.CreateCollection(CollectionReportsTest);
}
db.DropCollection(CollectionReportsTest);
db.CreateCollection(CollectionReportsTest);
RegisterCustomSerializer();
var collection = db.GetCollection <TotalProductSales>(CollectionReportsTest);
collection.InsertBatch(productReports);
}
private static void ProductsTransfer(SupermarketDbMySQL mySqlDbContext)
{
using (var sqlDbContext = new SupermarketDB())
{
var uploadedProductsIds = sqlDbContext.Products.Select(p => p.ID).ToList();
var uploadedVendorsIds = sqlDbContext.Vendors.Select(v => v.ID).ToList();
var uploadedMeasuresIds = sqlDbContext.Measures.Select(m => m.ID).ToList();
var newProducts = mySqlDbContext.Products
.Include(x => x.Vendor)
.Include(x => x.Measure)
.Where(p => !uploadedProductsIds.Contains(p.ID)).ToList();
UploadNewProducts(sqlDbContext, uploadedVendorsIds, uploadedMeasuresIds, newProducts);
sqlDbContext.SaveChanges();
}
}
static List <VendorIncomesForCurrentMonth> GetVendorIncomesFromSQLServer()
{
using (var supermarketDb = new SupermarketDB())
{
var currentMonthReport = supermarketDb.Database
.SqlQuery <VendorIncomesForCurrentMonth>(@"
SELECT MAX(v.VendorName) as Vendor,
SUM(s.UnitPrice * s.Quantity) as Incomes,
max(p.ProductName) as Product,
max(v.ID) as VendorId
FROM [Supermarket].[dbo].[SaleByDates] s
JOIN Products p ON s.ProductId = p.ID
JOIN Vendors v ON p.Vendor_ID = v.ID
WHERE YEAR(s.[Date]) = YEAR(GETDATE()) AND MONTH(s.[Date]) = Month(GETDATE())
GROUP BY p.ProductName").ToList();
return(currentMonthReport);
}
}
static void Main()
{
MongoClient mongoClient = new MongoClient("mongodb://localhost/");
MongoServer mongoServer = mongoClient.GetServer();
MongoDatabase supermarket = mongoServer.GetDatabase("teamwork-sidecar");
MongoCollection expences = supermarket.GetCollection("expences");
using (var db = new SupermarketDB())
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("../../../../fileVendors-Expenses.xml");
string salePath = "/sales/sale";
XmlNodeList sales = xmlDoc.SelectNodes(salePath);
int count = 1;
foreach (XmlElement item in sales)
{
string name = item.GetAttribute("vendor");
Vendor vendor = db.Vendors.First(x => x.VendorName == name);
if (vendor != null)
{
var expencesss = item.SelectNodes("expenses");
foreach (XmlElement expence in expencesss)
{
string month = expence.GetAttribute("month");
decimal amount = decimal.Parse(expence.InnerText);
var vendorExpence = new VendorExpence();
vendorExpence.VendorId = vendor.ID;
vendorExpence.Month = month;
vendorExpence.Ammount = amount;
db.VendorExpence.Add(vendorExpence);
// used for MongoDB
vendorExpence.Id = count++;
AddExpenceToMongoDB(expences, vendorExpence);
}
}
}
db.SaveChanges();
}
}
public void GenerateXML(SupermarketDB sqlDbContext)
{
Console.WriteLine("Generating XML....");
string fileName = "../../../sales.xml";
Encoding encoding = Encoding.GetEncoding("utf-8");
using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding))
{
writer.Formatting = Formatting.Indented;
writer.IndentChar = '\t';
writer.Indentation = 1;
writer.WriteStartDocument();
writer.WriteStartElement("sales");
var query = sqlDbContext.Database.SqlQuery <VendorReportModel>
(@" select v.VendorName as Vendor, sd.Date as Date, sd.Sum as Sum
from SaleByDates sd
join Products p on sd.ProductId = p.ID
join Vendors v on v.ID = p.Vendor_ID
group by v.VendorName, sd.Date, sd.Sum").ToList();
DateTime currentDate = DateTime.Now; //query[0].Date;
string currentVendor = string.Empty;
decimal currentSum = 0;
bool isOpened = false;
foreach (var item in query)
{
if (item.Vendor != currentVendor)
{
if (isOpened)
{
writer.WriteEndElement();
}
writer.WriteStartElement("sale");
isOpened = true;
writer.WriteAttributeString("vendor", item.Vendor);
currentVendor = item.Vendor;
}
currentSum += item.Sum;
if (item.Date != currentDate)
{
currentDate = item.Date;
WriteSale(writer, item.Date, currentSum);
currentSum = 0;
}
}
if (isOpened)
{
writer.WriteEndElement();
}
writer.WriteEndDocument();
}
}
static void ExtractFromExcel()
{
DirectoryInfo root = new DirectoryInfo("../../../../ZippedDailyReports");
if (root.GetDirectories().Length == 0)
{
string zipPath = "../../../../ZippedDailyReports/Daily-Reports.zip";
string extractPath = "../../../../ZippedDailyReports/Daily-Reports";
ZipFile.ExtractToDirectory(zipPath, extractPath);
}
DirectoryInfo dir = new DirectoryInfo("../../../../ZippedDailyReports/Daily-Reports");
var directories = dir.GetDirectories();
foreach (var directory in directories)
{
var date = DateTime.Parse(directory.Name);
var files = directory.GetFiles();
foreach (var file in files)
{
string fileName = file.FullName;
DataSet sheet = new DataSet();
OleDbConnectionStringBuilder conString = new OleDbConnectionStringBuilder();
conString.Provider = "Microsoft.ACE.OLEDB.12.0";
conString.DataSource = fileName;
conString.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
using (var dbCon = new OleDbConnection(conString.ConnectionString))
{
dbCon.Open();
string command = "SELECT * FROM [Sales$]";
using (var adapter = new OleDbDataAdapter(command, dbCon))
{
adapter.Fill(sheet);
}
}
DataTable table = sheet.Tables[0];
string supermarketName = GetSuperMarketName(table);
AddSupermarket(supermarketName);
int superMarketId = GetSupermarketId(supermarketName);
decimal sum = GetSum(table);
List <decimal[]> reportData = GetReportData(table);
foreach (var report in reportData)
{
var productId = (int)report[0];
var quantity = (int)report[1];
var unitPrice = report[2];
var reportSum = report[3];
var salesReport = new SaleByDate
{
Date = date,
Sum = reportSum,
SupermarketId = superMarketId,
ProductId = productId,
Quantity = quantity,
UnitPrice = unitPrice
};
using (var db = new SupermarketDB())
{
db.SalesByDate.Add(salesReport);
db.SaveChanges();
}
}
}
}
}
public void GeneratePDF(SupermarketDB sqlDbContext)
{
Console.WriteLine(GeneratingMsg);
string path = PdfPath;
Document doc = new Document();
FileStream fs = File.Create(path);
PdfWriter.GetInstance(doc, fs);
doc.Open();
PdfPTable table = new PdfPTable(5);
table.WidthPercentage = 100;
var colWidthPercentages = new[] { 70f, 20f, 20f, 70f, 20f };
table.SetWidths(colWidthPercentages);
var cell = new PdfPCell(new Phrase(TableHeader));
cell.Colspan = 5;
cell.Padding = 20;
cell.HorizontalAlignment = 1;
table.AddCell(cell);
DateTime currentDate = DateTime.Now;
decimal sum = -1;
decimal grandTotal = 0;
var query = sqlDbContext.Database
.SqlQuery <AggregatedReportModel>(@"select p.ProductName as Product,
sd.Quantity as Quantity,
sd.UnitPrice as UnitPrice,
s.StoreName as Location,
m.MeasureName as MeasureName,
sd.Sum as Sum,
sd.Date as Date
from SaleByDates sd
join Products p on sd.ProductId = p.ID
join Measures m on m.ID = p.Measure_ID
join Supermarkets s on s.Id = sd.SupermarketId
group by sd.Date, p.ProductName, sd.Quantity, m.MeasureName,
sd.UnitPrice, s.StoreName, sd.Sum")
.ToList();
foreach (var item in query)
{
if (item.Date != currentDate)
{
currentDate = item.Date;
if (sum != -1)
{
var totalSumTextCell = new PdfPCell(new Phrase("Total sum for " + item.Date.ToString("d-MMM-yyyy", CultureInfo.CreateSpecificCulture("en-US")) + ":"));
totalSumTextCell.HorizontalAlignment = 2;
totalSumTextCell.Colspan = 4;
table.AddCell(totalSumTextCell);
var totalSumCell = new PdfPCell(new Phrase(sum.ToString()));
table.AddCell(totalSumCell);
sum = -1;
}
var newDateCell = new PdfPCell(new Phrase("Date: " + item.Date.ToString("d-MMM-yyyy", CultureInfo.CreateSpecificCulture("en-US"))));
newDateCell.BackgroundColor = BaseColor.LIGHT_GRAY;
newDateCell.Colspan = 5;
table.AddCell(newDateCell);
var product = new PdfPCell(new Phrase("Product"));
product.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(product);
var quantity = new PdfPCell(new Phrase("Quantity"));
quantity.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(quantity);
var unitPrice = new PdfPCell(new Phrase("Unit Price"));
unitPrice.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(unitPrice);
var location = new PdfPCell(new Phrase("Location"));
location.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(location);
var sumHeaderCell = new PdfPCell(new Phrase("Sum"));
sumHeaderCell.BackgroundColor = BaseColor.LIGHT_GRAY;
table.AddCell(sumHeaderCell);
}
table.AddCell(item.Product);
table.AddCell(item.Quantity + " " + item.Measurename);
table.AddCell(item.UnitPrice.ToString());
table.AddCell(item.Location);
table.AddCell(item.Sum.ToString());
sum += item.Sum;
grandTotal += item.Sum;
}
var totalSumTextCellEnd = new PdfPCell(new Phrase("Total sum for " + currentDate.ToString("d-MMM-yyyy", CultureInfo.CreateSpecificCulture("en-US")) + ":"));
totalSumTextCellEnd.HorizontalAlignment = 2;
totalSumTextCellEnd.Colspan = 4;
table.AddCell(totalSumTextCellEnd);
var totalSumCellEnd = new PdfPCell(new Phrase(sum.ToString()));
table.AddCell(totalSumCellEnd);
var grandTotalSumTextCellEnd = new PdfPCell(new Phrase("Grand Total:"));
grandTotalSumTextCellEnd.HorizontalAlignment = 2;
grandTotalSumTextCellEnd.Colspan = 4;
table.AddCell(grandTotalSumTextCellEnd);
var grandTotalCell = new PdfPCell(new Phrase(grandTotal.ToString()));
table.AddCell(grandTotalCell);
doc.Add(table);
doc.Close();
}