using System;
using System.Text;
using System.Collections;
using System.Drawing;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;

using System.Data.SqlClient;

using Excel = Microsoft.Office.Interop.Excel;

namespace Interaction
{
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	class Program
	{
		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main(string[] args)
		{
			//ArrayList arr = new ArrayList();
			//string.Join( ", ", (string[])arr.ToArray( ) );

			// Excel object references.
			Excel.Application m_objExcel = null;
			Excel.Workbooks m_objBooks = null;
			Excel._Workbook m_objBook = null;
			Excel.Sheets m_objSheets = null;
			Excel._Worksheet m_objSheet = null;
			Excel.Range m_objRange = null;
			Excel.Font m_objFont = null;

			// Frequenty-used variable for optional arguments.
			object m_objOpt = System.Reflection.Missing.Value;

			// Paths used by the sample code for accessing and storing data.
			object m_strSampleFolder = Environment.CurrentDirectory + System.IO.Path.DirectorySeparatorChar;

			/* Invalid if the number of params passed is less than 4 */
			if (args.Length < 3)
			{
				Console.WriteLine("Invalid number of arguments passed.");
				Console.WriteLine("Syntax: Interaction.exe <server> <database_name> <user_id> <password>");
				Console.ReadLine();
				return;
			}

			/* Get the connection string params from the command line */
			/*
			string server = args[0];
			string catalog = args[1];
			string user_id = args[2];
			string password = string.Empty;
			if( args.Length == 4 )
				password = args[3];
			*/

			string server = "Agni";
			string catalog = "Test";
			string user_id = "sa";
			string password = "sa";

			/* Build the connection string */
			string constr = string.Format("Data Source={0}; Initial Catalog={1}; User ID={2}; Password={3}", server, catalog, user_id, password);
			SqlConnection con = new SqlConnection();
			con.ConnectionString = constr;

			try
			{	
				/* Open connection to the database */
				con.Open();

				/* Read all the SQL files from the current folder and Generate Spreadsheets for each SQL */
				string []files = Directory.GetFiles( Environment.CurrentDirectory, "*.txt" );

				foreach( string file in files )
				{
					/* File name */
					FileInfo fi = new FileInfo( file );
					string excelName = fi.Name.Replace( ".sql", "" ) + ".xls";

					/* Hold the query */
					StringBuilder sb = new StringBuilder();

					/* Open the file stream */
					FileStream  fs = File.OpenRead( file );

					/* Open the stream reader */
					StreamReader sr = new StreamReader(fs);

					/* Read until you hit the end of file */
					while( sr.Peek() > -1 )
					{
						sb.Append( sr.ReadLine( ) );
					}

					/* Close the streams */
					sr.Close();
					fs.Close(); 

					/* Create the command object */
					SqlCommand cmd = new SqlCommand(sb.ToString(), con);

					/* Read the output from the database */
					SqlDataReader objReader = cmd.ExecuteReader();

					// Create the FileStream and StreamWriter object to write 
					// the recordset contents to file.
					fs = new System.IO.FileStream(
						m_strSampleFolder + "Output.txt", System.IO.FileMode.Create);
					System.IO.StreamWriter sw = new System.IO.StreamWriter(
						fs, System.Text.Encoding.Unicode);

					int count = objReader.FieldCount;

					// Write the field names (headers) as the first line in the text file.
					for (int i = 0; i < objReader.FieldCount; i++)
						sw.Write(objReader.GetName(i) + "\t");

					sw.WriteLine();

					/* Eliminate Duplicates in the result set */
					ArrayList []columns = new ArrayList[count];

					/* Company */
					string company = string.Empty;

					// Write the columns in the recordset to a tab-delimited text file
					while( objReader.Read( ) )
					{
						if( company == string.Empty )
							company = objReader.GetValue(0).ToString( );
						/* 
						 * Write the values to the file only if the company is not 
						 * null and company != current company 
						 */
						if( company != objReader.GetValue(0).ToString( ) )
						{
							for( int j = 0 ; j < count ; j++ )
							{
								string col = string.Empty;
								col = string.Join( ", ", (string [])( columns[j].ToArray( typeof(string) ) ) );
								
								if( col.Length > 2 && col[0] == ',' )
									col = col.Substring( 2 );

								sw.Write( col );
								sw.Write( "\t" );

								columns[j].Clear();
							}

							sw.WriteLine();

							company = objReader.GetValue(0).ToString( );
						}

						for (int i = 0; i < objReader.FieldCount; i++)
						{
							if( columns[i] == null )
								columns[i] = new ArrayList();

							if (!objReader.IsDBNull(i))
							{
								if( columns[i].Contains( objReader.GetValue(i).ToString() ) == false )
									columns[i].Add( objReader.GetValue(i).ToString() );
							}
							else
							{
								if( columns[i].Contains( "" ) == false )
									columns[i].Add( "" );
							}
						}
					}

					for( int j = 0 ; j < count ; j++ )
					{
						string col = string.Empty;
						col = string.Join( ", ", (string [])( columns[j].ToArray( typeof(string) ) ) );

						if( col.Length > 2 && col[0] == ',' )
							col = col.Substring( 2 );

						sw.Write( col.Trim( ) );
						sw.Write( "\t" );

						columns[j].Clear();
					}

					sw.WriteLine();

					sw.Flush();	// Write the buffered data to the filestream.

					// Close the FileStream.
					fs.Close();

					// Close the reader and the connection.
					objReader.Close();

					// Open the text file in Excel.
					m_objExcel = new Excel.Application();
					m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
					m_objBooks.OpenText(m_strSampleFolder + "Output.txt", Excel.XlPlatform.xlWindows, 1,
						Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
						false, true, false, false, false, false, m_objOpt, m_objOpt,
						m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

					m_objSheets = (Excel.Sheets)m_objBooks[1].Worksheets;
					m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
					// Apply bold to cells A1:B1.
					m_objRange = m_objSheet.get_Range("A1", m_objOpt);
					m_objRange = m_objRange.get_Resize(1, count);
					m_objFont = m_objRange.Font;
					m_objFont.Bold = true;

					m_objBook = m_objExcel.ActiveWorkbook;

					// Save the text file in the typical workbook format and quit Excel.
					m_objBook.SaveAs(m_strSampleFolder + excelName, Excel.XlFileFormat.xlWorkbookNormal,
						m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,
						m_objOpt, m_objOpt, m_objOpt);
					m_objBook.Close(false, m_objOpt, m_objOpt);
					m_objExcel.Quit();

					/* Clean up */
					System.IO.File.Delete(m_strSampleFolder + "Output.txt");
				}
			}
			catch (Exception ex)
			{
				if (null != m_objExcel)
					m_objExcel.Quit();
				Console.WriteLine(ex.Message);
			}

			if (con.State == System.Data.ConnectionState.Open)
				con.Close();
		}
	}
}