/
Program.cs
144 lines (129 loc) · 5.75 KB
/
Program.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
namespace Example
{
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Xml;
using Cavity;
using Cavity.Collections;
using Cavity.Data;
using Cavity.IO;
using Cavity.Models;
using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Driver.Linq;
public static class Program
{
private static MongoDatabase Database
{
get
{
var client = new MongoClient("mongodb://localhost");
var server = client.GetServer();
return server.GetDatabase("Land_Registry");
}
}
public static void Main()
{
////Load();
////var destination = new FileInfo(@"C:\Development.Temp\query.csv");
////TempCsvFile.Create(Query(), destination);
var destination = new FileInfo(@"C:\Development.Temp\test.csv");
TempCsvFile.Create(Aggregate(), destination);
}
private static IEnumerable<BsonDocument> Entries()
{
var count = 0;
var file = new FileInfo(@"C:\Development.Temp\extract.csv");
foreach (var entry in new CsvDataSheet(file))
{
count++;
if (0 == count % 100000)
{
Console.WriteLine("{0:0,0}".FormatWith(count));
}
BritishPostcode postcode = entry["POSTCODE"];
var result = new BsonDocument
{
{"UID", BsonValue.Create(Guid.Parse(entry["UID"]))},
{"DAY", BsonValue.Create(entry["DAY"].ToDate().ToDateTime())},
{"ADDRESS", BsonValue.Create(entry["ADDRESS"])},
{"POSTCODE", BsonValue.Create((string)postcode)},
{"TENURE", BsonValue.Create(entry["TENURE"])},
{"NEW BUILD", BsonValue.Create(entry.NotEmpty("NEW BUILD"))},
{"SUBDIVIDED", BsonValue.Create(entry.NotEmpty("SUBDIVIDED"))},
{"PRICE", BsonValue.Create(entry.Empty("PRICE") ? 0 : XmlConvert.ToInt32(entry["PRICE"]))},
}.AddRange(new BsonDocument
{
{"POSTAL AREA", BsonValue.Create(postcode.Area)},
{"POSTAL DISTRICT", BsonValue.Create(postcode.District)},
{"POSTAL SECTOR", BsonValue.Create(postcode.Sector)},
});
yield return result;
}
}
private static void Load()
{
var database = Database;
////database.DropCollection("PRICE PAID");
////server.DropDatabase("Land_Registry");
if (!database.CollectionExists("PRICE PAID"))
{
database.CreateCollection("PRICE PAID");
}
var collection = database.GetCollection("PRICE PAID");
var options = new MongoInsertOptions
{
WriteConcern = WriteConcern.Acknowledged,
};
collection.InsertBatch(Entries(), options);
}
private static IEnumerable<KeyStringDictionary> Query()
{
var total = new Dictionary<string, decimal>();
var tally = new Dictionary<string, int>();
var database = Database;
var documents = new MongoQueryable<BsonDocument>(new MongoQueryProvider(database.GetCollection("PRICE PAID")));
foreach (var document in documents)
{
BritishPostcode postcode = document.GetValue("POSTCODE").AsString;
if (postcode.Unit.IsEmpty())
{
continue;
}
var price = document.GetValue("PRICE").AsInt32;
if (price.Is(0))
{
continue;
}
total.TryAdd(postcode.Area, 0m);
total[postcode.Area] += price;
tally.TryAdd(postcode.Area, 0);
tally[postcode.Area]++;
}
return from item in total.OrderBy(item => item.Key.ToPostcode())
let average = Math.Round(total[item.Key] / tally[item.Key], 2, MidpointRounding.AwayFromZero)
select new KeyStringDictionary
{
{"POSTAL AREA", item.Key},
{"AVERAGE PRICE", XmlConvert.ToString(average)},
{"TALLY", XmlConvert.ToString(tally[item.Key])}
};
}
private static IEnumerable<KeyStringDictionary> Aggregate()
{
var database = Database;
var documents = new MongoQueryable<BsonDocument>(new MongoQueryProvider(database.GetCollection("PRICE PAID")));
foreach (var grp in documents.GroupBy(document => document.GetValue("POSTCODE").AsString.ToPostcode().Area))
{
yield return new KeyStringDictionary
{
{"POSTAL AREA", grp.Key},
{"AVERAGE PRICE", XmlConvert.ToString(grp.Average(x => x.GetValue("PRICE").AsInt32) / grp.Count())},
{"TALLY", XmlConvert.ToString(grp.Count())}
};
}
}
}
}