public AnonymousMapping()
{
_rd = new Mock<IDataReader>();
var dt = new { Id = 23, Name = "" };
_tp = dt.GetType();
_rd.Setup(d => d["Id"]).Returns(11);
_rd.Setup(d => d["Name"]).Returns("bla");
_rd.Setup(d => d["Extra"]).Returns("bla");
_rd.Setup(d => d.FieldCount).Returns(2);
}
private static void ExportDataToExcel(SQL.DataSet dataSet)
{
var excelApplication = new Excel.Application();
String excelReportPath = @"D:\SharedFolder\Education\SoftUni\03-AdvancedLevel-BackEnd\02-Database-Applications\Teamwork-Project\GitHub\Project\Marek\DatabaseProject\ExcelReport\Report.xlsx";
String excelReportPathBackup = String.Format(@"D:\SharedFolder\Education\SoftUni\03-AdvancedLevel-BackEnd\02-Database-Applications\Teamwork-Project\GitHub\Project\Marek\DatabaseProject\ExcelReport\Report-{0}-{1}-{2}-{3}-{4}-{5}.xlsx",
DateTime.Now.Year,
DateTime.Now.Month,
DateTime.Now.Day,
DateTime.Now.Hour,
DateTime.Now.Minute,
DateTime.Now.Second);
if (System.IO.File.Exists(excelReportPath))
{
System.IO.File.Move(excelReportPath, excelReportPathBackup);
}
var excelWorkBook = excelApplication.Workbooks.Add(Missing.Value);
const int VendorsIndex = 1;
const int IncomesIndex = 2;
const int ExpensesIndex = 3;
const int TotalTaxesIndex = 4;
const int FinancialResultIndex = 5;
foreach (SQL.DataTable table in dataSet.Tables)
{
Excel.Worksheet excelWorksheet = excelWorkBook.Sheets.Add();
excelWorksheet.Name = table.TableName;
excelWorksheet.Cells[1, VendorsIndex] = table.Columns[VendorsIndex - 1].ColumnName;
excelWorksheet.Cells[1, IncomesIndex] = table.Columns[IncomesIndex - 1].ColumnName;
excelWorksheet.Cells[1, ExpensesIndex] = table.Columns[ExpensesIndex - 1].ColumnName;
excelWorksheet.Cells[1, TotalTaxesIndex] = table.Columns[TotalTaxesIndex - 1].ColumnName;
excelWorksheet.Cells[1, FinancialResultIndex] = table.Columns[FinancialResultIndex - 1].ColumnName;
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
excelWorksheet.Cells[i + 2, j + 1] = table.Rows[i].ItemArray[j].ToString();
}
}
}
excelWorkBook.SaveAs(excelReportPath);
excelWorkBook.Save();
excelWorkBook.Close();
excelApplication.Quit();
if (System.IO.File.Exists(excelReportPath))
{
RunExcelReport(excelReportPath);
}
}
private string[] WriteOutFirebirdEmbeddedLibraries()
{
var result = new List<string>();
lock (this)
{
var blobs = new[]
{
new EmbeddedLibraryResource("aliases.conf", FirebirdEmbeddedLibrariesForWindows.aliases),
new EmbeddedLibraryResource("fbembed.dll", FirebirdEmbeddedLibrariesForWindows.fbembed),
new EmbeddedLibraryResource("firebird.conf", FirebirdEmbeddedLibrariesForWindows.firebird_conf),
new EmbeddedLibraryResource("firebird.msg", FirebirdEmbeddedLibrariesForWindows.firebird_msg),
new EmbeddedLibraryResource("ib_util.dll", FirebirdEmbeddedLibrariesForWindows.ib_util),
new EmbeddedLibraryResource("icudt30.dll", FirebirdEmbeddedLibrariesForWindows.icudt30),
new EmbeddedLibraryResource("icuin30.dll", FirebirdEmbeddedLibrariesForWindows.icuin30),
new EmbeddedLibraryResource("icuuc30.dll", FirebirdEmbeddedLibrariesForWindows.icuuc30),
new EmbeddedLibraryResource("msvcp80.dll", FirebirdEmbeddedLibrariesForWindows.msvcp80),
new EmbeddedLibraryResource("msvcr80.dll", FirebirdEmbeddedLibrariesForWindows.msvcp80)
};
foreach (var blob in blobs)
{
var path = Path.Combine(Environment.CurrentDirectory, blob.Name);
if (!File.Exists(path))
{
System.Console.WriteLine("-- " + path);
File.WriteAllBytes(path, blob.Data);
result.Add(path);
}
}
}
return result.ToArray();
}
// GET: Households/GetCharts
public ActionResult GetCharts()
{
var id = User.Identity.GetHouseholdId();
var hhid = Convert.ToInt32(id);
var hh = db.Households.Find(hhid);
var catBarList = (from cat in hh.Categories
where cat.Type == "Expense" && cat.IsDeleted == false
let sumBud = (from bud in cat.BudgetItems
select bud.Amount
).DefaultIfEmpty().Sum()
let sumAct = (from tran in cat.Transactions
where tran.PostedDate.Year == DateTime.Now.Year &&
tran.PostedDate.Month == DateTime.Now.Month
select tran.Amount).DefaultIfEmpty().Sum()
select new
{
category = cat.Name,
budgeted = sumBud,
actual = sumAct
}).ToArray();
var inc = db.Transactions.Where(t => t.Account.HouseholdId == hh.Id &&
t.Category.Type == "Income" &&
t.Account.IsDeleted == false &&
t.PostedDate.Year == DateTime.Now.Year &&
t.PostedDate.Month == DateTime.Now.Month)
.Select(t => t.Amount).DefaultIfEmpty().Sum();
var exp = db.Transactions.Where(t => t.Account.HouseholdId == hh.Id &&
t.Category.Type == "Expense" &&
t.Account.IsDeleted == false &&
t.PostedDate.Year == DateTime.Now.Year &&
t.PostedDate.Month == DateTime.Now.Month)
.Select(t => t.Amount).DefaultIfEmpty().Sum();
var donutList = new[] { new { label = "Income", value = (int)inc },
new { label = "Expenses", value = (int)exp } };
var data = new
{
donutList = donutList,
carBarList = catBarList
};
return Content(JsonConvert.SerializeObject(data), "application/json");
}
private static void ExportDataToExcel(SQL.DataSet dataSet)
{
const int VendorsIndex = 1;
const int IncomesIndex = 2;
const int ExpensesIndex = 3;
const int TotalTaxesIndex = 4;
const int FinancialResultIndex = 5;
var excelApplication = new Application();
//String excelReportPath = @"D:\DbApps\Team-Bottle-Green-\BottleGreen\08-SQLiteAndMySQLToExcel\Report.xlsx";
//String excelReportPathBackup = String.Format(@"D:\DbApps\Team-Bottle-Green-\BottleGreen\08-SQLiteAndMySQLToExcel\Report-{0}-{1}-{2}-{3}-{4}-{5}.xlsx",
String excelReportPath = @"C:\Users\p.kanev\Desktop\BottleGreen\08-SQLiteAndMySQLToExcel\Report.xlsx";
String excelReportPathBackup = String.Format(@"..\..\..\Excel-Reports\Report-{0}-{1}-{2}-{3}-{4}-{5}.xlsx",
DateTime.Now.Year,
DateTime.Now.Month,
DateTime.Now.Day,
DateTime.Now.Hour,
DateTime.Now.Minute,
DateTime.Now.Second);
if (File.Exists(excelReportPath))
{
File.Move(excelReportPath, excelReportPathBackup);
}
var excelWorkBook = excelApplication.Workbooks.Add(Missing.Value);
foreach (SQL.DataTable table in dataSet.Tables)
{
Worksheet excelWorksheet = excelWorkBook.Sheets.Add();
excelWorksheet.Name = table.TableName;
excelWorksheet.Cells[1, VendorsIndex] = table.Columns[VendorsIndex - 1].ColumnName;
excelWorksheet.Cells[1, IncomesIndex] = table.Columns[IncomesIndex - 1].ColumnName;
excelWorksheet.Cells[1, ExpensesIndex] = table.Columns[ExpensesIndex - 1].ColumnName;
excelWorksheet.Cells[1, TotalTaxesIndex] = table.Columns[TotalTaxesIndex - 1].ColumnName;
excelWorksheet.Cells[1, FinancialResultIndex] = table.Columns[FinancialResultIndex - 1].ColumnName;
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Columns.Count; j++)
{
excelWorksheet.Cells[i + 2, j + 1] = table.Rows[i].ItemArray[j].ToString();
}
}
}
excelWorkBook.SaveAs(excelReportPath);
excelWorkBook.Save();
excelWorkBook.Close();
excelApplication.Quit();
if (File.Exists(excelReportPath))
{
RunExcelReport(excelReportPath);
}
}
private void AddSalesIntoDataTable(List<SaleForExcelReport> sales, SQL.DataTable productsTaxes)
{
var groupedSales = sales
.GroupBy(s => new {s.Name, s.Expense, s.Income})
.Select(s => new
{
VendorName = s.Key.Name,
Expense = s.Key.Expense,
Income = s.Key.Income,
FinancialResult = (s.Key.Income - s.Key.Expense - s.Sum(s1 => s1.Tax)).ToString(new CultureInfo("en-US")),
Tax = s.Sum(s1 => s1.Tax).ToString(new CultureInfo("en-US")),
})
.ToList();
groupedSales.ForEach(gs => productsTaxes.Rows.Add(
gs.VendorName,
gs.Income,
gs.Expense,
gs.Tax,
gs.FinancialResult));
}
private bool IsSupported(FilterFunctions op, Procedure procedure, Parameter parameter)
{
if (op == FilterFunctions.None)
return false;
var fullTextFunctions = new[] {
FilterFunctions.FullTextContains,
FilterFunctions.NotFullTextContains,
FilterFunctions.FreeText,
FilterFunctions.NotFreeText
};
var likeFunctions = new[] {
FilterFunctions.StartsWith,
FilterFunctions.EndsWith,
FilterFunctions.Contains,
FilterFunctions.NotContains
};
var parameterFunctions = GetFilterFunctions(parameter);
if ((parameterFunctions & op) != op)
return false;
if (fullTextFunctions.Contains(op))
{
if (!IsFullTextCompatible(parameter.DbType))
return false;
}
if (op == FilterFunctions.StartsWith || op == FilterFunctions.EndsWith || op == FilterFunctions.Contains || op == FilterFunctions.NotContains)
{
if (!IsLikeCompatible(parameter.DbType))
return false;
}
return true;
}
