private static void TransferFromMongoDbToSqLite()
{
string mongoDbConnectionString = Settings.Default.MongoDbConnectionString;
MongoDatabase mongoDb = Connector.GetDb(mongoDbConnectionString, "Supermarkets");
var productVendorSalesRecords = mongoDb.GetCollection("ProductVendorSales").FindAll();
using (SupermarketsSqLiteEntities sqlLiteContext = new SupermarketsSqLiteEntities())
{
foreach (var productVendorSalesRecord in productVendorSalesRecords)
{
ProductSalesReport newProductSalesReport = new ProductSalesReport()
{
ProductId = long.Parse(productVendorSalesRecord.GetValue("product-id").ToString()),
ProductName = productVendorSalesRecord.GetValue("product-name").ToString(),
VendorName = productVendorSalesRecord.GetValue("vendor-name").ToString(),
TotalQuantitySold = long.Parse(productVendorSalesRecord.GetValue("total-quantity-sold").ToString()),
TotalIncomes = decimal.Parse(productVendorSalesRecord.GetValue("total-incomes").ToString()),
};
sqlLiteContext.ProductSalesReports.Add(newProductSalesReport);
Console.WriteLine(productVendorSalesRecord.GetValue("product-id"));
}
;
sqlLiteContext.SaveChanges();
}
}
public static void VendorsTotalReport(DateTime forDate)
{
//TransferFromMongoDbToSqLite();
using (SupermarketsSqLiteEntities sqlLiteContext = new SupermarketsSqLiteEntities())
{
var vendorProductSalesAndTaxes =
from ps in sqlLiteContext.ProductSalesReports
join pt in sqlLiteContext.ProductTaxes
on ps.ProductName equals pt.ProductName
select new
{
vendorName = ps.VendorName,
productTotalIncomes = ps.TotalIncomes,
productTotalTaxes = ps.TotalIncomes * pt.Tax
};
var vendorSalesAndTaxes =
(from vst in vendorProductSalesAndTaxes
group vst by vst.vendorName into vendorGroup
select new
{
vendorName = vendorGroup.Key,
vendorTotalIncomes = vendorGroup.Sum(x => x.productTotalIncomes),
vendorTotalTaxes = vendorGroup.Sum(x => x.productTotalTaxes)
}).ToArray();
using (SupermarketsEntities1 mssqlContext = new SupermarketsEntities1())
{
var vendorExpences =
(from vs in mssqlContext.VendorSales
join v in mssqlContext.Vendors
on vs.VendorId equals v.VendorId
where ((vs.Date.Year == forDate.Year) && (vs.Date.Month == forDate.Month))
select new
{
vendorExpences = vs.Expenses,
vendorName = v.VendorName.TrimEnd()
}).ToArray();
var vendorFinancialResult =
from ve in vendorExpences
join vst in vendorSalesAndTaxes
on ve.vendorName equals vst.vendorName
select new
{
vendorName = vst.vendorName,
vendorTotalIncomes = vst.vendorTotalIncomes,
vendorTotalTaxes = vst.vendorTotalTaxes,
vendorExpences = ve.vendorExpences,
vendorFinancialResult = vst.vendorTotalIncomes - ve.vendorExpences - vst.vendorTotalTaxes
};
//System.IO.DirectoryInfo zipReportsExportDirectoryInfo = new DirectoryInfo(zipReportsExportPath);
string excelFilePath = Settings.Default.ExcelExportTotalProductReportPath;
System.IO.FileInfo excelFile = new FileInfo(excelFilePath);
excelFile.Delete();
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 12.0 XML;HDR=YES'";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
using (excelConnection)
{
excelConnection.Open();
OleDbCommand createTable = new OleDbCommand(
"CREATE TABLE [Sheet1] ([Vendor] varchar(255), [Incomes] number, [Expenses] number, [Taxes] number, [Financial Result] number)"
, excelConnection);
createTable.ExecuteNonQuery();
OleDbCommand setExcelSheetData = new OleDbCommand(
"INSERT INTO [Sheet1$] ([Vendor], [Incomes], [Expenses], [Taxes], [Financial Result]) " +
"VALUES (@vendorName, @invomes, @expenses, @taxes, @financialResult)", excelConnection);
foreach (var vendorFinancialRecord in vendorFinancialResult)
{
setExcelSheetData.Parameters.AddWithValue("@vendorName", vendorFinancialRecord.vendorName);
setExcelSheetData.Parameters.AddWithValue("@invomes", vendorFinancialRecord.vendorTotalIncomes);
setExcelSheetData.Parameters.AddWithValue("@expenses", vendorFinancialRecord.vendorExpences);
setExcelSheetData.Parameters.AddWithValue("@taxes", vendorFinancialRecord.vendorTotalTaxes);
setExcelSheetData.Parameters.AddWithValue("@financialResult", vendorFinancialRecord.vendorFinancialResult);
setExcelSheetData.ExecuteNonQuery();
}
}
}
}
}