using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using digioz;

public partial class Install : System.Web.UI.Page
{
  private static PortalSettings coPortalSettings = PortalSettingsManagement.GetPortalSettings();
  
    private void LoadDBSettings()
    {
        lblDBHost.Text = ConfigurationManager.AppSettings["dbhostkey"];
        lblDBName.Text = ConfigurationManager.AppSettings["dbnamekey"];
        lblDBUser.Text = ConfigurationManager.AppSettings["dbuserkey"];
        lblDBPass.Text = ConfigurationManager.AppSettings["dbpasskey"];
        lblDBType.Text = ConfigurationManager.AppSettings["DBType"];
    }

  private ArrayList GetListOfFiles(string lsFullPath)
  {
    ArrayList laFileList = new ArrayList();
    string lsFileName = string.Empty;

    laFileList.Clear();

    foreach (string sFile in System.IO.Directory.GetFiles(lsFullPath, "*.sql"))
    {
      lsFileName = new System.IO.FileInfo(sFile).Name;
      laFileList.Add(lsFileName);
    }

    return laFileList;
  }

  protected void Page_Load(object sender, EventArgs e)
  {
    // Load Configuration Settings ----------------------
    LoadDBSettings();
  }

    protected void btnInstall_Click(object sender, EventArgs e)
    {
        txtInstallProgress.Text = "";
        if (coPortalSettings.DBRelational)
        {
            string lsSchemaFolderPath = Server.MapPath("./schema/mssql/");
            ArrayList loSQLFiles = GetListOfFiles(lsSchemaFolderPath);
            IDataRelational loDB = DataFactory.GetDataObject(coPortalSettings);
            digioz.Utility loUtility = new digioz.Utility();
            string lsSQLFileContent = String.Empty;
            bool lbGoAhead = true;

            // Open the database connection --------------------------
            txtInstallProgress.Text = "// Opening the database connection ----------------------\n\n";            
            loDB.openConnection();

            // This indicates there was some error while processing the DB Command.
            if (!"".Equals(loDB.sErr))
            {
                lbGoAhead = false;
                txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
            }

            // Check new installation or upgrade
            if (lbGoAhead)
            {
                if (rbLstOption.SelectedValue == "New Installation")
                {
                    string lsRemovAll = this.GetRemoveAllSQL();
                    loDB.ExecDB(lsRemovAll);
                    if (loDB.sErr != "")
                    {
                        lbGoAhead = false;
                        txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
                    }

                    // Install All Tables First --------------------------
                    txtInstallProgress.Text = "// Installing Tables ----------------------\n\n";
                    if (lbGoAhead)
                    {
                        foreach (string lsFile in loSQLFiles)
                        {
                            if ((lsFile.IndexOf("tbl") == 0) && lbGoAhead)
                            {
                                try
                                {
                                    lsSQLFileContent = Utility.GetFileContents(lsSchemaFolderPath + lsFile);
                                    loDB.ExecDB(lsSQLFileContent);
                                    if (!"".Equals(loDB.sErr))//This indicates there was some error while processing the DB Command.
                                    {
                                        lbGoAhead = false;
                                        txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
                                    }
                                    if (lbGoAhead)
                                    {
                                        txtInstallProgress.Text += lsFile + "\n";
                                    }
                                    else
                                    {
                                        txtInstallProgress.Text += "Error Running File " + lsSchemaFolderPath + lsFile;
                                        break;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    txtInstallProgress.Text += "Error Running File " + ex.ToString();
                                }
                            }
                        }
                    }

                    // Installing Stored Procedures ----------------------
                    txtInstallProgress.Text += "// Installing Stored Procedures -----------\n\n";

                    if (lbGoAhead)
                    {
                        foreach (string lsFile in loSQLFiles)
                        {
                            if (lsFile.IndexOf("sp") == 0)
                            {
                                try
                                {
                                    lsSQLFileContent = Utility.GetFileContents(lsSchemaFolderPath + lsFile);
                                    loDB.ExecDB(lsSQLFileContent);
                                    if (!"".Equals(loDB.sErr))//This indicates there was some error while processing the DB Command.
                                    {
                                        lbGoAhead = false;
                                        txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
                                    }
                                    if (lbGoAhead)
                                    {
                                        txtInstallProgress.Text += lsFile + "\n";
                                    }
                                    else
                                    {
                                        txtInstallProgress.Text += "Error Running File " + lsSchemaFolderPath + lsFile;
                                        break;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    txtInstallProgress.Text += "Error Running File " + ex.ToString();
                                }
                            }
                        }
                    }

                    // Installing Stored Procedures ----------------------
                    txtInstallProgress.Text += "// Installing Inserts and Other ----------\n\n";
                    if (lbGoAhead)
                    {
                        foreach (string lsFile in loSQLFiles)
                        {
                            if (lsFile.IndexOf("sp") != 0 && lsFile.IndexOf("tbl") != 0)
                            {
                                try
                                {
                                    lsSQLFileContent = Utility.GetFileContents(lsSchemaFolderPath + lsFile);
                                    loDB.ExecDB(lsSQLFileContent);
                                    if (!"".Equals(loDB.sErr))//This indicates there was some error while processing the DB Command.
                                    {
                                        lbGoAhead = false;
                                        txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
                                    }
                                    if (lbGoAhead)
                                    {
                                        txtInstallProgress.Text += lsFile + "\n";
                                    }
                                    else
                                    {
                                        txtInstallProgress.Text += "Error Running File " + lsSchemaFolderPath + lsFile;
                                        break;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    txtInstallProgress.Text += "Error Running File " + ex.ToString();
                                }
                            }
                        }
                    }
                }
                else if (rbLstOption.SelectedValue == "Upgrade")
                {
                    lsSchemaFolderPath = Server.MapPath("./schema/mssql/upgrade/"); 
                    loSQLFiles = GetListOfFiles(lsSchemaFolderPath);
                    //string lsRemovAll = this.GetRemoveAllSQL();
                    //loDB.ExecDB(lsRemovAll);
                    if (loDB.sErr != "")
                    {
                        lbGoAhead = false;
                        txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
                    }

                    // Install All Tables First --------------------------
                    txtInstallProgress.Text = "// Installing Tables ----------------------\n\n";
                    if (lbGoAhead)
                    {
                        foreach (string lsFile in loSQLFiles)
                        {
                            if ((lsFile.IndexOf("tbl") == 0) && lbGoAhead)
                            {
                                try
                                {
                                    lsSQLFileContent = Utility.GetFileContents(lsSchemaFolderPath + lsFile);
                                    loDB.ExecDB(lsSQLFileContent);
                                    if (!"".Equals(loDB.sErr))//This indicates there was some error while processing the DB Command.
                                    {
                                        lbGoAhead = false;
                                        txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
                                    }
                                    if (lbGoAhead)
                                    {
                                        txtInstallProgress.Text += lsFile + "\n";
                                    }
                                    else
                                    {
                                        txtInstallProgress.Text += "Error Running File " + lsSchemaFolderPath + lsFile;
                                        break;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    txtInstallProgress.Text += "Error Running File " + ex.ToString();
                                }
                            }
                        }
                    }

                    // Dropping Changed Stored Procedures ----------------
                    txtInstallProgress.Text += "// Dropping Changed Stored Procedure ------\n\n";
                    try
                    {
                        lsSQLFileContent = Utility.GetFileContents(lsSchemaFolderPath + "Drop.sql");
                        loDB.ExecDB(lsSQLFileContent);
                        if (!"".Equals(loDB.sErr))
                        {
                            lbGoAhead = false;
                            txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
                        }
                        if (lbGoAhead)
                        {
                            txtInstallProgress.Text += "Drop.sql\n";
                        }
                        else
                        {
                            txtInstallProgress.Text += "Error Running File " + lsSchemaFolderPath + "Drop.sql";
                        }
                    }
                    catch (Exception ex)
                    {
                        txtInstallProgress.Text += "Error Running File " + ex.ToString();
                    }

                    // Installing Stored Procedures ----------------------
                    txtInstallProgress.Text += "// Installing Stored Procedures -----------\n\n";

                    if (lbGoAhead)
                    {
                        foreach (string lsFile in loSQLFiles)
                        {
                            if (lsFile.IndexOf("sp") == 0)
                            {
                                try
                                {
                                    lsSQLFileContent = Utility.GetFileContents(lsSchemaFolderPath + lsFile);
                                    loDB.ExecDB(lsSQLFileContent);
                                    if (!"".Equals(loDB.sErr))//This indicates there was some error while processing the DB Command.
                                    {
                                        lbGoAhead = false;
                                        txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
                                    }
                                    if (lbGoAhead)
                                    {
                                        txtInstallProgress.Text += lsFile + "\n";
                                    }
                                    else
                                    {
                                        txtInstallProgress.Text += "Error Running File " + lsSchemaFolderPath + lsFile;
                                        break;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    txtInstallProgress.Text += "Error Running File " + ex.ToString();
                                }
                            }
                        }
                    }

                    // Installing Stored Procedures ----------------------
                    txtInstallProgress.Text += "// Installing Inserts and Other ----------\n\n";
                    if (lbGoAhead)
                    {
                        foreach (string lsFile in loSQLFiles)
                        {
                            if (lsFile.IndexOf("sp") != 0 && lsFile.IndexOf("tbl") != 0 && lsFile.IndexOf("Drop") != 0)
                            {
                                try
                                {
                                    lsSQLFileContent = Utility.GetFileContents(lsSchemaFolderPath + lsFile);
                                    loDB.ExecDB(lsSQLFileContent);
                                    if (!"".Equals(loDB.sErr))//This indicates there was some error while processing the DB Command.
                                    {
                                        lbGoAhead = false;
                                        txtInstallProgress.Text += "// Error ----------------------\n\n" + loDB.sErr + "\n\n";
                                    }
                                    if (lbGoAhead)
                                    {
                                        txtInstallProgress.Text += lsFile + "\n";
                                    }
                                    else
                                    {
                                        txtInstallProgress.Text += "Error Running File " + lsSchemaFolderPath + lsFile;
                                        break;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    txtInstallProgress.Text += "Error Running File " + ex.ToString();
                                }
                            }
                        }
                    }
                }
            }
            loDB.closeConnection();
            txtInstallProgress.Text += "// Closed the database connection ----------------------\n\n";            
        }
        else // Non-relational
        {
            try
            {
                if (rbLstOption.SelectedValue == "New Installation")
                {
                    if (coPortalSettings.DBType == "XML")
                    {
                        NonRelationalHelper.RemoveExistingXML();
                    }
                    NonRelationalHelper loNonRelationalHelper = new NonRelationalHelper();
                    loNonRelationalHelper.Install();
                    txtInstallProgress.Text += loNonRelationalHelper.InstallationMessage;
                    }
                else if (rbLstOption.SelectedValue == "Upgrade")
                {
                }
            }
            catch (Exception ex)
            {
                txtInstallProgress.Text += "// Error ----------------------\n\n" + ex.Message + "\n\n";
            }
        }
    }
    private string GetRemoveAllSQL()
    {
        StringBuilder loSB = new StringBuilder();

        loSB.Append("declare @name varchar(100);");
        loSB.Append("declare @xtype char(1);");
        loSB.Append("declare @sqlstring nvarchar(1000);");

        loSB.Append("declare SPViews_cursor cursor for ");
        loSB.Append("SELECT sysobjects.name, sysobjects.xtype ");
        loSB.Append("FROM sysobjects ");
        loSB.Append("where sysobjects.xtype in ('P','U','V');");

        loSB.Append("open SPViews_cursor ");
        loSB.Append("fetch next from SPViews_cursor into @name, @xtype ");

        loSB.Append("while @@fetch_status = 0 ");
        loSB.Append("begin ");

        loSB.Append("if @xtype = 'U' ");
        loSB.Append("begin ");
        loSB.Append("set @sqlstring = 'drop table ' + @name;");
        loSB.Append("exec sp_executesql @sqlstring;");
        loSB.Append("set @sqlstring = ' ';");
        loSB.Append("end;");

        loSB.Append("if @xtype = 'P' ");
        loSB.Append("begin ");
        loSB.Append("set @sqlstring = 'drop procedure ' + @name;");
        loSB.Append("exec sp_executesql @sqlstring;");
        loSB.Append("set @sqlstring = ' ';");
        loSB.Append("end;");

        loSB.Append("if @xtype = 'V' ");
        loSB.Append("begin ");
        loSB.Append("set @sqlstring = 'drop view ' + @name;");
        loSB.Append("exec sp_executesql @sqlstring;");
        loSB.Append("set @sqlstring = ' ';");
        loSB.Append("end;");

        loSB.Append("fetch next from SPViews_cursor into @name, @xtype ");
        loSB.Append("end;");

        loSB.Append("close SPViews_cursor; ");
        loSB.Append("deallocate SPViews_cursor;");

        return loSB.ToString();
    }
}
