namespace WorkflowActivity.Scripting.SDKcreateReport
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using System.IO;
    using Laserfiche.RepositoryAccess;

    /// <summary>
    /// Provides one or more methods that can be run when the workflow scripting activity is performed.
    /// </summary>
    public class Script1 : RAScriptClass110
    {
        /// <summary>
        /// This method is run when the activity is performed.
        /// </summary>
        protected override void Execute()
        {
            // Write your code here. The BoundEntryInfo property will access the entry, RASession will get the Repository Access session
            string SqlServer =   @"<ServerName>";
            string SqlLogin =    @"<Username>";
            string SqlPassword = @"<Password>";
            string SqlDatabase = @"<DatabaseName>";

            string SqlViewToExport  = GetTokenValue("ExportView").ToString();    
            string SqlWhereClause   = GetTokenValue("WhereClause").ToString();   
            string SqlStopColumn    = GetTokenValue("StopColumn").ToString();    
            string ReportName       = GetTokenValue("EntryName").ToString();     
            string ExportFilePath = "";

            try
            {   ExportFilePath = String.Format(@"<rootfolder>\{0} {1}.csv", ReportName, DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss"));
                string sqlQueryGetViewDefinition = String.Format(@"SELECT v.TABLE_NAME, c.COLUMN_NAME FROM INFORMATION_SCHEMA.VIEWS v " +
                    "JOIN INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_SCHEMA = v.TABLE_SCHEMA AND c.TABLE_NAME = v.TABLE_NAME " +
                    "WHERE UPPER(V.TABLE_NAME) = UPPER('{0}');", SqlViewToExport);
                string connectionString = String.Format(@"Server={0};Database={1};User Id={2};Password={3};", SqlServer, SqlDatabase, SqlLogin, SqlPassword);
                //SetTokenValue("ShowExportFilePath",ExportFilePath);
                //SetTokenValue("ShowsqlQueryGetViewDefinition",sqlQueryGetViewDefinition);
                // DesignTools.Watch("File Path", ExportFilePath);

                List<string> columns = new List<string>();

                using (SqlConnection connection = new SqlConnection(connectionString))
                {   SqlCommand command = new SqlCommand(sqlQueryGetViewDefinition, connection);
                    connection.Open();
                    SqlDataReader readerDefinition = command.ExecuteReader();
                    try
                    {   while (readerDefinition.Read())
                        {   //columns.Add(string.Format(@"[{0}]",readerDefinition["COLUMN_NAME"].ToString()));
                            columns.Add(readerDefinition["COLUMN_NAME"].ToString());
                        }
                    }
                    finally
                    {   readerDefinition.Close(); // Always call Close when done reading.
                    }

                    string sqlQueryGetValues = String.Format(@"SELECT {0} FROM {1} {2};", String.Join(",", columns), SqlViewToExport, SqlWhereClause);
                    //SetTokenValue("ShowsqlQueryGetValues",sqlQueryGetValues);
                    command.CommandText = sqlQueryGetValues;

                    using (var w = new StreamWriter(ExportFilePath))
                    // Write the column header row to the CSV file
                    {   var header = String.Empty;
                        //var colname = string.Empty;
                        foreach (string columnName in columns)
                        {   //colname = columnName.Replace("[","").Replace("]","");
                            if (String.Compare(columnName,SqlStopColumn) == 0) { break; }
                            if (!String.IsNullOrWhiteSpace(header)) header += ","; // Skip for the first column
                            header += String.Format("\"{0}\"",columnName.Replace("_"," "));
                        }
                        w.WriteLine(header);
                        w.Flush();

                        // Execute query and write column values to the CSV file
                        SqlDataReader readerValues = command.ExecuteReader();
                        try
                        {   while (readerValues.Read())
                            {   var line = String.Empty;
                                foreach (string columnName in columns)
                                {   //colname = columnName.Replace("[","").Replace("]","");
                                    if (String.Compare(columnName,SqlStopColumn) == 0) {break;} // Stop when we get to the Batch column
                                    if (!String.IsNullOrWhiteSpace(line)) line += ",";       // Skip for the first column
                                    line += String.Format("\"{0}\"", readerValues[columnName].ToString());
                                }
                                w.WriteLine(line);
                                w.Flush();
                            }
                        }
                        finally
                        {   // Always call Close when done reading.
                            readerValues.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {   WorkflowApi.TrackError(ex); }
            SetTokenValue("ExportFilePath",ExportFilePath);
        }
    }
}