· 8 years ago · Apr 02, 2018, 09:54 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 = { "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
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(" ", "") != "")
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
670 {
671 COM.CommandText = "";
672 }
673 }
674 CON.Close();
675 }
676
677
678 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)
679 {
680 NpgsqlCommand COM = new NpgsqlCommand();
681 CON.Open();
682 COM.CommandText = "SELECT ";
683 foreach (int i in selectRowsTable1)
684 {
685 COM.CommandText = COM.CommandText + tablename1 + "." + table1[0][selectRowsTable1[i]] + ", ";
686 }
687 int j = 0;
688 foreach (int i in selectRowsTable2)
689 {
690 COM.CommandText = COM.CommandText + tablename2 + "." + table2[0][selectRowsTable2[i]];
691 if (j != selectRowsTable2.Count - 1)
692 {
693 COM.CommandText = COM.CommandText + ", ";
694 }
695 j++;
696 }
697 COM.CommandText = COM.CommandText + "\n";
698 COM.CommandText = COM.CommandText + "INTO " + newtable + "\n";
699 COM.CommandText = COM.CommandText + "FROM " + tablename1 + "\n";
700 COM.CommandText = COM.CommandText + "INNER JOIN " + tablename2 + " ON " + tablename1 + "." + table1[0][joinRowTable1] + " = " + tablename2 + "." + table2[0][joinRowTable2] + ";";
701 Console.WriteLine(COM.CommandText);
702 Console.Read();
703 COM.ExecuteNonQuery();
704 CON.Close();
705 }
706
707 public void readTable(string tablename, List<int> rows)
708 {
709 DataSet DS = new DataSet();
710 DataTable DT = new DataTable();
711
712 CON.Open();
713 NpgsqlDataAdapter DA = new NpgsqlDataAdapter(("SELECT * FROM " + tablename), CON);
714 DA.Fill(DS);
715 DT = DS.Tables[0];
716 foreach (DataRow ROW in DT.Rows)
717 {
718 for (int i = 0; i < rows.Count; i++)
719 {
720 Console.Write(ROW[rows[i]].ToString() + " ");
721 }
722 Console.Write("\n");
723 }
724 CON.Close();
725 }
726
727
728 public int straatnaamtoint(string straatnaam, string postcode)
729 {
730 NpgsqlCommand COM = new NpgsqlCommand();
731 COM.Connection = CON;
732 GeneralParser GP = new GeneralParser();
733 postcode = postcode.Replace(" ", "");
734
735 Console.WriteLine("Trying to convert " + postcode + " to wijk");
736 string wijk = PostcodeNaarWijk(postcode);
737 if (wijk != "COULDNOTCONVERT")
738 {
739 Console.ForegroundColor = ConsoleColor.Green;
740 Console.WriteLine("Managed to convert " + postcode + " to wijk " + wijk);
741 }
742 else
743 {
744 Console.ForegroundColor = ConsoleColor.Red;
745 Console.WriteLine("Did not manage to convert " + postcode);
746 }
747
748 Console.ForegroundColor = ConsoleColor.White;
749 if (allestraten.Count != 0)
750 {
751 for (int i = 0; i < allestraten.Count - 1; i++)
752 {
753 if (allestraten[i].straatnaam == straatnaam)
754 {
755 Console.ForegroundColor = ConsoleColor.Green;
756 Console.WriteLine("MATCH!");
757 Console.WriteLine("FOUND " + straatnaam + " ON ID " + allestraten[i].id.ToString());
758 System.Threading.Thread.Sleep(10);
759 return allestraten[i].id;
760 }
761 }
762 }
763
764
765 System.Threading.Thread.Sleep(30);
766 Console.ForegroundColor = ConsoleColor.Red;
767 Console.WriteLine("NO MATCH!");
768 allestraten.Add(new straatnamen(straatnaam, wijk, allestraten.Count));
769 Console.WriteLine("ADDING " + straatnaam + "TO DATABASE ON ID " + allestraten[allestraten.Count - 1].id.ToString());
770 Console.ForegroundColor = ConsoleColor.DarkCyan;
771 CON.Open();
772 COM.Connection = CON;
773 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";
774 try
775 {
776 COM.ExecuteNonQuery();
777 Console.WriteLine(COM.CommandText);
778 }
779 catch (Exception e)
780 {
781 }
782 CON.Close();
783 return allestraten[allestraten.Count - 1].id;
784 }
785
786 public int straatnaamtoint2(string straatnaam)
787 {
788 NpgsqlCommand COM = new NpgsqlCommand();
789 COM.Connection = CON;
790 GeneralParser GP = new GeneralParser();
791 for (int i = 0; i < allestraten.Count; i++)
792 {
793 if (allestraten[i].straatnaam.ToUpper().Contains("ZUIDWIJ"))
794 {
795 Console.WriteLine(allestraten[i].straatnaam);
796 }
797 if (allestraten[i].straatnaam.ToUpper().Replace(" ", "") == straatnaam.ToUpper().Replace(" ", ""))
798 {
799 Console.ForegroundColor = ConsoleColor.Green;
800 Console.WriteLine("MATCH!");
801 Console.WriteLine("FOUND " + straatnaam + " ON ID " + allestraten[i].id.ToString());
802 System.Threading.Thread.Sleep(10);
803 return allestraten[i].id;
804 }
805 }
806 return 0;
807 }
808
809 public string PostcodeNaarWijk(string postcode)
810 {
811 for (int i = 0; i < Postcodesenwijken.Count; i++)
812 {
813 if (Postcodesenwijken[i][0].Contains(postcode))
814 {
815 return Postcodesenwijken[i][1].ToString();
816 }
817 }
818
819 return "COULDNOTFINDWIJK";
820 }
821
822 public string findWijk(int straatint)
823 {
824 for (int i = 0; i < allestraten.Count; i++)
825 {
826 if (allestraten[i].id == straatint)
827 {
828 return allestraten[i].wijk;
829 }
830 }
831 return "";
832 }
833 }
834
835 public class straatnamen
836 {
837 public string straatnaam;
838 public string wijk;
839 public int id;
840 public straatnamen(string straatnaam, string wijk, int id)
841 {
842 this.straatnaam = straatnaam;
843 this.wijk = wijk;
844 this.id = id;
845 }
846 }
847}