context: /*
   * Copyright 2017 Nathan Ell.
   * 
   * This file is a part of PsycStudy.
   * 
   * PsycStudy is free software: you can redistribute ir and/or modify
   * it under the terms of the GNU General Public License as published by
   * the Free Software Foundation, either version 3 of the License, or
   * (at your option) any later version.
   * 
   * PsycStudy is distributed in the hope that it will be useful,
   * but WITHOUT ANY WARRANTY; without even the implied warranty of
   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
   * GNU General Public License for more details.
   * 
   * You should have received a copy of the GNU General Public License
   * along with this program. If not, see <http://www.gnu.org/licenses/>.
   */
  
  
  using System;
  using System.Collections.Generic;
  using System.Data.SQLite;
  using System.IO;
  using System.Windows;
  using System.Windows.Media;
  using Microsoft.Win32;
  using Newtonsoft.Json;
  using OfficeOpenXml;
  
  namespace ResponseManager
  {
      /// <summary>
      /// Interaction logic for MainWindow.xaml
      /// </summary>
      public partial class MainWindow
      {
          private string _databaseFilename;
          private string _databasePassword;
          public MainWindow()
          {
              InitializeComponent();
          }
  
          private void LoadDBButton_Click(object sender, RoutedEventArgs e)
          {
              var ofd = new OpenFileDialog()
              {
                  FileName = "Responses",
                  DefaultExt = ".db",
                  Filter = "SQLite3 Databases|*.db"
              };
              var result = ofd.ShowDialog();
  
              if (result == true)
              {
                  var passBox = new PasswordDialog {Owner = this};
                  if (passBox.ShowDialog() == true)
                  {
                      _databasePassword = passBox.Password;
                      _databaseFilename = ofd.FileName;
                      DBFname.Content = _databaseFilename;
                      DBFname.Foreground = Brushes.Black;
                      AddRecordsButton.IsEnabled = true;
                      ExportButton.IsEnabled = true;
                  }
                  else
                  {
                      DBFname.Content = "Error";
                      DBFname.Foreground = Brushes.Red;
                  }
              }
              else
              {
                  DBFname.Content = "Error";
                  DBFname.Foreground = Brushes.Red;
              }
  
          }
  
          private void AddRecordsButton_Click(object sender, RoutedEventArgs e)
          {
              try
              {
                  if (_databaseFilename == null || _databasePassword == null)
                  {
                      return;
                  }
                  var responses = new List<Response>();
                  var ofd = new OpenFileDialog()
                  {
                      FileName = "Response",
                      DefaultExt = ".json",
                      Filter = "JSON Documents|*.json",
                      Multiselect = true
                  };
                  var result = ofd.ShowDialog();
                  if (result == true)
                  {
                      foreach (var file in ofd.FileNames)
                      {
                          using (var f = File.OpenText(file))
                          {
                              var serializer = new JsonSerializer();
                              var response = (Response) serializer.Deserialize(f, typeof(Response));
                              responses.Add(response);
                          }
                      }
                  }
  
                  var connectionString = @"Data Source=" + _databaseFilename
                                         + ";Password=" + _databasePassword + ";";
  
                  var connection = new SQLiteConnection(connectionString);
                  connection.Open();
  
                  foreach (var response in responses)
                  {
                      using (var cmd = connection.CreateCommand())
                      {
                          cmd.CommandText =
                              "INSERT INTO Responses (SurveyDate, DisplayedOpinion, WrittenOpinion," +
                              " HighSchoolAverage, HighSchoolExperience," +
                              " DisplayedHighSchoolExperience," +
                              " L1, L2, L3, L4, L5, L6, L7, L8, L9A, L9B, L9C, L9D," +
                              " L9E, L9F, L10, L11, L12, L13, L14, L15, L16, L17, L18A, L18B, L18C, L18D, L18E, L18F, D1A, D1B, D1C, D2, D3, D4, D5, D6) " +
                              "VALUES (@0,@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)";
  /*                    cmd.Parameters.Add(String, response.DisplayedOpinion));
                      cmd.Parameters.Add(String, response.WrittenOpinion));
                      cmd.Parameters.Add(UInt16, response.HighSchoolAverage));
                      cmd.Parameters.Add(String, response.HighSchoolExperience));
                      cmd.Parameters.Add(String, response.DisplayedHighSchoolExperience));
                      cmd.Parameters.Add(UInt16, response.L1));
                      cmd.Parameters.Add(UInt16, response.L2));
                      cmd.Parameters.Add(UInt16, response.L3));
                      cmd.Parameters.Add(UInt16, response.L4));
                      cmd.Parameters.Add(UInt16, response.L5));
                      cmd.Parameters.Add(UInt16, response.L6));
                      cmd.Parameters.Add(UInt16, response.L7));
                      cmd.Parameters.Add(UInt16, response.L8));
                      cmd.Parameters.Add(UInt16, response.L9A));
                      cmd.Parameters.Add(UInt16, response.L9B));
                      cmd.Parameters.Add(UInt16, response.L9C));
                      cmd.Parameters.Add(UInt16, response.L9D));
                      cmd.Parameters.Add(UInt16, response.L9E));
                      cmd.Parameters.Add(UInt16, response.L9F));
                      cmd.Parameters.Add(UInt16, response.D1A));
                      cmd.Parameters.Add(UInt16, response.D1B));
                      cmd.Parameters.Add(UInt16, response.D1C));
                      cmd.Parameters.Add(UInt16, response.D2));
                      cmd.Parameters.Add(String, response.D3));
                      cmd.Parameters.Add(String, response.D4));*/
                          cmd.Parameters.AddWithValue("@0", response.DateTime);
                          cmd.Parameters.AddWithValue("@1", response.DisplayedOpinion);
                          cmd.Parameters.AddWithValue("@2", response.WrittenOpinion);
                          cmd.Parameters.AddWithValue("@3", response.HighSchoolAverage);
                          cmd.Parameters.AddWithValue("@4", response.HighSchoolExperience);
                          cmd.Parameters.AddWithValue("@5", response.DisplayedHighSchoolExperience);
                          cmd.Parameters.AddWithValue("@6", response.L1);
                          cmd.Parameters.AddWithValue("@7", response.L2);
                          cmd.Parameters.AddWithValue("@8", response.L3);
                          cmd.Parameters.AddWithValue("@9", response.L4);
                          cmd.Parameters.AddWithValue("@10", response.L5);
                          cmd.Parameters.AddWithValue("@11", response.L6);
                          cmd.Parameters.AddWithValue("@12", response.L7);
                          cmd.Parameters.AddWithValue("@13", response.L8);
                          cmd.Parameters.AddWithValue("@14", response.L9A);
                          cmd.Parameters.AddWithValue("@15", response.L9B);
                          cmd.Parameters.AddWithValue("@16", response.L9C);
                          cmd.Parameters.AddWithValue("@17", response.L9D);
                          cmd.Parameters.AddWithValue("@18", response.L9E);
                          cmd.Parameters.AddWithValue("@19", response.L9F);
                          cmd.Parameters.AddWithValue("@20", response.L10);
                          cmd.Parameters.AddWithValue("@21", response.L11);
                          cmd.Parameters.AddWithValue("@22", response.L12);
                          cmd.Parameters.AddWithValue("@23", response.L13);
                          cmd.Parameters.AddWithValue("@24", response.L14);
                          cmd.Parameters.AddWithValue("@25", response.L15);
                          cmd.Parameters.AddWithValue("@26", response.L16);
                          cmd.Parameters.AddWithValue("@27", response.L17);
                          cmd.Parameters.AddWithValue("@28", response.L18A);
                          cmd.Parameters.AddWithValue("@29", response.L18B);
                          cmd.Parameters.AddWithValue("@30", response.L18C);
                          cmd.Parameters.AddWithValue("@31", response.L18D);
                          cmd.Parameters.AddWithValue("@32", response.L18E);
                          cmd.Parameters.AddWithValue("@33", response.L18F);
                          cmd.Parameters.AddWithValue("@34", response.D1A);
                          cmd.Parameters.AddWithValue("@35", response.D1B);
                          cmd.Parameters.AddWithValue("@36", response.D1C);
                          cmd.Parameters.AddWithValue("@37", response.D2);
                          cmd.Parameters.AddWithValue("@38", response.D3);
                          cmd.Parameters.AddWithValue("@39", response.D4);
                          cmd.Parameters.AddWithValue("@40", response.D5);
                          cmd.Parameters.AddWithValue("@41", response.D6);
                          cmd.ExecuteNonQuery();
                      }
                  }
  
                  connection.Close();
                  RecordsLabel.Content = "Response(s) saved.";
              }
              catch (Exception ex)
              {
                  RecordsLabel.Content = "Error (check your password).";
              }
          }
  
          private void ExportButton_Click(object sender, RoutedEventArgs e)
          {
              try
              {
                  var connectionString = @"Data Source=" + _databaseFilename
                                         + ";Password=" + _databasePassword + ";";
  
                  var connection = new SQLiteConnection(connectionString);
                  connection.Open();
  
                  var responses = new List<Response>();
  
                  using (var cmd = connection.CreateCommand())
                  {
                      cmd.CommandText = "SELECT * FROM Responses";
                      var reader = cmd.ExecuteReader();
                      while (reader.Read())
                      {
                          var response = new Response
                          {
  
gt:                             ID = int.Parse(reader["ID"].ToString()),