public void ParseAndStore(int year)
{
using (var db = new Context())
{
var lines = Read(year);
foreach (var line in lines)
{
db.BudgetLines.Add(line);
}
Console.WriteLine("Submitting {0}...", year);
db.SaveChanges();
}
}
public void ParseAndStoreAll()
{
var lines = Enumerable.Range(2003, 11).SelectMany(x => Read(x));
var context = new Context();
try
{
context.Configuration.AutoDetectChangesEnabled = false;
foreach (var line in lines)
{
context.BudgetLines.Add(line);
}
context.SaveChanges();
}
finally
{
context.Configuration.AutoDetectChangesEnabled = true;
}
}
public void ExportAll()
{
var header = new FullExportLine
{
PreviousYear1Budget = "PreviousYear1Budget",
PreviousYear2Budget = "PreviousYear2Budget",
Year1Budget = "Year1Budget",
Year2Budget = "Year2Budget",
Year3Budget = "Year3Budget",
CurrentYearBudget = "CurrentYearBudget",
Id = "Id",
LineCode = "LineCode",
LineLevel = "LineLevel",
LineName = "LineName",
ParentBudgetLineId = "ParentBudgetLine",
Year = "Year"
};
var db = new Context();
var lines = db.BudgetLines
.OrderBy(x => x.Year).ThenBy(x => x.LineCode)
.ToList();
Mapper.CreateMap<BudgetLine, FullExportLine>();
var exportlines = (new List<FullExportLine>() { header }).
Concat(
lines.Select(x =>
{
var output = Mapper.Map<BudgetLine, FullExportLine>(x);
if (x.ParentBudgetLine != null)
{
output.ParentBudgetLineId = string.Format("{0}", x.ParentBudgetLine.Id);
}
return output;
})
);
foreach (var l in exportlines)
{
l.LineName = l.LineName.Kapow();
}
FileHelperEngine<FullExportLine> eng = new FileHelperEngine<FullExportLine>();
eng.WriteFile("alllines.csv", exportlines);
}
private static void OutputMultipleYears(int year)
{
var db = new Context();
var lastlevels = GetTopLevelLines(db, _ => _.Year == year);
var lines = lastlevels.Select(l => new
{
Paragraph = l.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.LineName,
MainArea = l.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.LineName,
ActivityArea = l.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.LineName,
MainAccount = l.ParentBudgetLine.ParentBudgetLine.LineName,
SubAccount = l.ParentBudgetLine.LineName,
StandardAccount = l.LineName,
Y2011 = l.CurrentYearBudget,
Y2010 = l.PreviousYear1Budget,
Y2009 = l.PreviousYear2Budget,
Y2012 = l.Year1Budget,
Y2013 = l.Year2Budget,
Y2014 = l.Year3Budget,
}).OrderBy(l => l.Paragraph).
ThenBy(l => l.MainArea).
ThenBy(l => l.ActivityArea).
ThenBy(l => l.MainAccount).
ThenBy(l => l.SubAccount).
ThenBy(l => l.StandardAccount);
var outputlines = lines.Select(l => new OutBudgetLineMultiple
{
Paragraph = l.Paragraph,
MainArea = l.MainArea,
ActivityArea = l.ActivityArea,
MainAccount = l.MainAccount,
SubAccount = l.SubAccount,
StandardAccount = l.StandardAccount,
Y2009 = l.Y2009,
Y2010 = l.Y2010,
Y2011 = l.Y2011,
Y2012 = l.Y2012,
Y2013 = l.Y2013,
Y2014 = l.Y2014
});
var headerline = new List<OutBudgetLineMultiple> { GetHeaderRowMultiple() };
var eng = new FileHelperEngine<OutBudgetLineMultiple>();
eng.WriteFile("budgetmultiple" + year + ".csv", headerline.Concat(outputlines));
}
private static void OutputLimitedColsAll()
{
var db = new Context();
var lastlevels = GetTopLevelLines(db);
var lines = lastlevels.Select(l => new
{
Paragraph = l.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.LineName,
MainArea = l.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.LineName,
Ammount = l.CurrentYearBudget
});
var foolines = from l in lines
group l by new { l.Paragraph, l.MainArea }
into g
select new
{
g.Key.Paragraph,
g.Key.MainArea,
Sum = g.Sum(_ => _.Ammount)
};
//GroupBy(_ => new { _.Paragraph, _.MainArea }).
//Select(_ => new { _.Key.Paragraph, _.Key.MainArea, Sum = _. })
//OrderBy(l => l.Paragraph).
//ThenBy(l => l.MainArea);
var outputlines = foolines.Select(l => new OutBudgetLineLimited
{
Paragraph = l.Paragraph,
MainArea = l.MainArea,
Ammount = l.Sum
});
var headerline = new List<OutBudgetLineLimited> { GetHeaderRowLimited() };
var eng = new FileHelperEngine<OutBudgetLineLimited>();
eng.WriteFile("budget" + "all" + ".csv", headerline.Concat(outputlines));
}
private static void OutputAll()
{
var db = new Context();
var lastlevels = GetTopLevelLines(db);
var lines = lastlevels.Select(l => new
{
Paragraph = l.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.LineName,
MainArea = l.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.LineName,
ActivityArea = l.ParentBudgetLine.ParentBudgetLine.ParentBudgetLine.LineName,
MainAccount = l.ParentBudgetLine.ParentBudgetLine.LineName,
SubAccount = l.ParentBudgetLine.LineName,
StandardAccount = l.LineName,
Year2010 = l.Year == 2010 ? l.CurrentYearBudget : 0,
Year2011 = l.Year == 2011 ? l.CurrentYearBudget : 0
});
var foolines = from l in lines
group l by new
{
l.Paragraph,
l.MainArea,
l.ActivityArea,
l.MainAccount,
l.SubAccount,
l.StandardAccount
}
into g
select new
{
g.Key.Paragraph,
g.Key.MainArea,
g.Key.ActivityArea,
g.Key.MainAccount,
g.Key.SubAccount,
g.Key.StandardAccount,
Year2010 = g.Sum(_ => _.Year2010),
Year2011 = g.Sum(_ => _.Year2011)
};
//OrderBy(l => l.Paragraph).
//ThenBy(l => l.MainArea).
//ThenBy(l => l.ActivityArea).
//ThenBy(l => l.MainAccount).
//ThenBy(l => l.SubAccount).
//ThenBy(l => l.StandardAccount);
var outputlines = foolines.Select(l => new OutBudgetLineYear
{
Paragraph = l.Paragraph,
MainArea = l.MainArea,
ActivityArea = l.ActivityArea,
MainAccount = l.MainAccount,
SubAccount = l.SubAccount,
StandardAccount = l.StandardAccount,
Year2010 = l.Year2010,
Year2011 = l.Year2011
});
var headerline = new List<OutBudgetLineYear> { GetHeaderRowYear() };
var eng = new FileHelperEngine<OutBudgetLineYear>();
eng.WriteFile("budget" + "all" + ".csv", headerline.Concat(outputlines));
}
private static IEnumerable<BudgetLine> GetTopLevelLines(Context db,
Expression<Func<BudgetLine, bool>> selector)
{
return GetTopLevelLines(db.BudgetLines.Where(selector));
}
private static IEnumerable<BudgetLine> GetTopLevelLines(Context db)
{
return GetTopLevelLines(db, _ => true);
}
