context: /*
      efisSQL - data base management tool
      Copyright (C) 2011  Lucian Voinescu
  
      This file is part of efisSQL
  
      efisSQL is free software: you can redistribute it and/or modify
      it under the terms of the GNU Lesser General Public License as published by
      the Free Software Foundation, either version 3 of the License, or
      (at your option) any later version.
  
      efisSQL 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 Lesser General Public License for more details.
  
      You should have received a copy of the GNU Lesser General Public License
      along with efisSQL. If not, see <http://www.gnu.org/licenses/>.
  */
  
  
  using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Text;
  using System.Windows.Forms;
  using System.Xml;
  using System.IO;
  using System.Data.SQLite;
  using System.Text.RegularExpressions;
  using DBMS.core;
  
  namespace DBMS.SQLite
  {
      public partial class ExportData : Form
      {
          private SQLiteConnection connection;
          private string dataBase;
          private string tableName;
          private string[] columns;
          private string filter;
          private DataGridView dgView;
          private enum ModExport { select, dgview };
          private ModExport mod;
          public ExportData()
          {
              InitializeComponent();
          }
  
          public ExportData(SQLiteConnection connection, string dataBase, string tableName, string [] columns)
          {
              this.connection = connection;
              this.dataBase = dataBase;
              this.tableName = tableName;
              this.columns = columns;
              InitializeComponent();
              for (int i = 0; i < columns.Length; i++)
              {
                  ListViewItem it = new ListViewItem(columns[i]);
                  it.Checked = true;
                  listView1.Items.Add(it);
                  it.Selected = true;
              }
              mod = ModExport.select;
          }
  
          public ExportData(DataGridView dgView, string[] cols)
          {
              this.dgView = dgView;
              mod = ModExport.dgview;
              InitializeComponent();
              this.columns = cols;
              for (int i = 0; i < cols.Length; i++)
              {
                  ListViewItem it = new ListViewItem(cols[i]);
                  it.Checked = true;
                  listView1.Items.Add(it);
                  it.Selected = true;
              }
              
          }
  
          private string EscapeString(string s)
          {
              Regex r = new Regex("\\\\+");
              MatchCollection matches = r.Matches(s,0);
              foreach (Match m in matches)
              {
                  char[] special = new char[] { 'n', 'r', 't', 'b' };
                  if(m.Value.Length%2!=0)
                  {
                      int next = m.Index+m.Value.Length;
                      if (next < s.Length)
                      {
                          if (Array.IndexOf(special, s[next]) < 0)
                              s = s.Insert(m.Index + m.Value.Length, m.Value);
                      }
                      else
                          s = s.Insert(m.Index + m.Value.Length, m.Value);
                  }
              }
              s = s.Replace("\'", "\\'");
              return s;
          }
  
  
          private string FormatFieldByType(object data)
          {
              Type type =data.GetType();
              if (type == typeof(byte[]))
                  return System.Text.Encoding.Default.GetString((byte[])data);
              else
                  if (data == DBNull.Value)
                      return "(null)";
                  else
                      if (type == typeof(DateTime))
                      {
                          DateTime t = (DateTime)data;
                          return t.ToString("yyyy-MM-dd HH:mm:ss");
                      }
                      else
                          return data.ToString();
          }
  
          private void ExportToCsv(string fileName, string[] cols, List<SqlDataGridViewRow> rows, int[] indexes)
          {
              string lineEnd = textBox4.Text.Replace("\\n", "\n");
              lineEnd = lineEnd.Replace("\\r", "\r");
              lineEnd = lineEnd.Replace("\\t", "\t");
              string fieldEnd = textBox1.Text.Replace("\\n", "\n");
              fieldEnd = fieldEnd.Replace("\\r", "\r");
              fieldEnd = fieldEnd.Replace("\\t", "\t");
              string fieldEnclosed = textBox2.Text.Replace("\\n", "\n");
              fieldEnclosed = fieldEnclosed.Replace("\\r", "\r");
              fieldEnclosed = fieldEnclosed.Replace("\\t", "\t");
              try
              {
                  switch (mod)
                  {
                      case ModExport.select:
                          fileName = fileName.Replace('\\', '/');
                          string s = "select ";
                          for (int i = 0; i < indexes.Length; i++)
                          {
                              s += "`" + cols[indexes[i]] + "`,";
                          }
                          s = s.Remove(s.Length - 1, 1);
                          s += " from `" +dataBase +"`.`"+ tableName+ "`";
                          SQLiteCommand cmd = new SQLiteCommand(s, connection);
                          using (StreamWriter writter = new StreamWriter(fileName))
                          {
                              SQLiteDataReader reader = cmd.ExecuteReader();
                              object[] temp = new object[reader.FieldCount];
                              try
                              {
                                  while (reader.Read())
                                  {
                                      reader.GetValues(temp);
                                      for (int j = 0; j < indexes.Length; j++)
                                      {
                                          string value = FormatFieldByType(temp[j]);
                                          writter.Write(fieldEnclosed);
                                          writter.Write(value);
                                          writter.Write(fieldEnclosed);
                                          if (j < indexes.Length - 1)
                                              writter.Write(fieldEnd);
                                      }
                                      writter.Write(lineEnd);
                                  }
  
                              }
                              catch (Exception exception)
                              {
                              }
                              finally
                              {
                                  reader.Close();
                              }
                              writter.Close();
                          }
                          break;
                      case ModExport.dgview:
                          TextWriter  tw = new StreamWriter(fileName);
                          for (int i = 0; i < rows.Count; i++)
                          {
                              SqlDataGridViewRow row = rows[i];
                              for (int j = 0; j < indexes.Length; j++)
                              {
                                  object data = row.Data[indexes[j]];
                                  string value = FormatFieldByType(data);
                                  value = value.Replace("\\", textBox3.Text+"\\");
                                  tw.Write(fieldEnclosed);
                                  tw.Write(value);
                                  tw.Write(fieldEnclosed);
                                  tw.Write(fieldEnd);
                              }
                              tw.Write(lineEnd);
                          }
                          tw.Close();
                          break;
  
                  }
  
  
              }
              catch (Exception ex)
              {
                  MessageBox.Show(ex.Message);
              }
          }
  
          private void ExportToXml(string fileName, string [] cols, List<SqlDataGridViewRow> rows, int[] indexes)
          {
              XmlDocument doc = new XmlDocument();
              switch (mod)
              {
                  case ModExport.select:
                      string s = "select ";
                      for(int i=0;i<indexes.Length;i++)
                      {
                          s +="`" + cols[indexes[i]] + "`,";
                      }
                      s = s.Remove(s.Length - 1, 1) + " from `" +dataBase +"`.`" + tableName+"`";
                      using(SQLiteCommand cmd = new SQLiteCommand(s,connection))
                      {
                          SQLiteDataReader reader = cmd.ExecuteReader(); 
                          object[] temp = new object[reader.FieldCount];
                          XmlNode nod = doc.CreateElement("data");
                          try
                          {
                              while (reader.Read())
                              {
                                  temp = new object[reader.FieldCount];
                                  reader.GetValues(temp);
                                  XmlNode n = doc.CreateElement("row");
                                  for (int j = 0; j < temp.Length; j++)
                                  {
                                      XmlNode nc = doc.CreateElement(cols[j]);
                                      nc.InnerText = FormatFieldByType(temp[j]);
                                      n.AppendChild(nc);
                                  }
                                  nod.AppendChild(n);
                              }
                          }
                          catch (Exception exception)
                          {
                          }
                          finally
                          {
                              reader.Close();
                          }
                          doc.AppendChild(nod);
                          doc.Save(fileName);
                      }
                      break;
                  case ModExport.dgview:
                      XmlNode nod2 = doc.CreateElement("data");
                      for(int i=0;i<rows.Count;i++)
                      {
                          SqlDataGridViewRow row = rows[i];
                          XmlNode n = doc.CreateElement("row");
                          for (int j = 0; j < indexes.Length; j++)
                          {
  
gt:                             XmlNode nc = doc.CreateElement(cols[indexes[j]]);