/tab2sql/Classes/Convertors/list2csv.cs

using System;

using System.Text;

using System.Collections;



namespace Tab2Sql

{

    /// <summary>

    /// basic class to convert a list of values (separated by line breaks) and convert them into a sql friendly string - or similar.

    /// 

    /// </summary>

    public class List2Csv

    {



      

        /// <summary>

        /// Produces an output of the form "AND something in ('1','2','3','4','5'). INPUT MUST BE LINE BREAK SEPARATED.

        /// Will show summary statistics if desired. (Although they are suppressed if the string is 400k characters or more in length)

        /// </summary>

        /// <param name="input">Line delimited list</param>

        /// <param name="showStats">Do you want some "interesting" statistics on your input?</param>

        /// <param name="sortOption">How should output be sorted?</param>

        /// <returns></returns>

        public static string List2InStatement(string input,  bool showStats, List2CSVSortBy sortOption)

        {

            

            if (input.Trim().Length > 400000) { showStats = false; }

            int totalNumberOfElements = 0;

            int uniqueNumberoOfElements = 0;

            int longestLine = 0;

            string longestLineIs = "";

            bool skipFirstLine = false;

            //HACK : hard coded value

            int DesiredWidth = 3; //in columns, not characters



            var csvString = new StringBuilder();

            string columnName = "something";

            string[] tmpString = input.Split('\n');

            totalNumberOfElements = tmpString.Length;

            if (char.IsLetter(tmpString[0].ToCharArray()[0]))

            {

                columnName = tmpString[0].Trim();

                skipFirstLine = true;

                totalNumberOfElements--;

            }

            csvString.AppendLine("WHERE 1=1 ");

            csvString.AppendLine(" AND ["+columnName+"] IN \n\t(");

            

            ArrayList myList = new ArrayList();

 

            

            bool allNumerics=true;

            var termLengths = new int[tmpString.Length];



            int i = 0; // elements

            int k = 0; // unique elements



            foreach (string lines in tmpString)

            {

                if (skipFirstLine == true && i == 0)

                {

                    //do nothing

                }

                else

                {

                    if (!myList.Contains(lines))

                    {

                        k++;

                        if(allNumerics==true)

                        {

                            if (DGen.DTypes.IsNumeric(lines) != true || (lines[0] == '0' && lines[1] == '0'))

                            {

                                allNumerics = false;

                            }

                        }



                        myList.Add(lines);

                    }

                }

                i++;

            }

            myList.Sort(new WhichIsLonger(sortOption));

            uniqueNumberoOfElements = k;

            string[] tmpString2 = (string[])myList.ToArray(typeof(string));

            tmpString = tmpString2;

            i = 0;

            foreach (string linus in tmpString)

            {

                if (linus.Trim().Length > longestLine)

                {

                    longestLine = linus.Trim().Length;

                    longestLineIs = linus.Trim();

                }

                i++;

            }

            i = 0;

            

            longestLine += 2;

            foreach (string line in tmpString)

            {

                

                if (i % DesiredWidth == 0 && i>0)

                {

                    csvString.AppendLine();

                    

                }

                

                string theTerm = line.Trim();

                if (theTerm.Length > 0)

                {

                    if (allNumerics == false)

                    {

                        theTerm = string.Format("'{0}'", theTerm);

                    }

                    csvString.Append("\t\t");

                    csvString.Append(theTerm.PadRight(longestLine, ' '));

                    csvString.Append(",");

                }

               





                i++;

            }

            csvString.Remove(csvString.Length - 1, 1);



            

            csvString.AppendLine("\n\t)\n");            

            





            var e = string.Format("-- Elements : {0} / Unique : {1} / Longest Element : {2} / Which is : {3} ",new object[]{ totalNumberOfElements, uniqueNumberoOfElements, longestLine, longestLineIs });

            csvString.AppendLine(e.ToString());

            if (showStats == true)

            {

                WordStats ws = new WordStats(input,@"[^_\[\]'\b]");

                csvString.AppendLine(@"/*"+Environment.NewLine+ws.EndString+Environment.NewLine+@"*/");

                ws=null;

            }

            return csvString.ToString();

        }

        /// <summary>

        ///

        /// </summary>

        public class WhichIsLonger : IComparer

        {

            List2CSVSortBy sortOption;

            /// <summary>

            ///

            /// </summary>

            /// <param name="sortChoice">

            /// A <see cref="List2CSVSortBy"/>

            /// </param>

            public WhichIsLonger (List2CSVSortBy sortChoice)

            {

                sortOption = sortChoice;

            }

            /// <summary>

            /// 

            /// </summary>

            /// <param name="x">

            /// A <see cref="System.Object"/>

            /// </param>

            /// <param name="y">

            /// A <see cref="System.Object"/>

            /// </param>

            /// <returns>

            /// A <see cref="System.Int32"/>

            /// </returns>

            public int Compare(object x, object y)

            {

                string sX = x.ToString();

                string sY = y.ToString();

                switch (sortOption)

                {

                    case List2CSVSortBy.Name:

                        return numericCompare(sX, sY);

                    case List2CSVSortBy.Length:

                        if (sX.Length > sY.Length)

                        {

                            return 1;

                        }

                        if (sY.Length > sX.Length)

                        {

                            return -1;

                        }

                        if (sY.Length == sX.Length)

                        {

                            return 0;

                        } 

                        break;

                       

                    case List2CSVSortBy.ValueDate:

                        return dateCompare(sX, sY);

                    case List2CSVSortBy.None:

                        return 0;

                    default:

                        return 0;

                    

                }

                return 0;

                

            }



            private int dateCompare(string sX, string sY)

            {

                DateTime dX;

                DateTime dY;

                bool xPass = DateTime.TryParse(sX,out dX);

                bool yPass = DateTime.TryParse(sY, out dY);

                if (xPass == true && yPass == true)

                {

                    return  dX.CompareTo(dY);

                }

                else { return 0; }





            }



            private static int numericCompare(string sX, string sY)

            {

                int numberOfDigitsX = 0;

                int numberOfDigitsY = 0;

                for (int i = 0; i < sX.Length; i++)

                {

                    if (Char.IsNumber(sX[i]) == true) { numberOfDigitsX++; }

                    else { break; }

                }

                for (int ix = 0; ix < sY.Length; ix++)

                {

                    if (Char.IsNumber(sY[ix]) == true) { numberOfDigitsY++; }

                    else { break; }

                }



                if (numberOfDigitsY > 0 && numberOfDigitsY > 0)

                {

                    int nx = 0;

                    int ny = 0;

                    bool xPass = Int32.TryParse(sX.Substring(0, numberOfDigitsX), out nx);

                    bool yPass = Int32.TryParse(sY.Substring(0, numberOfDigitsY), out ny);

                    if (xPass == true && yPass == true)

                    {

                        if (nx > ny) { return 1; }

                        if (ny > nx) { return -1; }

                        else return 0;

                    }

                }





                return 0;

            }



        

        }





        /// <summary>

        /// Turns line separated list into c# friendly string array.

        /// </summary>

        /// <param name="input"></param>

        /// <returns></returns>

        public static string List2Array(string input)

        {

            int DesiredWidth = 5; //in columns, not characters

            var csvString = new StringBuilder();

            csvString.AppendLine("string[] something=");

            csvString.AppendLine("  \n\t\t{");

            string[] tmpString = input.Split('\n');



            int i = 0;

            var termLengths = new int[tmpString.Length];

            int LongestLine = 0;



            foreach (string linus in tmpString)

            {

                if (linus.Trim().Length > LongestLine)

                {

                    LongestLine = linus.Trim().Length;

                }

                i++;

            }

            i = 0;

            LongestLine += 2;

            foreach (string line in tmpString)

            {

                if (i%DesiredWidth == 0)

                {

                    csvString.AppendLine();



                }

                string theTerm = "\"" + line.Trim() + "\"";

                csvString.Append("\t\t\t");

                csvString.Append(theTerm.PadRight(LongestLine, ' '));

                csvString.Append(",");





                i++;

            }

            csvString.Remove(csvString.Length - 1, 1);



            csvString.AppendLine();

            csvString.Append("\t\t};");

            return csvString.ToString();

        }



        /// <summary>

        /// generates c# style code for a float[] array

        /// </summary>

        /// <param name="input"></param>

        /// <param name="dataType"></param>

        /// <returns></returns>

        public static string List2Array(string input, DataTypes dataType)

        {

            var sb = new StringBuilder();

            string[] tmpString = input.Split('\n');

            string prefix = "";

            string varName = "myFloat";

            switch (dataType)

            {

                case DataTypes.numeric:

                    sb.AppendLine("float[] " + varName + " = new float[] ");

                    sb.AppendLine("\t{");

                    sb.Append("\t\t");

                    int i = 0;

                    foreach (string line in tmpString)

                    {

                        if (i > 0)

                        {

                            sb.Append(",");

                        }

                        sb.Append(line);

                        i++;

                    }

                    sb.AppendLine("\t};");

                    prefix = "// " + i + " elements.\n";

                    break;

            }





            return prefix + sb;

        }

    }

}