Imports System.ComponentModel
Imports System.Configuration.Install
Imports System.Windows.Forms
Imports System.IO
Imports System.Reflection
Imports System.Data.SqlClient

Imports SystemCommon


Public Class VbDeployInstaller

    Dim mCnctMaster As New System.Data.SqlClient.SqlConnection
    Dim mCnctStringMaster As String = "Data Source=localhost\sqlexpress;Initial Catalog=master;Integrated Security=True;Connection Timeout=0;"

    Dim mCnctKB2010 As New System.Data.SqlClient.SqlConnection
    Dim mCnctStringKB2010 As String = "Data Source=localhost\sqlexpress;Initial Catalog=KB2010;Integrated Security=True;Connection Timeout=0;"


    Public Sub New()
        MyBase.New()

        'This call is required by the Component Designer.
        InitializeComponent()

        'Add initialization code after the call to InitializeComponent

    End Sub


    Private Sub EXEC_CREATE_SP()
        Dim Sql As String
        Sql = "EXEC	SP_CREATE_TABLE; EXEC	SP_INSERT_初期値;"
        Dim Command As New SqlClient.SqlCommand(Sql, mCnctKB2010)

        ' Initialize the connection, open it, and set it to the "master" database
        mCnctKB2010.ConnectionString = mCnctStringKB2010
        'Command.Connection.ChangeDatabase(DatabaseName)
        Try
            Command.Connection.Open()
            Command.ExecuteNonQuery()
        Finally
            ' Closing the connection should be done in a Finally block
            Command.Connection.Close()
        End Try
    End Sub


    Private Sub EXEC_DROP_ALL()

        Dim strTmp As String = ""
        If C05DBCommon.ChkSQLServer(".", "MSSQL$SQLEXPRESS", mCnctStringMaster, strTmp) = False Then
            Return
        End If

        Dim Sql As String
        Sql = "	if exists(select * from sys.databases where name = 'KB2010')      " + _
              "	 SELECT 1 as a                                                    " + _
              "	else                                                              " + _
              "	 SELECT 0 as a                                                    "

        Dim Command As New SqlClient.SqlCommand(Sql, mCnctMaster)
        mCnctMaster.ConnectionString = mCnctStringMaster

        Try

            Command.Connection.Open()
            Dim cReader As System.Data.SqlClient.SqlDataReader = Command.ExecuteReader()
            cReader.Read()

            If (cReader("a").ToString() = "0") Then
                Exit Sub
            End If

        Finally
            Command.Connection.Close()
        End Try

        '        scnMASTER.Open();

        '        SqlCommand cmd = new SqlCommand(strSQL, scnMASTER);
        '        SqlDataReader rdr = cmd.ExecuteReader();
        '        rdr.Read();
        '        if (rdr[0].ToString() == "1")
        '            bExist = true;
        'Else
        '            bExist = false;


        Sql = "" + _
              "	if exists (select * from sysobjects where id = object_id(N'C02RFM') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                                " + _
              "		DROP TABLE C02RFM;                                                                                                                         " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C03お勧め商品_顧客毎') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                  " + _
              "		DROP TABLE C03お勧め商品_顧客毎;                                                                                                           " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C04お勧め商品_RFM') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                     " + _
              "		DROP TABLE C04お勧め商品_RFM;                                                                                                              " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C05売れ筋商品_RFM') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                     " + _
              "		DROP TABLE C05売れ筋商品_RFM;                                                                                                              " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C06顧客動向表') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                         " + _
              "		DROP TABLE C06顧客動向表;                                                                                                                  " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C07顧客動向表_顧客') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                    " + _
              "		DROP TABLE C07顧客動向表_顧客;                                                                                                             " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C08購買回数') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE C08購買回数;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C09購買回数の内積') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                     " + _
              "		DROP TABLE C09購買回数の内積;                                                                                                              " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C10商品間の類似度') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                     " + _
              "		DROP TABLE C10商品間の類似度;                                                                                                              " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C11FM') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                     " + _
              "		DROP TABLE C11FM;                                                                                                              " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'C12TMP商品間の類似度') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                     " + _
              "		DROP TABLE C12TMP商品間の類似度;                                                                                                              " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H01販売履歴') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H01販売履歴;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H02コンタクト履歴') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H02コンタクト履歴;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H03操作履歴') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H03操作履歴;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H04家族構成') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H04家族構成;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H05顧客補足') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H05顧客補足;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H06PR履歴') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H06PR履歴;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H07プロモーション予定') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H07プロモーション予定;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H08プロモーション予定_対象広告') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H08プロモーション予定_対象広告;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H09プロモーション予定_対象商品') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H09プロモーション予定_対象商品;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H10プロモーション予定_対象顧客') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H10プロモーション予定_対象顧客;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H11プロモーション実績') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H11プロモーション実績;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'H12プロモーション実績_対象広告') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                           " + _
              "		DROP TABLE H12プロモーション実績_対象広告;                                                                                                                    " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'M02商品') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                               " + _
              "		DROP TABLE M02商品;                                                                                                                        " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'M03顧客') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                               " + _
              "		DROP TABLE M03顧客;                                                                                                                        " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'M04属性') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                               " + _
              "		DROP TABLE M04属性;                                                                                                                        " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'M05RFM計算条件') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                        " + _
              "		DROP TABLE M05RFM計算条件;                                                                                                                 " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'M06メールテンプレート') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                        " + _
              "		DROP TABLE M06メールテンプレート;                                                                                                                 " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'M07スタッフ') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                        " + _
              "		DROP TABLE M07スタッフ;                                                                                                                 " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'M08オープン可能画面') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                        " + _
              "		DROP TABLE M08オープン可能画面;                                                                                                                 " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'M09属性区分') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                        " + _
              "		DROP TABLE M09属性区分;                                                                                                                 " + _
              "	                                                                                                                                               " + _
              "	if exists (select * from sysobjects where id = object_id(N'M10広告') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                        " + _
              "		DROP TABLE M10広告;                                                                                                                 " + _
              "	                                                                                                                                               " + _
              "	                                                                                                                                               " + _
              " if exists (select * from sysobjects where id = object_id(N'SP_CREATE_TABLE') )                                                                    " + _
              "     DROP PROCEDURE SP_CREATE_TABLE;                                                                                                               " + _
              "                                                                                                                                                " + _
              " if exists (select * from sysobjects where id = object_id(N'SP_CREATE_INDEX') )                                                                    " + _
              "     DROP PROCEDURE SP_CREATE_INDEX;                                                                                                               " + _
              "                                                                                                                                                " + _
              " if exists (select * from sysobjects where id = object_id(N'SP_DROP_TABLE') )                                                                      " + _
              "     DROP PROCEDURE SP_DROP_TABLE;                                                                                                                 " + _
              "	                                                                                                                                               " + _
              " if exists (select * from sysobjects where id = object_id(N'SP_DROP_SP') )                                                                         " + _
              "     DROP PROCEDURE SP_DROP_SP;                                                                                                                    " + _
              "                                                                                                                                                " + _
              " if exists (select * from sysobjects where id = object_id(N'SP_INSERT_初期値') )                                                                   " + _
              "     DROP PROCEDURE SP_INSERT_初期値;                                                                                                              " + _
              "	                                                                                                                                               "

        'Sql = " EXEC DROP_TABLE; EXEC DROP_SP; "
        Command = New SqlClient.SqlCommand(Sql, mCnctKB2010)

        ' Initialize the connection, open it, and set it to the "master" database
        mCnctKB2010.ConnectionString = mCnctStringKB2010
        'Command.Connection.ChangeDatabase(DatabaseName)
        Try
            Command.Connection.Open()
            Command.ExecuteNonQuery()
        Finally
            ' Closing the connection should be done in a Finally block
            Command.Connection.Close()
        End Try
    End Sub


    Public Overrides Sub Install(ByVal stateSaver As System.Collections.IDictionary)

        MyBase.Install(stateSaver)

        'If IsExistDB() = False Then
        '    CreateDB()
        '    CreateSP_DROP_TABLE()
        '    CreateSP_CREATE_TABLE()
        '    CreateSP_INSERT_初期値()
        '    EXEC_SP()
        'End If

        'MessageBox.Show("Install")

    End Sub

    Public Overrides Sub Uninstall(ByVal savedState As System.Collections.IDictionary)

        MyBase.Uninstall(savedState)

        'MessageBox.Show("Uninstall")

        EXEC_DROP_ALL()

    End Sub

End Class
