· 8 years ago · Apr 02, 2018, 09:50 PM
1using System;
2using System.IO;
3using System.Collections.Generic;
4using System.Linq;
5using Npgsql;
6using System.Data;
7using System.Text.RegularExpressions;
8
9
10
11namespace Project3
12{
13 class Program
14 {
15 static void Main(string[] args)
16 {
17 GeneralParser parser = new GeneralParser();
18
19
20
21 string[] ScholenNaamTypes = { "id", "instellingsnaam", "straatnaam", "wijk"};
22 SQLConnectionTool N1 = new SQLConnectionTool("Host = localhost; Username = postgres; Password = Test; Database = Project3Test");
23
24
25
26
27
28 List<int> straatnamen = new List<int>();
29 #region scholen
30 //Scholen table
31 //0. Brin nummer (PK) (varchar) 2/2/4/3
32 //1. Instellingsnaam (varchar) 3/3/5/4
33 //2. Straatnaam (integer) 4/4/6/5 (NEEDS CONVERSION)
34 //3. Wijk (varchar) 6/6/8/7 (NEEDS CONVERSION)
35 //4. Niveau (varchar) x/x/x/x
36 //5. Plaatsnaam 7/7/9/8
37
38 List<List<String>> School1 = parser.ParseCSV
39 (
40 @"D:\AlleCsv\scholen\school01_Basis.csv",
41 ';',
42 (int line, int column, string value, string[] columns) =>
43 {
44 if (column == 2 || column == 3 || column == 4 || column == 6 || column == 7) { return true; } else { return false; }
45 },
46 (string value) => value + " "
47 );
48 List<List<String>> School2 = parser.ParseCSV
49 (
50 @"D:\AlleCsv\scholen\school02_Middelbaar.csv",
51 ';',
52 (int line, int column, string value, string[] columns) =>
53 {
54 if (column == 2 || column == 3 || column == 4 || column == 6 || column == 7) { return true; } else { return false; }
55 },
56 (string value) => value + " "
57 );
58 List<List<String>> School3 = parser.ParseCSV
59 (
60 @"D:\AlleCsv\scholen\school03_Mbo.csv",
61 ';',
62 (int line, int column, string value, string[] columns) =>
63 {
64 if (column == 4 || column == 5 || column == 6 || column == 8 || column == 9) { return true; } else { return false; }
65 },
66 (string value) => value + " "
67 );
68 List<List<String>> School4 = parser.ParseCSV
69 (
70 @"D:\AlleCsv\scholen\school04_HboWo.csv",
71 ';',
72 (int line, int column, string value, string[] columns) =>
73 {
74 if (column == 3 || column == 4 || column == 5 || column == 7 || column == 8) { return true; } else { return false; }
75 },
76 (string value) => value + " "
77 );
78
79 List<List<String>> postcodesenwijken = parser.ParseCSV
80 (
81 @"D:\AlleCsv\wijken\Postcodesenwijken.csv",
82 ';',
83 (int line, int column, string value, string[] columns) =>
84 {
85 if (column == 0 || column == 3) { return true; } else { return false; }
86 },
87 (string value) => value
88 );
89 N1.Postcodesenwijken = postcodesenwijken;
90
91
92 string[] typenamespostcodes = { "Varchar(10)", "Varchar(40)" };
93
94 string[] typenames = { "varchar(10)", "varchar(150)", "varchar(100)", "varchar(100)" };
95
96 List<List<string>> nschool1 = new List<List<string>>();
97 for (int i = 0; i < School1.Count; i++)
98 {
99 if (School1[i][4].ToString().Contains("ROTTERDAM"))
100 {
101 List<string> l = new List<string>();
102 School1[i][2] = N1.straatnaamtoint(School1[i][2], School1[i][3]).ToString();
103 School1[i][3] = N1.PostcodeNaarWijk(School1[i][3].Replace(" ", ""));
104 for (int j = 0; j < School1[i].Count - 1; j++)
105 {
106 Console.ForegroundColor = ConsoleColor.White;
107 Console.WriteLine("SCHOOL: " + School1[0][j] + " " + School1[i][j].ToString() + " " + j.ToString());
108 l.Add(School1[i][j]);
109 }
110 nschool1.Add(l);
111 }
112 }
113 N1.insertIntoTable("scholen", ScholenNaamTypes.ToList(), nschool1, "niveau", "'lager'");
114
115
116
117 List<List<string>> nschool2 = new List<List<string>>();
118 for (int i = 0; i < School2.Count; i++)
119 {
120 if (School2[i][4].ToString().Contains("ROTTERDAM"))
121 {
122 List<string> l = new List<string>();
123 School2[i][2] = N1.straatnaamtoint(School2[i][2], School2[i][3]).ToString();
124 School2[i][3] = N1.PostcodeNaarWijk(School2[i][3].Replace(" ", ""));
125 for (int j = 0; j < School2[i].Count - 1; j++)
126 {
127 Console.ForegroundColor = ConsoleColor.White;
128 Console.WriteLine("SCHOOL: " + School2[0][j] + " " + School2[i][j].ToString() + " " + j.ToString());
129 l.Add(School2[i][j]);
130 }
131 nschool2.Add(l);
132 }
133 }
134 N1.insertIntoTable("scholen", ScholenNaamTypes.ToList(), nschool2, "niveau", "'middelbaar'");
135
136
137
138 List<List<string>> nschool3 = new List<List<string>>();
139 for (int i = 0; i < School3.Count; i++)
140 {
141 if (School3[i][4].ToString().Contains("ROTTERDAM"))
142 {
143 List<string> l = new List<string>();
144 School3[i][2] = N1.straatnaamtoint(School3[i][2], School3[i][3]).ToString();
145 School3[i][3] = N1.PostcodeNaarWijk(School3[i][3].Replace(" ", ""));
146 for (int j = 0; j < School3[i].Count - 1; j++)
147 {
148 Console.ForegroundColor = ConsoleColor.White;
149 Console.WriteLine("SCHOOL: " + School3[0][j] + " " + School3[i][j].ToString() + " " + j.ToString());
150 l.Add(School3[i][j]);
151 }
152 nschool3.Add(l);
153 }
154 }
155 N1.insertIntoTable("scholen", ScholenNaamTypes.ToList(), nschool3, "niveau", "'hoger'");
156
157
158
159 List<List<string>> nschool4 = new List<List<string>>();
160 for (int i = 0; i < School4.Count; i++)
161 {
162 if (School4[i][4].ToString().Contains("ROTTERDAM"))
163 {
164 List<string> l = new List<string>();
165 School4[i][2] = N1.straatnaamtoint(School4[i][2], School4[i][3]).ToString();
166 School4[i][3] = N1.PostcodeNaarWijk(School4[i][3].Replace(" ", ""));
167 for (int j = 0; j < School4[i].Count - 1; j++)
168 {
169 Console.ForegroundColor = ConsoleColor.White;
170 Console.WriteLine("SCHOOL: " + School4[0][j] + " " + School4[i][j].ToString() + " " + j.ToString());
171 l.Add(School4[i][j]);
172 }
173 nschool4.Add(l);
174 }
175 }
176 N1.insertIntoTable("scholen", ScholenNaamTypes.ToList(), nschool4, "niveau", "'hoger'");
177 #endregion
178
179 #region misdaden
180 /*create table if not exists misdaad(
181 casenummer char(10) primary key,
182 einddatumtijd date,
183 begindatumtijd date,
184 type varchar(20),
185 straatnaam int,
186 wijk varchar(30),
187 jaartal date,
188 foreign key (jaartal) references jaartal(jaartal),
189 constraint fk_misdaad_straatnaam foreign key (straatnaam, wijk) references straatnaam(id, wijk),
190 constraint typecontrole check (type='straatroof' or type='fietsdiefstal')
191 */
192
193 //Misdaden table
194 //0. Casenummer (PK) (varchar) 00
195 //1. Begindatumtijd (datetime) 08,09 (NEEDS CONVERSION)
196 //2. Einddatumtijd (datetime) 10,11 (NEEDS CONVERSION)
197 //3. Type (varchar) 13
198 //4. Straatnaam (int) 19 (NEEDS CONVERSION)
199 //5. Wijk (varchar) 17
200 //6. Jaartal (varchar) x
201
202 string[] misdaadtypesarray = {"casenummer", "begindatumtijd", "einddatumtijd", "type", "straatnaam", "wijk", "jaartal"};
203 List<string> misdaadtypes = misdaadtypesarray.ToList();
204
205
206 List<List<String>> misdaden = parser.ParseCSV
207 (
208 @"D:\AlleCsv\misdaden\Straatroof-2011.csv",
209 ';',
210 (int line, int column, string value, string[] columns) =>
211 {
212 if (column == 0 || column == 8 || column == 9 || column == 10 || column == 11 || column == 13 || column == 19 || column == 22) { return true; } else { return false; }
213 },
214 (string value) => value
215 );
216
217 List<List<String>> misdaden2 = new List<List<string>>();
218
219 for (int i = 0; i < misdaden.Count; i++)
220 {
221 List<String> l = new List<string>();
222 for (int j = 0; j < misdaden[0].Count; j++)
223 {
224 try
225 {
226 Console.ForegroundColor = ConsoleColor.White;
227 l.Add(misdaden[i][j]);
228 }
229 catch
230 {
231 Console.ForegroundColor = ConsoleColor.Red;
232 Console.WriteLine("NOT ADDED");
233 break;
234 }
235 if (l.Count == misdaden[0].Count)
236 {
237 misdaden2.Add(l);
238 Console.ForegroundColor = ConsoleColor.Green;
239 Console.WriteLine("ADDED");
240
241 }
242 }
243 Console.WriteLine();
244 }
245
246
247 Console.ForegroundColor = ConsoleColor.White;
248 for (int i = 0; i < misdaden2.Count; i++)
249 {
250 for (int j = 0; j < misdaden2[0].Count; j++)
251 {
252 Console.Write(misdaden2[i][j] + ", ");
253 }
254 Console.WriteLine();
255 }
256
257 List<List<string>> misdaden3 = new List<List<string>>();
258
259 for (int i = 0; i < misdaden2.Count; i++)
260 {
261 //0. Casenummer (PK) (varchar) 00
262 //1. Begindatumtijd (datetime) 08,09 (NEEDS CONVERSION)
263 //2. Einddatumtijd (datetime) 10,11 (NEEDS CONVERSION)
264 //3. Type (varchar) 13
265 //4. Straatnaam (int) 19 (NEEDS CONVERSION)
266 //5. Wijk (varchar) 17
267 //6. Jaartal (varchar) x
268 if (misdaden2[i][7].Replace(" ", "") != "")
269 {
270 List<string> l = new List<string>();
271 Console.ForegroundColor = ConsoleColor.White;
272 l.Add(misdaden2[i][0]);
273 l.Add(dateANDtimeTOdatetime(misdaden2[i][1], misdaden2[i][2]));
274 l.Add(dateANDtimeTOdatetime(misdaden2[i][3], misdaden2[i][4]));
275 l.Add(misdaden2[i][5]);
276 l.Add(N1.straatnaamtoint(misdaden2[i][6], misdaden2[i][7]).ToString());
277 l.Add(N1.PostcodeNaarWijk(misdaden2[i][7]));
278 l.Add("2011");
279 misdaden3.Add(l);
280 }
281 }
282 for (int i = 0; i < misdaden3.Count; i++)
283 {
284 for (int j = 0; j < misdaden3[i].Count; j++)
285 {
286 Console.WriteLine(misdaadtypes[j] + ": " + misdaden3[i][j]);
287 }
288 }
289
290 N1.insertIntoTable("misdaad", misdaadtypes, misdaden3, "", "");
291
292
293
294 //Misdaden table
295 //0. Casenummer (PK) (varchar) 00
296 //1. Begindatumtijd (datetime) 11,12 (NEEDS CONVERSION)
297 //2. Einddatumtijd (datetime) 14,15 (NEEDS CONVERSION)
298 //3. Type (varchar) 3
299 //4. Straatnaam (int) 9 (NEEDS CONVERSION)
300 //5. Wijk (varchar) 8 (NEEDS CONVERSION)
301 //6. Jaartal (varchar) 16
302 List<List<String>> fietsendiefstallen1 = parser.ParseCSV
303 (
304 @"D:\AlleCsv\misdaden\fietsdiefstal-2011-2013.csv",
305 ';',
306 (int line, int column, string value, string[] columns) =>
307 {
308 if (column == 0 || column == 11 || column == 12 || column == 14 || column == 15 || column == 3 || column == 9 || column == 8 || column == 16 || column == 7) { return true; } else { return false; }
309 },
310 (string value) => value
311 );
312
313 for (int i = 0; i < fietsendiefstallen1.Count; i++)
314 {
315 for (int j = 0; j < fietsendiefstallen1[i].Count; j++)
316 {
317 Console.Write(fietsendiefstallen1[i][j] + ", ");
318 }
319 Console.WriteLine();
320 }
321 List<List<string>> fietsendiefstallen2 = new List<List<string>>();
322 for (int i = 0; i < fietsendiefstallen1.Count; i++)
323 {
324 try
325 {
326 if (fietsendiefstallen1[i][2].Contains("ROTTER"))
327 {
328 Console.ForegroundColor = ConsoleColor.Blue;
329 Console.WriteLine("Fietsendiefstal");
330 List<string> l = new List<string>();
331 l.Add(fietsendiefstallen1[i][0]);
332 l.Add(dateANDtimeTOdatetime(fietsendiefstallen1[i][5], fietsendiefstallen1[i][6]));
333 l.Add(dateANDtimeTOdatetime(fietsendiefstallen1[i][7], fietsendiefstallen1[i][8]));
334 l.Add("fietsdiefstal");
335 Console.WriteLine((Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", "")).ToString());
336 Console.WriteLine(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", "")).ToString());
337 if (N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", "")) != 0)
338 {
339 l.Add(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", "")).ToString());
340 Console.WriteLine(N1.findWijk(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", ""))));
341 if (N1.findWijk(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", ""))) != "")
342 {
343 Console.WriteLine("KJ");
344 }
345 }
346 if (N1.findWijk(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", ""))) != "")
347 {
348 l.Add(N1.findWijk(N1.straatnaamtoint2(Regex.Replace(fietsendiefstallen1[i][4], @"\d", "").Replace(" ", ""))));
349 }
350 l.Add(fietsendiefstallen1[i][9]);
351 for (int j = 0; j < l.Count; j++)
352 {
353 Console.Write(l[j].ToString() + ", ");
354 }
355 Console.WriteLine(l.Count);
356 if (l.Count == 7)
357 {
358 fietsendiefstallen2.Add(l);
359 }
360
361 }
362 }
363 catch
364 {
365
366 }
367 }
368 Console.Write("KJKJ");
369
370
371 for (int i = 0; i < fietsendiefstallen2.Count; i++)
372 {
373 for (int j = 0; j < fietsendiefstallen2[i].Count; j++)
374 {
375 Console.WriteLine(fietsendiefstallen2[i][j] + ": " + misdaadtypes[j]);
376 }
377 Console.WriteLine();
378 }
379 N1.insertIntoTable("misdaad", misdaadtypes, fietsendiefstallen2, "", "");
380
381 Console.Read();
382
383
384 #endregion
385 }
386
387 public static string dateANDtimeTOdatetime(string date, string time)
388 {
389 return date + " " + time;
390 }
391 }
392
393 //Created by Allon
394 public class GeneralParser
395 {
396 public List<List<string>> ParseCSV(string location, char seperator, Func<int, int, string, string[], bool> filter, Func<string, string> action)
397 {
398 List<List<string>> parsed = new List<List<string>>();
399 try
400 {
401 using (StreamReader reader = new StreamReader(location))
402 {
403 string line;
404 /* Lees elke regel van het csv bestand */
405 int lineCounter = 0;
406 while ((line = reader.ReadLine()) != null)
407 {
408 /* Splits de regel op in kolommen en loop door elke kolom heen */
409 string[] columns = line.Split(seperator);
410 int columnCounter = 0;
411 List<string> rowList = new List<string>();
412 foreach (string column in columns)
413 {
414 /* Filter */
415 if (filter(lineCounter, columnCounter, column, columns))
416 {
417 /* Voer een actie uit op de waarde en voeg het toe aan de rijlijst */
418 rowList.Add(action(column));
419 }
420 columnCounter += 1;
421 }
422 if (rowList.Count > 0)
423 parsed.Add(rowList);
424 lineCounter += 1;
425 }
426 }
427 }
428 catch (Exception e)
429 {
430 Console.WriteLine("Something went wrong while reading the csv file: " + e);
431 }
432 return parsed;
433 }
434
435 public List<List<string>> parseList(List<List<string>> list, Func<List<List<string>>, List<string>, bool> filter, Func<List<List<string>>, List<string>, List<string>> action)
436 {
437 List<List<string>> parsed = new List<List<string>>();
438 foreach (List<string> sublist in list)
439 {
440 if (filter(list, sublist))
441 if (sublist.Count > 0)
442 parsed.Add(action(list, sublist));
443 }
444 return parsed;
445 }
446
447 public List<string> parseList(List<string> list, Func<List<string>, string, bool> filter, Func<List<string>, string, string> action)
448 {
449 List<string> parsed = new List<string>();
450 foreach (string value in list)
451 {
452 if (filter(list, value))
453 if (value != "")
454 parsed.Add(action(list, value));
455 }
456 return parsed;
457 }
458
459 public List<List<string>> compareLists(List<string> list1, List<string> list2, Func<List<string>, string, Func<List<string>, string, bool>> filter, Func<List<string>, string, Func<List<string>, string, string>> action)
460 {
461 List<List<string>> compared = new List<List<string>>();
462 compared.Add(parseList(list1, (list, value) => true, (list, value) => {
463 compared.Add(parseList(list2, filter(list, value), action(list, value)));
464 return value;
465 }));
466 return compared;
467 }
468
469 public List<List<string>> compareLists(List<List<string>> list1, List<List<string>> list2, Func<List<string>, string, Func<List<string>, string, bool>> filter, Func<List<string>, string, Func<List<string>, string, string>> action)
470 {
471 List<List<string>> compared = new List<List<string>>();
472 parseList(list1, (list, sublist) => true, (list, sublist) => {
473 return parseList(sublist, (list1Sublist, list1Value) => true, (list1Sublist, list1Value) => {
474 parseList(list2, (list2List, list2Sublist) => true, (list2List, list2Sublist) => {
475 compared.Add(parseList(list2Sublist, filter(list1Sublist, list1Value), action(list1Sublist, list1Value)));
476 return list2Sublist;
477 });
478 return list1Value;
479 });
480 });
481 return compared;
482 }
483
484 public List<string> ListTo1d(List<List<string>> list, Func<int, string, bool> filter)
485 {
486 List<string> result = new List<string>();
487 /* Loop door de eerste laag */
488 foreach (List<string> sublist in list)
489 {
490 int counter = 0;
491 /* Loop door de tweede laag */
492 foreach (string value in sublist)
493 {
494 /* Filter */
495 if (filter(counter, value))
496 {
497 result.Add(value);
498 }
499 counter += 1;
500 }
501 }
502 return result;
503 }
504
505 public List<List<string>> removeDuplicates(List<List<string>> list)
506 {
507 List<List<string>> filtered = new List<List<string>>();
508
509 return filtered;
510 }
511
512 public string BuurtNaarWijk(string buurt)
513 {
514 var buurtenlijst = ParseCSV(@"D:\AlleCsv\wijken", ';', (line, row, column, columns) => columns[1] == buurt && row == 0, (column) => column);
515 return buurtenlijst[0][0].Substring(0, 5);
516 }
517
518 public string PostcodeNaarWijk(string postcode)
519 {
520 var postcodeWijkLijst = ParseCSV(@"D:\AlleCsv\wijken\Postcodeseenwijken.csv", ';', (line, row, column, columns) => line > 0 && columns[0].Substring(0, 4) == postcode && row == 3, (column) => column);
521 return postcodeWijkLijst[0][0];
522 }
523 }
524
525 //Created by Tim
526 public class SQLConnectionTool
527 {
528 public List<List<string>> Postcodesenwijken;
529
530 public SQLConnectionTool(string connectionstring)
531 {
532 CON = new NpgsqlConnection(connectionstring);
533 }
534 NpgsqlConnection CON = new NpgsqlConnection("Host = localhost; Username = postgres; Password = Test; Database = Project3Test");
535 public static List<straatnamen> allestraten = new List<straatnamen>();
536
537
538 //Voorbeeld input:
539 //tablename: "Mijntable"
540 //names = {"naam", "leeftijd"}
541 //typenames = {"Varchar(30)", "integer"}
542 //droptable = true
543 //PrimaryKey = null
544
545 //Voorbeeld output:
546 //DROP TABLE Mijntable;
547 //CREATE TABLE if not exists Mijntable
548 //(
549 // id SERIAL PRIMARY KEY,
550 // naam varchar(30),
551 // leeftijd integer
552 //);
553 public void createTable(string tablename, List<string> names, List<string> typenames, bool dropTable, int PrimaryKey, string extra)
554 {
555 if (names.Count != typenames.Count)
556 {
557 //MessageBox.Show("Please make sure that the amount of names equals the amount of type names");
558 throw new Exception("YA DUN! Names: " + names.Count + " typenames: " + typenames.Count);
559 }
560 if (PrimaryKey >= names.Count && PrimaryKey != 0)
561 {
562 //MessageBox.Show("Please make sure that the primary key is smaller than the amount of data types");
563 throw new Exception("YA DUN!");
564 }
565
566
567 NpgsqlCommand COM = new NpgsqlCommand();
568 COM.Connection = CON;
569 CON.Open();
570 if (dropTable)
571 {
572 COM.CommandText = "DROP TABLE if exists " + tablename + ";";
573 Console.Write(COM.CommandText + "\n");
574 COM.ExecuteNonQuery();
575 }
576 COM.CommandText = "CREATE TABLE if not exists " + tablename + "\n ( \n ";
577 if (PrimaryKey == 0)
578 {
579 COM.CommandText = COM.CommandText + "id SERIAL PRIMARY KEY, \n ";
580 }
581 for (int i = 0; i < typenames.Count; i++)
582 {
583 COM.CommandText = COM.CommandText + names[i].Replace(" ", "").Replace("-", "_") + " " + typenames[i];
584 if (i + 1 == PrimaryKey)
585 {
586 COM.CommandText = COM.CommandText + " PRIMARY KEY";
587 }
588 if ((i != typenames.Count && extra != "") || (i != typenames.Count - 1 && extra == ""))
589 {
590 COM.CommandText = COM.CommandText + ", ";
591 }
592 COM.CommandText = COM.CommandText + "\n ";
593 }
594 if (extra != "")
595 {
596 COM.CommandText = COM.CommandText + extra + " varchar (100)";
597 }
598 COM.CommandText = COM.CommandText + "\n );\n";
599 Console.Write(COM.CommandText);
600 COM.ExecuteNonQuery();
601 CON.Close();
602 }
603
604 //Voorbeeld input:
605 //tablename = "Mijntable"
606 //names = {"naam", "leeftijd"}
607 //typenames = {"Varchar(30)", "integer"}
608 //Values = {"Eddy", "42"},{"Fred", "28"}, {"Jos", "6"}, {"Loubna", "6"}, {"Rini", "6"}}
609 //
610 //Voorbeeld output:
611 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Eddy', 42);
612 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Fred', 28);
613 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Jos', 6);
614 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Loubna', 6);
615 //INSERT INTO Mijntable (naam, leeftijd) VALUES ('Rini', 6);
616
617 public void insertIntoTable(string tablename, List<string> names, List<List<string>> Values, string extraName, string extraVal)
618 {
619 if (names.Count != Values[0].Count)
620 {
621 //MessageBox.Show("Please make sure that the amount of typenames, names and values are equal");
622 throw new Exception("YA DUN! Names: " + names.Count + " values[0]: " + Values[0].Count);
623 }
624
625 NpgsqlCommand COM = new NpgsqlCommand();
626 COM.Connection = CON;
627 CON.Open();
628
629 for (int i = 1; i < Values.Count; i++)
630 {
631 COM.CommandText = "INSERT INTO " + tablename + "(";
632 for (int j = 0; j < Values[i].Count; j++)
633 {
634 COM.CommandText = COM.CommandText + names[j].Replace(" ", "");
635 if ((j != Values[i].Count && extraName != "") || (j != Values[i].Count - 1 && extraName == ""))
636 {
637 COM.CommandText = COM.CommandText + ", ";
638 }
639 }
640 if (extraName != "")
641 {
642 COM.CommandText = COM.CommandText + extraName;
643 }
644 COM.CommandText = COM.CommandText + ") VALUES (";
645 for (int j = 0; j < Values[i].Count; j++)
646 {
647 COM.CommandText = COM.CommandText + "'" + Values[i][j] + "'";
648 if ((j != Values[i].Count && extraName != "") || (j != Values[i].Count - 1 && extraName == ""))
649 {
650 COM.CommandText = COM.CommandText + ", ";
651 }
652 }
653 if (extraName != "")
654 {
655 COM.CommandText = COM.CommandText + extraVal;
656 }
657 COM.CommandText = COM.CommandText + "); \n";
658 Console.Write(COM.CommandText);
659 try
660 {
661 COM.ExecuteNonQuery();
662 }
663 catch
664 {
665 COM.CommandText = "";
666 }
667 }
668 CON.Close();
669 }
670
671
672 public void joinTables(List<List<string>> table1, List<List<string>> table2, string tablename1, string tablename2, string newtable, int joinRowTable1, int joinRowTable2, List<int> selectRowsTable1, List<int> selectRowsTable2)
673 {
674 NpgsqlCommand COM = new NpgsqlCommand();
675 CON.Open();
676 COM.CommandText = "SELECT ";
677 foreach (int i in selectRowsTable1)
678 {
679 COM.CommandText = COM.CommandText + tablename1 + "." + table1[0][selectRowsTable1[i]] + ", ";
680 }
681 int j = 0;
682 foreach (int i in selectRowsTable2)
683 {
684 COM.CommandText = COM.CommandText + tablename2 + "." + table2[0][selectRowsTable2[i]];
685 if (j != selectRowsTable2.Count - 1)
686 {
687 COM.CommandText = COM.CommandText + ", ";
688 }
689 j++;
690 }
691 COM.CommandText = COM.CommandText + "\n";
692 COM.CommandText = COM.CommandText + "INTO " + newtable + "\n";
693 COM.CommandText = COM.CommandText + "FROM " + tablename1 + "\n";
694 COM.CommandText = COM.CommandText + "INNER JOIN " + tablename2 + " ON " + tablename1 + "." + table1[0][joinRowTable1] + " = " + tablename2 + "." + table2[0][joinRowTable2] + ";";
695 Console.WriteLine(COM.CommandText);
696 Console.Read();
697 COM.ExecuteNonQuery();
698 CON.Close();
699 }
700
701 public void readTable(string tablename, List<int> rows)
702 {
703 DataSet DS = new DataSet();
704 DataTable DT = new DataTable();
705
706 CON.Open();
707 NpgsqlDataAdapter DA = new NpgsqlDataAdapter(("SELECT * FROM " + tablename), CON);
708 DA.Fill(DS);
709 DT = DS.Tables[0];
710 foreach (DataRow ROW in DT.Rows)
711 {
712 for (int i = 0; i < rows.Count; i++)
713 {
714 Console.Write(ROW[rows[i]].ToString() + " ");
715 }
716 Console.Write("\n");
717 }
718 CON.Close();
719 }
720
721
722 public int straatnaamtoint(string straatnaam, string postcode)
723 {
724 NpgsqlCommand COM = new NpgsqlCommand();
725 COM.Connection = CON;
726 GeneralParser GP = new GeneralParser();
727 postcode = postcode.Replace(" ", "");
728
729 Console.WriteLine("Trying to convert " + postcode + " to wijk");
730 string wijk = PostcodeNaarWijk(postcode);
731 if (wijk != "COULDNOTCONVERT")
732 {
733 Console.ForegroundColor = ConsoleColor.Green;
734 Console.WriteLine("Managed to convert " + postcode + " to wijk " + wijk);
735 }
736 else
737 {
738 Console.ForegroundColor = ConsoleColor.Red;
739 Console.WriteLine("Did not manage to convert " + postcode);
740 }
741
742 Console.ForegroundColor = ConsoleColor.White;
743 if (allestraten.Count != 0)
744 {
745 for (int i = 0; i < allestraten.Count - 1; i++)
746 {
747 if (allestraten[i].straatnaam == straatnaam)
748 {
749 Console.ForegroundColor = ConsoleColor.Green;
750 Console.WriteLine("MATCH!");
751 Console.WriteLine("FOUND " + straatnaam + " ON ID " + allestraten[i].id.ToString());
752 System.Threading.Thread.Sleep(10);
753 return allestraten[i].id;
754 }
755 }
756 }
757
758
759 System.Threading.Thread.Sleep(30);
760 Console.ForegroundColor = ConsoleColor.Red;
761 Console.WriteLine("NO MATCH!");
762 allestraten.Add(new straatnamen(straatnaam, wijk, allestraten.Count));
763 Console.WriteLine("ADDING " + straatnaam + "TO DATABASE ON ID " + allestraten[allestraten.Count - 1].id.ToString());
764 Console.ForegroundColor = ConsoleColor.DarkCyan;
765 CON.Open();
766 COM.Connection = CON;
767 COM.CommandText = "INSERT INTO straatnaam(id, straatnaam, wijk) VALUES ( " + allestraten[allestraten.Count - 1].id.ToString() + ", '" + allestraten[allestraten.Count - 1].straatnaam.Replace("'", "") + "', '" + allestraten[allestraten.Count - 1].wijk + "');\n";
768 try
769 {
770 COM.ExecuteNonQuery();
771 Console.WriteLine(COM.CommandText);
772 }
773 catch (Exception e)
774 {
775 }
776 CON.Close();
777 return allestraten[allestraten.Count - 1].id;
778 }
779
780 public int straatnaamtoint2(string straatnaam)
781 {
782 NpgsqlCommand COM = new NpgsqlCommand();
783 COM.Connection = CON;
784 GeneralParser GP = new GeneralParser();
785 for (int i = 0; i < allestraten.Count; i++)
786 {
787 if (allestraten[i].straatnaam.ToUpper().Contains("ZUIDWIJ"))
788 {
789 Console.WriteLine(allestraten[i].straatnaam);
790 }
791 if (allestraten[i].straatnaam.ToUpper().Replace(" ", "") == straatnaam.ToUpper().Replace(" ", ""))
792 {
793 Console.ForegroundColor = ConsoleColor.Green;
794 Console.WriteLine("MATCH!");
795 Console.WriteLine("FOUND " + straatnaam + " ON ID " + allestraten[i].id.ToString());
796 System.Threading.Thread.Sleep(10);
797 return allestraten[i].id;
798 }
799 }
800 return 0;
801 }
802
803 public string PostcodeNaarWijk(string postcode)
804 {
805 for (int i = 0; i < Postcodesenwijken.Count; i++)
806 {
807 if (Postcodesenwijken[i][0].Contains(postcode))
808 {
809 return Postcodesenwijken[i][1].ToString();
810 }
811 }
812
813 return "COULDNOTFINDWIJK";
814 }
815
816 public string findWijk(int straatint)
817 {
818 for (int i = 0; i < allestraten.Count; i++)
819 {
820 if (allestraten[i].id == straatint)
821 {
822 return allestraten[i].wijk;
823 }
824 }
825 return "";
826 }
827 }
828
829 public class straatnamen
830 {
831 public string straatnaam;
832 public string wijk;
833 public int id;
834 public straatnamen(string straatnaam, string wijk, int id)
835 {
836 this.straatnaam = straatnaam;
837 this.wijk = wijk;
838 this.id = id;
839 }
840 }
841}