Imports EnvDTE
Imports EnvDTE80
Imports Microsoft.VisualBasic
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Text
Imports System.IO
Imports System.Collections
Imports System.Collections.Generic
Imports System.Diagnostics
Imports System.Xml
Imports System
Imports Microsoft.Win32

Public Class C
	Implements VisualCommanderExt.ICommand


    Sub Run(DTE As EnvDTE80.DTE2, package As Microsoft.VisualStudio.Shell.Package) Implements VisualCommanderExt.ICommand.Run
        Dim solution As Solution = DTE.Solution
        For Each proj As Project In solution.Projects
            If proj.Name = "AMODITDBScripts" Then
                GenerateSQL(proj)
                Return
            End If
        Next
    End Sub




    Sub GenerateSQL(ByVal proj As Project)
        Dim conn As MySqlConnection
	 conn = Nothing
        Try
            conn = New MySqlConnection(GetConnectionString())
            conn.Open()
            Dim cmd As MySqlCommand = New MySqlCommand("select * from information_schema.columns " +
"where table_schema = 'amod' and table_name in (SELECT table_name FROM information_schema.tables where table_schema = 'amod' and table_type='BASE TABLE') "+
"and column_name not like 'casevarchar%' and column_name not like 'casetext%' and column_name not like 'casenumber%' and column_name not like 'casedatetime%' "+
"order by table_name,ordinal_position;", conn)
            Dim rdr As MySqlDataReader
            rdr = cmd.ExecuteReader()
            Dim sb As StringBuilder = New StringBuilder()
            While rdr.Read()
                Dim table_name As String = Convert.ToString(rdr("table_name"))
                Dim column_name As String = Convert.ToString(rdr("column_name"))
                Dim column_type As String = Convert.ToString(rdr("column_type"))
                Dim column_default As String = Convert.ToString(rdr("column_default"))

                sb.AppendLine(table_name+";"+column_name+";"+column_type+";"+column_default)
            End While
            rdr.Close()
            Dim res As String = sb.ToString()
            Dim sw As StreamWriter = New StreamWriter("C:\temp\columns.txt", False)
            sw.Write(res)
            sw.Flush()
            sw.Close()
            Dim itm As ProjectItem = proj.ProjectItems.Item("inserts")
            AddFile(itm, "columns.txt", "C:\temp\columns.txt")
  
            MsgBox("Gotowe")
        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            conn.Close()
        End Try

    End Sub

    Function FormatSQL(ByVal SqlText As String) As String
        Return SqlText.Replace("'", "''").Replace(vbCrLf, "\n").Replace(vbCr, "\n").Replace(vbLf, "\n")
    End Function

    Sub AddFile(ByVal itm As ProjectItem, ByVal fileName As String, ByVal filePath As String)
        For Each file As ProjectItem In itm.ProjectItems
            If file.Name = fileName Then
                file.Delete()
            End If
        Next
        Dim pItem As ProjectItem = itm.ProjectItems.AddFromFileCopy(filePath)
        pItem.Properties.Item("CopyToOutputDirectory").Value = 2
        pItem.Properties.Item("BuildAction").Value = 3
    End Sub

    Function GetRegistryValue(ByVal RegCode As String, ByVal RegPath As String, ByVal RegKey As String) As String
        If (RegCode = String.Empty Or RegPath = String.Empty Or RegKey = String.Empty) Then
            Return ""
        End If
        Try
            Dim rk_ret_value As RegistryKey = Nothing
            Select Case RegCode
                Case "HKCU"
                    rk_ret_value = Registry.CurrentUser.OpenSubKey(RegPath)
                Case "HKLM"
                    rk_ret_value = Registry.LocalMachine.OpenSubKey(RegPath)
                Case Else
                    Return ""
            End Select
            If rk_ret_value Is Nothing Then
                Return ""
            End If
            Return rk_ret_value.GetValue(RegKey).ToString()
        Catch
            Return Nothing
        End Try
    End Function

    Function GetConnectionString() As String
        Dim cs As String
        cs = GetRegistryValue("HKLM", "SOFTWARE\\Datacom Software\\AMOD", "ConnectionString")
        Return cs
    End Function


End Class