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